Solution Engineer

Poskytuji služby v oblasti podnikových informačních systémů. Mým cílem je navrhnout a udržovat spolehlivou infrastrukturu informačních systémů, která pokrývá celou řadu požadavků stanovených IT strategií.

Consultancy Optimalisation Clouds

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)
| where CounterName == „Avg. Disk sec/Red“
and InstanceName == „C:“
| project Computer, TimeGenerated, ObjectName, CounterName, InstanceName, CounterValue
| sort by Computer, TimeGenerated

Sort will sort the output od a query. „Sort by“ could be replaced by „order by“
Perf

| where ObjectName == „LogicalDisk“
and InstanceName matches regex „[A-Z]:“
| project Computer, CounterName, extract(„[A-Z]:“,1,InstanceName)

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“
and InstanceName matches regex „[A-Z]:“
| project Computer, CounterName, extract(„([A-Z]):“,1,InstanceName)

When the second param is 1, it returns just the part in the parenthesis.
Event

| where RenderedDescription startswith „Event code: “
| parse RenderedDescription with „Event code: “ myEventCode
„Event message: “ myEventMessage
„Event time: “ myEventTime
„Event time (UTC): “ myEventTimeUTC
„Event ID: “ myEventID
„Event sequence: “ myEventSequence
„Event occurrence: “ *
| project myEventCode, myEventMessage, myEventTime, myEventTimeUTC, myEventID, myEventSequence

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)