KQL Search

// ---- Network Allowlist (exclude trusted IPs / CIDR / ranges) --------------
let _allow = materialize(union isfuzzy=true (print R="" | take 0), (_GetWatchlist('NetworkAllowlist') | project R = tostring(IPOrRange)) | where isnotempty(R));
let _allowCIDR  = toscalar(_allow | where not(R matches regex @'^\d+\.\d+\.\d+\.\d+-\d+\.\d+\.\d+\.\d+$') | extend R = iff(R has '/', R, strcat(R, '/32')) | summarize make_list(R));
let _allowRange = toscalar(_allow | where R matches regex @'^\d+\.\d+\.\d+\.\d+-\d+\.\d+\.\d+\.\d+$' | summarize make_list(R));
let _ExcludeAllowlistedIPs = (T:(IPAddress:string)) {
    T
    | extend IPAddress = tostring(IPAddress)
    | where array_length(_allowCIDR) == 0 or isnull(ipv4_is_in_any_range(IPAddress, _allowCIDR)) or not(ipv4_is_in_any_range(IPAddress, _allowCIDR))
    | mv-apply _r = _allowRange to typeof(string) on (
        extend _lo = tostring(split(_r,'-')[0]), _hi = tostring(split(_r,'-')[1])
        | extend _inRange = ipv4_compare(IPAddress, _lo) >= 0 and ipv4_compare(IPAddress, _hi) <= 0
        | summarize _anyInRange = max(toint(_inRange)))
    | where isnull(_anyInRange) or _anyInRange == 0
    | project-away _anyInRange
};
// ---------------------------------------------------------------------------
let failureThreshold = 10;
let legacyProtocols  = dynamic([
    "IMAP", "POP", "SMTP", "Authenticated SMTP",
    "Exchange ActiveSync", "Other clients"]);
SigninLogs
| invoke _ExcludeAllowlistedIPs()
| where TimeGenerated > ago(1h)
| where ClientAppUsed in (legacyProtocols)
| where ResultType != "0"
| summarize
    FailedAttempts = count(),
    UniqueUsers    = dcount(UserPrincipalName),
    TargetedUsers  = make_set(UserPrincipalName, 50),
    Protocols      = make_set(ClientAppUsed),
    StartTime      = min(TimeGenerated),
    EndTime        = max(TimeGenerated),
    Locations      = make_set(Location, 5)
  by IPAddress
| where FailedAttempts >= failureThreshold
| project
    TimeGenerated = StartTime,
    IPAddress, FailedAttempts, UniqueUsers,
    TargetedUsers, Protocols, Locations, EndTime