Multiple Unfamiliar Sign In Properties

KQL detection rule by ep3p · MIT
No description provided
let query_frequency = 5m;
let query_period = 2d;
let office_365_id = "72782ba9-4490-4f03-8d82-562370ea3566";
let office_home_id = "4765445b-32c6-49b0-83e6-1d93765276ca";
let legacy_client_apps = dynamic(["Authenticated SMTP", "AutoDiscover", "Exchange ActiveSync", "Exchange Online PowerShell", "Exchange Web Services", "IMAP4", "MAPI Over HTTP", "Outlook Anywhere (RPC over HTTP)", "Outlook Service", "POP3", "Reporting Web Services", "Other clients"]);
let legacy_auth_protocols = dynamic(["ropc"]);
let legacy_user_agents = dynamic(["BAV2ROPC", "CBAinPROD", "CBAinTAR", "MSRPC"]);
let suspicious_user_agents = dynamic(["fasthttp", "node-fetch", "python-requests"]);
let _ExpectedASNs = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "CommonUserASN"
    | summarize make_list(Auxiliar)
);
let _CorporateGeolocations = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "CorporateGeolocation"
    | summarize make_list(Auxiliar)
);
let _ShellApps = toscalar(
    _GetWatchlist("UUID-EntraIdApps")
    | where Notes has "[PowerShell]"
    | summarize make_list(AppId)
);
let _ExpectedIPAddresses = toscalar(
    _GetWatchlist("IP-CorporateCollaborators")
    | summarize make_list(IPAddress)
);
AADUserRiskEvents
| where TimeGenerated > ago(query_period)
| where Source == "IdentityProtection" and RiskEventType == "unfamiliarFeatures"
| summarize FirstTimeGenerated = min(TimeGenerated), arg_max(TimeGenerated, *) by Id
| where FirstTimeGenerated > ago(query_frequency)
| project
    //TimeGenerated,
    ActivityDateTime,
    DetectedDateTime,
    Source,
    Activity,
    DetectionTimingType,
    UserDisplayName,
    UserPrincipalName,
    UserId,
    IpAddress,
    RequestId,
    CorrelationId,
    TokenIssuerType,
    RiskEventType,
    RiskDetail,
    RiskLevel,
    RiskState,
    AdditionalInfo,
    Id
| mv-apply Auxiliar = AdditionalInfo on (
    summarize AdditionalInfoBag = make_bag(bag_pack(tostring(Auxiliar["Key"]), Auxiliar["Value"]))
    )
| extend
    AltAlertLink = strcat("https://entra.microsoft.com/#blade/Microsoft_AAD_IAM/RiskDetectionsBlade/riskState~/[]/userId/", UserId, "/riskLevel/[]/daysBack/90"),// Someone wrote "90s" incorrectly in Defender XDR portal
    AltDescription = iff(tostring(AdditionalInfoBag["riskReasons"][0]) == "NotSet",
            "Sign-in with properties we have not seen recently for the given user",
            strcat(
                "The following properties of this sign-in are unfamiliar for the given user: ",
                strcat_array(todynamic(replace_string(tostring(AdditionalInfoBag["riskReasons"]), "Unfamiliar", "")), ", "))
            ),
    AltAlertSeverity = strcat(toupper(substring(RiskLevel, 0, 1)), substring(RiskLevel, 1)),
    AltTactics = "InitialAccess",
    AltTechniques = replace_regex(tostring(split(tostring(AdditionalInfoBag["mitreTechniques"]), ",")), @'(\")(T\d+)(\.\d+)(\")', @"\1\2\4"),
    AltSubTechniques = replace_regex(tostring(split(tostring(AdditionalInfoBag["mitreTechniques"]), ",")), @'(\,)?(\"T\d+\")', @"")
| join kind=leftouter (
    SecurityAlert
    | where ProviderName == "IPC" and ProductName == "Azure Active Directory Identity Protection" and AlertType == "UnfamiliarLocation"
    | summarize arg_max(TimeGenerated, *) by VendorOriginalId
    | project
        AlertName,
        AlertSeverity,
        Description,
        AlertStatus = Status,
        Entities,
        ExtendedProperties,
        VendorName,
        ProviderName,
        ProductName,
        ProductComponentName,
        RemediationSteps,
        Tactics,
        Techniques,
        SubTechniques,
        VendorOriginalId,
        SystemAlertId,
        CompromisedEntity,
        AlertLink
    ) on $left.Id == $right.VendorOriginalId
| extend
    AlertLink = coalesce(AlertLink, AltAlertLink),
    Description = coalesce(Description, AltDescription),
    AlertSeverity = coalesce(AlertSeverity, AltAlertSeverity),
    Tactics = coalesce(Tactics, AltTactics),
    Techniques = coalesce(Techniques, iff(array_length(todynamic(AltTechniques)) == 0, "", AltTechniques)),
    SubTechniques = coalesce(SubTechniques, iff(array_length(todynamic(AltSubTechniques)) == 0, "", AltSubTechniques))
