KQL Search

Query Details

HomeAI ToolsDevice Query

Signin Logs Legacy Protocols Used In Azure AD Authentication

Query

let legacy_auth_protocols = 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_user_agents = dynamic(["BAV2ROPC", "CBAinPROD", "CBAinTAR", "MSRPC"]);
let _UncompromisedResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has "[Failure]" and not(Notes has "[Expired]") and isnotempty(ResultDescription)
    | summarize make_list(ResultType)
);
let _ExpectedLocations = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "CorporateGeolocation"
    | summarize make_list(Auxiliar)
);
let _ExpectedIPRanges = toscalar(
    union
        (_GetWatchlist("IP-Vendors")
        | where Vendor == "Microsoft"
        ),
        (_GetWatchlist("IP-CorporateCollaborators")
        | where Notes has "[Egress]")
    | summarize make_list(IPAddress)
);
let _CorrelationIds = toscalar(
    SigninLogs
    | where ClientAppUsed in (legacy_auth_protocols) or UserAgent in (legacy_user_agents)
    | where not(AuthenticationDetails has "Incorrect password" or ResultType in (_UncompromisedResultTypes))
    | where not(isnotempty(parse_ipv4(IPAddress)) and ipv4_is_in_any_range(IPAddress, _ExpectedIPRanges))
    | summarize make_list(CorrelationId)
);
SigninLogs
| where CorrelationId in (_CorrelationIds) and not(RiskState in ("dismissed", "remediated"))
| summarize
    arg_min(TimeGenerated, Type, UserPrincipalName, UserDisplayName, Location, ResultDescription, DeviceDetail),
    ClientAppUsed = make_set(ClientAppUsed),
    UserAgent = make_set(UserAgent),
    AppDisplayName = make_set(AppDisplayName),
    ResourceDisplayName = make_set(ResourceDisplayName),
    AuthenticationDetails = make_set_if(AuthenticationDetails, not(AuthenticationDetails == "[]")),
    RiskState = make_set_if(RiskState, not(RiskState == "none")),
    RiskEventTypes = make_set_if(todynamic(RiskEventTypes), not(RiskEventTypes == "[]")),
    RiskLevelDuringSignIn = make_set_if(RiskLevelDuringSignIn, not(RiskLevelDuringSignIn == "none")),
    RiskLevelAggregated = make_set_if(RiskLevelAggregated, not(RiskLevelAggregated == "none")),
    OriginalRequestId = make_set(OriginalRequestId),
    CorrelationId = make_set(CorrelationId)
    by UserId, IPAddress, ResultType
| extend AlertSeverity = case(
    not(Location in (_ExpectedLocations)), "High",
    not(ResultType == "53003"), "High", //Blocked by Conditional Access
    Location in (_ExpectedLocations), "Low",
    "High"
    )
| project
    TimeGenerated,
    UserPrincipalName,
    UserDisplayName,
    IPAddress,
    Location,
    ResultType,
    ResultDescription,
    ClientAppUsed,
    UserAgent,
    AppDisplayName,
    ResourceDisplayName,
    DeviceDetail,
    AuthenticationDetails,
    RiskState,
    RiskEventTypes,
    RiskLevelDuringSignIn,
    RiskLevelAggregated,
    UserId,
    OriginalRequestId,
    CorrelationId,
    Type,
    AlertSeverity

Explanation

This query is designed to analyze sign-in logs for potential security risks. It first defines a list of legacy authentication protocols and user agents, as well as a list of result types that indicate a sign-in failure that is not due to an expired password. It also defines a list of expected locations and IP ranges based on corporate geolocation and Microsoft vendors.

The query then identifies correlation IDs from the sign-in logs where the client app used or user agent matches the defined legacy lists, and where the authentication details do not indicate an incorrect password or a result type from the uncompromised list. It also excludes sign-ins from expected IP ranges.

Using these correlation IDs, the query pulls detailed sign-in data, excluding any dismissed or remediated risks. It summarizes this data by user ID, IP address, and result type, creating sets of various data points such as client app used, user agent, app display name, and risk state.

Finally, the query assigns an alert severity based on whether the location is expected and whether the result type indicates a conditional access block. It then projects all the summarized data along with the alert severity.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: August 22, 2023

Tables

SigninLogs

Keywords

SigninLogs,LegacyAuthProtocols,LegacyUserAgents,UncompromisedResultTypes,ExpectedLocations,ExpectedIPRanges,CorrelationIds,TimeGenerated,UserPrincipalName,UserDisplayName,Location,ResultDescription,ClientAppUsed,UserAgent,AppDisplayName,ResourceDisplayName,DeviceDetail,AuthenticationDetails,RiskState,RiskEventTypes,RiskLevelDuringSignIn,RiskLevelAggregated,UserId,OriginalRequestId,AlertSeverity

Operators

letdynamictoscalar_GetWatchlistwherehasnotisnotemptysummarizemake_listunioninparse_ipv4ipv4_is_in_any_rangearg_minmake_setmake_set_iftodynamicextendcaseproject

Actions