Sign-in log query lab
Query 45 sign-in events in KQL or SPL style and separate a password spray from a brute force and from users mistyping their passwords.
Goal
The SigninLogs table holds 45 sign-in events from one morning. The SOC got a vague alert about “lots of failed logins” and wants to know whether it is a spray, a brute force, or users mistyping passwords.
Write queries in KQL style (SigninLogs | where … | summarize … by …) or SPL style (search index=signin … | stats … by …). The engine supports where, search, summarize or stats with count and dcount, project, order or sort, take or head, distinct, and count.
4 rows
Columns: TimeGenerated UserPrincipalName IPAddress ResultType AppDisplayName Location · ResultType 0 is success, 50126 is a bad password, 50053 is a locked account.
| IPAddress | count_ |
|---|---|
203.0.113.45 | 17 |
198.51.100.23 | 15 |
198.51.100.145 | 2 |
198.51.100.141 | 1 |
Steps
01Write a query whose result is exactly one row: the address behind the password spray.
Do this in the workbench above. This step completes itself when the condition is met.
02Run the starter query. Which address has the most failed sign-ins?
03The spray worked once. Which account signed in successfully from the spraying address?
04What is 198.51.100.23 doing?
05Turning this into a scheduled detection, which condition is the most useful?