KQL for Security Teams: A Practical Guide to Threat Hunting

Threat hunting is about actively searching for threats that detection rules miss. In Microsoft Sentinel, KQL (Kusto Query Language) is the tool that makes this possible. Master KQL, and you master Sentinel.

What is KQL?

KQL is the query language for Azure Log Analytics and Microsoft Sentinel. It resembles SQL but is optimized for time-series data and log analysis. The syntax is intuitive: start with a table, pipe data through operators, and filter down to what you're looking for.

Practical Hunting Queries

1. Failed Login Followed by Success

A classic brute force pattern — many failed attempts followed by success:

```kusto SigninLogs | where TimeGenerated > ago(24h) | summarize FailedCount = countif(ResultType != "0"), SuccessCount = countif(ResultType == "0"), FailedThenSuccess = iff( countif(ResultType != "0") > 5 and countif(ResultType == "0") > 0, true, false) by UserPrincipalName, IPAddress | where FailedThenSuccess == true | order by FailedCount desc ```

This query finds users with more than 5 failed logins followed by at least one success — from the same IP address.

2. Impossible Travel

Logins from geographically incompatible locations within a short timeframe:

```kusto SigninLogs | where ResultType == "0" | project TimeGenerated, UserPrincipalName, Location, IPAddress | sort by UserPrincipalName, TimeGenerated asc | extend PrevLocation = prev(Location), PrevTime = prev(TimeGenerated), PrevUser = prev(UserPrincipalName) | where UserPrincipalName == PrevUser | extend TimeDiffMinutes = datetime_diff('minute', TimeGenerated, PrevTime) | where TimeDiffMinutes < 60 and Location != PrevLocation and PrevLocation != "" and Location != "" ```

3. Privilege Escalation

Users suddenly receiving new roles or permissions:

```kusto AuditLogs | where TimeGenerated > ago(7d) | where OperationName has_any ( "Add member to role", "Add eligible member to role", "Add owner to application") | extend TargetUser = tostring(TargetResources[0].userPrincipalName) | extend AddedBy = tostring(InitiatedBy.user.userPrincipalName) | project TimeGenerated, OperationName, TargetUser, AddedBy ```

4. Lateral Movement via Remote Logon

Unusual use of remote administration:

```kusto SecurityEvent | where TimeGenerated > ago(24h) | where EventID == 4624 and LogonType == 3 | where AccountType == "User" | summarize TargetCount = dcount(Computer) by Account | where TargetCount > 5 | order by TargetCount desc ```

Users logging into more than 5 different machines via network logon are worth investigating.

Tips for Effective Threat Hunting

• Start with a hypothesis — — "I believe someone has compromised a service account" is better than random searching
• Use time filters — — `ago(24h)`, `ago(7d)` keep queries fast
• Combine tables — — `join` and `union` let you correlate across log sources
• Save hunting queries — — good findings become Sentinel analytics rules
• Use `let` statements — — make complex queries readable

From Hunt to Detection

When a hunting query finds something interesting, convert it to an analytics rule:

• Optimize the query to run automatically (avoid overly broad time windows)
• Define entity mapping (user, IP, host) for incident correlation
• Set severity level and response playbook
• Deploy and monitor the false positive rate

ForSec trains security teams in KQL and threat hunting, and helps build hunting programs that continuously strengthen detection capabilities. Contact us for a course tailored to your team.