| as _Events
| lookup kind=leftouter (
    SigninLogs
    | where TimeGenerated > ago(query_period)
    //| where RiskEventTypes_V2 has "unfamiliarFeatures"
    | where OriginalRequestId in (toscalar(_Events | summarize make_list(RequestId)))
    | extend TimeReceived = _TimeReceived
    | summarize
        Location = take_any(Location),
        AutonomousSystemNumber = take_any(AutonomousSystemNumber),
        arg_max(TimeReceived, *)
        by OriginalRequestId
    | project
        TimeGenerated,
        CreatedDateTime,
        Type,
        //UserDisplayName,
        //UserPrincipalName,
        //UserId,
        AlternateSignInName,
        SignInIdentifier,
        UserType,
        IPAddress,
        AutonomousSystemNumber,
        Location,
        NetworkLocationDetails,
        ResultType,
        ResultSignature,
        ResultDescription,
        ClientAppUsed,
        AppDisplayName,
        ResourceDisplayName,
        DeviceDetail,
        UserAgent,
        Status,
        MfaDetail,
        AuthenticationContextClassReferences,
        AuthenticationDetails,
        AuthenticationProcessingDetails,
        AuthenticationProtocol,
        AuthenticationRequirement,
        AuthenticationRequirementPolicies,
        SessionLifetimePolicies,
        //TokenIssuerType,
        IncomingTokenType,
        TokenProtectionStatusDetails,
        ConditionalAccessStatus,
        ConditionalAccessPolicies,
        SignInLogs_RiskDetail = RiskDetail,
        RiskEventTypes_V2,
        RiskLevelAggregated,
        RiskLevelDuringSignIn,
        SignInLogs_RiskState = RiskState,
        HomeTenantId,
        ResourceTenantId,
        CrossTenantAccessType,
        AppId,
        AppOwnerTenantId,
        ResourceIdentity,
        ResourceOwnerTenantId,
        UniqueTokenIdentifier,
        SessionId,
        OriginalRequestId//,
        //CorrelationId
    ) on $left.RequestId == $right.OriginalRequestId
| mv-apply Auxiliar = AdditionalInfo on (
    summarize AdditionalInfoBag = make_bag(bag_pack(tostring(Auxiliar["Key"]), Auxiliar["Value"]))
    )
| extend
    RiskReasons = AdditionalInfoBag["riskReasons"],
    ParsedUserAgent = parse_user_agent(UserAgent, dynamic(["os", "browser"]))
| extend
    // DeviceId = tostring(DeviceDetail["deviceId"]),
    // DeviceName = tostring(DeviceDetail["displayName"]),
    // DeviceIsCompliant = tostring(DeviceDetail["isCompliant"]),
    // DeviceIsManaged = tostring(DeviceDetail["isManaged"]),
    // DeviceTrustType = tostring(DeviceDetail["trustType"]),
    DeviceDetailOS = tostring(DeviceDetail["operatingSystem"]),
    UserAgentOS = tostring(ParsedUserAgent["OperatingSystem"]["Family"]),
    Browser = tostring(ParsedUserAgent["Browser"]["Family"])
| extend
    OperatingSystem = case(
        isempty(DeviceDetailOS), UserAgentOS,
        isempty(UserAgent), extract(@"^([A-Za-z]+)", 1, DeviceDetailOS),
        UserAgentOS == "Other", extract(@"^([A-Za-z]+)", 1, DeviceDetailOS),
        UserAgentOS
    ),
    Browser = case(
        Browser == "Other", iff(UserAgent != "-", extract(@"^([^\/\s]+)", 1, UserAgent), ""),
        Browser
    )
| extend
    OperatingSystem = case(
        OperatingSystem has "ios", "iOS",
        OperatingSystem has_any ("mac", "macos"), "macOS",
        OperatingSystem == "Ubuntu", "Linux",
        Browser == "Samsung Internet", "Android",
        Browser == "MacOutlook", "macOS",
        OperatingSystem
    )
