5 KQL Queries Every SOC Analyst Should Have Bookmarked

Queries that find what your detection rules miss

Detection rules fire when known patterns match. These five queries find the activity that falls between your rules — the anomalies, the first-time events, and the subtle indicators that don’t trigger alerts but should make an analyst look twice.

Each query is production-ready. Copy it, run it in your Sentinel workspace or Defender XDR Advanced Hunting, and review the results. If you find something interesting, promote it to an analytics rule.

1. First-time country sign-in (last 24 hours)

An attacker using stolen credentials will almost always sign in from a country the legitimate user has never used. This query builds a 90-day baseline per user and flags any sign-in from a new country in the last 24 hours.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

let HistoricalCountries = SigninLogs
| where TimeGenerated between(ago(90d) .. ago(1d))
| where ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize Countries = make_set(Country) by UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| join kind=inner HistoricalCountries on UserPrincipalName
| where Country !in (Countries)
| project TimeGenerated, UserPrincipalName, IPAddress, Country,
    AppDisplayName, PreviousCountries = Countries

What to look for: Any result is worth investigating. Cross-reference with HR travel records or VPN usage. If the user is not travelling and the country is unexpected: you may have a credential compromise.

MITRE ATT&CK: T1078.004 — Valid Accounts: Cloud Accounts

2. MFA fatigue detection

Attackers with valid passwords spam MFA push notifications hoping the user approves one out of frustration. This query finds accounts with more than 5 MFA denials in a single hour — the signature of a fatigue attack.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SigninLogs
| where TimeGenerated > ago(14d)
| where ResultType in ("50074", "500121")
| summarize
    DenialCount = count(),
    DenialWindow = datetime_diff('minute', max(TimeGenerated), min(TimeGenerated))
    by UserPrincipalName, IPAddress, bin(TimeGenerated, 1h)
| where DenialCount > 5
| project TimeGenerated, UserPrincipalName, IPAddress,
    DenialCount, DenialWindow
| order by DenialCount desc

What to look for: High denial counts from a single IP targeting a single user. If followed by a successful sign-in from the same IP: the user approved a push. Investigate immediately.

MITRE ATT&CK: T1621 — Multi-Factor Authentication Request Generation

3. External email forwarding rules created

Inbox forwarding rules to external addresses are a key indicator of BEC. The attacker creates a rule to silently copy financial emails to their own mailbox. This query finds all forwarding rules created in the last 30 days.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CloudAppEvents
| where TimeGenerated > ago(30d)
| where ActionType in ("New-InboxRule", "Set-InboxRule")
| extend RuleData = parse_json(RawEventData)
| extend Parameters = tostring(RuleData.Parameters)
| where Parameters has_any ("ForwardTo", "ForwardAsAttachment", "RedirectTo")
| extend Creator = tostring(RuleData.UserId)
| extend ClientIP = tostring(RuleData.ClientIP)
| project TimeGenerated, Creator, ClientIP, ActionType, Parameters
| order by TimeGenerated desc

What to look for: Any external forwarding rule deserves investigation. Legitimate rules exist (users forwarding to personal email while on leave) but each one should be verified. Rules targeting financial keywords (invoice, payment, bank) are high-priority.

MITRE ATT&CK: T1114.003 — Email Collection: Email Forwarding Rule

4. Encoded PowerShell execution

Attackers encode PowerShell commands to evade basic detection. The -EncodedCommand flag and FromBase64String are strong indicators. This query finds all encoded PowerShell execution across your endpoints in the last 14 days.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

DeviceProcessEvents
| where TimeGenerated > ago(14d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine has_any ("-enc", "-EncodedCommand",
    "FromBase64String", "Invoke-Expression", "IEX")
| extend CommandLength = strlen(ProcessCommandLine)
| where CommandLength > 500
| project TimeGenerated, DeviceName, AccountName,
    Command = substring(ProcessCommandLine, 0, 200), CommandLength
| order by CommandLength desc

What to look for: Long encoded commands (500+ characters) are almost always malicious or at minimum suspicious. Short encoded commands may be legitimate automation. Decode the Base64 content to determine intent.

MITRE ATT&CK: T1059.001 — Command and Scripting Interpreter: PowerShell

5. Consent to OAuth application from non-corporate IP

OAuth consent phishing tricks users into granting permissions to attacker-controlled applications. This query finds all application consents from IPs outside your corporate network.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName == "Consent to application"
| where Result == "success"
| extend ConsentUser = tostring(InitiatedBy.user.userPrincipalName)
| extend IPAddress = tostring(InitiatedBy.user.ipAddress)
| extend AppName = tostring(TargetResources[0].displayName)
| extend Permissions = tostring(TargetResources[0].modifiedProperties)
| where IPAddress !in (_GetWatchlist('CorporateExternalIPs')
    | project SearchKey)
| project TimeGenerated, ConsentUser, AppName, IPAddress, Permissions
| order by TimeGenerated desc

What to look for: Any consent from a non-corporate IP. Check: is the application publisher-verified? When was it registered? What permissions were granted? Mail.ReadWrite and Files.ReadWrite.All are the highest-risk permissions.

Note: This query requires a CorporateExternalIPs watchlist in Sentinel. Module 10 walks through creating and maintaining watchlists.

MITRE ATT&CK: T1550.001 — Use Alternate Authentication Material: Application Access Token

Deploy these today

These five queries take 10 minutes to run against your workspace. If any return results: investigate. If you want to convert them into automated analytics rules that fire alerts: Module 10 (Create Detections and Perform Investigations) walks through the full process — entity mapping, MITRE tagging, alert grouping, and tuning.

The complete query library, plus 29 deployable detection rules, is available to Premium subscribers.

Start with the free KQL module →