Azure Application Insights analytics language – union and join

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 union and join.

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.

Union

The keyword in Analytics that’s equivalent to UNION in SQL is union. So that the equivalent to “SELECT * FROM requests UNION SELECT * FROM exceptions” is “union requests, exceptions”

Select Union

The keyword in Analytics that’s equivalent to UNION in SQL is union. So that the equivalent to “SELECT * FROM requests WHERE timestamp> getdate() – 1 UNION SELECT * FROM exceptions WHERE timestamp>
getdate() – 1
” is “requests | where timestamp > ago(1d)| union(exceptions | where timestamp > ago(1d) )”

Select Union

Join

The keyword in Analytics that’s equivalent to UNION in SQL is union. So that the equivalent to “SELECT * FROM requests LEFT OUTER JOIN traces ON requests.operation_Id = traces.operation_Id” is “requests| join kind=leftouter(traces)on $left.operation_Id == $right.operation_Id”

Select Join

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 *