/kusto-queries

example queries for learning the kusto language

MIT LicenseMIT

Kusto-queries

Example queries for learning the Kusto Query language in Azure Data Explorer. Kusto can be used in Azure Monitor Logs, Application Insights, Time Series Insights and Defender Advanced Threat Perception.

Azure Data Explorer is a Microsoft service for analysing log and telemetry data. You can use it to log events in your mobile applications and for monitoring devices enrolled in a company network.

You can start learning KQL at https://portal.loganalytics.io/demo

Useful learning resources

Contents

Searching

Search across all datasets

search "event name" | take 10

We can use the take command to limit our search to 10 search results. This speeds up our querying substantially. Kusto queries can take a long time to execute if the datasets are large. To avoid this, use the take command before running queries on a full dataset.

The timeout can take anything from 10 seconds up to 30 minutes. You can cancel your query if you don't want to wait, or allow the query to run and open a new query in a new tab if you need it.

Searching in a single dataset

Perf | search "Contoso"

Searching in multiple datasets

search in (Perf, Event, Alert) "Contoso" | take 10

Searching in specific columns for a match

Perf
| search CounterName=="Available MBytes" | take 10

Searching for a term anywhere in text

Perf
| search "*Bytes*" | take 10

Searching for columns that begins with

Perf
| search * startswith "Bytes" | take 10

Searching for columns that ends with

Perf
| search * endswith "Bytes" | take 10

Search starts with specific term, anything in between and ends with a specific term

Perf
| search "Free*bytes" | take 10

Combining searches

Multiple terms

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

Using regular expressions

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

Time and timerange

Dynamic timerange

| where timestamp between (datetime(2019-01-01T00:01:24.615Z)..now())

Where

Perf
| where TimeGenerated >= ago(1h)

Note the time range for the query is automatically set in the query when we use time operators in our where clause

Using the and statement

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

Using the or statement

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

Stacking multiple where clauses

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

Other uses for where - simulating search with where

Perf
| where * has "Bytes" | take 10

Positional matching with where

Matches words beginning with Bytes

Perf
| where * hasprefix "Bytes" | take 10

Matches words ending with Bytes

Perf
| where * hassuffix "Bytes" | take 10

Using contains

Perf
| where * contains "Bytes" | take 10

Using regular expressions with where

Perf
| where InstanceName matches regex "[A-Z]:" | take 10

Take and Limit command

Take retrieves a random number of rows, f.ex

Perf
| take 10

Takes 10 rows

Limit is the equivalent

Perf
| limit 10

Count operator

Count rows in a table

Perf
| count

Counts all rows in the Perf dataset

Count can be used with filtering to count rows in a selected table

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

Summarize

Aggregation function for summarizing data in a table, can include by

Perf
| summarize count()  by CounterName

Summarizing multiple columns

Perf
| summarize count()  by ObjectName, CounterName

Note it generates a third column called count_. The name is automatically generated.

If we want to supply a more suitable name as a variable, we can name it

Perf
| summarize PerfCount=count()
            by ObjectName, CounterName

With Summarize, use other aggregation functions

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

Using Bin to create logical groups

Perf
| summarize NumberOfEntries=count()
            by bin(TimeGenerated, 1d)

Using other values for binning

Perf
| where CounterName == "% Free Space"
| summarize NumberOfRowsAtThisPercentLevel=count()
            by bin(CounterValue, 10)

Extend

Extend allows you to create calculated columns to add to your tables

Perf
| where CounterName == "Free Megabytes"
| extend FreeGB = CounterValue / 1000

You can also create multiple columns

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

Repeating a column

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

Create new string values

We can create a new column with string values

Perf
| where TimeGenerated >= ago(10m)
| extend ObjectCounter = strcat(ObjectName, " - ", CounterName)

We use strcat to concatenate strings

Project command

Project allows us to select which columns we want in our table

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

Project and Extend are very useful when creating tables with our specific data.

Perf
| project ObjectName
        , CounterName 
        , InstanceName 
        , CounterValue 
        , TimeGenerated
| extend FreeGB = CounterValue / 1000
        , FreeMB = CounterValue 
        , FreeKB = CounterValue * 1000

If we want to omit a specific column we must calculate our values first, then project afterwards

Perf
| extend FreeGB = CounterValue / 1000
        , FreeMB = CounterValue 
        , FreeKB = CounterValue * 1000
| project ObjectName
        , CounterName 
        , InstanceName 
        , TimeGenerated
        , FreeGB 
        , FreeMB 
        , FreeKB

