KQL
Cheatsheet
1. Basic Query Format
Start with the table name followed by a series of query operators.
2. Filtering
Use
where
to filter results based on a condition.
3. Sorting
Use
order by
to sort results based on a column.
4. Aggregation
Use
summarize
to aggregate data.
5. Joining Tables
Use
join
to combine tables based on a related column.
6. Selecting Columns
Use
project
to select which columns to display.
7. Renaming Columns
Use
extend
orproject
withas
to rename columns.
8. Limiting Results
Use
take
to limit the number of results returned.
9. Calculating Time Difference
Use
datetime_diff
to calculate the difference between two datetime columns.
10. String Manipulation
Use `strcat`, `substring`, etc., for string operations.
Examples for Detection Query in KQL
1. Basic Query
SecurityEvent
2. Filter for a Specific Event ID
SecurityEvent
| where EventID == 4624
3. Retrieve Specific Columns
SecurityEvent
| project TimeGenerated, Computer, EventID
4. Count by Event ID
SecurityEvent
| summarize count() by EventID
5. Filter and Sort by Time
SecurityEvent
| where EventID == 4624
| order by TimeGenerated desc
6. Join Two Tables
SecurityEvent
| join (
Syslog
) on Computer
7. Limit Results
SecurityEvent
| take 10
8. Calculate Time Difference
SecurityEvent
| extend duration = datetime_diff('second', TimeGenerated, TimeGenerated)
9. String Concatenation
SecurityEvent
| extend info = strcat(Computer, ":", EventID)
10. Filter with Multiple Conditions
SecurityEvent
| where EventID == 4624 and Computer == "MY-PC"
11. Count Events per Computer
SecurityEvent
| summarize count() by Computer
12. Filter for a Specific Time Range
SecurityEvent
| where TimeGenerated between (datetime(2022-01-01) .. datetime(2022-01-31))
13. Find Unique Values
SecurityEvent
| summarize count() by Account
| project Account
14. Calculate Average
Perf
| summarize avg(CounterValue) by CounterName
15. Group by Time Interval
SecurityEvent
| summarize count() by bin(TimeGenerated, 1h)
16. Use of Case Statement
SecurityEvent
| extend EventType = case(EventID == 4624, "Login", EventID == 4625, "Failed Login", "Other")
17. Filter with String Contains
SecurityEvent
| where Computer contains "MY-PC"
18. Top N Entities
SecurityEvent
| summarize count() by Computer
| top 5 by count_
19. Calculate Percentage
SecurityEvent
| summarize EventCount = count()
| extend Percentage = EventCount * 100 / toscalar(SecurityEvent | count())
20. Filter with Not Equal
SecurityEvent
| where Computer != "MY-PC"
Last updated