KQL Search

Query Details

HomeAI ToolsDevice Query

Multiple Password Spray

Query

let query_frequency = 5m;
let query_period = 2d;
let disallowed_risks = dynamic(["high"]);
SecurityAlert
| where TimeGenerated > ago(query_period)
| where ProductName has "Azure Active Directory Identity Protection" and ProviderName != "ASI Scheduled Alerts" and AlertName has "Password Spray"
| extend ExtendedProperties = todynamic(ExtendedProperties)
| extend OriginalRequestId = tostring(ExtendedProperties["Request Id"])
| summarize minTimeGenerated = min(TimeGenerated), arg_max(TimeGenerated, *) by OriginalRequestId, AlertName, AlertSeverity
| where minTimeGenerated > ago(query_frequency)
| project
    Alert_TimeGenerated = TimeGenerated,
    ProductName,
    AlertName,
    Description,
    AlertSeverity,
    Status,
    Tactics,
    Techniques,
    Entities,
    ExtendedProperties,
    OriginalRequestId
| evaluate bag_unpack(ExtendedProperties, OutputColumnPrefix="Alert_", ignoredProperties=dynamic(["Alert generation status", "ProcessedBySentinel", "Request Id", "Tenant Login Source", "User Account", "User Name"]))
| as _Alerts
| lookup kind=leftouter (
    (SigninLogs
    | where TimeGenerated > ago(query_period)
    | where OriginalRequestId in (toscalar(_Alerts | summarize make_list(OriginalRequestId))) and RiskState != "none"
    | extend
        DeviceDetail = tostring(DeviceDetail),
        TimeReceived = _TimeReceived
    )
    | summarize
        arg_max(TimeReceived, *) by OriginalRequestId
    | project
        TimeGenerated,
        Type,
        UserPrincipalName,
        UserDisplayName,
        IPAddress,
        Location,
        ResultType,
        ResultDescription,
        ClientAppUsed,
        AppDisplayName,
        ResourceDisplayName,
        DeviceDetail,
        UserAgent,
        AuthenticationDetails,
        RiskEventTypes,
        RiskLevelDuringSignIn,
        RiskLevelAggregated,
        UserId,
        OriginalRequestId,
        CorrelationId
    ) on OriginalRequestId
| extend
    Alert_State = column_ifexists("Alert_State", ""),
    ["Alert_Detection Subcategory"] = column_ifexists("Alert_Detection Subcategory", "")
| extend
    BenignAlert = case(
        // Remove cases where Identity Protection considers the alert solved
        Status == "Resolved" or Alert_State == "Closed", true,
        false
    ),
    // If a user is put at high risk, the alert severity should be High and the incident name should have the string "User at risk"
    AlertSeverity = case(
        RiskLevelAggregated in (disallowed_risks) or RiskLevelDuringSignIn in (disallowed_risks), "High",
        AlertSeverity
    ),
    IncidentName = case(
        RiskLevelAggregated in (disallowed_risks), strcat(AlertName, " - User at risk"),
        AlertName
    )
// Remove benign cases where alert severity is not High
// | where not(BenignAlert and not(AlertSeverity in ("High")))
| project-reorder
    TimeGenerated,
    ProductName,
    AlertName,
    Description,
    Alert_*,
    Type,
    UserPrincipalName,
    UserDisplayName,
    IPAddress,
    Location,
    ResultType,
    ResultDescription,
    ClientAppUsed,
    AppDisplayName,
    ResourceDisplayName,
    DeviceDetail,
    UserAgent,
    AuthenticationDetails,
    AlertSeverity,
    RiskEventTypes,
    RiskLevelDuringSignIn,
    RiskLevelAggregated,
    Entities,
    UserId,
    OriginalRequestId,
    CorrelationId

Explanation

This query is designed to analyze security alerts from Azure Active Directory Identity Protection over a period of two days. It specifically looks for alerts related to "Password Spray" attacks, excluding those generated by "ASI Scheduled Alerts".

The query then extracts additional details from the alerts, such as the original request ID and the earliest time the alert was generated. It only considers alerts that were generated within the last five minutes.

Next, the query unpacks additional properties of the alert and joins this data with sign-in logs that match the original request ID and have a risk state other than "none".

The query then adds additional columns for the alert state and detection subcategory. It also determines whether the alert is benign (i.e., the issue has been resolved or the alert has been closed) and adjusts the alert severity and incident name if a user is at high risk.

Finally, the query reorders the columns and presents the data, excluding benign cases where the alert severity is not high.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: May 4, 2023

Tables

SecurityAlertSigninLogs

Keywords

SecurityAlert,TimeGenerated,ProductName,AzureActiveDirectoryIdentityProtection,ProviderName,ASIScheduledAlerts,AlertName,PasswordSpray,ExtendedProperties,RequestId,AlertSeverity,Status,Tactics,Techniques,Entities,AlertGenerationStatus,ProcessedBySentinel,TenantLoginSource,UserAccount,UserName,SigninLogs,DeviceDetail,TimeReceived,Type,UserPrincipalName,UserDisplayName,IPAddress,Location,ResultType,ResultDescription,ClientAppUsed,AppDisplayName,ResourceDisplayName,UserAgent,AuthenticationDetails,RiskEventTypes,RiskLevelDuringSignIn,RiskLevelAggregated,UserId,CorrelationId,AlertState,AlertDetectionSubcategory,BenignAlert,IncidentName

Operators

letdynamicSecurityAlertwherehas!=extendtodynamictostringsummarizeminarg_maxbyprojectevaluatebag_unpackaslookupkind=leftouterintoscalarmake_listcolumn_ifexistscasestrcatproject-reorder.

Actions