KQL Search

let query_frequency = 1h;
let query_period = 3d;
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 "Anomalous Token"
| 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 (
    union
        (SigninLogs
        | where TimeGenerated > ago(query_period)
        | where OriginalRequestId in (toscalar(_Alerts | summarize make_list(OriginalRequestId))) and RiskState != "none"
        | extend
            DeviceDetail = tostring(DeviceDetail),
            TimeReceived = _TimeReceived
        ),
        (AADNonInteractiveUserSignInLogs
        | where TimeGenerated > ago(query_period)
        | where OriginalRequestId in (toscalar(_Alerts | summarize make_list(OriginalRequestId))) and RiskState != "none"
        | extend TimeReceived = _TimeReceived
        )
    | summarize arg_max(TimeReceived, *) by OriginalRequestId
    | project
        TimeGenerated,
        Type,
        UserPrincipalName,
        UserDisplayName,
        IPAddress,
        Location,
        ResultType,
        ResultDescription,
        ClientAppUsed,
        AppDisplayName,
        ResourceDisplayName,
        DeviceDetail,
        UserAgent,
        AuthenticationDetails,
        ConditionalAccessStatus,
        AuthenticationRequirement,
        AuthenticationRequirementPolicies,
        RiskEventTypes,
        RiskLevelDuringSignIn,
        RiskLevelAggregated,
        UserId,
        OriginalRequestId,
        CorrelationId
    )
    on OriginalRequestId
// | extend
//     DeviceDetail = todynamic(DeviceDetail),
//     UserName = extract(@"([^\@]+)\@", 1, UserPrincipalName)
// | extend ParsedDeviceName = extract_all(
//     @'^(?P<UserName>.+)_(?P<App>AndroidForWork|AndroidEnterprise)_(?P<Month>[0-9]+)\/(?P<Day>[0-9]+)\/(?P<Year>[0-9]+)_(?P<Hour>[0-9]+\:[0-9]+\s[APM]+)$',
//     dynamic(["UserName", "App", "Year", "Month", "Day"]),
//     tostring(DeviceDetail.displayName)
//     )[0]
// | extend
//     DeviceUserName = ParsedDeviceName[0],
//     DeviceApp = ParsedDeviceName[1],
//     DeviceRegistrationDate = make_datetime(toint(ParsedDeviceName[2]), toint(ParsedDeviceName[3]), toint(ParsedDeviceName[4]))
| 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,
        // // Remove cases where the device has been registered more than a week ago
        // DeviceDetail.isCompliant == true and DeviceDetail.isManaged == true and UserName == DeviceUserName and DeviceApp in ("AndroidEnterprise", "AndroidForWork") and DeviceRegistrationDate < (TimeGenerated - 7d), 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-away UserName, ParsedDeviceName, DeviceUserName, DeviceApp
| 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