SreejithReji/kql-soc-queries
GitHub: SreejithReji/kql-soc-queries
Stars: 0 | Forks: 0
# KQL SOC Query Library 🔍
A comprehensive KQL (Kusto Query Language) reference for SOC analysts — from first query to advanced threat hunting.
Used in **Microsoft Sentinel**, **Microsoft Defender XDR**, **Log Analytics**, and **Microsoft 365 Defender**.
Built by a SOC analyst, for SOC analysts.
## What is KQL?
KQL is the query language used across Microsoft's security stack. If your SOC runs Sentinel or Defender, you will write KQL every single day — for alert triage, threat hunting, incident investigation, and building detection rules.
## Repository Structure
kql-soc-queries/
│
├── 01_foundations/
│ ├── 01_basic_syntax.kql
│ ├── 02_filtering_where.kql
│ ├── 03_selecting_fields.kql
│ ├── 04_sorting_limiting.kql
│ └── 05_time_ranges.kql
│
├── 02_authentication/
│ ├── 01_failed_logons.kql
│ ├── 02_brute_force_detection.kql
│ ├── 03_successful_after_failures.kql
│ ├── 04_account_lockouts.kql
│ └── 05_impossible_travel.kql
│
├── 03_network/
│ ├── 01_suspicious_ports.kql
│ ├── 02_c2_beaconing.kql
│ ├── 03_dns_tunnelling.kql
│ ├── 04_large_data_transfers.kql
│ └── 05_port_scanning.kql
│
├── 04_endpoint/
│ ├── 01_suspicious_processes.kql
│ ├── 02_powershell_execution.kql
│ ├── 03_persistence_mechanisms.kql
│ ├── 04_lateral_movement.kql
│ └── 05_credential_dumping.kql
│
├── 05_threat_hunting/
│ ├── 01_lolbins_detection.kql
│ ├── 02_ransomware_indicators.kql
│ ├── 03_data_exfiltration.kql
│ ├── 04_cobalt_strike_indicators.kql
│ └── 05_mitre_attack_mapping.kql
│
├── 06_incident_response/
│ ├── 01_user_activity_timeline.kql
│ ├── 02_ip_investigation.kql
│ ├── 03_host_investigation.kql
│ ├── 04_alert_summary.kql
│ └── 05_ioc_search.kql
│
└── 07_dashboards/
├── 01_soc_daily_overview.kql
├── 02_top_alerts_by_severity.kql
└── 03_shift_handover_summary.kql
## Quick Reference — KQL Syntax Cheatsheet
### Basic structure
TableName
| where Condition
| project Field1, Field2, Field3
| sort by Field desc
| take 100
### Common operators
| Operator | What it does | Example |
|---|---|---|
| `where` | Filter rows | `where EventID == 4625` |
| `project` | Select columns | `project TimeGenerated, Account` |
| `extend` | Add new column | `extend Hour = hourofday(TimeGenerated)` |
| `summarize` | Aggregate | `summarize count() by Account` |
| `sort by` | Sort results | `sort by TimeGenerated desc` |
| `take` / `limit` | Limit rows | `take 100` |
| `distinct` | Unique values | `distinct SrcIpAddr` |
| `join` | Join tables | `join kind=inner OtherTable on AccountName` |
| `union` | Combine tables | `union Table1, Table2` |
| `render` | Visualise | `render timechart` |
### Time filters
// Last 24 hours
| where TimeGenerated > ago(24h)
// Last 7 days
| where TimeGenerated > ago(7d)
// Specific range
| where TimeGenerated between (datetime(2024-01-15) .. datetime(2024-01-16))
### String operators
| where AccountName contains "admin"
| where AccountName startswith "svc_"
| where AccountName endswith "_test"
| where AccountName matches regex @"^[a-z]{3}\d{4}$"
| where CommandLine has_any ("mimikatz", "invoke-mimikatz", "sekurlsa")
## Tables You Will Use Most
| Table | Contains | SIEM |
|---|---|---|
| `SecurityEvent` | Windows Security Event Logs | Sentinel |
| `SigninLogs` | Azure AD sign-in logs | Sentinel |
| `AuditLogs` | Azure AD audit events | Sentinel |
| `SecurityAlert` | All security alerts | Sentinel |
| `SecurityIncident` | Incidents | Sentinel |
| `CommonSecurityLog` | CEF format logs (firewalls, IDS) | Sentinel |
| `DnsEvents` | DNS queries | Sentinel |
| `NetworkCommunicationEvents` | Network connections | Defender XDR |
| `DeviceProcessEvents` | Process execution | Defender XDR |
| `DeviceLogonEvents` | Device logon events | Defender XDR |
| `DeviceNetworkEvents` | Network events on device | Defender XDR |
| `EmailEvents` | Email logs | Defender XDR |
| `IdentityLogonEvents` | Identity logon events | Defender XDR |
## The Queries
### 01 — Foundations
#### Basic syntax
// Your first KQL query — get the last 10 security events
SecurityEvent
| take 10
// Count all events in the last 24 hours
SecurityEvent
| where TimeGenerated > ago(24h)
| count
// See all unique Event IDs
SecurityEvent
| where TimeGenerated > ago(24h)
| distinct EventID
| sort by EventID asc
#### Filtering with where
// Filter by a single value
SecurityEvent
| where EventID == 4625
// Filter by multiple values
SecurityEvent
| where EventID in (4624, 4625, 4648, 4672, 4740)
// Filter by text
SecurityEvent
| where AccountName contains "admin"
// Combine conditions with and / or
SecurityEvent
| where EventID == 4625
and AccountName contains "admin"
and TimeGenerated > ago(24h)
#### Selecting fields with project
// Only show the columns you care about
SecurityEvent
| where EventID == 4625
| project TimeGenerated, Account, IpAddress, WorkstationName, LogonTypeName
| sort by TimeGenerated desc
#### Summarise and aggregate
// Count events grouped by EventID
SecurityEvent
| where TimeGenerated > ago(24h)
| summarize EventCount = count() by EventID
| sort by EventCount desc
// Count failed logons per account
SecurityEvent
| where EventID == 4625
| where TimeGenerated > ago(24h)
| summarize FailedAttempts = count() by Account
| sort by FailedAttempts desc
### 02 — Authentication Queries
#### Failed logon detection (Event 4625)
// All failed logons in last 24 hours
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| project TimeGenerated, Account, IpAddress, WorkstationName, LogonTypeName, SubStatus
| sort by TimeGenerated desc
#### Brute force detection — threshold based
// Accounts with more than 10 failed logons in 1 hour — brute force indicator
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| summarize FailedAttempts = count() by Account, IpAddress
| where FailedAttempts > 10
| sort by FailedAttempts desc
| extend Severity = case(
FailedAttempts > 50, "Critical",
FailedAttempts > 20, "High",
FailedAttempts > 10, "Medium",
"Low")
#### Successful logon after multiple failures — possible breach
// Find accounts that failed many times then succeeded — classic brute force success
let FailedLogons = SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| summarize FailCount = count() by Account, IpAddress
| where FailCount > 5;
let SuccessLogons = SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| project Account, IpAddress, SuccessTime = TimeGenerated;
SuccessLogons
| join kind=inner FailedLogons on Account
| project Account, IpAddress, SuccessTime, FailCount
| sort by FailCount desc
#### Account lockouts (Event 4740)
// All account lockouts — who got locked out and from where
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4740
| project TimeGenerated, TargetAccount, Computer, SubjectUserName
| sort by TimeGenerated desc
#### Privileged account logon outside business hours
// Admin accounts logging in outside 07:00–19:00
SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4624
| where AccountType == "User"
| extend Hour = hourofday(TimeGenerated)
| where Hour !between (7 .. 19)
| where Account contains_any ("admin", "administrator", "svc_", "root")
| project TimeGenerated, Account, IpAddress, WorkstationName, Hour
| sort by TimeGenerated desc
### 03 — Network Queries
#### Connections to suspicious ports
// Traffic to commonly abused ports
let SuspiciousPorts = dynamic([22, 23, 445, 3389, 4444, 8080, 9001, 1337, 31337]);
CommonSecurityLog
| where TimeGenerated > ago(24h)
| where DestinationPort in (SuspiciousPorts)
| where DeviceAction == "deny"
| project TimeGenerated, SourceIP, DestinationIP, DestinationPort, Protocol, DeviceAction
| sort by TimeGenerated desc
#### C2 beaconing detection — regular interval connections
// Find hosts making connections at suspiciously regular intervals — C2 beacon pattern
NetworkCommunicationEvents
| where TimeGenerated > ago(24h)
| summarize
ConnectionCount = count(),
FirstSeen = min(TimeGenerated),
LastSeen = max(TimeGenerated)
by DeviceName, RemoteIP, RemotePort
| extend Duration = datetime_diff('minute', LastSeen, FirstSeen)
| extend BeaconRate = ConnectionCount / (Duration + 1)
| where ConnectionCount > 10
and BeaconRate > 1
| sort by BeaconRate desc
#### DNS tunnelling detection
// Long subdomain queries — classic DNS tunnelling indicator
DnsEvents
| where TimeGenerated > ago(24h)
| extend SubdomainLength = strlen(Name)
| where SubdomainLength > 50
| summarize
QueryCount = count(),
UniqueDomains = dcount(Name)
by Computer, ClientIP
| where QueryCount > 5
| sort by QueryCount desc
#### Suspicious outbound data transfers
// Large outbound transfers to external IPs — possible exfiltration
CommonSecurityLog
| where TimeGenerated > ago(24h)
| where SentBytes > 10000000
| where not(DestinationIP matches regex @"^(10\.|172\.(1[6-9]|2[0-9]|3[01])\.|192\.168\.)")
| project TimeGenerated, SourceIP, DestinationIP, SentBytes, ReceivedBytes, Protocol
| sort by SentBytes desc
#### Port scanning detection
// Single IP connecting to many different ports — port scan indicator
NetworkCommunicationEvents
| where TimeGenerated > ago(1h)
| summarize
UniquePortsScanned = dcount(RemotePort),
PortList = make_set(RemotePort)
by DeviceName, RemoteIP
| where UniquePortsScanned > 20
| sort by UniquePortsScanned desc
### 04 — Endpoint Queries
#### Suspicious process execution
// Processes commonly used by attackers — LOLBins and hacking tools
let SuspiciousProcesses = dynamic([
"mimikatz.exe", "procdump.exe", "psexec.exe", "wce.exe",
"fgdump.exe", "pwdump.exe", "gsecdump.exe", "meterpreter",
"cobaltrike", "cobaltstrike", "empire.exe", "covenant"
]);
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName has_any (SuspiciousProcesses)
or ProcessCommandLine has_any (SuspiciousProcesses)
| project TimeGenerated, DeviceName, AccountName, FileName, ProcessCommandLine, InitiatingProcessFileName
| sort by TimeGenerated desc
#### Suspicious PowerShell execution
// PowerShell with encoded commands or download cradles — classic attacker technique
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName =~ "powershell.exe" or FileName =~ "pwsh.exe"
| where ProcessCommandLine has_any (
"-enc", "-encodedcommand", "-nop", "-noprofile",
"invoke-expression", "iex", "downloadstring",
"invoke-webrequest", "wget", "curl",
"bypass", "hidden", "windowstyle hidden"
)
| project TimeGenerated, DeviceName, AccountName, ProcessCommandLine
| sort by TimeGenerated desc
#### Persistence mechanisms
// New scheduled tasks created — common attacker persistence technique
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4698
| project TimeGenerated, Computer, Account, TaskName, TaskContent
| sort by TimeGenerated desc
// New services installed — another common persistence method
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 7045
| project TimeGenerated, Computer, Account, ServiceName, ServiceFileName, ServiceType
| sort by TimeGenerated desc
#### Lateral movement — pass the hash and remote execution
// Remote logons using network credentials — lateral movement pattern
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4624
| where LogonType == 3
| where AccountType == "User"
| where not(WorkstationName == Computer)
| summarize
RemoteHosts = dcount(Computer),
HostList = make_set(Computer)
by Account, IpAddress
| where RemoteHosts > 3
| sort by RemoteHosts desc
#### Credential dumping indicators
// LSASS memory access — classic credential dumping technique (Mimikatz etc)
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where InitiatingProcessFileName !in~ ("MsMpEng.exe", "svchost.exe", "lsass.exe")
| where ProcessCommandLine has "lsass"
or FileName =~ "lsass.exe"
| project TimeGenerated, DeviceName, AccountName, FileName,
ProcessCommandLine, InitiatingProcessFileName
| sort by TimeGenerated desc
### 05 — Threat Hunting Queries
#### Living off the Land (LOLBins) detection
// Built-in Windows tools used maliciously by attackers
let LOLBins = dynamic([
"certutil.exe","bitsadmin.exe","regsvr32.exe","mshta.exe",
"wscript.exe","cscript.exe","rundll32.exe","msiexec.exe",
"installutil.exe","regasm.exe","regsvcs.exe","msconfig.exe",
"at.exe","schtasks.exe","wmic.exe","net.exe","netsh.exe"
]);
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName has_any (LOLBins)
| where InitiatingProcessFileName !in~ ("explorer.exe","services.exe","svchost.exe")
| project TimeGenerated, DeviceName, AccountName,
FileName, ProcessCommandLine, InitiatingProcessFileName
| sort by TimeGenerated desc
#### Ransomware indicators
// Mass file modification or deletion — ransomware behaviour
DeviceFileEvents
| where TimeGenerated > ago(1h)
| where ActionType in ("FileModified","FileDeleted","FileRenamed")
| summarize
FileChanges = count(),
UniqueExtensions = dcount(FileExtension)
by DeviceName, InitiatingProcessFileName, bin(TimeGenerated, 5m)
| where FileChanges > 100
| sort by FileChanges desc
#### Cobalt Strike beacon indicators
// Network connections on common Cobalt Strike ports with beacon-like intervals
NetworkCommunicationEvents
| where TimeGenerated > ago(24h)
| where RemotePort in (80, 443, 8080, 8443)
| summarize
ConnectionCount = count(),
BytesSent = sum(SentBytes),
BytesReceived = sum(ReceivedBytes)
by DeviceName, RemoteIP, RemotePort
| where ConnectionCount > 20
and BytesSent between (1000 .. 50000)
| sort by ConnectionCount desc
#### Data exfiltration hunting
// Unusual volume of data leaving a specific host
DeviceNetworkEvents
| where TimeGenerated > ago(24h)
| where RemoteIPType == "Public"
| summarize
TotalBytesSent = sum(SentBytes),
UniqueDestinations = dcount(RemoteIP)
by DeviceName, bin(TimeGenerated, 1h)
| where TotalBytesSent > 100000000
| sort by TotalBytesSent desc
| extend TotalMB = round(TotalBytesSent / 1048576, 2)
#### MITRE ATT&CK — T1059 Command and Scripting Interpreter
// All scripting interpreter activity mapped to MITRE T1059
let ScriptingInterpreters = dynamic([
"powershell.exe","cmd.exe","wscript.exe","cscript.exe",
"mshta.exe","bash.exe","python.exe","python3.exe","perl.exe"
]);
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName has_any (ScriptingInterpreters)
| extend MITRE_Technique = "T1059 - Command and Scripting Interpreter"
| extend MITRE_Tactic = "Execution"
| project TimeGenerated, DeviceName, AccountName,
FileName, ProcessCommandLine,
MITRE_Technique, MITRE_Tactic
| sort by TimeGenerated desc
### 06 — Incident Response Queries
#### Full user activity timeline
// Complete timeline of everything a user did — essential for IR
let TargetUser = "administrator"; // change this
let StartTime = ago(24h);
let EndTime = now();
union
(SecurityEvent
| where TimeGenerated between (StartTime .. EndTime)
| where Account contains TargetUser
| project TimeGenerated, Category="Auth Event",
Details=strcat("EventID:", tostring(EventID), " | ", Activity),
Computer, IpAddress),
(SigninLogs
| where TimeGenerated between (StartTime .. EndTime)
| where UserPrincipalName contains TargetUser
| project TimeGenerated, Category="Azure AD Signin",
Details=strcat("App:", AppDisplayName, " | Status:", ResultDescription),
Computer=DeviceDetail, IpAddress=IPAddress),
(DeviceProcessEvents
| where TimeGenerated between (StartTime .. EndTime)
| where AccountName contains TargetUser
| project TimeGenerated, Category="Process",
Details=ProcessCommandLine,
Computer=DeviceName, IpAddress="")
| sort by TimeGenerated asc
#### IP address investigation
// Everything associated with a specific IP — for alert triage
let TargetIP = "185.220.101.45"; // change this
union
(SecurityEvent
| where IpAddress == TargetIP
| where TimeGenerated > ago(24h)
| project TimeGenerated, Source="SecurityEvent",
Details=strcat("EventID:", tostring(EventID), " Account:", Account)),
(CommonSecurityLog
| where SourceIP == TargetIP
| where TimeGenerated > ago(24h)
| project TimeGenerated, Source="Firewall",
Details=strcat("Action:", DeviceAction, " Port:", tostring(DestinationPort))),
(DnsEvents
| where ClientIP == TargetIP
| where TimeGenerated > ago(24h)
| project TimeGenerated, Source="DNS",
Details=strcat("Query:", Name))
| sort by TimeGenerated desc
#### Host investigation
// Full picture of a specific host — for incident scoping
let TargetHost = "DESKTOP-HR-042"; // change this
union
(DeviceProcessEvents
| where DeviceName == TargetHost
| where TimeGenerated > ago(24h)
| project TimeGenerated, Category="Process", Details=ProcessCommandLine),
(DeviceNetworkEvents
| where DeviceName == TargetHost
| where TimeGenerated > ago(24h)
| project TimeGenerated, Category="Network",
Details=strcat(RemoteIP, ":", tostring(RemotePort))),
(DeviceLogonEvents
| where DeviceName == TargetHost
| where TimeGenerated > ago(24h)
| project TimeGenerated, Category="Logon",
Details=strcat(AccountName, " - ", ActionType))
| sort by TimeGenerated desc
#### IOC search across all tables
// Search for a hash, IP, or domain across all relevant tables at once
let IOC = "d41d8cd98f00b204e9800998ecf8427e"; // replace with your IOC
union
(DeviceFileEvents | where SHA256 == IOC or MD5 == IOC
| project TimeGenerated, Source="FileEvent", DeviceName, Details=FileName),
(DeviceProcessEvents | where SHA256 == IOC or MD5 == IOC
| project TimeGenerated, Source="ProcessEvent", DeviceName, Details=ProcessCommandLine),
(CommonSecurityLog | where SourceIP == IOC or DestinationIP == IOC
| project TimeGenerated, Source="Firewall", DeviceName=DeviceName, Details=strcat(SourceIP,"->",DestinationIP))
| sort by TimeGenerated desc
### 07 — Dashboard Queries
#### SOC daily overview
// High level summary for start of shift
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID in (4624, 4625, 4740, 4720, 4728, 7045, 4698)
| summarize Count = count() by EventID
| extend EventDescription = case(
EventID == 4624, "Successful Logons",
EventID == 4625, "Failed Logons",
EventID == 4740, "Account Lockouts",
EventID == 4720, "Accounts Created",
EventID == 4728, "Added to Admin Group",
EventID == 7045, "Services Installed",
EventID == 4698, "Scheduled Tasks Created",
"Other")
| project EventDescription, EventID, Count
| sort by Count desc
#### Top alerts by severity — shift overview
// Alert count by severity for current shift
SecurityAlert
| where TimeGenerated > ago(8h)
| summarize AlertCount = count() by AlertSeverity, AlertName
| sort by case(
AlertSeverity == "High", 1,
AlertSeverity == "Medium", 2,
AlertSeverity == "Low", 3, 4) asc,
AlertCount desc
#### Shift handover summary
// Everything that happened in the last 8 hours — for handover report
let ShiftStart = ago(8h);
let FailedLogons = toscalar(SecurityEvent
| where TimeGenerated > ShiftStart
| where EventID == 4625 | count);
let Lockouts = toscalar(SecurityEvent
| where TimeGenerated > ShiftStart
| where EventID == 4740 | count);
let NewAccounts = toscalar(SecurityEvent
| where TimeGenerated > ShiftStart
| where EventID == 4720 | count);
let HighAlerts = toscalar(SecurityAlert
| where TimeGenerated > ShiftStart
| where AlertSeverity == "High" | count);
print
ShiftPeriod = strcat(format_datetime(ShiftStart, 'HH:mm'), " - ", format_datetime(now(), 'HH:mm')),
FailedLogons = FailedLogons,
AccountLockouts = Lockouts,
NewAccountsCreated = NewAccounts,
HighSeverityAlerts = HighAlerts
## Key Event IDs Reference
| EventID | Description | Why it matters |
|---|---|---|
| 4624 | Successful logon | Baseline — look for unusual times or sources |
| 4625 | Failed logon | Brute force indicator |
| 4634 | Logoff | Session duration analysis |
| 4648 | Logon with explicit credentials | Lateral movement indicator |
| 4672 | Special privileges assigned | Privilege escalation |
| 4688 | Process created | Malware execution |
| 4698 | Scheduled task created | Persistence mechanism |
| 4720 | User account created | Attacker backdoor |
| 4728 | Added to security group | Privilege escalation |
| 4740 | Account locked out | Brute force threshold exceeded |
| 7045 | Service installed | Malware persistence |
| 4776 | Credential validation | Pass-the-hash indicator |
| 4771 | Kerberos pre-auth failed | Kerberoasting indicator |
## Learning Resources
- 📖 [Microsoft KQL Documentation](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/)
- 🎓 [Microsoft Learn — KQL for Sentinel](https://learn.microsoft.com/en-us/training/paths/sc-200-utilize-kql-for-azure-sentinel/)
- 🧪 [KQL Playground](https://dataexplorer.azure.com/clusters/help/databases/Samples)
- 🎯 [TryHackMe — Microsoft Sentinel Room](https://tryhackme.com)
- 📚 [MITRE ATT&CK Framework](https://attack.mitre.org)
## About
Built as part of a SOC analyst learning portfolio.
**Author:** Sreejith Reji | MSc Cyber Security | CEH | Security+
Part of the [cybersecurity-portfolio](https://github.com/SreejithReji/cybersecurity-portfolio) collection.