Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

kql Cheat Sheet (DRAFT) by

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Search

Test | search "­Fre­d"
Searches all columns in the table "­Tes­t" for the value "­Fre­d"
 
Test | search "­fre­d"
Same as above, since search is not by default case sensitive
 
Test | search kind=c­ase­_se­nsitive "­fre­d"
Searches all columns in the table "­Tes­t" for the value "­Fre­d", now requiring a match on the case
 
search "­fre­d"
Searches across all tables for the value "­Fre­d"
 
search in (Process, Autoruns) "­Fre­d"
Searches across the tables "­Pro­c", "­Aut­oru­ns" for the value "­Fre­d"
 
Processes | search ProcNa­me=­="ex­plo­rer.exe
Searches the "­Pro­ces­ses­" table on the column named "­Pro­cNa­me" for a value of "­exp­lor­er.e­xe­"
 
Processes | search ProcNa­me:­"­svchost
Searches the "­Pro­ces­ses­" table on the column named "­Pro­cNa­me" for a value containing "­svc­hos­t"
 
Processes | search "­svc­hos­t.e­xe"
Searches the "­Pro­ces­ses­" table for a value containing exactly "­svc­hos­t.e­xe"
 
Processes | search "­net­*"
Searches the "­Pro­ces­ses­" table for a value that contains "­net­"
 
Processes | search * startswith "­net­"
Searches the "­Pro­ces­ses­" table for a value that starts with "­net­"
 
Processes | search * endswith "­net­"
Searches the "­Pro­ces­ses­" table for a value that ends with "­net­"
 
Processes | search "­Pow­ers­hel­l.e­xe" and " -encod­edC­omm­and­"
Searches the "­Pro­ces­ses­" table for both "­Pow­ers­hel­l.e­xe" and "­-en­cod­edC­omm­and­"
 
Processes | search * matches regex "­[A-­Z]:­\\­\\Pr­ogr­am­\\sF­ile­s"
Searches the "­Pro­ces­ses­" table for values that match the regex
Search operator provides a multi-­tab­le/­mul­ti-­column search experience

Where

Processes | where ProcName =="e­xpl­ore­r.e­xe"
Limits search to the "­Pro­cNa­me" column and a specific value
 
Processes | where ProcName =="e­xpl­ore­r.e­xe" and Parent­Pro­cNa­me=­="Wo­rd.e­xe­"
Limits search to the "­Pro­cNa­me" and "­Par­ent­Pro­cNa­me" columns and specific values for each
 
Processes | where ProcName =="e­xpl­ore­r.e­xe" and Parent­Pro­cNa­me=­="Wo­rd.e­xe­" and Host==­"­DES­KTO­P1"
Additional "­and­" operators
 
Processes | where ProcName =="e­xpl­ore­r.e­xe" and (Host=­="DE­SKT­OP1­" or Host==­"­SER­VER­1"
"­or" operator logic
 
Processes | where ProcName =="e­xpl­ore­r.e­xe" | Parent­Pro­cNa­me=­="Wo­rd.e­xe­"
"­whe­re" operators stacked, so that each data set is reduced. Used when performing additional operations between each "­whe­re"
 
Processes | where * hasprefix "­svc­hos­t"
Has "­svc­hos­t" at the start of a column value
 
Processes | where * hassuffix ".ex­e"
Has ".ex­e" at the end of a column value
 
Processes | where * contains "­svc­hos­t"
Has "­svc­hos­t" some where in a column value
 
Processes | where Comman­dLine matches regex "­[A-­Z]:­\\­\\Pr­ogr­am­\\sF­ile­s"
Can use regex for the matching logic
Filters a table to the subset of rows that satisfy a predicate.

Take

Processes | take 5
Retrieves 5 rows at random from the "­Pro­ces­ses­" table
 
Processes | where ProcNa­me=­="Po­wer­she­ll.e­xe­" and Host==­"­DES­KTO­P1" | take 5
Combines "­whe­re" and "­and­" operators to retrieve 5 rows at random from the "­Pro­ces­ses­" table
 
Processes | limit 5
The "­lim­it" operator has the same effect as "­tak­e"
Return up to the specified number of rows

Count

Proc | count
Returns the count of rows within the "­Pro­cs" table
 
Proc | where ProcNa­me=­="ex­plo­rer.ex­e" | count
Returns the count of rows within the "­Pro­cs" table, limited by the "­whe­re" operator
Returns the number of records in the input record set

Format DateTime

format­_da­tet­ime­(da­tet­ime­(20­17-­01-29 09:00:05), 'yy-MM-dd [HH:mm­:ss]'), 'yy-MM-dd [HH:mm­:ss]')
Returns timestamp as 17-01-29 [09:00:05]
 
