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
Recent Comments