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.


  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!

Tuesday, October 27, 2020

Azure Data Explorer - Kusto Query - Transform Rows To Columns

In my previous post, I discussed about getting the result set which lies between the given date range. This time, let’s take an another interesting example, wherein we need to transform the number to rows into number of columns as our result set.

Consider below data for which we need to write query:

  1. let demoData = datatable(Environment: string, Feature:string, Location:string, Version: string)    
  2. [      
  3.    "dev""Feature1""Site1""v1",      
  4.    "test""Feature1""Site2""v2",      
  5.    "prod""Feature1""Site3""v3",     
  6.    "dev""Feature2""Site1""v4",      
  7.    "test""Feature2""Site4""v5",      
  8.    "dev""Feature3""Site1""v6",      
  9.    "test""Feature3""Site2""v7",      
  10.    "prod""Feature3""Site3""v7"      
  11. ];

Query description

Generate result set in such a way that there should be exactly one row for each Feature.


  1. let versionList = my_data  
  2. | summarize d = make_bag(pack(strcat(Environment,"Version"), Version)) by Feature  
  3. | evaluate bag_unpack(d);  
  4. let locationList = my_data  
  5. | summarize d = make_bag(pack(strcat(Environment,"Location"), Location)) by Feature 
  6. | evaluate bag_unpack(d);  
  7. versionList  
  8. join locationList on Feature  
  9. | project-away Feature1   

Now if you run the query, you will get the below output:

In terms of expectation, the result looks good, but let’s make it more readable by moving location and version next to each other. 

This can be achieved by appending another pipe for project-reorder and it would change our query to something like this:

  1. let versionList = my_data  
  2. | summarize d = make_bag(pack(strcat(Environment,"Version"), Version)) by Feature  
  3. | evaluate bag_unpack(d);  
  4. let locationList = my_data  
  5. | summarize d = make_bag(pack(strcat(Environment,"Location"), Location)) by Feature 
  6. | evaluate bag_unpack(d);  
  7. versionList  
  8. join locationList on Feature  
  9. | project-away Feature1  
  10. | project-reorder Feature , * asc 

Now, if you run above query, you will see the output as shown below:

I hope you enjoyed this data transformation query.

Happy kustoing!

Tuesday, October 20, 2020

Azure Data Explorer - Kusto Query - Get data for last 20 days

In continuation to my previous post on Get Min/Max from each category, this time let’s do one of the most demanded queries with filter criteria on date time field.

For the purpose of simplicity and keep this article more focused, I have removed data from all the additional columns as shown below:





2020-10-02 00:00:00:0000000

2020-10-21 00:00:00:0000000

2020-10-21 00:00:00:0000000

2020-10-21 00:00:00:0000000

2020-10-21 00:00:00:0000000

2020-10-22 00:00:00:0000000

2020-10-22 00:00:00:0000000

Query description

The idea is to fetch all the records, which occurred in past 20 days of GenerationDate.


Now to achieve our expected result, there could be more than a way.  One way could be:

Approach 1

Find out the date which fall exactly twenty days back using ago(…) and then use conditional operator (<= and >=) to achieve this result. 

Above approach would work perfectly but the problem with this approach is lot many lines of code and calculation.

Approach 2

