KQL: Common Operators

contains the database referenced in this document.

Count rows

How many storm records are there in the table?

Count
59066

StormEvents
| count

count returns a table with a single column and a single row containing the count of the remaining rows. The output is not a scalar value.

See a sample of data

Sample N rows from the StormEvents table.

StartTime	…	State	EventType	…
2007-09-18T20:00:00.000Z	…	FLORIDA	Heavy Rain	…
2007-09-29T08:11:00.000Z	…	ATLANTIC SOUTH	Waterspout	…

StormEvents
| take 2

take is arbitrary in the sense that the underlying sampling mechanism can change. It’s not arbitrary in the “sample 5 random records from the table”. Running take multiple times does not lead to different results.

Select a subset of columns

Show the State, EventType, and DamageProperty for 2 records.

State	EventType	DamageProperty
ATLANTIC SOUTH	Waterspout	0
FLORIDA	Heavy Rain	0

StormEvents
| take 2
| project State, EventType, DamageProperty

List unique values

List all of the unique storm types.

EventType
Thunderstorm Wind
Hail
…

StormEvents
| distinct EventType

Filter by condition

List the start time, end time, and property damage of all of the floods that occurred in Texas.

StartTime	EndTime	State	EventType	DamageProperty
2007-01-13T08:45:00.000Z	2007-01-13T10:30:00.000Z	TEXAS	Flood	0
…	…	…	…	…

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| project StartTime, EndTime, State, EventType, DamageProperty

Sort results

List the start time, end time, and property damage of all of the floods that occurred in Texas. Sort by descending damage property.

StartTime	EndTime	State	EventType	DamageProperty
2007-08-18T21:30:00.000Z	2007-08-19T23:00:00.000Z	TEXAS	Flood	5,000,000

StormEvents
| where State == "TEXAS" and EventType == "Flood"
| sort by DamageProperty desc
| project StartTime, EndTime, State, EventType, DamageProperty

The default sort order is desc and can be omitted.

Filter by date and time range

List storm events that happened between Aug 1, 2007 and Aug 30, 2007, along with their states, event types, start and end times. Sort in ascending order by start time.

State	EventType	StartTime	EndTime
GEORGIA	Excessive Heat	2007-08-01 00:00:00	2007-08-27 23:59:00
…	…	…	…

StormEvents
| where StartTime between (datetime(2007-08-01 00:00:00) .. datetime(2007-08-30 23:59:59))
| sort by StartTime asc
| project State, EventType, StartTime, EndTime

Get the top \(n\) rows

Return the two Texas floods that caused the most damaged property.

StartTime	EndTime	State	EventType	DamageProperty
2007-08-18T21:30:00.000Z	2007-08-19T23:00:00.000Z	TEXAS	Flood	5,000,000
2007-06-27T00:00:00.000Z	2007-06-27T12:00:00.000Z	TEXAS	Flood	1,200,000

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| top 2 by DamageProperty
| project StartTime, EndTime, State, EventType, DamageProperty

Note that the operator piping is sequential; applying top before where does not give the same result.

Create calculated columns

Query the top 2 Texas floods by property damage. Display the start time, end time, duration, and property damage.

StartTime	EndTime	Duration	DamageProperty
2007-08-18T21:30:00.000Z	2007-08-19T23:00:00.000Z	1.01:30:00	5,000,000
2007-06-27T00:00:00.000Z	2007-06-27T12:00:00.000Z	12:00:00	1,200,000

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| project StartTime, EndTime, Duration = EndTime - StartTime, DamageProperty
| top 2 by DamageProperty

Show the top 2 Texas floods by property damage. Show their durations as well.

StartTime	EndTime	DamageProperty	…	Duration
2007-08-18T21:30:00.000Z	2007-08-19T23:00:00.000Z	5,000,000	…	1.01:30:00
2007-06-27T00:00:00.000Z	2007-06-27T12:00:00.000Z	1,200,000	…	12:00:00

StormEvents
| where State == 'TEXAS' and EventType == 'Flood'
| top 2 by DamageProperty
| extend Duration = EndTime - StartTime

Map values from one set to another

Query the event report sources and list them as either public or private.

EventId	Source	FriendlyName
68796	Emergency Manager	Public
…	…	…
72609	Utility Company	Private
…	…	…

let sourceMapping = dynamic({
	"Emergency Manager": "Public",
	"Utility Company": "Private"
});
StormEvents
| where Source == 'Emergency Manager' or Source == 'Utility Company'
| project EventId, Source, FriendlyName = sourceMapping[Source]

FriendlyName defaults to an empty value if sourceMapping[Source] is not defined. Not sure if this empty value is null or an empty string. Either way, it does not crash at runtime.

References

Kusto Query Language (KQL) overview - Kusto | Microsoft Learn. learn.microsoft.com . Feb 23, 2025. Accessed May 24, 2025.
help.Samples | Azure Data Explorer. dataexplorer.azure.com . Accessed May 24, 2025.
Tutorial: Learn common Kusto Query Language operators - Kusto | Microsoft Learn. learn.microsoft.com . Dec 4, 2024. Accessed May 24, 2025.