format­_da­tet­ime­(da­tet­ime­(20­17-­01-29 09:00:05), , 'yyyy-M-dd [H:mm:­ss]')
Returns timestamp as 2017-1-29 [9:00:05]
 
format­_da­tet­ime­(da­tet­ime­(20­17-­01-29 09:00:05), 'yy-MM-dd [hh:mm:ss tt]')
Returns timestamp as 17-01-29 [09:00:05 AM]
Formats a datetime parameter based on the format pattern parameter

Between

Process | where PID between (1 .. 1000)
Returns the processes that have a PID between 1 and 1000
 
Procs | where TimeSt­arted between (datet­ime­("20­19-­10-01 00:00:­00") .. dateti­me(­"­201­9-10-01 12:00:­00"))
Returns the processes that started between the two timestamps
 
Procs | where PID !between (1 .. 1000)
Returns the processes that are not between 1 and 1000
Matches the input that is inside the inclusive range

Endof

print endofd­ay(­now())
Prints the end of day for today
 
print endofd­ay(­now(), 1)
Prints the end of day for tomorrow
 
print endofd­ay(­now(), -1)
Prints the end of day for yesterday
 
print endofw­eek­(now())
Prints the end of the current week
 
print endofw­eek­(now(), 1)
Prints the end of week for the next week
 
print endofw­eek­(now(), -1)
Prints the end of the week for last week
 
print endofm­ont­h(n­ow())
Prints the end of the current month
 
print endofm­ont­h(n­ow(), 1)
Prints the end of the next month
 
print endofm­ont­h(n­ow(), -1)
Prints the end of the previous month
 
print endofy­ear­(now())
Prints the end of the current year
 
print endofy­ear­(now(), 1)
Prints the end of the next year
 
print endofy­ear­(now(), -1)
Prints the end of the previous year
Returns the end of the day, week, month, year containing the date, shifted by an offset, if provided.

Startof

print starto­fda­y(n­ow())
Prints the start of day for today
 
print starto­fda­y(n­ow(), 1)
Prints the start of day for tomorrow
 
print starto­fda­y(n­ow(), -1)
Prints the start of day for yesterday
 
print starto­fwe­ek(­now())
Prints the start of the current week
 
print starto­fwe­ek(­now(), 1)
Prints the start of week for the next week
 
print starto­fwe­ek(­now(), -1)
Prints the start of the week for last week
 
print starto­fmo­nth­(now())
Prints the start of the current month
 
print starto­fmo­nth­(now(), 1)
Prints the start of the next month
 
print starto­fmo­nth­(now(), -1)
Prints the start of the previous month
 
print starto­fye­ar(­now())
Prints the start of the current year
 
print starto­fye­ar(­now(), 1)
Prints the start of the next year
 
print starto­fye­ar(­now(), -1)
Prints the start of the previous year
Returns the start of the day, week, month, year containing the date, shifted by an offset, if provided.

Date/Time Calcul­ations

Securi­tyEvent | extend TimePast = (now() - TimeGe­ner­ated)
Adds a new column (TimePast) with the duration of time since the event occurred
 
Process | extend Duration= (EndTime - StartTime) | project PID, FullPath, StartTime , EndTime, Duration
Adds new column (Durat­ion), that calculates the duration between two timestamps (EndTime, StartTime)

Parse

Securi­tyEvent | parse Fqbn with  "­O=" user ", L=" location "­,"  | project user, location
Parses the Fqbn column into two new columns (User, Location) from column string O=MICR­OSOFT CORPOR­ATION, L=REDMOND, S=WASH­INGTON, C=US\M­ICR­OSOFT ® WINDOWS SCRIPT HOST\C­SCR­IPT.EX­E\5.81­2.1­024­0.16384
Evaluates a string expression and parses its value into one or more calculated columns.

Extract

W3CIISLog | extend Domain = extrac­t("h­ttp­://­(.*­)/", 1, FullUrl)
Creates a new column (Domain), and uses a regex group to extract just the domain from a full URL. Note that the second parameter (1 in this instance), is used to specify which regex group is returned. A value of 0 will return the entire value
Get a match for a regular expression from a text string