The other approach having very less lines of code could be using the between(…) as shown below:

  • DemoData   
  • where (todatetime(GenerationDate) – now()) between(0d…20d);     
  • NOTE: Make sure to do the proper datetime cast else you may end up getting an error ‘Arithmetic expression cannot be carried-out between StringBuffer and DateTime’.

    Happy kustoing!

    Friday, October 16, 2020

    Tips for Effective Code Reviews

    In the field of software development, code review plays a very vital role. It not only enhances the code quality, but also identifies issues before the code goes into the tester’s hand.

    Throughout my development experience, I came across many amazing guidelines that can contribute to an effective code review.

    So, as part of this article I’m going to list down all those points.

    Naming Conventions, Data types, Sizes and Comments

    • Variable names should be small but self-explanatory. Based on the language and platform, standards and casing should be followed.
    • Always prefer field names using adjective or noun.
    • Prefer to use PascalCasing for resource keys.
    • Always prefer to name events with a verb. It’s good to think about tense while naming events, i.e. rendered, rendering, etc.
    • Keep method size as small as possible. I personally prefer, size of method body of <=12 lines. So, that complete method can be seen without scrolling the screen.
    • All the parameters should pass validation check before passing to any method.
    • Keep class size as small as possible. If it is not possible to achieve this due to business requirement and design, then preferred way is to go for partial classes.
    • Comments should present at class, interface and at method level. If logic inside a method is complex and not self-explanatory, it’s good to add few lines of comment inside the method also.
    • Proper indentation plays a very big role in the readability of any source code. So, never ignore this.
    • While working with expression trees, prefer anonymous types over tuples as you may have a need to rename properties.
    • Avoid boxing and unboxing as much as possible. It’s always good to go with concreate data types.
    • Prefer using StringBuilder over String class, whenever situation demands high concatenation operation.
    • Prefer lazy loading and initialization as it would increase memory footprint. Do only when it is really required.
    • Avoid extending ValueType
    • Prefer enums over static constants.
    • Avoid using enum for storing single value.

    Object Construction

    • Never define a public constructor in the abstract class as public constructor is useful only if an instance has to be created.
    • Avoid doing much work inside constructor, apart from initializations.
    • Avoid using static classes unless there is a specific need.

    Object Destruction

    • Make sure to take care of memory allocation and deallocation.
    • Avoid using Finalize method for managed objects as it would be released automatically by garbage collector. Recommended approach would be to implement IDisposable.

    Termination/Exit Criteria and Exception Handling

    • There should be a single exit point for any function. Excessive use of return statements in a function body should strictly be restricted.
    • Always add Default case to switch case statement.
    • Always add else, if using if-elseif.
    • Anyone can call your code without asking you. So, always handle NULL checks.
    • Prefer Using statement to automatically cleanup resources, that implements IDisposable.
    • Use Finally block to clean up resources that don’t implement IDisposable.
    • Need proper exception handling with more generic exception types on top.
    • Prefer to throw exceptions, iff you do not want error to be unnoticed. In other words, it is always better to avoid throwing exceptions for simple scenarios.


    • All the classes that needs to be serialized must be marked with attribute Serializable. Once class is marked as serializable, all it’s public members will automatically be serialized. For any private member to be serialized, it has to be explicitly decorated with attribute SerializeField.
    • Serialize only the required members rather than serializing the complete class.
    • While working with serialization, map all the properties explicitly as serialized name. It won’t break the code in case if anyone renames any property in future.
    • Prefer a class or a struct over anonymous types or tuple types, for serialization.


    Avoid using Thread.Abort for thread termination.

    Avoid using Thread.Suspend and Thread.Resume to synchronize threads. Better way to achieve similar behavior is to use Mutex and Monitor.

    Avoid synchronization as it decreases performance and maximizes the likelihood of deadlocks.

    Prefer Interlocked class over lock statement.

    Code Structuring

    Implement proper design patterns wherever possible.

    Prefer parallelism.

    Make intelligent decision between Abstract class and Interface.

    Do not add members to already released/shipped interface as it will create versioning problems.

    Inheritance hierarchy should be chosen very carefully as badly implemented inheritance could be very dangerous. I personally avoid inheritance as much as I can, specially beyond 2 levels.

    Reflection and Caching

    Try to avoid reflection as much as possible.

    Analyze caching scenarios very carefully as not all scenarios are suitable for caching.

    Database Practices

    Prefer stored proc over inline SQL queries.

    Be wise while taking indexing decisions. There are some tools (Database Tuning Advisor), which also provides indexing suggestions.

    Code Analysis and Coverage

    Code should be error and warning free.

    Using code analysis tool can help in identifying many of the common mistakes. I prefer the one, which comes with Visual Studio.

     Keep an eye on various code metrics, i.e. cyclomatic complexity, depth of inheritance, cyclomatic complexity, loc, etc. If any of these are beyond certain levels, then refactoring has to be taken up. 

    A good code coverage can tell, how maintainable the code is. Hence always prefer to write unit test cases, which will also help in identifying unused and unreachable code blocks.

    Last but not the least, there are many very well-known coding principles exists, which one can follow:

    DRY – Don’t Repeat Yourself, KISS – Keep It Simple Stupid, Hollywood Principle – Don’t call us , we’ll call youSOLIDYAGNI – You Are not Gonna Need It

    Proper code review helps in slashing maintenance cost of the software and it should be part of day-to-day activities.

    Hope you enjoyed this checklist.

    Back to You

    This post is just the start and there is lot more which can be added.

    Please share your thoughts and comments, so that I can make this list more extensive.