CODEWITHSUNDEEP
c#mysqlazurepowerbiazure-application-insights
johnwrite
johnwrite

Reputation: 85

Azure Application Insights Analytics Query for exporting to Power BI

I need some help for writing queries in AI Analytics with which i can generate kusto query and export the result to Power BI . I am new to query language in general ( didn't have much exp in sql also )

So , I have created a couple of custom events( lets say statusA and statusB ) in my app , which i will be getting in Customdimensions . statusA and StatusB will be having lets say true or false . I should be able to get the result data as Status containing count of true/false values of StatusA and statusB each , so that i will be able to render it in a piechart.

the query which i tried :

customEvents | extend queryA = customDimensions.['StatusA'] , queryB = customDimensions.['StatusB'] | where queryA = true or queryB = true | project queryA, queryB | summarize count() by queryA , queryB

Result I got is this

I want the result to be like this in pie chart : Pie chart

Upvotes: 2

Views: 3668

Answers (2)

Dan Hadari
Dan Hadari

Reputation: 264

Try this query:

customEvents 
| extend queryA = tostring(customDimensions.['StatusA']) , queryB = tostring(customDimensions.['StatusB'])
| extend status = iff(queryA == 'true', "StatusA", (iff(queryB == 'true', "StatusB", "N/A")))
| summarize count() by status
| render piechart

To save this overhead of comparing values, you can simply report in the customDimensions on queryType and assign it with the value of QueryA or QueryB. Then it would be only a matter of using summarize count() by queryType.

Upvotes: 2

Assaf Neufeld
Assaf Neufeld

Reputation: 743

A simple solution could be converting those true/falses into ints using iff, and then summing.

Try:

customEvents 
| extend queryA = tostring(customDimensions.['StatusA']) , queryB = tostring(customDimensions.['StatusB'])
| extend queryACount = iff(queryA == 'true', 1, 0)
| extend queryBCount = iff(queryB == 'true', 1, 0)
| summarize sum(queryACount),  sum(queryBCount)
| render piechart

Upvotes: 1

Related Questions