Exploring Scalar Operators And Functions Kusto Query For App Insights
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.
Scalar Operators
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- and the order operators
<,<=,>and>=.
1
print 'hello' == 'hello'
will return true.
1
print 'HellO' =~ 'hello'
will also return true as we use the case insensitive operator.
The other logical operators are and and 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 +, -, etc…
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 and !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
String operators provide extra functionalities to check strings or string equivalent.
We have access to the following:
contains,!containsandcontains_cs: test if contains a string case insensitive,_csis used for case insensitivein,in~,!inand!in~: test if the value is in the provided array,~for case insensitive and!for oppositestartswithandstartswith_cs: test that the value starts with the prefix provided
1
2
3
StormEvents
| take 2000
| where State in~ ('florida', 'Ohio')
Scalar Functions
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 providedbin: rounding function rounding down to the closest bin sizecase: conditional function evaluating the predicates and returning the value for the first predicate satisfiedisnotempty: string function used to returntrueif the value is not empty - useful for filtering out rows that do not have the specified columnstrcat: string function used to concatenate strings to build a new stringformat_datetime: used to format datetime to stringformat_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 duration.
And here is an example using case:
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!