OR we can just project our data and calculate it on the fly

Perf
| where CounterName == "Free Megabytes"
| project ObjectName
        , CounterName 
        , InstanceName 
        , TimeGenerated
        , FreeGB = CounterValue / 1000
        , FreeMB = CounterValue 
        , FreeKB = CounterValue * 1000

Project-away lets you remove selected columns from the output

Perf
| where CounterName == "Free Megabytes"
| project-away TenantId
            , SourceSystem 
            , CounterPath 
            , MG

Project-rename lets you rename a specific column

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

Distinct

Retrieves a list of deduplicated values

Perf
| distinct ObjectName, CounterName

Only show unique error events

Event
| where EventLevelName == "Error"
| distinct Source

Using top for the first 20 rows

Perf
| top 20 by TimeGenerated desc

Sort rows ascending with asc or descending with desc

Combining everything so far

Perf
| where CounterName == "Free Megabytes"     // Get the free Megabytes
        and TimeGenerated >= ago(1h)        // ... within the last hour
| project Computer                          // For each return the Computer Name
        , TimeGenerated                     // ...and when the counter was generated
        , CounterName                       // ...and the Counter Name
        , FreeMegaBytes=CounterValue        // ...and rename the counter value
| distinct Computer                         // Now weed out the duplicate rows as
        , TimeGenerated
        , CounterName                       // ...the perf table will have
        , FreeMegaBytes                     // ...multiple entries during the day
| top 25 by FreeMegaBytes asc               // Filter to most critical ones

Scalar operators

Scalar operators allow us to format and transform data, and logical operators for "if then" logic.

Print retrieves an output that is defined or calculated

print "Hello world"

This command is useful for debugging a calculation stepwise

You can also name the output

print something="something"

Get the time in UTC. This is the standard in all Azure logs. We can use it for any changes to date and time data.

print now()

Print time one hour ago

print ago(1) // 1h 1m 1d 1s 1ms 1microsend 1tick

Print time in the future

print ago(-1d) // print time tomorrow, -365d gives us a year in the future

This lets us retrieve values for a specific timerange when combined with other clauses such as where and other operators.

Sort lets us sort the columns as desired

Perf
| where TimeGenerated > ago(15m)
| where CounterName == "Avg. Disk sec/Read"
        and InstanceName == "C:"
| project Computer 
        , TimeGenerated 
        , ObjectName 
        , CounterName 
        , InstanceName 
        , CounterValue 
| sort by Computer
        , TimeGenerated

Sort by ascending

Perf
| where TimeGenerated > ago(15m)
| where CounterName == "Avg. Disk sec/Read"
        and InstanceName == "C:"
| project Computer 
        , TimeGenerated 
        , ObjectName 
        , CounterName 
        , InstanceName 
        , CounterValue 
| sort by Computer asc
        , TimeGenerated asc

You can also mix sorting clauses

Perf
| where TimeGenerated > ago(15m)
| where CounterName == "Avg. Disk sec/Read"
        and InstanceName == "C:"
| project Computer 
        , TimeGenerated 
        , ObjectName 
        , CounterName 
        , InstanceName 
        , CounterValue 
| sort by Computer asc
        , TimeGenerated

order by is an alias for sort by

String operators

String operators let us match a specifc string, take into account case sensitivity and use affixes to match several strings in sequence.

Extending our previous queries we can supplement with an additional Equals operator that is case-insensitive by using =~. This is useful when a log can contain multiple entries with different casing.

Perf
| where TimeGenerated > ago(15m)
| where CounterName == "Avg. Disk sec/Read" and InstanceName =~ "SDA"  
| take 10

The affixes in Kusto offer very specific string matching, including prefix, suffix for the beginning and ending of a string respectively

Perf
| where TimeGenerated > ago(15m)
| where CounterName == "Avg. Disk sec/Read" and Computer hassuffix "cluster-master" 
| take 10

There are also sequential matches and negated matches

Perf
| where TimeGenerated > ago(15m)
| where CounterName == "Avg. Disk sec/Read" and Computer hasprefix "gangams-kind" 
| take 10

Negated matches also work, but are slower

Perf
| where TimeGenerated > ago(15m)
| where CounterName == "Avg. Disk sec/Read" and Computer !hasprefix "gangams-kind" 
| take 10

If we are looking for the presence of a specific term it is much faster to look it up using has and in, than using contains, startswith and endswith.

Perf
| where TimeGenerated > ago(15m)
| where CounterName == "Avg. Disk sec/Read" and InstanceName has "SDA"  
| take 10

