Kusto Query Language (KQL)
Basics queries in Kusto query language.
Azure demo environment here.
Mostly used Operators:
- Search
- Where
- Take
- Count
- Summarize
- Extend
- Project
- Distinct
- Top
Search
| Perf | search „Memory“ | This searched for a column whose value exactly matched the word Memory. |
| search in (Perf, Event, Alert) „memory“ | A better choice is to limit the search to specific tables. |
| Perf | search CounterName==“Available MBytes“ | Within a table can search a specific column for an exact value. |
| Perf | search CounterName:“MBytes“ | Search for the value anywhere in the text in the specific column, return „Available MBytes“, „Used mBytes“ . |
| Perf | search „*Bytes*“ | Search across all columns using wildcards. |
| Perf | search * startswith „Bytes“ | Begins with Bytes then any text after it. |
| Perf | search * endswith „Bytes“ | Ends with Bytes. |
| Perf | search „Free*Bytes“ | Begins with Free, ends with bytes, anything in beetwin. |
| Perf | search „Free*bytes“ and („C:“ or „D“) | Searches can be combined logically. |
| Perf | search InstanceName matches regex „[A-Z]:“ | Search also supports regular expressions. |
Where
Similar to search, where limits the result set. Rather than looking across columns for values, where limits based on conditions
| Perf | where TimeGenerated >= ago(1h) | Returns all during 1 hour (d – day, h – hours, m – minutes, s – seconds, ms – milliseconds). |
| Perf | where TimeGenerated >= ago(1h) and CounterName == „Bytes Received/sec“ | Can build up the where by adding to it logically. |
| Perf | where TimeGenerated >= ago(1h) and (CounterName == „Bytes Received/sec“ or CounterName == „% Processor Time“) | OR logic is allowed too! |
| Perf | where TimeGenerated >= ago(1h) | where (CounterName == „Bytes Received/sec“ or CounterName == „% Processor Time“) | where CounterValue > 0 |
Stackable where operators. |
| Perf | where * has „Bytes“ | You can simulate search using where. Here it searches all columns in the input for the phrase Bytes somewhere in a column’s value. |
| Perf | where * hassuffix „Bytes“ | At the start. |
| Perf | where * hassuffix „Bytes“ | At the end. |
| Perf | where * contains „Bytes“ | Contains and has behave the same. |
| Perf | where InstanceName matches regex „[A-Z]:“ | Where supports regex as well. |
Take
Take is used to grap a random number of rows from the input data.
| Perf | take 10 | Returns 10 random rows. |
| Perf | limit 10 | Limit is synonym for „Take“. |
Count
| Perf | count | Returns the numbers of rows in the input dataset |
| Perf | where TimeGenerated >= ago(1h) and CounterName == „Bytes Received/sec“ and CounterValue > 0 | count |
Can also use with other filters. |
Summarize
| Perf | summarize count() by CounterName |
Summariaze allows you count number or rows by column using the count() aggregation function. |
| Perf | summarize count() by ObjectName,CounterName |
Can break down by multiple columns. |
| Perf | summarize PerfCount=count() by ObjectName, CounterName |
You can rename the output column for count. |
| Perf | where CounterName == „% Free Space“ | summarize NumberOfEntries=count(), AverageFreeSpace=avg(CounterValue) by CounterName |
With Summarize, you can use other aggregation functions. |
| Perf | summarize NuberOfEntries=count() by bin(TimeGenerated, 1d) |
Bin allows you to summarize into logical groups, like days. |
| Perf | summarize NuberOfEntries=count() by CounterName, bin(TimeGenerated, 1d) |
Can bin at multiple levels. |
Extends
| Perf | where CounterName == „Free Megabytes“ | extend FreeGB = CounterValue / 1000 |
Extend creates c calculated column and adds to the result set. |
| Perf | where CounterName == „Free Megabytes“ | extend FreeGB = CounterValue / 1000, FreeKB = CounterValue * 1000 |
Can extend multiple columns at the same time. |
| Perf | where TimeGenerated >= ago(10m) | extend ObjectName = strcat(ObjectName, “ – „, CounterName) |
Can also use with strcat to create new string colums. |
Project
| Perf | project ObjectName, CounterName, InstanceName, CounterValue, TimeGenerated |
Project allows you to select a subset of columns. |
| Perf | where CounterName ==“Free Megabytes“ | project ObjectName, CounterName, InstanceName, CounterValue, TimeGenerated | extend FreeGB = CounterValue /1000, FreeMB = CounterValue, FreeKB = CounterValue * 1000 |
Combine Project with Extend. |
| Perf | where CounterName ==“Free Megabytes“ | project ObjectName, CounterName, InstanceName, CounterValue, TimeGenerated, FreeGB = CounterValue /1000, FreeMB = CounterValue, FreeKB = CounterValue * 1000 |
Project can simulate the extend. |
| Perf | where TimeGenerated > ago(1h) | project-away TenantId, SourceSystem, CounterPath, MG |
There is a variant called project-away. It will project except the columns listed. |
| Perf | where TimeGenerated > ago(1h) | project-rename myRenamedComputer = Computer |
If you only want to rename a column, then another variant of project is project-rename. It will rename the specified column but then pass the rest of the columns through. |
Distinct
| Perf | distinct ObjectName, CounterName | Return a list of deduplicated values fro columns for the input dataset |
| Event | where EventLevelName == „Error“ | distinct Source |
Distinct can be used to limit a result set. Get a list of all sources that had an error event. |
Top
| Perf | top 20 by TimeGenerated desc | Top returns the first N rows of the dataset when the dataset is sirted by the „by“ clause. |
Scalar Operators
| print „Hello World“ | Print can be used to display output to the result grid. It is primarily a debugging tool. You can use it for static text. |
| print 2 * 3 | More commonly to confirm calculations. |
| print NameOfColumnt = 2 * 3 | You can also name the output column. |
| print now() | Returns the current date/time. |
| print ago(1d) print ago(1h) print ago(1m) print ago(1s) print ago(1ms) print ago(-1d) print ago(-365d) |
Back 1 day Back 1 hour Back 1 minute Back 1 second Tomorrow 1 year in the future |
| Perf
| where TimeGenerated > ago(1h) |
Sort will sort the output od a query. „Sort by“ could be replaced by „order by“ |
| Perf
| where ObjectName == „LogicalDisk“ |
Extract pulls part of a passed in string (the third parameter) base on the regular expression place inside parnethesis. The second param detemines what is returned. A 0 returns whe wholw epression |
| Perf
| where ObjectName == „LogicalDisk“ |
When the second param is 1, it returns just the part in the parenthesis. |
| Event
| where RenderedDescription startswith „Event code: “ |
Evaluates a string expression and parses its value into one or more calculated columns. The calculated columns will have nulls, for unsuccessfully parsed strings. For more information, see the parse-where operator. |
| Perf | where CounterName == „AVG. Disk sec/Read“ | where CounterValue > 0 | take 10 | extend HowLongAgo=( now() – TimeGenerated) | project Computer, CounterName, TimeGenerated, HowLongAgo |
Determine how long ago a counter was generated |
| Perf | where CounterName == „AVG. Disk sec/Read“ | where CounterValue > 0 | take 10 | extend HowLongAgo=( now() – TimeGenerated), TimeSinceStartOfYear=(TimeGenerated – datetime(2019-01-01)) | project Computer, CounterName, TimeGenerated, HowLongAgo, TimeSinceStartOfYear |
Time since a specifig date (i.e. star of the year) |