Exploring Query Statements In Kusto Queries
Dec 24th, 2021 - written by Kimserey with .
In the last post, we looked at scalar operators in Kusto queries. To complete this mini series on Kusto queries, today we will be looking at useful query statements with examples.
Let Statement
The first statement we will be looking at is the let statement. It allows us to declare variables to be reused in our queries.
Taking back our example with StormEvents:
1
2
3
4
5
6
let n = 10;
let place = "Ohio";
let start = datetime(2007-01-01);
StormEvents
| where StartTime > start and State =~ place
| take n
Here we specify n, place, start as scalar variables which we can use in our query. The syntax is let [name] = [expression];.
let also can be used to declare variables for tabular results. A useful feature to use with in:
1
2
3
4
5
6
7
8
9
let states =
StormEvents
| take 2000
| summarize count() by State
| top 5 by count_
| project State;
StormEvents
| take 2000
| where State in (states)
We can see here that we declared a variable holding the top 5 states that had the most storm events and then take all the storm events from those states.
Lastly let can also be used to assign views. If we want to use views with operators supporting wilcard syntax, we would need to define them explicitly with view:
1
2
3
4
5
6
7
8
9
10
11
12
13
let T1=
view () {
StormEvents
| where State =~ 'Ohio'
| take 2
};
let T2 =
view () {
StormEvents
| where State =~ 'California'
| take 2
};
union T*
Pattern Statement
Lastly we’ll look into the pattern statement of Kusto queries. A pattern is a mapping between a string tuple and a tabular expression.
To define a pattern we use declare pattern and define the string tuple with its associated tabular expression:
1
2
3
4
5
6
declare pattern Data = (tablename:string, state: string)[scope:string]
{
('stormEvents', 'ohio').['events'] = { StormEvents | where State =~ 'ohio' | take 1 };
('stormEvents', 'california').['events'] = { StormEvents | where State =~ 'california' | take 1 };
};
union Data('stormEvents', 'ohio').events, Data('stormEvents', 'california').events
Here we defined a mapping Data with two mappings one for ohio and another one for california. And we see that we can use the mapping in another expression by using the tuple reference.
And that concludes the series on Kusto query! I hope you liked it and I’ll see you on the next post!