Azure Application Insights analytics language – grouping and ordering

To query Azure Application Insights the used query language is Analytics. It has its specific syntax that’s different from Structured Query Language(SQL).

In this post, I am going to compare Analytics query language to SQL with examples for grouping and ordering.

To have a basic understanding of Analytics query language in terms of selection and filtration you can refer to this post http://amdevblog.com/2019/01/azure-application-insights-analytics-language-select-and-filter/

Navigate to analytics page of any Application Insights App by clicking analytics tab in the overview page of the app

Navigate to Analytics page

Analytics tab opens a new editor window that you can type your query in it.

Analytics Query Editor

In the query editor we are going to write our queries using the Analytics Query Language. The easiest way to understand this language is by referring to a well known language which is SQL.

Select Distinct

The keyword in Analytics that’s equivalent to distinct in SQL is summarize. So that the equivalent to “SELECT DISTINCT name, appName FROM
requests
” is “requests | summarize by name, appName”

Summarize by

Grouping and aggregation

The equivalent to SQL query “SELECT name, AVG(duration) FROM
requests GROUP BY name
” in Analytics language is “requests | summarize avg(duration) by name “.

Summarize by and AVG

Aliases

The equivalent to SQL query “SELECT operation_Name as Name, AVG(duration) as AvgD FROM requests GROUP BY name” in Analytics language is “requests | summarize AvgD=avg(duration) by operation_Name | project Name=operation_Name, AvgD”.

Summarize and Project

Ordering

The equivalent to SQL query “SELECT name, timestamp FROM requests ORDER BY timestamp asc” in Analytics language is “requests | project name, timestamp | order by timestamp asc nulls last”.

Order by

Select top by count function

The equivalent to SQL query “SELECT TOP 100 name, COUNT(*) as Count FROM requests GROUP BY name ORDER BY Count desc” in Analytics language is “requests | summarize Count=count() by name | top 100 by Count desc”.

Top by Count

Adding Custom metrics to Azure Application Insights

You can add custom metrics to your insights by referring to this post
http://amdevblog.com/2019/01/azure-application-insights-for-system-behaviour-analysis/

Leave a Reply

Your email address will not be published. Required fields are marked *