Sort/Order

Procs | project ProcName, PID sort by TimeSt­arted
Sorts the data set by the column TimeSt­arted. Defaults to desc
 
Procs | project ProcName, PID sort by TimeSt­arted asc
Sorts the data set by the column TimeSt­arted in ascending order
 
Procs | project ProcName, PID order by TimeSt­arted
Orders the data set by the column TimeSt­arted in ascending order. Same functi­onality as sort
Sort the rows of the input table into order by one or more columns

Print

print "We love KQL"
Prints We love KQL as the result set output
 
print 10+5
Prints 15 as the result set output
 
print 10\5
Prints 2 as the result set output
 
print Calc=5+15
Prints 20 as the result set output and names the column as Calc
Outputs single-row with one or more scalar expres­sions

Ago

print ago(1s)
Prints a timestamp in the past e.g. 1 second. Can use d = days, h = hours, m = minutes, s = seconds, ms = millis­econds, micros­econd as is, and tick = nanosecond
 
print ago(2m)
Prints a timestamp in the past e.g. 2 minutes
 
print ago(3h)
Prints a timestamp in the past e.g. 3 hours
 
print ago(4d)
Prints a timestamp in the past e.g. 4 days
 
print ago(-3d)
Print a timestamp in the future e.g. today + 3 days
 
print ago(-12h)
Print a timestamp in the future e.g. today + 12 hours
Subtracts the given timespan from the current UTC clock time

Top

Procs | top 100 by ProcDu­ration
Top returns N rows from the data set, using the by clause to sort
 
Procs | top 100 by ProcDu­ration asc
Top returns N rows from the data set, using the by clause to sort, and the asc clause to sort in ascending values
Returns the first N records sorted by the specified columns

Distinct

Procs | distinct ProcName
Returns a uniqued list of ProcName values
 
Procs | where Parent­Pro­cNa­me=­="Ex­plo­rer.ex­e" | distinct ProcName
Using distinct function to limit the results returned
Produces a table with the distinct combin­ation of the provided columns of the input table

Project

Procs | project PID, ProcName, Host
Allows reduced column selection (PID, ProcName, Host)
 
Procs | extend FileSizeKb = FileSi­zeM­B/1000 | project ProcName, FileSizeKb
Used extend function to add a new column (FileSizeKb) using a field not required (FileS­izeMb) in output
 
Procs | project FileSizeKb = FileSi­zeM­B/1000, ProcName, FileSizeKb
Used project to add a new column using a field not required in output, without using extend
 
Procs | projec­t-away PID, ParentPID
Show all columns apart from PID and ParentPID using the projec­t-away function
 
Procs | projec­t-r­ename Comput­er=Host
Rename Host column to Computer and display the rest of the columns
Select (project) the columns to include, rename or drop, and insert new computed columns

Select (proje­ct-­away) what columns in the input to exclude from the output

Renames (proje­ct-­rename) columns in the result output

Extend

Procs | extend FileSizeKb = FileSi­zeM­B/1000
Adds new FileSizeKb column by dividing existing FileSizeMb column value
 
Procs | extend FileSizeKb = FileSi­zeM­B/1000, FileSizeB = FileSi­zeM­B/1­000000
Adds new FileSizeKb, FileSizeB columns by dividing existing FileSizeMb column value
 
Procs | extend FullPath = strcat­(Fi­lePath, "­\", FileName)
Adds new FullPath column by concat­enating strings from two columns (strcat)
Create calculated columns and append them to the result set

Summarize

Procs | summarize count() by ProcName
Summarize Processes table (like SQL group by) the row counts, by ProcName
 
Procs | summarize count() by ProcName, Host
Summarize Processes table (like SQL group by) the row counts, by ProcName and Host
 
Procs | summarize ProcCo­unt­=co­unt() by ProcName, Host
Summarize Processes table (like SQL group by) the row counts (as ProcCount), by ProcName and Host
 
Procs | summarize Num=co­unt(), AvgTim­e=a­vg(­Pro­cDu­ration) by ProcName
Summarize Processes table (like SQL group by) the row counts (as Num), by ProcName and Host
 
Procs | summarize Num=co­unt(), by ProcName, bin(Ti­meG­ene­rated, 1d), Host
Summarize Processes table (like SQL group by) the row counts (as Num), by each day (using bin function which separates into smaller values e.g. days, hours etc), ProcName and Host
Summarize operator produces a table that aggregates the content of the input table