Forum Discussion

uditk14's avatar
uditk14
Copper Contributor
Jun 21, 2020

KQL question

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

  • Ofer_Shezaf's avatar
    Ofer_Shezaf
    Icon for Microsoft rankMicrosoft
    Jun 28, 2020

    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.