KQL question

%3CLINGO-SUB%20id%3D%22lingo-sub-1479952%22%20slang%3D%22en-US%22%3EKQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EAzureActivity%20%7C%20summarize%20LastActivity%20%3D%20max(TimeGenerated)%20by%20ResourceProvider%2C%20ResourceGroup%20%7C%20join%20kind%20%3D%20innerunique(%20AzureActivity%20%7C%20summarize%20Operations%20%3D%20count()%20by%20ResourceGroup%2C%20ResourceProvider)%20on%20ResourceGroup%2C%20ResourceProvider%20%7Cproject%20ResourceProvider%2C%20ResourceGroup%2C%20Operations%2C%20LastActivity%20%7Csort%20by%20Operations%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20above%20KQL%20is%20used%20to%20print%204%20columns%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20need%20to%20print%20the%20fifth%20column%20as%20well%20that%20highlights%20the%20percentage%20of%20operations%20per%20Resource%20Group%20and%20Resource%20provider.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThere%20have%20to%205%20columns%20in%20the%20result%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EResource%20Provider%2C%20Resource%20Group%2CNumber%20of%20Operations%20(Activities)%2C%20Last%20activity%20time%2C%20Percentage%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494784%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494784%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F693835%22%20target%3D%22_blank%22%3E%40uditk14%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CPRE%3E%3CSPAN%3Elet%20total%20%3D%20toscalar(AzureActivity%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E())%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3EAzureActivity%26nbsp%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20Operations%20%3D%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%2C%20LastActivity%20%3D%20max%20(TimeGenerated)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20ResourceGroup%2C%20ResourceProvider%20%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20ResourceProvider%2C%20ResourceGroup%2C%20Operations%2C%20LastActivity%2C%20precetage%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E1.0%3C%2FSPAN%3E%3CSPAN%3E%20*%20Operations%20%2F%20total%20%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Operations%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20that%20you%20don't%20need%20the%20join%20for%20the%20max%20aggregation%20function%20as%20you%20can%20have%20multiple%20aggregation%20functions%20in%20a%20single%20summarize%20operator.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20practice%2C%20the%20above%20should%20be%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CPRE%3E%3CSPAN%3Elet%20Activity%20%3D%20materialize%20(AzureActivity%20%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20Operations%20%3D%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%2C%20LastActivity%20%3D%20max%20(TimeGenerated)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20ResourceGroup%2C%20ResourceProvider%20%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20ResourceProvider%2C%20ResourceGroup%2C%20Operations%2C%20LastActivity)%3B%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3Elet%20total%20%3D%20toscalar(Activity%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20sum(Operations))%3B%3C%2FSPAN%3E%3CSPAN%3E%3CBR%20%2F%3EActivity%20%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20precetage%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E1.0%3C%2FSPAN%3E%3CSPAN%3E%20*%20Operations%20%2F%20total%20%3CBR%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Operations%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3EWhy%3F%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EWithout%20materialize%20(i.e.%20caching%20partial%20results)%2C%20the%20two%20uses%20of%20AzureActivity%20may%20differ%20as%20their%20run%20is%20slightly%20shifted%20in%20time.%3C%2FLI%3E%0A%3CLI%3EOnce%20I%20did%20materialzie%2C%20I%20wanted%20to%20optimize%20performance%20so%20wanted%20to%20cache%20the%20least%2C%20hence%20breaking%20it%20up%20to%20three%20stages.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional 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.