KQL Search

Query Details

HomeAI ToolsDevice Query

Signin Logs Unapplied Conditional Access Authentication

Query

SigninLogs
| where ConditionalAccessStatus == "notApplied" and ConditionalAccessPolicies != "[]"
| mv-apply ConditionalAccessPolicy = ConditionalAccessPolicies on (
    extend PolicyResult = tostring(ConditionalAccessPolicy["result"])
    | summarize ConditionalAccessPoliciesResults = make_set(PolicyResult)
    )
| where not(
    TokenIssuerType == "AzureADBackupAuth"
    and ((array_length(ConditionalAccessPoliciesResults) == 1 and ConditionalAccessPoliciesResults[0] == "unknownFutureValue")
        or (array_length(ConditionalAccessPoliciesResults) == 2 and ConditionalAccessPoliciesResults has_all ("unknownFutureValue", "notEnabled")))
    and isnotempty(tostring(DeviceDetail["deviceId"]))
    and NetworkLocationDetails has "trustedNamedLocation"
    and (UserType == "Member" and AADTenantId == ResourceTenantId)
    and AuthenticationRequirement == "singleFactorAuthentication"
    )
| 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)),
    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,
    CorrelationIds

Explanation

This query is looking at sign-in logs where certain conditions are met. It first filters for logs where the Conditional Access Status is "notApplied" and Conditional Access Policies are not empty. It then applies each Conditional Access Policy to extend the Policy Result.

The query further filters out logs where the Token Issuer Type is "AzureADBackupAuth" and a series of other conditions are met, such as the Conditional Access Policies Results array length being 1 or 2 with specific values, the Device ID is not empty, the Network Location Details include "trustedNamedLocation", the User Type is "Member" and the AAD Tenant ID matches the Resource Tenant ID, and the Authentication Requirement is "singleFactorAuthentication".

The query then summarizes the data by the earliest and latest Created Date Time, sorts and sets the Result Type, App Display Name, Resource Display Name, User Agent, and Correlation ID. It also takes any value from a list of fields like User Principal Name, User Display Name, Location, etc.

Finally, it projects these fields in a specific order. This query essentially provides a detailed summary of sign-in logs based on specific filtering and sorting criteria.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: August 18, 2023

Tables

SigninLogs

Keywords

SigninLogs,ConditionalAccessStatus,ConditionalAccessPolicies,ConditionalAccessPolicy,PolicyResult,ConditionalAccessPoliciesResults,TokenIssuerType,AzureADBackupAuth,DeviceDetail,NetworkLocationDetails,UserType,AADTenantId,ResourceTenantId,AuthenticationRequirement,StartTime,CreatedDateTime,EndTime,ResultTypes,ResultType,AppDisplayNames,AppDisplayName,ResourceDisplayNames,ResourceDisplayName,UserAgents,UserAgent,CorrelationIds,CorrelationId,UserPrincipalName,UserDisplayName,Location,AuthenticationRequirementPolicies,ClientAppUsed,UserId,IPAddress

Operators

SigninLogswheremv-applyextendtostringsummarizemake_setnotarray_lengthhas_allisnotemptyminmaxarray_sort_asctake_anyproject.

Actions