SIEM Query Repository

Failed Sign-ins via KQL

Tracks multiple failed authentication events across your environments.

SigninLogs
| where ResultType == "50126"
| summarize Count=count() by UserPrincipalName, IPAddress
| where Count > 5

Windows Process Creation (Event ID 4688)

Used for tracking process execution and command-line activity on Windows endpoints when Defender MDE logs are unavailable.

SecurityEvent
| where TimeGenerated >= ago(24h) and EventID == 4688
| where CommandLine has_any ("-enc", "-EncodedCommand", "-e")
| project TimeGenerated, Computer, Account, CommandLine

• EventID == 4688: Specifically filters for Windows Process Creation events.
• has_any (...): Efficiently searches for multiple string variants (e.g., encoded PowerShell flags) in a single line.
• project: Trims the dataset early to display only critical triage fields.

Failed Logon Aggregations (Event ID 4625)

Identifies potential brute-force targets by counting failed Windows login attempts per user account.

SecurityEvent
| where TimeGenerated >= ago(7d) and EventID == 4625
| summarize FailedCount = count() by Account
| top 5 by FailedCount desc

• EventID == 4625: Windows event code for failed logon attempts.
• summarize count() by Account: Aggregates total occurrences grouped by user identity.
• top 5 by ... desc: Isolates the highest-volume accounts directly.

Correlating Endpoint File Downloads with Identity Sign-ins

Correlates file downloads in user directories with successful cloud sign-ins within the same timeframe.

DeviceFileEvents
| where TimeGenerated >= ago(1d)
| where FolderPath has "Downloads" or FolderPath has "Temp"
| where isnotempty(InitiatingProcessAccountUpn)
| join kind=inner (
    SigninLogs
    | where TimeGenerated >= ago(1d)
    | where ResultType == 0
) on $left.InitiatingProcessAccountUpn == $right.UserPrincipalName
| project TimeGenerated, DeviceName, FileName, UserPrincipalName, IPAddress, Location

• Key Matching ($left / $right): Connects two distinct schemas by matching InitiatingProcessAccountUpn to UserPrincipalName.
• Pre-Filtering: Filtering both tables for time (ago(1d)) before joining prevents query timeouts.
• isnotempty(): Filters out system processes that lack associated user accounts.

Detection of Password Spray Attacks

Detects IP addresses attempting logins across multiple unique user accounts with failures (ResultType 50126/50053) that eventually yield at least one successful login (ResultType 0).

let Lookback = 2h;
let FailedThreshold = 10;
// Step 1: Identify IPs targeting multiple unique accounts
let SuspiciousIPs = SigninLogs
    | where TimeGenerated >= ago(Lookback)
    | where ResultType in (50126, 50053)
    | summarize UniqueUsers = dcount(UserPrincipalName) by IPAddress
    | where UniqueUsers >= FailedThreshold
    | project IPAddress;
// Step 2: Match those IPs against successful logins
SigninLogs
| where TimeGenerated >= ago(Lookback) and ResultType == 0
| where IPAddress in (SuspiciousIPs)
| summarize SuccessfulUsers = make_set(UserPrincipalName), TotalSuccesses = count() by IPAddress

• dcount(): Calculates distinct user counts per IP to identify spray patterns versus single-account brute-forcing.
• let variables: Stores intermediate subquery results in memory to optimize multi-stage detection logic.
• make_set(): Combines all compromised usernames into a single readable array per suspicious IP.

Time-Series Anomaly Detection

Uses built-in machine learning functions to detect sudden spikes in process executions against a 14-day baseline.

let StartTime = ago(14d);
let EndTime = now();
let Step = 1h;
DeviceProcessEvents
| where TimeGenerated between (StartTime .. EndTime)
| make-series ProcessCount = count() default=0 on TimeGenerated from StartTime to EndTime step Step by FileName
| extend (Anomalies, Score, Baseline) = series_decompose_anomalies(ProcessCount, 2.5)
| mv-expand TimeGenerated to typeof(datetime), ProcessCount to typeof(long), Anomalies to typeof(long), Baseline to typeof(long)
| where Anomalies == 1
| project TimeGenerated, FileName, ProcessCount, Baseline, Anomalies

• make-series ... from ... to: Builds an unbroken vector of time intervals required by KQL analysis functions.
• series_decompose_anomalies(): Evaluates statistical deviations (1 flags a positive anomaly spike).
• mv-expand: Unpacks multi-value time arrays back into rows for display.

Identify Active Log Tables

Lists all tables actively ingesting logs in your environment alongside total row counts.

union withsource=TableName *
| where TimeGenerated >= ago(24h)
| summarize Count=count() by TableName
| order by Count desc

• union *: Scans across all available tables simultaneously.
• withsource=TableName: Creates a dynamic column identifying which table supplied each row.

Inspect Table Schema & Column Names

Reveals all column names and data types for a given table without executing large scans.

DeviceProcessEvents
| getschema

• getschema: Returns metadata (Column Name, Column Type) instantly without consuming query quota.

Locate Tables Containing Specific Keywords

Finds which security log tables store a specific term (e.g., "powershell").

search in (SecurityEvent, DeviceProcessEvents, CommonSecurityLog, SigninLogs) "powershell"
| where TimeGenerated >= ago(2h)
| summarize Count=count() by $table

• search in (...): Targets string searches to candidate tables.
• $table: A system variable that groups hit counts by table origin.

Brute Force Detection via Splunk (SPL) - Event ID 4625

Monitors Windows Security Event ID 4625 for potential target attacks.

index=security sourcetype="WinEventLog:Security" EventCode=4625
| stats count by user, src_ip
| where count > 10