Dec 17th, 2021 - written by Kimserey with .
Last week we looked at tabular operators in Kusto queries. In today post we will look at the most common used scalar operators and functions with some examples.
Similar to last week, we’ll use the Sample Data Explorer where we get a sample of storm events to demonstrate the different features of Kusto query language.
Starting from scalar operators, the most common operators are the logical operators:
==: testing for equality
!=: testing for non equality
=~: testing for equality case insensitive
!~: testing for non equality case insensitive
1 print 'hello' == 'hello'
1 print 'HellO' =~ 'hello'
will also return
true as we use the case insensitive operator.
The other logical operators are
or to compose queries.
Most of the time those will be used in the
where tabular operator:
1 2 3 StormEvents | sort by StartTime desc | where State =~ 'FloRIdA' or State =~ 'Ohio'
There are arithmetic operators supported for numerical values, for example
1 print 1 + 10
and there are also arithmetic operators supported for datetime and timespan:
1 print timespan(1h) + 100ms
will add 100ms to the timespan.
1 print datetime(2021-01-01T15:00) + 1d - 1h
will add one day to the datetime and remove one hour.
Thanks to those operators, we also have access to
!between which will check if the value is between or not between the specified range.
1 print datetime(2021-01-01) between (datetime(2020-01-01) .. datetime(2022-01-01))
Note that there are spaces around the double dots.
String operators provide extra functionalities to check strings or string equivalent.
We have access to the following:
contains_cs: test if contains a string case insensitive,
_csis used for case insensitive
!in~: test if the value is in the provided array,
~for case insensitive and
startswith_cs: test that the value starts with the prefix provided
1 2 3 StormEvents | take 2000 | where State in~ ('florida', 'Ohio')
Kusto query also provides scalar functions which can be used to generate scalar values. The most commonly used are:
ago: will return the datetime substracting from now the timespan provided
bin: rounding function rounding down to the closest bin size
case: conditional function evaluating the predicates and returning the value for the first predicate satisfied
isnotempty: string function used to return
trueif the value is not empty - useful for filtering out rows that do not have the specified column
strcat: string function used to concatenate strings to build a new string
format_datetime: used to format datetime to string
format_timespan: uused to format timestamp to string
In the last post we had a good example using scalar functions:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 StormEvents | sort by StartTime desc | where State == 'FLORIDA' | extend duration = EndTime - StartTime | extend duration_format = strcat( format_timespan(duration, 'd'), ' days ', format_timespan(duration, 'H'), ' hours ', format_timespan(duration, 'm'), ' minutes' ) | project EventId, State, duration_format, StartTime, EndTime | take 10
Here we use
strcat to create a human readable string with the timespan
And here is an example using
1 2 3 StormEvents | extend damage = case(DamageProperty > 250000, 'high', DamageProperty between (10000 .. 250000), 'medium', 'low') | project EventId, damage
We create 3 buckets where we classify the damages from high to low.
And that concludes today’s post, I hope you liked this post and I’ll see you on the next one!