Skip to content
SOC FluentSpeak SIEM and EDR
Join the waitlist

For Microsoft Defender and Microsoft Sentinel

Practise and learn KQL

KQL is the query language behind advanced hunting in Microsoft Defender XDR and log search in Microsoft Sentinel. Practise it with exercises written like the tickets you get on shift, on realistic lab data, and see straight away whether your query found the right rows.

Join the waitlistSee pricing
DEFENDER · KQL
DeviceProcessEvents
| where Timestamp > ago(24h)
| where InitiatingProcessFileName =~ "outlook.exe"
| where FileName in~ ("powershell.exe", "cmd.exe")

Questions you’ll answer

Every exercise starts as a ticket. You write the query that answers it.

Did anyone run encoded PowerShell this week?
DEFENDER
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName =~ "powershell.exe"
| where ProcessCommandLine contains "-enc"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
Which accounts had the most failed sign-ins today?
SENTINEL
SigninLogs
| where TimeGenerated > startofday(now())
| where ResultType != "0"
| summarize Failures = count() by UserPrincipalName
| top 10 by Failures

Where you’ll use it

  • Microsoft Defender XDRCalled advanced hunting in the portal.
  • Microsoft SentinelSame language as Defender, different tables.

What you’ll learn, from beginner to advanced

Twelve modules in three levels. Each one has a short lesson, questions to answer and then exercises.

Beginner

  1. Your first queryTables, take, where and project, with ago() for time.
  2. Matching text== and =~, has, contains, startswith and in~.
  3. Sorting and limitingsort by, top and distinct.
  4. Countingcount, summarize … by and dcount().

Intermediate

  1. Working with timebin(), between and building a timeline.
  2. Shaping resultsextend, parse, extract() and strcat().
  3. Joining tablesjoin kinds like inner, leftouter and leftanti, and lookup.
  4. Real investigationsProcess trees in Defender, sign-in patterns in Sentinel.

Advanced

  1. Reusable querieslet, user-defined functions and materialize().
  2. Nested dataparse_json(), mv-expand and bag_unpack.
  3. Baselines and anomaliesmake-series and arg_max() to find what’s unusual.
  4. Fast queries at scaleFilter on time first, prefer has to contains, drop columns early.

Worth knowing before you start

  • == is case-sensitive and =~ isn’t, so Outlook.EXE and outlook.exe are not the same to ==.
  • has matches whole words and is faster than contains, which matches any part of the text.
  • Defender and Sentinel share the language but not the tables. Defender uses Timestamp, Sentinel uses TimeGenerated.

Cheat sheet

Compare

Other languages

Be first in when it launches

KQL is one of the languages SOC Fluent launches with. Join the waitlist and we’ll email you once, on launch day.

Join the waitlistSee pricing

Free to join. Students get 50% off at launch.