Kusto Detective Agency, Season 2, Onboarding

It's time for season 2 of Kusto Detective Agency, a fun way to test and hone your Kusto Query Language (KQL) skills! The first mission of season 2 (Onboarding) asks you to find the id of the detective that earned the most money in 2022, based on the data present in the DetectiveCases table. After …

Search in all tables and columns in Kusto / Azure Data Explorer

Say you have a piece of information from the telemetry of your services. You'd like to know more about the data it references, but you don't know exactly where it fits in the schema. Maybe it's an operation id, a correlation id, a GUID, or something else. It might be the entire value of a …

Split an array into multiple rows in Kusto/Azure Data Explorer with mv-expand

I've recently learned about a handy command in Kusto that allows to expand a row into multiple rows by splitting a column with array or property bag values: mv-expand. This is very handy if you want to summarize results or join over the values that are part of the array/property bag. Say you have something …

Add as filters from Kusto Explorer results

Kusto Explorer has a nice feature that you should know if you want to save some typing. Once you obtain some results after writing a query, right-click on one cell of the results will show you the "Add as filters" command. Clicking on it will add a piped where operator with an equals condition where …

Prettify a query in Kusto Explorer

Perhaps you didn't know that Kusto Explorer has a very handy Prettify Query button, which will format your query. Among other things, it will put pipes (|) in a new line. You can find it in the toolbar of a query: The "Prettify Query" button in Kusto Explorer. Press it, and this KustoLog | where …

Summarize by time of day in Kusto / Azure Log Analytics

In Kusto / Azure Log Analytics it's simple to summarize your query by time of day, just use the datetime_part function. You might want to do that to see if something is happening more often on certain hours. ContainerLog | where TimeGenerated > ago(7d) | summarize count() by Hour=datetime_part("Hour", TimeGenerated) | order by Hour asc …

Round to the Nth decimal in Kusto / Azure Monitor queries

I was running some Kusto queries, and at some point I needed to limit floats to only have a certain number of decimals. The bin function can do exactly that. Actually it does more, as it works on various data types (like dates and times) and it can round values to an arbitrary "bin" size. …