Application Insights: select and filter

Application Insights wallpaper

Azure Application Insights has a specific language and syntax for select and filter data 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.

First, navigate to analytics page of any Application Insights App by clicking Logs tab in the overview page of the app.

Navigate to Analytics page
Navigate to Analytics page

Then, analytics tab opens a new editor window that you can type your query in it.

Analytics Logs Query Editor
Analytics Logs Query Editor

Now, 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

First of all, 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 in SQL to

SELECT * FROM requests
Select: retrieving all requests
Select: retrieving all requests

Selecting specific fields

The keyword “project” is used to include specific fields in the query output. Copy this query to the query editor to validate your understanding of this rule.

requests
| project resultCode, timestamp

This is equivalent in SQL to

SELECT resultCode, timestamp FROM requests
Selecting specific fields
Selecting specific fields

Select number of records

The equivalent to SQL query

SELECT TOP 10 * FROM requests

in Analytics language is

requests 
| take 10
Select specific fields and the number 10 records
Select specific fields and the number 10 records

Filters

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)
Filtering with non-null fields: not Null Filtration
Filtering with non-null fields: 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)

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(2020-07-10) and timestamp <= datetime(2020-07-11)
Filtering by comparing with dates
Filtering by comparing 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"
Filtering by comparing with Boolean
Filtering by comparing with Boolean

And this is it for Application Insights select and filter. Do you want more details about union? Follow me!

One thought on “Application Insights: select and filter

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.