Microsoft's official example of this is to compare these queries

EventLog | where continent has "North" | count;
EventLog | where continent contains "nor" | count

The reason the first query runs faster is because Kusto indexes all columns including those of type string. Words consisting of over 4 characters are treated as terms. These are transformed into sequences of alphanumeric characters, and therefore an exact match can be run much faster on these words.

See the String Operators documentation for more information on this feature. It will help you choose different operators to increase performance of your queries.

Extract

Extract will match a string based on a regular expression pattern, and retrieves only the part that matches.

Perf
| where ObjectName == "LogicalDisk"
        and InstanceName matches regex "[A-Z]:" 
| project Computer 
        , CounterName 
        , extract("[A-Z]:", 0, InstanceName)

Extract only the disk name without the colon

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

Parse

Parse takes a text string and extracts part of it into a column name using markers

Parse runs until it has parsed the entire dataset or reached the final match we've specified.

Parse is very useful when you have large blobs of text you want to turn into standard components

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

datetime arithmetic

Convert a string into a datetime for our query - for year to date, using datetime

Perf
| where CounterName == "Avg. Disk sec/Read"
| where CounterValue > 0
| take 100 
| extend HowLongAgo=( now() - TimeGenerated )
        , TimeSinceStartofYear=( TimeGenerated - datetime(2018-01-01) )
| project Computer 
        , CounterName 
        , CounterValue 
        , TimeGenerated 
        , HowLongAgo 
        , TimeSinceStartofYear

Converting a datetime, f.ex into hours can be done with simple arithmetic of division

Perf
| where CounterName == "Avg. Disk sec/Read"
| where CounterValue > 0
| take 100 
| extend HowLongAgo=( now() - TimeGenerated )
        , TimeSinceStartOfYear=( TimeGenerated - datetime(2018-01-01) )
| extend TimeSinceStartOfYearInHours=( TimeSinceStartOfYear / 1h)
| project Computer 
        , CounterName 
        , CounterValue 
        , TimeGenerated 
        , HowLongAgo 
        , TimeSinceStartOfYear 
        , TimeSinceStartOfYearInHours

Simple datetime calculations over columns

Usage
| extend Duration=( EndTime - StartTime )
| project Computer 
        , StartTime 
        , EndTime 
        , Duration

Combining summarize with datetime functions by a specific timeperiod

Event
| where TimeGenerated >= ago(7d)
| extend DayGenerated = startofday(TimeGenerated) 
| project Source 
        , DayGenerated 
| summarize EventCount=count() 
        by DayGenerated
        , Source

Retrieves number of events per source and for the last 7 days.

Event
| where TimeGenerated >= ago(365d)
| extend MonthGenerated = startofmonth(TimeGenerated) 
| project Source 
        , MonthGenerated 
| summarize EventCount=count() 
        by MonthGenerated
        , Source
| sort by MonthGenerated desc
        , Source asc

Retrieves number of events per source by month for the last 365 days.

You can also use startofweek and startofyear for similar operations.

There are also corresponding end of functions, f.ex endofday endofweek, endofmonth and endofyear

Event
| where TimeGenerated >= ago(7d)
| extend DayGenerated = endofday(TimeGenerated)
| project Source 
        , DayGenerated 
| summarize EventCount=count() 
        by DayGenerated
        , Source
| sort by DayGenerated desc
        , Source asc

Between commands

Allows us to specify a range of values, be it numeric or datetime, to retrieve.

Perf
| where CounterName == "% Free Space"
| where CounterValue between( 70.0 .. 100.0 )

Likewise for dates

Perf
| where CounterName == "% Free Space"
| where TimeGenerated between( datetime(2019-04-01) .. datetime(2019-04-03)  )
| take 10

Gathering data for start of and end of specific dates

Perf
| where CounterName == "% Free Space"
| where TimeGenerated between( startofday(datetime(2019-04-01)) .. endofday(datetime(2019-04-03))  )
| take 10

There is also a "not between" operator !between, which lets us fetch values not within a range - only those outside it.

Perf
| where CounterName == "% Free Space"
| where CounterValue !between ( 0.0 .. 69.9999 )
| take 10

Todynamic

Takes json stored in a string and lets you retrieve its individual values

Convert json to a variable using todynamic, then step into the json array and project the values

Use the key as column names

