Listen to this Post
Introduction:
In the era of big data and sophisticated cyber threats, the ability to rapidly query and analyze vast datasets is a critical superpower for security professionals. Kusto Query Language (KQL), the engine behind Azure Data Explorer and Azure Monitor, is rapidly becoming an indispensable tool for threat hunting, log analysis, and cloud security hardening. This guide will transform you from a KQL novice into a practitioner capable of wielding this powerful language to proactively defend your digital assets.
Learning Objectives:
- Master the fundamental KQL operators and functions essential for parsing security logs.
- Learn to construct advanced hunting queries to detect anomalous activity and potential breaches.
- Implement KQL for automated monitoring and hardening of your Azure cloud environment.
You Should Know:
- KQL Fundamentals: Your First Query Against a Threat Log
To effectively hunt for threats, you must first speak the language of your data. KQL is a read-only query language designed for large, complex datasets like those generated by security information and event management (SIEM) systems. Let’s start by querying a simulated sign-in log to identify failed login attempts, a common precursor to a brute-force attack.
// Basic structure to query the SigninLogs table SigninLogs | where TimeGenerated >= ago(24h) | where ResultType != "0" // 0 indicates success | project TimeGenerated, UserPrincipalName, IPAddress, ResultType, ResultDescription | top 10 by TimeGenerated desc
Step-by-step guide:
SigninLogs: This specifies the table you are querying, which contains Azure AD sign-in events.| where TimeGenerated >= ago(24h): This pipe filters the results to only show records from the last 24 hours.| where ResultType != "0": This filter excludes successful logins, leaving only failures and other results.| project TimeGenerated, UserPrincipalName, IPAddress, ResultType, ResultDescription: The `project` operator selects specific columns to display, making the output clean and focused.| top 10 by TimeGenerated desc: This returns the 10 most recent records from the filtered set.
- Advanced Threat Hunting: Correlating Events for Beaconing Detection
Advanced attackers often use beaconing, where a compromised host calls back to a command-and-control (C2) server at regular intervals. Detecting this requires correlating network events over time. We can use KQL’s `make-series` and `series_decompose_anomalies` operators to automatically flag this behavior.
// Detect potential beaconing using network logs let timeframe = 1d; let binSize = 5m; VMConnection | where TimeGenerated >= ago(timeframe) | where Direction == "outbound" | make-series Count = count() default=0 on TimeGenerated from ago(timeframe) to now() step binSize by Computer, RemoteIP | extend Anomalies = series_decompose_anomalies(Count, 1.5, 'linefit') | where array_length(Anomalies) > 0 | mv-expand Anomalies | where Anomalies [bash] == 1 | project Computer, RemoteIP, Anomalies
Step-by-step guide:
VMConnection: Queries the table containing virtual machine connection data.make-series ... step binSize: This powerful function aggregates the connection counts into a time series, binning the data into 5-minute intervals for each Computer and RemoteIP pair.series_decompose_anomalies(Count, 1.5, 'linefit'): This performs advanced time-series analysis, identifying points that are significantly different (anomalies) from the expected trend. The `1.5` is a sensitivity threshold.mv-expand: Expands the dynamic array of anomaly results into rows for easier filtering.- The final `where` clause filters to show only the positively flagged anomalies.
3. Cloud Hardening: Identifying Over-Privileged Service Principals
In cloud environments, over-permissioned identities are a primary attack vector. This query scans Azure Activity logs to find service principals (non-human identities) that have been granted high-privilege roles, such as `Owner` or User Access Administrator.
// Find high-privilege role assignments to Service Principals
AzureActivity
| where OperationNameValue == "Microsoft.Authorization/roleAssignments/write"
| where Properties @has "requestbody"
| extend RoleDefinitionId = tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties.roleDefinitionId)
| extend PrincipalId = tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties.principalId)
| where RoleDefinitionId has_any ("8e3af657", "18d7d88d") // Owner, User Access Admin GUIDs
| join kind=inner (AADServicePrincipal) on $left.PrincipalId == $right.ObjectId
| project TimeGenerated, ServicePrincipalName = DisplayName, RoleAssigned = RoleDefinitionId, Caller = Caller
Step-by-step guide:
AzureActivity: Queries the Azure control plane activity log.OperationNameValue ... "write": Filters for role assignment events.
– `parse_json` andtostring: These functions are used to navigate and extract data from the nested JSON structure in the `Properties` field.
– `where RoleDefinitionId has_any (…)` : Filters for the globally unique identifiers (GUIDs) of high-privilege roles.join kind=inner (AADServicePrincipal): Joins with the Azure Active Directory Service Principals table to resolve the principal ID into a readable name.
4. API Security: Detecting Suspicious Graph API Calls
Microsoft Graph API is a prime target for attackers moving laterally in a cloud environment. Monitoring for unusual application consent grants or specific high-risk API calls is crucial.
// Hunt for suspicious Graph API permissions being granted AuditLogs | where OperationName == "Consent to application" | extend Conditions = parse_json(tostring(parse_json(tostring(InitiatedBy.user)).appDisplayName)) | extend TargetApp = tostring(parse_json(Resources)[bash].displayName) | extend Permissions = parse_json(tostring(parse_json(AdditionalDetails)[bash].value)) | where Permissions has "Mail.ReadWrite" or Permissions has "Directory.ReadWrite.All" | project TimeGenerated, TargetApp, Permissions, ClientIP=InitiatedBy.user.ipAddress
Step-by-step guide:
AuditLogs: Queries the Azure AD Audit Log.OperationName == "Consent to application": Focuses on application consent events.- Nested `parse_json` calls: Used to drill down into the complex
InitiatedBy,Resources, and `AdditionalDetails` fields to extract the application name and the specific permissions granted. where Permissions has ...: Filters for the presence of high-risk permissions that allow writing to mail or the entire directory.
- Windows Security: Mapping Processes to Network Connections (KQL in Azure Sentinel)
When investigating a potentially compromised Windows host, you need to correlate processes with their network activity. This query uses the `DeviceProcessEvents` and `DeviceNetworkEvents` tables from Microsoft Defender for Endpoint (via Azure Sentinel).
// Link suspicious processes to their outbound connections
let SuspiciousProcesses = DeviceProcessEvents
| where TimeGenerated > ago(12h)
| where FileName in~ ("whoami.exe", "nltest.exe", "arp.exe") // Example hunting list
| distinct DeviceId, ProcessId;
SuspiciousProcesses
| join kind=inner (DeviceNetworkEvents
| where TimeGenerated > ago(12h)
| where ActionType == "ConnectionSuccess")
on DeviceId, ProcessId
| project Timestamp=TimeGenerated, DeviceName, Process=FileName, RemoteIP, RemotePort, RemoteUrl
Step-by-step guide:
– `DeviceProcessEvents` and DeviceNetworkEvents: These are advanced hunting tables provided by Microsoft Defender for Endpoint data.
– let SuspiciousProcesses = ...: This creates a variable holding the results of the first query, which finds processes with suspicious names.
– join kind=inner ... on DeviceId, ProcessId: This is the key step, joining the list of suspicious processes with the network events table to find any connections made by those specific processes.
– project: Formats the output to show the connection between a process and its network call.
- Linux Security: Hunting for Privilege Escalation via SUID Binars
Attackers often exploit misconfigured SUID binaries on Linux to escalate privileges. This KQL query, designed to run against logs from a Linux agent, identifies common SUID binaries that have been executed.
// Hunt for execution of common SUID binaries
Syslog
| where TimeGenerated >= ago(6h)
| where Facility == "auth"
| where SyslogMessage has "uid="
| extend Process = extract(@"COMMAND=(\S+)", 1, SyslogMessage)
| where Process has_any ("find", "nmap", "vim", "bash", "cp", "mv")
| summarize ExecutionCount=count(), LastTime=max(TimeGenerated) by Computer, Process, User
| where ExecutionCount > 2 // Filter for repeated execution
Step-by-step guide:
Syslog: Queries the system log table.Facility == "auth": Filters for authentication-related logs, which often contain process execution details.extract(@"COMMAND=(\S+)", 1, SyslogMessage): Uses a regular expression to pull the executed command out of the log message.where Process has_any (...): Filters for a known list of binaries that are suspicious when run with SUID permissions.summarize ... by Computer, Process, User: Aggregates the results to show how many times each binary was executed per user and computer.
- Automated Response: Creating a KQL Function for Proactive Alerting
To operationalize your hunting, you can package a query as a function in Azure Sentinel’s Log Analytics Workspace. This allows you to run it on a schedule or use it as a custom detection rule.
// Create a function for detecting rare processes (Save this as a function in Log Analytics)
let RareProcessDetection = (){
DeviceProcessEvents
| where TimeGenerated >= ago(1d)
| where isnotempty(FolderPath)
| where FolderPath startswith @"C:\Users\"
| where not(FolderPath has_any (@"\Windows\", @"\Program Files\", @"\Microsoft\"))
| summarize ProcessCount = dcount(DeviceId) by FileName, FolderPath
| where ProcessCount < 5 // Alert if seen on fewer than 5 distinct hosts
};
RareProcessDetection
| order by ProcessCount asc
Step-by-step guide:
- Define the function: `let RareProcessDetection = (){ … };` encapsulates the entire query logic.
FolderPath startswith @"C:\Users\": Focuses on user directories, a common location for dropped malware.not(FolderPath has_any (...)): Excludes known legitimate paths, reducing false positives.summarize ProcessCount = dcount(DeviceId): Counts the number of distinct devices a particular process has been seen on.where ProcessCount < 5: The core detection logic; processes seen on very few hosts are potentially malicious and warrant investigation. Saving this as a function allows SOC analysts to run `RareProcessDetection` as a simple command.
What Undercode Say:
- KQL is not just a query language; it is the operational backbone of a modern, data-driven SOC. Mastery of KQL shifts security teams from a reactive to a proactive and intelligence-led posture.
- The true power of KQL is unlocked not in isolated queries, but in building a library of reusable functions and detection rules that automate the initial stages of threat investigation, freeing up analysts for complex analysis.
The technical depth required to craft precise KQL queries bridges the gap between theoretical security knowledge and practical, impactful defense. As demonstrated, a single well-constructed query can identify everything from cloud misconfigurations to active beaconing. The future of security analytics lies in the ability to manipulate and interrogate data at scale, and KQL is a cornerstone skill for that future. Organizations that fail to cultivate this expertise internally will find themselves at a significant disadvantage against data-savvy adversaries.
Prediction:
The convergence of AI/ML with KQL-based analytics will lead to the development of self-healing cloud environments. Predictive KQL models will not only flag anomalous behavior but will also automatically initiate containment protocols, such as temporarily isolating a VM or revoking a suspicious application’s consent, before a human analyst has even reviewed the alert. This will fundamentally change the SOC analyst’s role from a first responder to an orchestration supervisor, managing and tuning these automated defense systems.
🎯Let’s Practice For Free:
IT/Security Reporter URL:
Reported By: Bert Janpals – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