| where case(
    //AlertStatus == "Resolved" and tostring(todynamic(ExtendedProperties)["State"]) == "Closed", false,
    //RiskState == "dismissed" and RiskDetail == "aiConfirmedSigninSafe", false,
    RiskState == "remediated" and RiskDetail == "userChangedPasswordOnPremises", false,
    RiskState == "remediated" and RiskDetail == "userPassedMFADrivenByRiskBasedPolicy", false,
    SignInLogs_RiskState == "remediated" and SignInLogs_RiskDetail == "userPassedMFADrivenByRiskBasedPolicy", false,
    AutonomousSystemNumber in (_ExpectedASNs) and Location in (_CorporateGeolocations)
        and not(UserAgent has_any (array_concat(legacy_user_agents, suspicious_user_agents)) or AuthenticationProtocol has_any (legacy_auth_protocols) or ClientAppUsed has_any (legacy_client_apps))
        and not((isempty(ResourceIdentity) and AppId == office_365_id) or ResourceIdentity == office_home_id or AppId in (_ShellApps))
        and RiskLevel in ("low", "medium")
        and OperatingSystem in ("iOS", "Android", "Windows", "macOS"), false,
    Location in (_CorporateGeolocations) and HomeTenantId == ResourceTenantId and HomeTenantId == AppOwnerTenantId
        and not(UserAgent has_any (array_concat(legacy_user_agents, suspicious_user_agents)) or AuthenticationProtocol has_any (legacy_auth_protocols) or ClientAppUsed has_any (legacy_client_apps))
        and not((isempty(ResourceIdentity) and AppId == office_365_id) or ResourceIdentity == office_home_id or AppId in (_ShellApps))
        and RiskLevel in ("low", "medium")
        and OperatingSystem in ("iOS", "Android", "Windows", "macOS"), false,
    (isnotempty(parse_ipv4(IPAddress)) and ipv4_is_in_any_range(IPAddress, _ExpectedIPAddresses)) or (isempty(parse_ipv4(IPAddress)) and isnotempty(parse_ipv6(IPAddress)) and ipv6_is_in_any_range(IPAddress, _ExpectedIPAddresses))
        and not(UserAgent has_any (array_concat(legacy_user_agents, suspicious_user_agents)) or AuthenticationProtocol has_any (legacy_auth_protocols) or ClientAppUsed has_any (legacy_client_apps))
        and not((isempty(ResourceIdentity) and AppId == office_365_id) or ResourceIdentity == office_home_id or AppId in (_ShellApps))
        and RiskLevel in ("low", "medium")
        and OperatingSystem in ("iOS", "Android", "Windows", "macOS"), false,
    true
    )
| project
    //TimeGenerated,
    ActivityDateTime,
    DetectedDateTime,
    Source,
    Activity,
    DetectionTimingType,
    UserDisplayName,
    UserPrincipalName,
    UserId,
    IpAddress,
    RequestId,
    CorrelationId,
    TokenIssuerType,
    RiskEventType,
    RiskDetail,
    RiskLevel,
    RiskState,
    RiskReasons,
    AdditionalInfo,
    Id,
    AlertName,
    AlertSeverity,
    Description,
    AlertStatus,
    Entities,
    ExtendedProperties,
    VendorName,
    ProviderName,
    ProductName,
    ProductComponentName,
    RemediationSteps,
    Tactics,
    Techniques,
    SubTechniques,
    VendorOriginalId,
    SystemAlertId,
    CompromisedEntity,
    AlertLink,
    TimeGenerated,
    CreatedDateTime,
    Type,
    //UserDisplayName,
    //UserPrincipalName,
    //UserId,
    AlternateSignInName,
    SignInIdentifier,
    UserType,
    IPAddress,
    AutonomousSystemNumber,
    Location,
    NetworkLocationDetails,
    ResultType,
    ResultSignature,
    ResultDescription,
    ClientAppUsed,
    AppDisplayName,
    ResourceDisplayName,
    DeviceDetail,
    UserAgent,
    Status,
    MfaDetail,
    AuthenticationContextClassReferences,
    AuthenticationDetails,
    AuthenticationProcessingDetails,
    AuthenticationProtocol,
    AuthenticationRequirement,
    AuthenticationRequirementPolicies,
    SessionLifetimePolicies,
    //TokenIssuerType,
    IncomingTokenType,
    TokenProtectionStatusDetails,
    ConditionalAccessStatus,
    ConditionalAccessPolicies,
    SignInLogs_RiskDetail,
    RiskEventTypes_V2,
    RiskLevelAggregated,
    RiskLevelDuringSignIn,
    SignInLogs_RiskState,
    HomeTenantId,
    ResourceTenantId,
    CrossTenantAccessType,
    AppId,
    AppOwnerTenantId,
    ResourceIdentity,
    ResourceOwnerTenantId,
    UniqueTokenIdentifier,
    SessionId//,
    //OriginalRequestId,
    //CorrelationId
[[ currentRule.title && currentRule.title.length > 60 ? currentRule.title.slice(0, 60) + '…' : currentRule.title ]]

1a86ccaa-5512-4a0d-8b25-bd28e798f502
Propose edits, view related rules, and engage with the community through comments.
Executable code, not a declarative rule
This submission is arbitrary [[ currentRule.format ? currentRule.format.toUpperCase() : '' ]] code, not a declarative detection rule — review it as you would any script before running it, and note that it cannot be safely aggregated or auto-executed alongside YARA/Sigma/Suricata/CRS rules.
Rejected pattern present
[[ reason ]]
What does this mean?
Format & documentation suggestions
Not a security issue — this rule is valid and was accepted as-is. These are optional fields/conventions its own format recommends, mainly affecting its quality score and how well it documents itself for other reviewers.
[[ reason ]]
What do these messages mean?
[[ currentRule.title && currentRule.title.length > 60 ? currentRule.title.slice(0, 60) + '…' : currentRule.title ]]

[[ currentRule.title ]]

Community Discussion
