Identity Summarize Suspicious IP Addresses

Query

//Investigate potentially suspicious IP addresses for your Azure AD signins. This query lists any IP address with more failures than successful connections and provides a summary for each with at least one successful signin.
//This may uncover password spray attacks that have had a successful connections.

//Data connector required for this query - Azure Active Directory - Signin Logs

//Microsoft Sentinel query
let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType in(successCodes) or ResultType in(failureCodes)
| summarize
    ['Count of successful signins'] = countif((ResultType in(successCodes))),
    ['Count of distinct successful sign ins'] = dcountif(UserPrincipalName, (ResultType in(successCodes))),
    ['List of successful users']=make_set_if(UserPrincipalName, (ResultType in(successCodes))),
    ['Successful result codes'] = make_set_if(ResultType, (ResultType in(successCodes))),
    ['Count of failed signins']=countif((ResultType in(failureCodes))),
    ['Count of distinct failed sign ins'] = dcountif(UserPrincipalName, (ResultType in(failureCodes))),
    ['List of failed users'] = make_set_if(UserPrincipalName, (ResultType in(failureCodes))),
    ['Failed result codes'] = make_set_if(ResultType, (ResultType in(failureCodes))),
    ['First successful login'] = maxif(TimeGenerated, (ResultType in(successCodes)))
    by IPAddress
//Exclude IP addresses with more successful signins than failed. This is a good way to exclude known locations as they will have lots of failures too, but they will be balanced by more successes.
//Then find IP addresses with 5 or more distinct failed signins and at least one successful signin
| where ['Count of failed signins'] > ['Count of successful signins'] and ['Count of distinct failed sign ins'] > ['Count of distinct successful sign ins'] and ['Count of distinct failed sign ins'] >= 5 and ['Count of distinct successful sign ins'] >= 1
| order by ['Count of distinct successful sign ins'] desc

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

//Advanced Hunting query
let failureCodes = dynamic([50053, 50126, 50055]);
let successCodes = dynamic([0, 50055, 50057, 50155, 50105, 50133, 50005, 50076, 50079, 50173, 50158, 50072, 50074, 53003, 53000, 53001, 50129]);
AADSignInEventsBeta
| where Timestamp > ago(7d)
| where ErrorCode in(successCodes) or ErrorCode in(failureCodes)
| summarize
    ['Count of successful signins'] = countif((ErrorCode in(successCodes))),
    ['Count of distinct successful sign ins'] = dcountif(AccountUpn, (ErrorCode in(successCodes))),
    ['List of successful users']=make_set_if(AccountUpn, (ErrorCode in(successCodes))),
    ['Successful result codes'] = make_set_if(ErrorCode, (ErrorCode in(successCodes))),
    ['Count of failed signins']=countif((ErrorCode in(failureCodes))),
    ['Count of distinct failed sign ins'] = dcountif(AccountUpn, (ErrorCode in(failureCodes))),
    ['List of failed users'] = make_set_if(AccountUpn, (ErrorCode in(failureCodes))),
    ['Failed result codes'] = make_set_if(ErrorCode, (ErrorCode in(failureCodes))) 
    by IPAddress
//Exclude IP addresses with more successful signins than failed. This is a good way to exclude known locations as they will have lots of failures too, but they will be balanced by more successes.
//Then find IP addresses with 5 or more distinct failed signins and at least one successful signin
| where ['Count of failed signins'] > ['Count of successful signins'] and ['Count of distinct failed sign ins'] > ['Count of distinct successful sign ins'] and ['Count of distinct failed sign ins'] >= 5 and ['Count of distinct successful sign ins'] >= 1
| order by ['Count of distinct successful sign ins'] desc

Explanation

This query is designed to identify potentially suspicious IP addresses related to Azure Active Directory (Azure AD) sign-ins. It aims to detect IP addresses that might be involved in password spray attacks, where an attacker attempts to gain unauthorized access by trying many passwords on many accounts.

Here's a simplified breakdown of what the query does:

Define Success and Failure Codes: The query starts by defining lists of error codes that represent successful and failed sign-in attempts.
Filter Sign-in Logs: It examines sign-in logs from the past 30 days (for Microsoft Sentinel) or 7 days (for Advanced Hunting) to find entries with either successful or failed result codes.
Summarize Data by IP Address: For each IP address, the query calculates:
- The number of successful sign-ins.
- The number of distinct users who successfully signed in.
- A list of users who successfully signed in.
- The result codes for successful sign-ins.
- The number of failed sign-ins.
- The number of distinct users who had failed sign-ins.
- A list of users who had failed sign-ins.
- The result codes for failed sign-ins.
- The time of the first successful login.
Filter Suspicious IP Addresses: The query then filters out IP addresses that have more successful sign-ins than failures, focusing on those with:
- More failed sign-ins than successful ones.
- At least 5 distinct failed sign-in attempts.
- At least 1 successful sign-in.
Order Results: Finally, it orders the results by the number of distinct successful sign-ins in descending order.

This approach helps identify IP addresses that might be involved in suspicious activities, such as password spray attacks, by highlighting those with a pattern of more failures than successes but with at least one successful login.

Details

Matt Zorich

Released: November 29, 2023

Tables

SigninLogsAADSignInEventsBeta

Keywords

AzureActiveDirectorySigninLogsMicrosoftSentinelAdvancedHuntingIPAddressUserPrincipalNameAccountUpnErrorCodeTimestamp

Operators

letdynamicinagowheresummarizecountifdcountifmake_set_ifmaxifbyorder by

Actions

GitHub

KQL Search