Wednesday, November 18, 2020

Perform Calculation On Multiple Values From Single Kusto Input

Let’s consider a scenario, wherein requirement is to find out the percentage of a particular type of values from the single input set.

Below can be considered as an example of input sample data and need is to find out how much percentage of dev releases and how much 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 total number of records from input 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 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; 

On execution of the above steps, you will get the desired output as shown below:





Hope you enjoyed learning. 

Happy kustoing.

Thursday, November 12, 2020

Working with Kusto Case Sensitivity

Like most of the other programming and query languages, Kusto too has sense of case sensitivity, which means, it can deal with upper-case and lower-case while performing comparisons between values.

Let’s consider below sample 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. ];

Case Sensitive Comparison

The Case sensitive means match should be exact, upper-case letter must match with upper-case only and same for lower-case. Whenever the match is performed between an upper-case character and a lower-case character, query would return false, although both the characters are same. For example, dev and Dev are not same.

Query description

Get list of features, which belongs to dev environment.

Query

  1. demoData| where Environment == "dev"  
As “==” stands for case sensitive comparison, above query will result in below output:








Case Insensitive Comparison

Case insensitive comparison behaves in completely opposite fashion as case sensitive comparison does. Whenever the match is performed between an upper-case character and a lower-case character, query would return true, as long as both the characters are same. For example, dev and Dev are same.

Now, to achieve this behavior there are multiple approaches.

Approach 1

In this approach, one can first convert the string using toupper(…) or tolower(…) functions and then perform the comparison as shown below:

  1. demoData| where tolower(Environment) == "dev"  

Approach 2

In this approach, no need to call any extra function as inbuild operator will do this for us as shown below:

  1. demoData| where Environment =~ "dev"  

Here “=~” performs the for case-insensitive comparison. 

Execution of both the above queries result in same output as shown below:

 


Performance Tip

  • Always prefer case-sensitive over case-insensitive, wherever possible.
  • Always prefer has or in over contains.
  • Avoid using short strings as it impacts indexing. 

Happy kustoing!