KQL & Log Analytics | Ben Thomson

// What is Log Analytics?

Azure Log Analytics is the data platform that underpins Microsoft Sentinel. All logs ingested into Sentinel � sign-ins, security events, network flows, endpoint telemetry � land in a Log Analytics workspace where they can be queried using KQL (Kusto Query Language).

As a SOC analyst, KQL is one of the most valuable skills you can have in a Microsoft environment. It lets you hunt for threats, investigate alerts, build detection rules, and create dashboards � all from the same query interface.

KQL is also used in Microsoft Defender XDR for Advanced Hunting, and in Azure Data Explorer. The same queries largely work across all three.

// KQL Fundamentals

KQL uses a pipe | syntax � you start with a table and chain operators together, each feeding into the next.

Operator	Description	Example
`where`	Filter rows by condition	`where EventID == 4625`
`project`	Select specific columns	`project TimeGenerated, UserName, IPAddress`
`summarize`	Aggregate data	`summarize count() by UserName`
`sort by`	Order results	`sort by TimeGenerated desc`
`limit`	Limit number of rows	`limit 100`
`extend`	Add a calculated column	`extend Hour = hourofday(TimeGenerated)`
`join`	Join two tables	`join kind=inner TableB on UserName`
`distinct`	Return unique values	`distinct UserPrincipalName`
`ago()`	Relative time filter	`where TimeGenerated > ago(24h)`
`has_any()`	Match any value in list	`where CommandLine has_any("mimikatz","dump")`
`contains`	Case-insensitive substring	`where FileName contains "powershell"`
`=~`	Case-insensitive equals	`where FileName =~ "cmd.exe"`

Always start queries with a time filter (where TimeGenerated > ago(24h)) to avoid scanning the entire dataset and hitting query limits.

// Key Tables in Sentinel

Knowing which table holds which data is half the battle. These are the most commonly used tables in a Microsoft Sentinel environment.

Table	Source	Contains
`SigninLogs`	Microsoft Entra ID	User sign-in events including MFA, location, result
`AuditLogs`	Microsoft Entra ID	Directory changes � user creation, role assignments
`SecurityEvent`	Windows (MMA/AMA)	Windows Security event log (4625, 4624, 4688, etc.)
`DeviceProcessEvents`	MDE	Process creation events on enrolled endpoints
`DeviceNetworkEvents`	MDE	Network connections made by endpoints
`DeviceLogonEvents`	MDE	Logon/logoff events on endpoints
`DeviceFileEvents`	MDE	File creation, modification, deletion events
`DeviceRegistryEvents`	MDE	Registry key changes on endpoints
`OfficeActivity`	Microsoft 365	SharePoint, Exchange, Teams, OneDrive activity
`AzureActivity`	Azure	Azure resource changes, deployments, role assignments
`SecurityAlert`	Sentinel / Defender	Alerts generated by Defender products
`CommonSecurityLog`	CEF connectors	Firewall, proxy, and network device logs
`Syslog`	Linux agents	Linux system logs

// Authentication Queries

Authentication logs are some of the most valuable for detecting account compromise, brute force, and suspicious access patterns.

Failed sign-in attempts

                        // Top users with failed sign-ins in the last 24 hours

                        SigninLogs

                        | where TimeGenerated > ago(24h)

                        | where ResultType != 0

                        | summarize FailedAttempts = count() by UserPrincipalName, IPAddress, Location

                        | where FailedAttempts > 5

                        | sort by FailedAttempts desc

Sign-ins from multiple countries

                        // Users signing in from more than one country � possible account sharing or compromise

                        SigninLogs

                        | where TimeGenerated > ago(7d)

                        | where ResultType == 0

                        | summarize Countries = dcount(Location), CountryList = make_set(Location) by UserPrincipalName

                        | where Countries > 1

                        | sort by Countries desc

Successful login after multiple failures (potential brute force)

                        // Accounts with 10+ failures followed by a success � classic brute force indicator

                        let failures = SigninLogs

                            | where TimeGenerated > ago(1h)

                            | where ResultType != 0

                            | summarize FailCount = count() by UserPrincipalName, IPAddress

                            | where FailCount > 10;

                        SigninLogs

                        | where TimeGenerated > ago(1h)

                        | where ResultType == 0

                        | join kind=inner failures on UserPrincipalName

                        | project TimeGenerated, UserPrincipalName, IPAddress, Location, FailCount

MFA failures

                        // MFA denials � could indicate MFA fatigue attack or legitimate user confusion

                        SigninLogs

                        | where TimeGenerated > ago(24h)

                        | where ResultType == 500121

                        | summarize MFAFailures = count() by UserPrincipalName, IPAddress

                        | where MFAFailures > 3

                        | sort by MFAFailures desc

ResultType	Meaning
`0`	Successful sign-in
`50126`	Invalid username or password
`50074`	MFA required
`500121`	MFA denied by user
`53003`	Blocked by Conditional Access
`50053`	Account locked out

// Endpoint Queries (MDE)

Microsoft Defender for Endpoint tables give you deep visibility into what's happening on endpoints � process execution, file changes, network connections, and registry modifications.

Suspicious PowerShell execution

                        // PowerShell with encoded commands or download cradles

                        DeviceProcessEvents

                        | where TimeGenerated > ago(24h)

                        | where FileName =~ "powershell.exe"

                        | where ProcessCommandLine has_any ("-enc", "-encodedcommand", "bypass", "hidden", "downloadstring", "iex", "invoke-expression")

                        | project TimeGenerated, DeviceName, AccountName, ProcessCommandLine

                        | sort by TimeGenerated desc

