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 selection and filtration

To create Application Insights Instance for your resource, you can follow this post http://amdevblog.com/2019/01/azure-application-insights-for-system-behaviour-analysis/

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.

Wild card queries

To write a wild card query (i.e. query without filtration), all you need to write is the name of the log type you are searching for. For example “requests”. This is equivalent to “SELECT * FROM requests” in SQL.

Retrieving all requests

Selecting specific fields

The keyword “project” is used to include specific fields in the query output. Copy this query “requests | project resultCode , timestamp” to the query editor to validate your understanding of this rule.
This is equivalent to “SELECT resultCode , timestamp FROM requests” in SQL.

Selecting specific fields

Select number of records

The equivalent to SQL query “SELECT TOP 10 * FROM requests ” in Analytics language is ” requests | take 10 “.

Filtering with non-null fields

The equivalent to SQL query “SELECT * FROM requests WHERE resultCode IS NOT NULL” in Analytics language is ” requests | where isnotnull(resultCode) “.

Not Null Filtration

Filtering by comparing with dates

The equivalent to SQL query “SELECT * FROM requests WHERE timestamp > getdate()-1” in Analytics language is ” requests | where timestamp > ago(1d) “.

Filtering with Dates

The equivalent to SQL query “SELECT * FROM requests WHERE timestamp BETWEEN ‘2019-01-10’ AND ‘2019-01-13’” in Analytics language is ” requests | where timestamp > datetime(2019-01-10) and timestamp <= datetime(2019-01-13) “.


Filtering with Dates

Filtering by comparing with strings

The equivalent to SQL query “SELECT * FROM requests WHERE
itemType =’request’
” in Analytics language is ” requests | where itemType == “request” “.

The equivalent to SQL query “SELECT * FROM requests WHERE
itemType LIKE ‘request%’
” in Analytics language is ” requests | where itemType startswith “request” “.

The equivalent to SQL query “SELECT * FROM requests WHERE
itemType LIKE ‘%request%’
” in Analytics language is ” requests | where itemType contains “request” “.

Filtering with Regular expressions

Analytics language has a keyword for regular expression comparisons as follows ” requests | where itemType matches regex “request””

Filtering by comparing with Boolean

The equivalent to SQL query “SELECT * FROM requests WHERE !(success)” in Analytics language is ” requests | where success == “False” “.

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/

2 thoughts on “Azure Application Insights analytics language – select and filter”

Leave a Reply

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