KQL Search

//Detect the first time a service principal fails Conditional Access

//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs

//Microsoft Sentinel query
//First find service principals that have previously failed
let knownfailures=
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago(30d) and TimeGenerated < ago (1d)
    | where ResultType == "53003"
    | distinct AppId;
//Find any new failures in the last day
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(1d)
| where ResultType == "53003"
| where AppId !in (knownfailures)
| project
    TimeGenerated,
    ServicePrincipalName,
    ServicePrincipalId,
    AppId,
    ConditionalAccessPolicies,
    IPAddress

//Detect the first time a service principal fails Conditional Access

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

//Advanced Hunting query
let knownfailures=
    AADSpnSignInEventsBeta
    | where Timestamp > ago(30d) and Timestamp < ago (1d)
    | where ErrorCode == "53003"
    | distinct ApplicationId;
AADSpnSignInEventsBeta
| where Timestamp > ago(1d)
| where ErrorCode == "53003"
| where ApplicationId  !in (knownfailures)
| project
    Timestamp,
    ServicePrincipalName,
    ServicePrincipalId,
    ApplicationId,
    IPAddress