KQL – The Kusto Query Language

The Kusto engine is a query language created by Microsoft.

I use it day-to-day primarily for Azure Monitor log queries. This blog will focus solely on Azure Log Analytics.

KQL also works well with:

• Azure Log Analytics
• Azure Application Insights
• Windows Defender ATP
• Azure Security Center
• Machine Learning

Microsoft provides a sandbox environment you can practice on, it is free and available to everyone, all you need is an Azure Account. https://portal.loganalytics.io/demo#

I’m not going to go into the basics of what Log Analytics is, but I will make notes about what some of the features mean and do.

Here are some of my favourite KQL queries:

Free Disk Space

Perf 
| where TimeGenerated > ago(1m)
| where CounterName == "Free Megabytes"
| where CounterValue < int(5000)
| extend FreeGB = CounterValue / 1000
| sort by FreeGB asc 

Node Down Alert

Heartbeat 
| where TimeGenerated > ago(24h)   
| summarize LastHeartbeat = max(TimeGenerated) by Computer  
| where isnotempty(Computer) and LastHeartbeat < ago(12h)  
| order by LastHeartbeat asc 

Servers manually rebooted by a user in the last 24 hours (Windows)

search in (Event) "shutdown" and EventLog == "System" and Source == "User32" and EventID == 1074 | where TimeGenerated > ago(24h) | sort by TimeGenerated desc | project TimeGenerated, Computer

Server Unexpected Shutdowns last 24 hours (Windows)

search in (Event) "shutdown" and EventLog == "System" and EventID == 6008| sort by TimeGenerated desc | project TimeGenerated, Computer

Windows Services Stopped in the last 1 hour (Windows)

Event
| where EventID == 7036
| project RenderedDescription , Computer , EventID , TimeGenerated
| where RenderedDescription contains "stopped"
| project Computer , TimeGenerated , RenderedDescription
| sort by TimeGenerated desc
| where TimeGenerated >= ago(1h)

Number of User Accounts Locked out

SecurityEvent
| where Activity contains "4740"
| count 

Note: You need to have Azure Security Events data collection enabled to atleast minimum for this to work. Click here for more info

Users Locked Out

SecurityEvent
| where Activity contains "4740"
| project TimeGenerated, Account, Computer, Activity