KQL Search

Query Details

HomeAI ToolsDevice Query

Signin Logs Unexpected Conditional Access Authentication Without Multifactor

Query

let query_frequency = 1h;
let query_period = 14d;
let _HomeTenantId = toscalar(
    _GetWatchlist("UUID-AADTenantIds")
    | where Notes has "[HomeTenant]"
    | summarize make_list(TenantId)
);
let _ExcludedResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has_all ("[Success]", "[Interrupt]", "[Consent]")
    | summarize make_list(ResultType)
);
let _ExcludedAppIds = toscalar(
    _GetWatchlist("UUID-AADApps")
    | where Notes has "[ExcludedConditionalAccess]"
    | summarize make_list(AppId)
);
let _ExcludedADGroups = toscalar(
    _GetWatchlist("SID-AuditADObjects")
    | where Notes has "[ExcludedConditionalAccess]"
    | summarize make_list(SAMAccountName)
);
let _ExcludedUserIds = toscalar(
    IdentityInfo
    | where TimeGenerated > ago(query_period)
    | summarize arg_max(TimeGenerated, GroupMembership) by AccountObjectId
    | mv-expand GroupMembership to typeof(string)
    | where GroupMembership in (_ExcludedADGroups)
    | summarize make_list(AccountObjectId)
);
let _TrustedNamedLocations = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "TrustedNamedLocation"
    | summarize make_list(SourceResource)
);
SigninLogs
| where TimeGenerated > ago(query_frequency)
| where not(AuthenticationRequirement == "multiFactorAuthentication") and not(ConditionalAccessStatus in ("failure", "notApplied")) and ResourceTenantId in (_HomeTenantId)
| where not(ResultType in (_ExcludedResultTypes))
| where not(AppId in (_ExcludedAppIds))
| where not(UserId in (_ExcludedUserIds))
| mv-expand NetworkLocationDetail = iff(NetworkLocationDetails == "[]", dynamic([""]), todynamic(NetworkLocationDetails))
| where not(NetworkLocationDetail["networkType"] == "trustedNamedLocation" or NetworkLocationDetail["networkNames"][0] in (_TrustedNamedLocations))
| summarize
    StartTime = min(CreatedDateTime),
    EndTime = max(CreatedDateTime),
    ResultTypes = array_sort_asc(make_set(ResultType)),
    AppDisplayNames = array_sort_asc(make_set(AppDisplayName)),
    ResourceDisplayNames = array_sort_asc(make_set(ResourceDisplayName)),
    UserAgents = array_sort_asc(make_set(UserAgent)),
    AppIds = array_sort_asc(make_set(AppId)),
    CorrelationIds = array_sort_asc(make_set(CorrelationId)),
    take_any(UserPrincipalName, UserDisplayName, Location, ConditionalAccessStatus, ConditionalAccessPolicies, DeviceDetail, AuthenticationRequirement, AuthenticationRequirementPolicies,NetworkLocationDetails, UserType, Category)
    by UserId, IPAddress, ClientAppUsed, TokenIssuerType
| project
    StartTime,
    EndTime,
    Category,
    UserPrincipalName,
    UserDisplayName,
    IPAddress,
    Location,
    NetworkLocationDetails,
    ResultTypes,
    AppDisplayNames,
    ResourceDisplayNames,
    AuthenticationRequirement,
    AuthenticationRequirementPolicies,
    ConditionalAccessStatus,
    ConditionalAccessPolicies,
    DeviceDetail,
    ClientAppUsed,
    UserAgents,
    TokenIssuerType,
    UserType,
    UserId,
    AppIds,
    CorrelationIds,
    AppDisplayNameSample = tostring(AppDisplayNames[0])

Explanation

This query is designed to analyze sign-in logs over a 14-day period, with updates every hour. It filters out certain types of results, apps, users, and network locations based on predefined lists.

The query first gathers lists of tenant IDs, result types, app IDs, AD groups, user IDs, and trusted network locations from various watchlists and the IdentityInfo table. These lists are used to exclude certain data from the sign-in logs.

The query then examines the sign-in logs, excluding entries where multi-factor authentication is required, where the conditional access status is either "failure" or "not applied", or where the tenant ID, result type, app ID, user ID, or network location matches any of the previously gathered exclusion lists.

The remaining entries are then summarized by user ID, IP address, and client app used. The summary includes details such as start and end times, result types, app and resource display names, user agents, app IDs, and correlation IDs.

Finally, the query projects these details into a new table for further analysis.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: August 18, 2023

Tables

_GetWatchlistIdentityInfoSigninLogs

Keywords

QueryFrequency,QueryPeriod,HomeTenantId,ExcludedResultTypes,ExcludedAppIds,ExcludedADGroups,ExcludedUserIds,TrustedNamedLocations,SigninLogs,TimeGenerated,AuthenticationRequirement,ConditionalAccessStatus,ResourceTenantId,ResultType,AppId,UserId,NetworkLocationDetail,NetworkLocationDetails,StartTime,EndTime,ResultTypes,AppDisplayNames,ResourceDisplayNames,UserAgents,AppIds,CorrelationIds,UserPrincipalName,UserDisplayName,Location,ConditionalAccessPolicies,DeviceDetail,AuthenticationRequirementPolicies,UserType,Category,IPAddress,ClientAppUsed,TokenIssuerType,AppDisplayNameSample

Operators

lettoscalar_GetWatchlistwherehassummarizemake_listhas_allarg_maxmv-expandtypeofinifftodynamicnotarray_sort_ascmake_settake_anyprojecttostring.

Actions