SecurityAlert
| where TimeGenerated > ago(365d)
| extend Extprops=todynamic(ExtendedProperties) 
| project AlertName 
        , TimeGenerated 
        , Extprops["Alert Start Time (UTC)"]
        , Extprops["Source"]
        , Extprops["Non-Existent Users"]
        , Extprops["Existing Users"]
        , Extprops["Failed Attempts"]
        , Extprops["Successful Logins"]
        , Extprops["Successful User Logons"]
        , Extprops["Account Logon Ids"]
        , Extprops["Failed User Logons"]
        , Extprops["End Time UTC"]
        , Extprops["ActionTaken"]
        , Extprops["resourceType"]
        , Extprops["ServiceId"]
        , Extprops["ReportingSystem"]
        , Extprops["OccuringDatacenter"]

You can use column-renaming to structure the output better

SecurityAlert
| where TimeGenerated > ago(365d)
| extend Extprops=todynamic(ExtendedProperties) 
| project AlertName 
        , TimeGenerated 
        , AlertStartTime = Extprops["Alert Start Time (UTC)"]
        , Source = Extprops["Source"]
        , NonExistentUsers = Extprops["Non-Existent Users"]
        , ExistingUsers = Extprops["Existing Users"]
        , FailedAttempts = Extprops["Failed Attempts"]
        , SuccessfulLogins = Extprops["Successful Logins"]
        , SuccessfulUserLogons = Extprops["Successful User Logons"]
        , AccountLogonIds = Extprops["Account Logon Ids"]
        , FailedUserLogons= Extprops["Failed User Logons"]
        , EndTimeUtc = Extprops["End Time UTC"]
        , ActionTaken = Extprops["ActionTaken"]
        , ResourceType = Extprops["resourceType"]
        , ServiceId = Extprops["ServiceId"]
        , ReportingSystem = Extprops["ReportingSystem"]
        , OccuringDataCenter = Extprops["OccuringDatacenter"]

If the JSON keys do not have spaces you can also use property notation

SecurityAlert
| where TimeGenerated > ago(365d)
| extend Extprops=todynamic(ExtendedProperties) 
| project AlertName 
        , TimeGenerated 
        , AlertStartTime = Extprops["Alert Start Time (UTC)"]
        , Source = Extprops.Source
        , NonExistentUsers = Extprops["Non-Existent Users"]
        , ExistingUsers = Extprops["Existing Users"]
        , FailedAttempts = Extprops["Failed Attempts"]
        , SuccessfulLogins = Extprops["Successful Logins"]
        , SuccessfulUserLogons = Extprops["Successful User Logons"]
        , AccountLogonIds = Extprops["Account Logon Ids"]
        , FailedUserLogons= Extprops["Failed User Logons"]
        , EndTimeUtc = Extprops["End Time UTC"]
        , ActionTaken = Extprops.ActionTaken
        , ResourceType = Extprops.resourceType
        , ServiceId = Extprops.ServiceId
        , ReportingSystem = Extprops.ReportingSystem
        , OccuringDataCenter = Extprops.OccuringDatacenter

Multilevel notation is also supported, f.ex Extprops.Level1.Level2

format_datetime

Allows you to return specific date formats

Perf
| take 100 
| project CounterName 
        , CounterValue 
        , TimeGenerated
        , format_datetime(TimeGenerated, "y-M-d")
        , format_datetime(TimeGenerated, "yyyy-MM-dd")
        , format_datetime(TimeGenerated, "MM/dd/yyyy")
        , format_datetime(TimeGenerated, "MM/dd/yyyy hh:mm:ss tt")
        , format_datetime(TimeGenerated, "MM/dd/yyyy HH:MM:ss")
        , format_datetime(TimeGenerated, "MM/dd/yyyy HH:mm:ss.ffff")

Calculating KPIs

DAU to MAU activity ratio - Daily Active Users to Monthly Active Users

pageViews | union *
| where timestamp > ago(90d)
| evaluate activity_engagement(user_Id, timestamp, 1d, 28d)
| project timestamp, Dau_Mau=activity_ratio*100 
| where timestamp > ago(62d) // remove tail with partial data
| render timechart

New Users

customEvents
| evaluate new_activity_metrics(session_Id, timestamp, startofday(ago(7d)), startofday(now()), 1d)

New Users, Returning and Churned https://docs.microsoft.com/en-us/azure/kusto/query/new-activity-metrics-plugin

T | evaluate new_activity_metrics(id, datetime_column, startofday(ago(30d)), startofday(now()), 1d, dim1, dim2, dim3)

Stepping into a JSON object - example: Summarise events

