KQL Search

//Summarize sign in activity from a particular country or countries to your tenant over the last week

//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(7d)
//Choose a country code, you could do multiples with | where Location in ("AU","US")
| where Location == "RU"
| extend ['Full Location'] = strcat(LocationDetails.countryOrRegion, '|', LocationDetails.state, '|', LocationDetails.city)
| where ResultType in(successCodes) or ResultType in(failureCodes)
| summarize
    ['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 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)))
    by ['Full Location'], IPAddress


//Summarize Russian sign in activity to your Azure AD tenant

//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)
//Choose a country code, you could do multiples with | where Country in ("AU","US")
| where Country == "RU"
| extend ['Full Location'] = strcat(Country, '|', State, '|', City)
| where ErrorCode in(successCodes) or ErrorCode in(failureCodes)
| summarize
    ['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 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 ['Full Location'], IPAddress