Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

KQL question

Copper Contributor

AzureActivity | summarize LastActivity = max(TimeGenerated) by ResourceProvider, ResourceGroup | join kind = innerunique( AzureActivity | summarize Operations = count() by ResourceGroup, ResourceProvider) on ResourceGroup, ResourceProvider |project ResourceProvider, ResourceGroup, Operations, LastActivity |sort by Operations

 

The above KQL is used to print 4 columns

I need to print the fifth column as well that highlights the percentage of operations per Resource Group and Resource provider. 

There have to 5 columns in the result

Resource Provider, Resource Group,Number of Operations (Activities), Last activity time, Percentage

 

 

1 Reply

@uditk14 

 

Try:

 

let total = toscalar(AzureActivity | summarize count());
AzureActivity 
| summarize Operations = count(), LastActivity = max (TimeGenerated) by ResourceGroup, ResourceProvider
| project ResourceProvider, ResourceGroup, Operations, LastActivity, precetage = 1.0 * Operations / total
| sort by Operations

 

Note that you don't need the join for the max aggregation function as you can have multiple aggregation functions in a single summarize operator. 

 

In practice, the above should be:

 

let Activity = materialize (AzureActivity 
| summarize Operations = count(), LastActivity = max (TimeGenerated) by ResourceGroup, ResourceProvider
| project ResourceProvider, ResourceGroup, Operations, LastActivity);
let total = toscalar(Activity | summarize sum(Operations));
Activity
| extend precetage = 1.0 * Operations / total
| sort by Operations

Why?

  • Without materialize (i.e. caching partial results), the two uses of AzureActivity may differ as their run is slightly shifted in time.
  • Once I did materialzie, I wanted to optimize performance so wanted to cache the least, hence breaking it up to three stages.