KQL Search

Query Details

HomeAI ToolsDevice Query

Security Alert Find Blast Radius Infrequent Country

Query

//When Defender for Cloud Apps detects activity from an infrequent country, summarize the impact to your users

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
//Data connector required for this query - Azure Active Directory - Signin Logs

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]);
//Microsoft Sentinel query
//Using the Security Alert table find any locations detected. This query looks back 7 days to find alerts as this can be an offline detection, but you can adjust.
let suspiciouslocation=
    SecurityAlert
    | where TimeGenerated > ago(7d)
    | where AlertName == "Activity from infrequent country"
    | mv-expand todynamic(Entities)
    | project Entities
    | extend Location = tostring(parse_json(tostring(Entities.Location)).CountryCode)
    | where isnotempty(Location)
    | distinct Location;
//Take that location and send back through the sign in logs to find the blast radius
SigninLogs
| where TimeGenerated > ago(7d)
| where Location in (suspiciouslocation)
| 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 Location

//Advanced Hunting query

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

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]);
let alertid=
AlertInfo
| where Timestamp > ago(7d)
| where Title == @"Activity from infrequent country"
| distinct AlertId;
let suspiciouslocation=
AlertEvidence
| where AlertId in (alertid)
| extend AF=parse_json(AdditionalFields)
| extend Location=tostring(AF.Location.CountryCode)
| where isnotempty(Location)
| distinct Location;
AADSignInEventsBeta
| where Timestamp > ago(7d)
| where Country in (suspiciouslocation)
| 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 Country

Explanation

This query is used to summarize the impact to users when Defender for Cloud Apps detects activity from an infrequent country. It uses two data connectors - Security Alert and Azure Active Directory - Signin Logs.

First, it looks for any locations detected in the Security Alert table within the past 7 days. It filters for alerts with the name "Activity from infrequent country" and extracts the location information. Then, it sends this location information to the Signin Logs table to find the blast radius.

In the Signin Logs table, it filters for logs within the past 7 days and matches the location with the suspicious locations found earlier. It then summarizes the data by location and calculates the count of distinct successful sign-ins, the list of successful users, the successful result codes, the count of distinct failed sign-ins, the list of failed users, and the failed result codes.

The same process is repeated in the Advanced Hunting query, but it uses different data connectors - AlertInfo, AlertEvidence, and AADSignInEventsBeta - to achieve the same result.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SecurityAlertSigninLogsAlertInfoAlertEvidenceAADSignInEventsBeta

Keywords

Defender,CloudApps,Users,Dataconnector,SecurityAlert,AzureActiveDirectory,SigninLogs,Location,BlastRadius,Signins,Resultcodes,Failedsignins,Successfulsignins,AdvancedHunting,AdvancedHuntinglicense,AdvancedHuntingwithAzureADP2,AlertInfo,AlertId,AlertEvidence,AF,AADSignInEventsBeta,AccountUpn,ErrorCode,Country

Operators

letfailureCodesdynamicsuccessCodesSecurityAlertwhereTimeGeneratedagoAlertNamemv-expandtodynamicEntitiesprojectextendLocationtostringparse_jsonisnotemptydistinctSigninLogsinsummarize['Count of distinct successful sign ins']dcountifUserPrincipalNameResultTypemake_set_if['List of successful users']['Successful result codes']['Count of distinct failed sign ins']['List of failed users']['Failed result codes']byAdvanced HuntingAlertInfoTimestampTitledistinctAlertIdAlertEvidenceextendAFAADSignInEventsBetaCountryErrorCode

Actions