8 Useful functions and techniques of Kusto language

Kusto is a superb query language.
It’s comfortable to get data by a complex set of conditions using it, as the syntax is something like a combination of python and SQL.
There are some syntactic sugars and techniques you must use —

Avoid “or hell” using the “in” operator.

Instead of check each condition separately:

metrics
| where (name ==“cpu-usage” 
 or name==“memory-usage” 
 or name == "memory-leak")
 and value >90

You can use the “in” keyword that way:

metrics
| where name in(“cpu-usage”, “memory-usage”, "memory-leak")
  and value >90

Aggregation of data using summarize operator

Trendy kusto function, and a great tool for complex aggregations.
The basic syntax is:

metrics
| summarize [function(optional parameter here) list] by [parameter list]

Examples:
If you want to get the maximum CPU-usage of each service, you can do:

metrics
| where name=="cpu-usage"
| summarize max(value) by service_name

Or, if you want to check the minimum and maximum per service and 1 hour:

metrics
| where name=="cpu-usage"
| summarize max(value), min(value) by service_name bin(timestamp, 1h)

Some famous summarize functions: count(), sum(), min(), max(), dcount() etc.

ingestion_time() for delay checking

ingestion_time is a scalar function that returns the approximate time at which the current record was ingested.
To check delay between the ingestion time to the time, it was written:

| extend delay = timestamp — ingestion_time()

To see the value in seconds/minutes/hours, you can divide it with the time unit you want:

| extend delay = (timestamp — ingestion_time())/1m

Referring data point as constants using “toscalar”

For illustration, if we want to know if less than 20 percents of users were in the shopping page clicked on the cart, we can simply:

let customers_in_shopping_page= toscalar(events |
 where name=="shopping_page" 
| count);
events
| where name == "cart" 
| count
| where Count <customers_in_shopping_page * 0.2

Deconstruct strings using the “split” array function

Converts a string into a list. Instrumental when you have structured lines, such as keys compounded from structured data, with parameters you want to extract:

products
| extend barcode_parts = split(barcode,".")
| extend product_key= barcode_parts[0], product_size = barcode_parts[1], product_place = barcode_parts[2]

Catch more instances at once using “contains.”

When you have a couple of columns that can adapt to the same structure, you can use “contains.” For example, let’s say there are known exceptions’ set you would like to get in one query, for instance: “bug in FlowA: bla bla bla,” “bug in FlowA bli bli bli”;
You can retrieve all in once:

errors
| where message contains "bug in flowA"

Get difference between times using datetime_diff

Great when you want to measure the difference between to times:

let finished_first_flow = events 
| where name ==flow_a" and customDimensions.some_id =="abc"
| summarize max(timestamp);
let finished_second_flow = events 
| where name ==flow_b" and customDimensions.some_id =="abc"
| summarize max(timestamp);
print datetime_diff('minute',finished_first_flow, finished_second_flow)

Notice you can control the format you get the data: seconds, minutes, hours, etc.

Calculate multiple conditions using array_iif function

Let’s say you have a couple of conditions you want to check.
When using “extend iif” repeatedly, you risk your query being wordy and not readable.
Look at this:

let a = 1;
let b =2;
let c=3;
DbInstance
| extend the_bigger_a_or_b=iif(a>b, a, b),  the_bigger_a_or_c=iif(a>c, a, c), the_bigger_b_or_c=iif(b>c, b, c);

VS this:

let a = 1;
let b =2;
let c=3;
DbInstance
| extend res=array_iif(dynamic([a>b,a>c,b>c]), dynamic([a,a,b]), dynamic([b,c,c]);