Exploring Tabular Operators Kusto Query For App Insights
Dec 10th, 2021 - written by Kimserey with .
Kusto query is the query language used to query App Insights logs with Azure DevOps. In today post we will look at the most common used operators with some examples.
Tables
For this post, we will be using the sample azure data explorer.
App Insights works with tables. By typing the table and `SHIFT+ENTER, we will return all the values for that table. For example:
1
StormEvents
will return all the storm events. This table is present in the sample explorer.
Tabular Operators
Starting from the table we can then use piping | to construct our queries, with the most commonly used tabular operators where and take:
1
2
3
StormEvents
| where State == 'FLORIDA'
| take 10
This will return the 10 first storm events appearing for FLORIDA and we could do != 'FLORIDA' for returning the 10 first that aren’t Florida. For case insensitive we can use ~ with =~ and !~.
So far we took the 10 first that appeared, but we usually want to have some sort of ordering. For that we can use sort by:
1
2
3
4
StormEvents
| where State == 'FLORIDA'
| sort by StartTime desc
| take 10
order by is an alias to sort by, both are equivalent. And just like in other query language, we have asc and desc and we can sort by other column by adding them after a comma.
1
2
3
4
StormEvents
| where State == 'FLORIDA'
| sort by StartTime desc, EventId asc
| take 10
In logs we usually have regular columns, and an extra metadata JSON object containing extra values. If we want to extract property of that object to bring it to the forefront, we can use extend:
1
2
print metadata = dynamic({"requestId":"123","userId":"abc"})
| extend requestId = metadata.requestId, userId = metadata.userId
Here I simulated a metadata column using print and extracted from the content of metadata the requestId and userId. Other than extracting from JSON, extract can be used for computed values:
1
2
3
4
5
6
7
8
9
10
11
StormEvents
| sort by StartTime desc
| where State == 'FLORIDA'
| extend duration = EndTime - StartTime
| extend duration_format = strcat(
format_timespan(duration, 'd'), ' days ',
format_timespan(duration, 'H'), ' hours ',
format_timespan(duration, 'm'), ' minutes'
)
| sort by duration desc
| take 10
We used extend to generate the duration, then used extend again with strcat to create a human readable format of the duration.
Using extend will add a new column on the result. To reduce the amount of columns return by the result and have a more compact view, we can use project:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
StormEvents
| sort by StartTime desc
| where State == 'FLORIDA'
| extend duration = EndTime - StartTime
| extend duration_format = strcat(
format_timespan(duration, 'd'), ' days ',
format_timespan(duration, 'H'), ' hours ',
format_timespan(duration, 'm'), ' minutes'
)
| project
EventId,
State,
duration_format,
StartTime,
EndTime
| take 10
Lastly in SQL we often use group by to aggregate result and get count or min or max. In Kusto we have the same capabilities with summarize:
1
2
3
4
5
StormEvents
| sort by StartTime desc
| take 2000
| summarize count() by State
| top 20 by count_
Here we count by state and get the top 20 by count. count() is an aggregate function. Another useful summary is per buckets which we can achieve with bin:
1
2
3
4
5
StormEvents
| sort by StartTime desc
| take 2000
| summarize count() by day = bin(StartTime, 1d)
| sort by day
bin will round the value given to the closest bin, here the day. So if we project the bin on each event, we will see that it is rounded to the start of the day:
1
2
3
4
5
StormEvents
| sort by StartTime desc
| take 2000
| extend day = bin(StartTime, 1d)
| project day
Aggregates
With summarize we seen that aggregate functions were used. The most common being:
count: returns the count per summary,max: returns the max value from the group,min: returns the min value from the group,sum: returns the sum of all the values (if numerical).
For example here we can gtet the sum of direct injured per day:
1
2
3
StormEvents
| summarize sum(InjuriesDirect) by bin(StartTime, 1d)
| take 20
Render
Lastly render is a special tabular operator which renders a chart.
The most common charts are:
barchartcolumnchartlinechartpiechart
Taking columnchart as example, we can diplay the count per day:
1
2
3
4
StormEvents
| summarize count() by bin(StartTime, 1d)
| take 20
| render columnchart
We can also split further per state:
1
2
3
4
StormEvents
| summarize count() by bin(StartTime, 1d), State
| take 20
| render columnchart
Some visualizations have a kind property for example with columnchart we can do kind=stacked100 which would stack the column from the same bucket into a 100% height column. Other kind for column chart are:
unstackedstackedstacked100
And that conludes today’s post, hope you like this post and I’ll see you on the next one!