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
Analytics tab opens a new editor window that you can type your query in it.
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.
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”
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 “.
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”.
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”.
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”.
Adding Custom metrics to Azure Application Insights
You can add custom metrics to your insights by referring to this post