Theory · 9 min · SIEM, EDR, SOAR and your first queries
Your first KQL hunt
Build a KQL query that filters, groups and sorts sign-in failures.
KQL (Kusto Query Language) is what Microsoft Sentinel, Defender advanced hunting and Azure Monitor speak. A query starts with a table and passes rows through operators with the pipe character, top to bottom.
where keeps rows that match a condition. project chooses columns. summarize groups rows and counts or aggregates them. sort by (or order by) orders the result. take limits it.
In SigninLogs, ResultType "0" is a successful sign-in; other values are error codes, such as 50126 for an invalid username or password. ResultType is a string column, so compare it to "0" in quotes.
Time first: filter on TimeGenerated early. It makes the query faster and keeps you honest about the window you looked at.
- where
- Filter rows.
- summarize count() by X
- Group by X and count each group.
- ago(1d)
- A time one day before now.
Your turn: build a query clause by clause. Build a query: failed sign-ins in the last day, counted per source IP, highest first.
Do the interactive check Free, in the browser.
Sources
- Kusto Query Language overview Microsoft Learn
- summarize operator Microsoft Learn
- SigninLogs table reference Microsoft Learn
- Microsoft Entra authentication and authorization error codes Microsoft Learn