KQL for beginners: querying IoT telemetry with Kusto Query Language

In the previous post we looked at Azure Data Explorer: what it is, how it’s structured, and why it’s a good fit for IoT telemetry. Once data is flowing into ADX, you need a way to work with it. That’s what KQL is for.

KQL (Kusto Query Language) is the query language built into ADX. This post covers the core concepts and the operators you’ll reach for most often. The examples all use a telemetry table with columns like DeviceId, Temperature, Humidity, and EnqueuedTime.

How a KQL query is structured

#

Every KQL query starts with a data source, most often a table name:

Telemetry

Run this and you get every row and every column in the table. Not very useful on its own, but it’s the foundation everything else builds on.

One thing to keep in mind right away: KQL is case-sensitive. Telemetry and telemetry are different things. The first works; the second returns nothing.

The pipe operator

#

Once you have a source, you chain operations onto it using the pipe character (|). Each operator takes the output of the previous step as its input:

Telemetry
| where DeviceId == 'RaspberryPiSimulator'
| count

Think of it as a funnel. You start with all the data, and each step filters, transforms, or summarizes it until you’re left with exactly what you need. The order matters - both for the result and for performance.

A common convention is to put each operator on its own line. It’s not required, but it makes queries much easier to read as they grow.

Filtering with ‘where’

#

The where operator filters rows based on a condition:

Telemetry
| where DeviceId == 'RaspberryPiSimulator'

You can use standard comparison operators: ==, !=, >, <, >=, <=. You can also combine conditions:

Telemetry
| where DeviceId == 'RaspberryPiSimulator' and Temperature > 25

Sorting with ‘sort by’

#

To get the most recent records first, sort by the timestamp column in descending order:

Telemetry
| where DeviceId == 'RaspberryPiSimulator'
| sort by EnqueuedTime desc

Descending is the default, so you can leave off desc if you want. That said, it’s usually clearer to be explicit.

Selecting columns with ‘project’

#

By default, a query returns all columns. If you only care about a few of them, use project to select just those:

Telemetry
| where DeviceId == 'RaspberryPiSimulator'
| sort by EnqueuedTime desc
| project Temperature, EnqueuedTime

The result only includes the two columns you specified. Useful both for readability and for reducing the amount of data returned.

Aggregating with ‘summarize’

#

This is where KQL gets interesting for IoT scenarios. The summarize operator groups rows and applies an aggregation function to each group.

Count messages per device:

Telemetry
| summarize NumberOfMessages = count() by DeviceId

Find the maximum temperature across all data:

Telemetry
| summarize MaxTemperature = max(Temperature)

You can combine multiple aggregations in a single summarize:

Telemetry
| summarize
    MaxTemperature = max(Temperature),
    MinTemperature = min(Temperature),
    AvgTemperature = avg(Temperature)

There’s also countif, which counts only the rows that match a condition. Useful for things like counting how often a temperature threshold was exceeded per device:

Telemetry
| summarize NumberOfAlerts = countif(Temperature > 30) by DeviceId

Filtering by time

#

Time-based filtering is something you’ll use constantly with IoT data. KQL has a built-in ago() function for relative time ranges:

Telemetry
| where EnqueuedTime > ago(1h)
| summarize avg(Temperature) by DeviceId

That query filters to the last hour and calculates the average temperature per device. You can use 1h, 30m, 7d - whatever fits your scenario.

Visualizing with ‘render’

#

One thing that makes ADX convenient for IoT monitoring is built-in visualization. Add the render operator at the end of a query and ADX turns the result into a chart directly in the interface:

Telemetry
| project EnqueuedTime, Temperature
| where EnqueuedTime > ago(5m)
| render linechart

The render operator has to be the last step in the query. It only works with tabular output - which is the case for any normal query result. Options include linechart, barchart, areachart, columnchart, and others.

Once you have a chart, you can pin it to an ADX dashboard. Give the tile a name, create a new dashboard or add it to an existing one, and you’re done. Dashboards support auto-refresh, so you can set them to update every 10 or 30 seconds and get a live view of incoming telemetry.

For more advanced dashboards or integration with existing tools, ADX also connects to Power BI and Grafana.

KQL beyond IoT

#

Everything here applies equally well outside of an IoT context. ADX underlies Log Analytics and Application Insights in Azure, so if you’ve ever written a query in those tools, you were already using KQL. Microsoft Sentinel also runs on KQL. If you invest time learning it here, that knowledge carries over.

For a deeper reference, Microsoft’s documentation covers the full language: KQL overview - Azure Data Explorer

What comes next?

#

With the warm path covered, the next post moves to the hot path: real-time alerting with Azure Stream Analytics. That’s where we detect critical conditions as they happen and trigger an automated response.