New local administrator added

                        // Event 4732 � member added to local Administrators group

                        SecurityEvent

                        | where TimeGenerated > ago(7d)

                        | where EventID == 4732

                        | where TargetSid == "S-1-5-32-544"

                        | project TimeGenerated, Computer, SubjectUserName, MemberName

Scheduled task creation

                        // Event 4698 � scheduled task created, common persistence mechanism

                        SecurityEvent

                        | where TimeGenerated > ago(24h)

                        | where EventID == 4698

                        | project TimeGenerated, Computer, SubjectUserName, TaskName, TaskContent

Process execution by a specific user

                        // Investigate all processes run by a specific account

                        DeviceProcessEvents

                        | where TimeGenerated > ago(24h)

                        | where AccountName =~ "username"

                        | project TimeGenerated, DeviceName, FileName, ProcessCommandLine, InitiatingProcessFileName

                        | sort by TimeGenerated desc

Event ID	Description	Why it matters
`4624`	Successful logon	Track user access
`4625`	Failed logon	Brute force detection
`4688`	Process created	Command execution tracking
`4698`	Scheduled task created	Persistence detection
`4720`	User account created	Unauthorised account creation
`4732`	Member added to local group	Privilege escalation
`4776`	NTLM authentication attempt	Lateral movement via pass-the-hash
`7045`	New service installed	Persistence / malware installation

// Network Queries

Network visibility is key for detecting C2 communication, lateral movement, and data exfiltration.

Large outbound data transfers

                        // Endpoints sending large amounts of data outbound � potential exfiltration

                        DeviceNetworkEvents

                        | where TimeGenerated > ago(24h)

                        | where RemoteIPType == "Public"

                        | summarize TotalBytes = sum(SentBytes) by DeviceName, RemoteIP, RemotePort

                        | where TotalBytes > 100000000 // 100MB

                        | sort by TotalBytes desc

Connections to rare external IPs

                        // External IPs contacted by only one device � unusual and worth investigating

                        DeviceNetworkEvents

                        | where TimeGenerated > ago(7d)

                        | where RemoteIPType == "Public"

                        | summarize DeviceCount = dcount(DeviceName), Devices = make_set(DeviceName) by RemoteIP

                        | where DeviceCount == 1

                        | sort by DeviceCount asc

Non-standard ports for common protocols

                        // HTTP/HTTPS traffic on non-standard ports � possible C2 or tunnelling

                        DeviceNetworkEvents

                        | where TimeGenerated > ago(24h)

                        | where RemoteIPType == "Public"

                        | where RemotePort !in (80, 443, 53, 22, 25, 465, 587)

                        | summarize count() by DeviceName, RemoteIP, RemotePort

                        | sort by count_ desc

// Threat Hunting

These queries go beyond alerting � they're designed for proactive hunting across your environment.

Living off the land binaries (LOLBins)

                        // Legitimate Windows binaries abused to download or execute code

                        DeviceProcessEvents

                        | where TimeGenerated > ago(24h)

                        | where FileName has_any ("certutil.exe", "bitsadmin.exe", "mshta.exe", "wscript.exe", "cscript.exe", "regsvr32.exe", "rundll32.exe")

                        | where ProcessCommandLine has_any ("http", "ftp", "download", "urlcache")

                        | project TimeGenerated, DeviceName, AccountName, FileName, ProcessCommandLine

Lateral movement via PsExec

                        // PsExec usage � common in both legitimate admin activity and attacker lateral movement

                        DeviceProcessEvents

                        | where TimeGenerated > ago(7d)

                        | where FileName =~ "psexec.exe" or FileName =~ "psexesvc.exe"

                        | project TimeGenerated, DeviceName, AccountName, ProcessCommandLine, InitiatingProcessFileName

Credential dumping indicators

                        // Common tools and techniques used to dump credentials from memory

                        DeviceProcessEvents

                        | where TimeGenerated > ago(24h)

                        | where ProcessCommandLine has_any ("mimikatz", "sekurlsa", "lsadump", "hashdump", "procdump")

                        | project TimeGenerated, DeviceName, AccountName, FileName, ProcessCommandLine

Anomalous sign-in hours

                        // Users signing in outside of normal business hours (before 7am or after 8pm)

                        SigninLogs

                        | where TimeGenerated > ago(7d)

                        | where ResultType == 0

                        | extend Hour = hourofday(TimeGenerated)

                        | where Hour < 7 or Hour > 20

                        | summarize SignIns = count() by UserPrincipalName, Hour, IPAddress

                        | sort by SignIns desc

// Tips & Best Practices

Always time-bound your queries. Start with ago(24h) and expand if needed. Querying without a time filter can be extremely slow or hit data caps.

Use project to reduce columns early. The fewer columns you carry through the pipeline, the faster the query runs.

has vs contains: Both are case-insensitive by default. The difference is boundaries � has matches on full word tokens (faster, index-backed), while contains matches any substring. Use has for whole terms (has "powershell") and contains when you need a mid-string match. For case-sensitive matching use has_cs or contains_cs.

Save your queries. In Sentinel you can save queries to the query library for reuse. Build a personal library of queries for your most common investigation tasks.

Context is everything. A LOLBin usage or off-hours login isn't automatically malicious � always investigate the full picture before escalating.