# 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.

• contains, !contains and contains_cs: test if contains a string case insensitive, _cs is used for case insensitive
• in, in~, !in and !in~: test if the value is in the provided array, ~ for case insensitive and ! for opposite
• startswith and startswith_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 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 true if 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 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!

## External Sources

Designed, built and maintained by Kimserey Lam.