KQL filter after result set with pivot

Question

I am trying to write a KQL query to return rows from Application Insights request logs attached to an API Management instance. I want to return a single row for an api name when that api name meets the following two criteria:

1. At least two failures. (success = "False")
2. At least 50% of total calls are failures.

In the follow example datatable I would want a query that would only return one row for "/api/person", since there are 5 failures out of 6 total calls. There would not be a row for "/api/banner" since, while there are 2 total failures, the rate of failures is less than 50%.

So far I have been able to use a pivot to get the total number of successes and failures for each table. I am more familiar with TSQL and would create a nested subquery and filter out the results. My understanding is that approach is not available in KQL.

[![datatable(apiname:string, success:bool, timestamp:timespan)
\[
   "/api/person", "True", time(00:00:00),
   "/api/status", "True", time(00:00:24),
   "/api/banner", "True", time(00:00:20),
   "/api/banner", "True", time(00:00:19),
   "/api/banner", "True", time(00:00:21),
   "/api/banner", "False", time(00:00:22),
   "/api/banner", "False", time(00:00:23),
   "/api/person", "False", time(00:00:00),
   "/api/person", "False", time(00:00:37),
   "/api/person", "False", time(00:00:47),
   "/api/person", "False", time(00:00:53),
   "/api/person", "False", time(00:00:55),
\] 
| project apiname, success
| evaluate pivot(success, count(tobool(success)))]

Current result set:

I suspect there is a way to achieve this in KQL with a different approach. Any tips or suggestions would be appreciated. Thank you in advance!

Answer 1

I want to return a single row for an api name when that api name meets the following two criteria:

1. At least two failures. (success = "False")
2. At least 50% of total calls are failures.

In the follow example datatable I would want a query that would only return one row for "/api/person"

you could use the count() & countif() aggregation functions.

datatable(apiname:string, success:bool, timestamp:timespan)
[
   "/api/person", "True", time(00:00:00),
   "/api/status", "True", time(00:00:24),
   "/api/banner", "True", time(00:00:20),
   "/api/banner", "True", time(00:00:19),
   "/api/banner", "True", time(00:00:21),
   "/api/banner", "False", time(00:00:22),
   "/api/banner", "False", time(00:00:23),
   "/api/person", "False", time(00:00:00),
   "/api/person", "False", time(00:00:37),
   "/api/person", "False", time(00:00:47),
   "/api/person", "False", time(00:00:53),
   "/api/person", "False", time(00:00:55),
]
| extend is_failure = success == false
| summarize total_failures = countif(is_failure),
            failure_percentage = 100.0 * countif(is_failure) / count() 
         by apiname
| where total_failures >= 2 and failure_percentage >= 50.0
| project apiname

apiname
/api/person