KQL Authentication Queries

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != "0"
| summarize
    FailCount = count(),
    DistinctIPs = dcount(IPAddress),
    LastAttempt = max(TimeGenerated)
    by UserPrincipalName, ResultDescription
| sort by FailCount desc
| take 20

let threshold = 10;
let timeWindow = 1h;
SigninLogs
| where TimeGenerated > ago(timeWindow)
| where ResultType == "50126"  // Invalid username or password
| summarize
    Attempts = count(),
    DistinctIPs = dcount(IPAddress),
    IPs = make_set(IPAddress),
    FirstAttempt = min(TimeGenerated),
    LastAttempt = max(TimeGenerated)
    by UserPrincipalName
| where Attempts > threshold
| sort by Attempts desc

let timeWindow = 1h;
let userThreshold = 5;
SigninLogs
| where TimeGenerated > ago(timeWindow)
| where ResultType == "50126"
| summarize
    TargetUsers = dcount(UserPrincipalName),
    Users = make_set(UserPrincipalName),
    Attempts = count()
    by IPAddress
| where TargetUsers > userThreshold
| sort by TargetUsers desc

// MFA failures
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "50074"  // MFA required
   or ResultType == "50076"    // MFA denied
| summarize count() by UserPrincipalName, ResultDescription
| sort by count_ desc

// Users without MFA
SigninLogs
| where TimeGenerated > ago(7d)
| where AuthenticationRequirement == "singleFactorAuthentication"
| summarize count() by UserPrincipalName, AppDisplayName
| sort by count_ desc

SigninLogs
| where TimeGenerated > ago(24h)
| where ConditionalAccessStatus == "failure"
| summarize count() by
    UserPrincipalName,
    AppDisplayName,
    ConditionalAccessPolicies
| sort by count_ desc

let timeThreshold = 60m;
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "0"
| project TimeGenerated, UserPrincipalName, IPAddress, Location
| sort by UserPrincipalName, TimeGenerated asc
| extend PrevLocation = prev(Location), PrevTime = prev(TimeGenerated), PrevUser = prev(UserPrincipalName)
| where UserPrincipalName == PrevUser
| where Location != PrevLocation
| where datetime_diff('minute', TimeGenerated, PrevTime) < 60
| project TimeGenerated, UserPrincipalName, IPAddress, Location, PrevLocation, TimeDiff = datetime_diff('minute', TimeGenerated, PrevTime)

let knownLocations = dynamic(["US", "United States"]);
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| where Location !in (knownLocations)
| summarize count() by UserPrincipalName, Location, IPAddress
| sort by count_ desc

SigninLogs
| where TimeGenerated > ago(24h)
| where UserPrincipalName startswith "svc-"
   or UserPrincipalName startswith "app-"
| summarize
    Logins = count(),
    UniqueIPs = dcount(IPAddress),
    Apps = make_set(AppDisplayName)
    by UserPrincipalName
| sort by Logins desc