Kusto Query Language Cheatsheet

KQL - Kusto Query Language

Docs and Sources

Description	Link
Markdown Best Practices	Markdown Best Practices
KQL QUick Reference	MS KQL Quick Reference
Visualization	MSLearn Chart Vizualization
Defender Hunting Queries	Github Defender Hunting Queries
Advanced Hunting Schema	MSLearn Table Schema

---

Most Used

Operator	Description	Syntax
take	Print limited Amout of Rows	SigninLogs \| take 100
where	Filter on Condition	SigninLogs \| where Identity has "giger" SigninLogs \| where Identity == "giger@contoso.com"
count	Count Results	SiginLogs \| where Country != "CH" \| count
order by	Sortieren nach	project Users \| order by UserPrincipalName
project	Anzeigen gewisser Spalten	SigninLogs \| project TimeGenerated, UserprincipalName, Location
extend	Erweitern der Tabelle um eine weitere Spalte	\| extend Status = tostring(Status.failureReason)
distinct	Filter duplicates	Distinct Location
summarize	Resultate zählen nach Spaltenname	summarize Total = count() by <Spaltename>
let	Variable definieren, Resultate abspeichern	let resultate = SigninLogs \| where Identity == "gig"
search	Durchsuchen aller Logs nach Stichwörtern	search "gig"
union	Tabellen zusammenführen	union SigninLogs, AadNoninteractiveSigninlogs
isnotempty	Alle leeren Felder rausfiltern	ExampleLogs \| where isnotempty(Spaltenname)

Operatoren

String Operatoren

Numerical Operatoren

Nützliche Queries

Array definieren um Werte zu filtern

let excludedAdresses = dynamic(["8.8.4.4", "8.8.8.8"]);
| where DestinationIP !in (excludedAdresses)

---

Herausfiltern von gewissen Resultaten

| where not(ResultType has_any ("10", "20"))

---

Resultate zeitlich begrenzen

Signinlogs | where Timegenerated > ago(1m)
Signinlogs | where Timegenerated > ago(1h)
Signinlogs | where Timegenerated > ago(1d)
Signinlogs | where Timegenerated > ago(1m)

---

Resultate visualisieren

MSLearn

Charttypes: Areachart, Barcahrt, Columnchart, Linechart, Piechart, Scatterchart, …

OfficeActivity
| summarize test = count() by Operation, bin(TimeGenerated, 4h)    #Generates 2 Axis
| render timechart      #Generates Graphics

Logins visualisieren

SigninLogs
| where Identity  has "gig"
| summarize x = count() by bin(TimeGenerated, 10m) 
| render timechart 

---

Wert aus einer Subtabelle extrahieren

| extend Status = tostring(Status.failureReason)

- Aus einem Array
| extend location = (todynamic(Entities)[2]).Location.City

- Aus XML Feld
| extend parsed = parse_xml(EventData)

---

Externe Daten laden

let IPList = externaldata(IPAddress:string)[@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Log4j_IOC_List.csv"] with (format="csv", ignoreFirstRecord=True);
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';