How to reuse the result from countif() within the summarize clause

109 views Asked by At

Is there a way not to repeat or repurpose the result of statement US = countif(Location == "US") within the summary clause in the KQL below? using USPercentage = round(100.0 * US / count(), 2) does not work and gives summarize' operator: Failed to resolve scalar expression named 'US' error . Is it mandatory to use same statement twice? If yes, then why?

SigninLogs
| project TimeGenerated, CorrelationId, Location
| summarize
    US = countif(Location == "US"),
    Outside_US = countif(Location != "US"),
    USPercentage = round(100.0 * countif(Location == "US") / count(), 2),
    NonUSPercentage = round(100.0 * countif(Location != "US") / count(), 2),
    Total = count()
    by bin(TimeGenerated, 1h)
1

There are 1 answers

1
Gyp the Cat On

By my understanding Kusto needs to run the entire summarize since the input data may change the output. In other words aggregating across the whole dataset.

But as you allude to not repeating the same calculation twice in the summarize could be good for performance especially if your input data set is large.

Given that and as you likely know you can always extend based on the colums you're creating, for instance this is adding in USPercentage2 which :

SigninLogs
| project TimeGenerated, CorrelationId, Location
| summarize
    US = countif(Location == "US"),
    Outside_US = countif(Location != "US"),
    USPercentage = round(100.0 * countif(Location == "US") / count(), 2),
    NonUSPercentage = round(100.0 * countif(Location != "US") / count(), 2),
    Total = count()
    by bin(TimeGenerated, 1h)
| extend USPercentage2 = round(((100.0 * US) / Total), 2)