customEvents
| where customDimensions != ""
| extend customDimensions.Properties.event 
| where customDimensions contains "Event name"
| take 10

Stepping into a JSON object - example: Counting users with an event

customEvents
| where timestamp > ago(30d)
| where customDimensions != ""
| extend customDimensions.Properties.event 
| where customDimensions contains "Event name"
| summarize dcount(user_Id) by bin(timestamp, 1d)
| take 10

Summarize average counter values for a specific machine by a specific metric for a given continuous variable, binning values

Perf
| where TimeGenerated > ago(30d)
| where Computer == "sqlserver-1.contoso.com"
| where CounterName == "Available MBytes"
| summarize avg(CounterValue) by bin(TimeGenerated, 1h)

Search for specific text - WORKS but not much data

customEvents
| search "2019"

Unique Users, New Users, Returning Users, Lost Users Can be used in Workbooks with Parameter fields - not in Log Analytics

let timeRange = {TimeRange};
let monthDefinition = {Metric};
let hlls = union customEvents, pageViews
| where timestamp >= startofmonth(now() - timeRange - 2 * monthDefinition)
| where name in ({Activities}) or '*' in ({Activities})
{OtherFilters}
| summarize Hlls = hll(user_Id) by bin(timestamp, 1d)
| project DaysToMerge = timestamp, Hlls;
let churnSeriesWithHllsToInclude = materialize(range d from 0d to timeRange step 1d
| extend Day = startofday(now() - d)
| extend R = range(0d, monthDefinition - 1d, 1d)
| mvexpand R
| extend ThisMonth = Day - totimespan(R)
| extend LastMonth = Day - monthDefinition - totimespan(R)
| project Day, ThisMonth, LastMonth);
churnSeriesWithHllsToInclude
| extend DaysToMerge = ThisMonth
| join kind= inner (hlls) on DaysToMerge 
| project Day, ThisMonthHlls = Hlls
| union (
churnSeriesWithHllsToInclude
| extend DaysToMerge = LastMonth
| join kind= inner (hlls) on DaysToMerge
| project Day, LastMonthHlls = Hlls)
| summarize ThisMonth = hll_merge(ThisMonthHlls), LastMonth = hll_merge(LastMonthHlls) by Day
| evaluate dcount_intersect(ThisMonth, LastMonth)
| extend NewUsers = s0 - s1
| extend ChurnedUsers = -1 * (dcount_hll(LastMonth) - s1) // Last Months Users - Returning Users
| project Day, ["Active Users"] = s1 + NewUsers, ["Returning Users"] = s1, ["Lost Users"] = ChurnedUsers, ["New Users"] = NewUsers

Retention for cohorts

// Finally, calculate the desired metric for each cohort. In this sample we calculate distinct users but you can change
// this to any other metric that would measure the engagement of the cohort members.
| extend 
    r0 = DistinctUsers(startDate, startDate+7d),
    r1 = DistinctUsers(startDate, startDate+14d),
    r2 = DistinctUsers(startDate, startDate+21d),
    r3 = DistinctUsers(startDate, startDate+28d),
    r4 = DistinctUsers(startDate, startDate+35d)
| union (week | where Cohort == startDate + 7d 
| extend 
    r0 = DistinctUsers(startDate+7d, startDate+14d),
    r1 = DistinctUsers(startDate+7d, startDate+21d),
    r2 = DistinctUsers(startDate+7d, startDate+28d),
    r3 = DistinctUsers(startDate+7d, startDate+35d) )
| union (week | where Cohort == startDate + 14d 
| extend 
    r0 = DistinctUsers(startDate+14d, startDate+21d),
    r1 = DistinctUsers(startDate+14d, startDate+28d),
    r2 = DistinctUsers(startDate+14d, startDate+35d) )
| union (week | where Cohort == startDate + 21d 
| extend 
    r0 = DistinctUsers(startDate+21d, startDate+28d),
    r1 = DistinctUsers(startDate+21d, startDate+35d) ) 
| union (week | where Cohort == startDate + 28d 
| extend 
    r0 = DistinctUsers (startDate+28d, startDate+35d) )
// Calculate the retention percentage for each cohort by weeks
| project Cohort, r0, r1, r2, r3, r4,
          p0 = r0/r0*100,
          p1 = todouble(r1)/todouble (r0)*100,
          p2 = todouble(r2)/todouble(r0)*100,
          p3 = todouble(r3)/todouble(r0)*100,
          p4 = todouble(r4)/todouble(r0)*100 
| sort by Cohort asc