Azure Application Insight Query to get success rate

Question

Have a question about how could I show success rate on Azure Dashboard. If I have single temeletry event that indicates success or failure - it's quite simple:

    customEvents
    | where name == "ResponseEvent" and customDimensions.Condition == "test"
    | summarize count() by tostring(customDimensions.State) //State could be Success|Failure
    | render piechart

But in my case - I have 2 events: RequestEvent, SuccessResponseEvent and from those two I want to get success rate, something like: successRate = 100*successCount/requestCount.

I end up with this join:

    customEvents
    | where name == "RequestEvent" and customDimensions.Condition == "test"
    | summarize  requestCount = count()
    | extend joinField = "1"
    | join ( customEvents
    | where name == "SuccessResponseEvent" and customDimensions.Condition == "test"
    | summarize  successCount = count()
    | extend joinField = "1")
    on joinField
    | extend successRate = (100 * successCount / requestCount)
    //////| extend failureRate = 100 - successRate 
    | project successRate
    | render table

I got the value I need, but I only manage to display it as table, while I need a piechart. I thought about adding union:

    let success = view () { print x=toint(80) };
    let failure = view () { print x=toint(20) };
    union withsource=TableName success, failure
    | render piechart

But I don't see how to do this in my request.

Or create variables using let statement and try to calculate everything and join using materialize(createRequestRecieved), but it causes quite a lot of errors and I hope some simple way exists.

Question is: maybe somebody could point me to how I could achieve this: calculate one value, maybe display it as two values (success and 100-success) and arrange them in format valid for "render piechart" operator?

And second question, not so important: could I join them by some existing field? Whey I'm trying to use joinField = tostring(customDimensions.MappingField) I'm getting an error: Ensure that expression: customDimensions.MappingField is indeed a simple name

Answer 1

If you are going for a piechart, it would require a string legend field and a value on each row for that legens, so union of two results should work:

requests
| summarize Success = sumif(itemCount, success == true)
| project Legend = "Success", Value = Success 
| union
(requests
| summarize Failed = sumif(itemCount, success == false)
| project Legend = "Failed", Value = Failed )
| render piechart

Going for a barchart would allow to use both summarize clauses in one query without join/union and may speed up performance:

requests
| summarize Success = sumif(itemCount, success == true), Failed = sumif(itemCount, success == false)
| project Legend = "Status", Success, Failed 
| render barchart 

Similarly, to calculate the rate in the same query:

requests
| summarize Success = sumif(itemCount, success == true), Failed = sumif(itemCount, success == false)
| extend SuccessRate = Success * 1.0 / (Success + Failed)

Answer 2

I'm quite sure it's not the best option an I'm mising something in this query language capabilities, but I could put my request in variable, apply some caching and repeat it twice, I suppose:

let dataSource = customEvents
| where name == "RequestEvent" and customDimensions.Condition == "test"
| summarize  requestCount = count()
| extend joinField = "1"
| join ( customEvents
| where name == "SuccessResponseEvent" and customDimensions.Condition == "test"
| summarize  successCount = count()
| extend joinField = "1")
on joinField
| extend successRate = (100 * successCount / requestCount)
| extend failureRate = 100 - successRate;
let cacheddataSource = materialize(dataSource);
cacheddataSource
| project Legend = "Success", Value = successRate
| union (
dataSource
|project Legend = "Failure", Value = failureRate 
) 
| render piechart

So, let and materialize more or less helps, maybe some tweaks will be necessary to display actual amount of successes and failures.