Post
Ask Question

Azure Data Explorer - Perform Calculation On Multiple Values From Single Kusto Input

Let’s consider a scenario, wherein the requirement is to find out the percentage of a particular type of values from a single input set.
 
Below can be considered as an example of input sample data and we need to find out what percentage of dev releases and what  percentage of prod releases are present in the input data.
  1. let demoData = datatable(Environment: string, Feature:string)  
  2. [  
  3. "dev", "Feature1",  
  4. "test", "Feature1",  
  5. "prod", "Feature1",  
  6. "Dev", "Feature2",  
  7. "test", "Feature2",  
  8. "dev", "Feature3",  
  9. "test", "Feature3",  
  10. "prod", "Feature3"  
  11. ];  

Approach

 
In order to achieve the solution, one has to go through various steps as mentioned below,
 
Step 1
 
Get the total number of records from the set.
  1. let totalRecords = demoData
  2. | count 
  3. | project TotalRecords = Count;  
Step 2
 
Get only those records which are of type ‘dev’
  1. let devRecords = demoData
  2. | where Environment =~ "dev" 
  3. | count 
  4. | project TotalDevRecords = Count;  
Step 3
 
Get only those records which are of type ‘prod’
  1. let prodRecords = demoData
  2. | where Environment =~ "prod" 
  3. | count
  4. | project TotalProdRecords=Count;  
So far we have got all the individual parts. The next task is to combine all the above mentioned 3 steps and generate a single result set and here comes the challenge.
 

Challenge

 
As input set is holding only two columns, there is no common field in all the above mentioned three queries, and as there is no commonality it is significantly difficult to bring such a result set together to form a single result set.
 
Addressing the challenge
 
Can’t we go ahead and introduce some new column just for the sake of projection? Well, let’s see how that changes our above 3 steps now,
 
Updated Step 1
  1. let totalRecords = demoData  
  2. | count |extend CommonCol="Dummy"   
  3. | project CommonCol, TotalRecords = Count;  
Updated Step 2
  1. let devRecords = demoData  
  2. | where Environment =~ "dev"   
  3. | count | extend CommonCol="Dummy"   
  4. | project CommonCol, TotalDevRecords = Count;  
Updated Step 3
  1. let prodRecords = demoData  
  2. | where Environment =~ "prod"   
  3. | count|extend CommonCol="Dummy"   
  4. | project CommonCol, TotalProdRecords = Count;  
Now comes the final step, wherein we need to bring all the above result set together to calculate the percentage.
 
Step 4
 
Combining the individual results to get a single result.
  1. totalRecords  
  2. | join (devRecords | join prodRecords on CommonCol) on CommonCol  
  3. | extend DevRecords = (TotalDevRecords * 100)/TotalRecords  
  4. | extend ProdRecords = (TotalProdRecords * 100)/TotalRecords  
  5. | project DevRecords, ProdRecords;  
Complete query 
  1. let totalRecords = demoData  
  2. | count   
  3. |extend CommonCol="Dummy"   
  4. | project CommonCol, TotalRecords = Count;  
  5. let devRecords = demoData  
  6. | where Environment =~ "dev"   
  7. | count   
  8. | extend CommonCol="Dummy"   
  9. | project CommonCol, TotalDevRecords = Count;  
  10. let prodRecords = demoData  
  11. | where Environment =~ "prod"   
  12. | count|extend CommonCol="Dummy"   
  13. | project CommonCol, TotalProdRecords = Count;  
  14. totalRecords  
  15. | join (devRecords   
  16. | join prodRecords on CommonCol) on CommonCol  
  17. | extend DevRecords = (TotalDevRecords * 100)/TotalRecords  
  18. | extend ProdRecords = (TotalProdRecords * 100)/TotalRecords  
  19. | project DevRecords, ProdRecords;  
Result 
 
On execution of the above steps, you will get the desired output as shown below,
 
 
Hope you enjoyed learning.
 
Happy kustoing.


Similar Articles