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.
Then, analytics tab opens a new editor window that you can type your query in it.
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
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
Select number of records
The equivalent to SQL query
SELECT TOP 10 * FROM requests
in Analytics language is
requests
| take 10
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 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 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"
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”