Maand: mei 2021

Categorieën
Distinct & Top

KQL – Distinct & Top

Distinct returns a deduplicated column results

Perf
| distinct ObjectName, CounterName

//Limit results exampe (only return unique event errors sources)
Event
| where EventLevelName == "Success"
| distinct Source

Perf
| where CounterName == "Total Bytes Received"
| distinct InstanceName

Top return the fist N rows of a column

Perf
| top 20 by TimeGenerated desc

//combine serveral functions - get a list of computers that are low on disk space
Perf
| where CounterName == "Free Megabytes"
and TimeGenerated >=ago(1h)
|project Computer
, TimeGenerated
, CounterName
, FreeMegaBytes =CounterValue
|distinct Computer
, TimeGenerated
, CounterName
, FreeMegaBytes
| top 25 by FreeMegaBytes asc

Categorieën
Project

KQL – Project

Project allows you to select a subset of columns

//adds a calculated column to the result set
Perf
| where CounterName == "Free Megabytes"
|extend FreeGB = CounterValue / 1000

Perf
| project ObjectName
, CounterName
, InstanceName
, CounterValue
, TimeGenerated

//Combine project with extend
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
| extend FreeGB = CounterValue / 1000
, FreeMB = CounterValue
,FreeKB = CounterValue * 1000

//Hide the CounterValue column in the results
Perf
| where CounterName == "Free Megabytes"
| extend FreeGB = CounterValue / 1000
, FreeMB = CounterValue
,FreeKB = CounterValue * 1000
| project ObjectName
, CounterName
, InstanceName
, TimeGenerated

//Use project to build extend columns directly
Perf
| where CounterName == "Free Megabytes"
| project ObjectName
, CounterName
, InstanceName
, CounterValue
, TimeGenerated
, FreeGB = CounterValue / 1000
, FreeMB = CounterValue
, FreeKB = CounterValue * 1000

//remove columns from result set
Perf
| where TimeGenerated > ago(1h)
| project-away CounterValue
, SourceSystem
, CounterPath
, MG

//Renaming a column
Perf
| where TimeGenerated > ago(1h)
| project-rename myRenamedComputer = Computer

Categorieën
Extend

KQL – Extend

Allows to add calculated column to your result set

//adds a calculated column to the result set
Perf
| where CounterName == "Free Megabytes"
|extend FreeGB = CounterValue / 1000

//create multiple columns at the same time
Perf
| where CounterName == "Free Megabytes"
|extend FreeGB = CounterValue / 1000
, FreeKB = CounterValue * 1000

//repeat a column
Perf
| where CounterName == "Free Megabytes"
|extend FreeGB = CounterValue / 1000
, FreeMB = CounterValue
, FreeKB = CounterValue * 1000

//create new string in column
Perf
| where TimeGenerated >=ago(10m)
| extend ObjectCounter = strcat(ObjectName, " - ", CounterName)

Categorieën
Summarize

KQL – Summarize

Create an aggregated value based by a provided function

//provides count per CounterName value
Perf
| summarize count() by CounterName

Example using multiple columns

//provides count per ObjectName and CounterName value
Perf
| summarize count() by ObjectName, CounterName

Example with renaming the default _count column

//provides count per ObjectName and CounterName value adds a renamed _count column
Perf
| summarize PerfCount=count()
by ObjectName, CounterName

Example leveraging the average function

Perf
| where CounterName == "% Free Space"
| summarize NumberofEntries=count()
, AverageFreeSpace=avg(CounterValue)
by CounterName

Summarize into logical groups

// Bin used to summarize into local groups, like days
Perf
| summarize NumberOfEntities=count()
by bin(TimeGenerated,1d)

//Bin used to group by mulitple levels - Count amount of entries and group by CounterName
Perf
| summarize NumberOfEntitites=count()
by CounterName
, bin(TimeGenerated, 1d)

Categorieën
Count

KQL – Count

Returns the number of rows

Perf
| where TimeGenerated >= ago(1h)
and CounterName == "Bytes Received/sec"
and CounterValue > 0
| count

Categorieën
Take & Limit

KQL – Take & Limit

Take takes a random amount of record from the date set

Perf
| where TimeGenerated >= ago(1h)
and CounterName == "Bytes Received/sec"
and CounterValue > 0
| take 5

Limit is the same and can be used to limit the amount of records returned

Perf
| where TimeGenerated >= ago(1h)
and CounterName == "Bytes Received/sec"
and CounterValue > 0
| limit 5

Categorieën
Where

KQL – Where

Where limits the result set

Search Table for data generated since a 1 hour ago

Perf
| where TimeGenerated >= ago(1h)

ago allows you to select relative date ranges

  • d – days
  • h – hours
  • m – minutes
  • s – seconds
  • ms – milliseconds
  • microsecond – microseconds

Combine where with AND statement

Perf
| where TimeGenerated >= ago(1h)
and CounterName == "Bytes Received/sec"
and CounterValue > 0

Combine where with OR statement

Perf
| where TimeGenerated >= ago(1h)
and (CounterName == "Bytes Received/sec"
or
CounterName == "% Processor Time"
)
and CounterValue > 0

Stacking where operators (useful for in between processing (if/else etc.)

Perf
| where TimeGenerated >= ago(1h)
| where (CounterName == "Bytes Received/sec"
or
CounterName == "% Processor Time"
)
| where CounterValue > 0

Simulate search with where command

//Search any column for string containing Bytes

Perf
| where * has "Bytes"

//Search any column that starts with the string Bytes
Perf
| where * hasprefix "Bytes"

//Search any column that ends with the string Bytes
Perf
| where * hassuffix "Bytes"

//Search any column that contains with the string Bytes
Perf
| where * contains "Bytes"

//Search any string in a column using regex
Perf
| where InstanceName matches regex "[A-Z]:"

Categorieën
Search

KQL – Search

String search through all tables (do not use in production!)

search "memory"

Limit string search to a specific range of tables

search in (Perf, Events) "memory"

Limit string search to one table

Perf| search "memory"

Search for exact string "Available MBytes"

Perf | search CounterName == "Available MBytes"

Search for string in column that contains MBytes

Perf | search CounterName:"MBytes"

Search for string in all columns containing string Bytes

Perf | search "Bytes"

Search for string in all columns starting with Bytes

Perf | search * startswith "Bytes"

Search for string in all columns ending with Bytes

Perf | search * endswith "Bytes"

Search for string in all columns that starts with Free and ends with Bytes

Perf | search "Free*bytes"

Search for string in all columns that starts with Free and ends with Bytes that contain either C: or D:

Perf | search "Free*bytes" and ("C:" or "D:")

Search table column for string using regex

Perf
| search InstanceName matches regex "[A-Z]:"