Links

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 or project with as 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"