This is a continuation of the sample queries written for Azure Public Cloud for diagnostic purposes. The topic was introduced in this article earlier.

Sample Kusto queries:

1)      When log entries do not have function names, scopes or duration of calls:

source

| where description Contains "<string-before-scope-of-execution>"

| project SessionId, StartTime=timestamp

| join (source

| where description Contains "<string-after-scope-of-execution>"

| project StopTime=timestamp, SessionId)

on SessionId

| project SessionId, StartTime, StopTime, duration = StopTime - StartTime

 

| summarize count() by duration=bin(min_duration/1s, 10)

 

| sort by duration asc

 

| render barchart

 

2)      Since the duration column is also relevant to other queries later

source  | extend duration = endTime – sourceTime

 

3)      When the log entries do not have an exact match for a literal:

source

| filter EventText like "NotifyPerformanceCounters" 

| extend Tenant = extract("tenantName=([^,]+),", 1, EventText)

 

4)      If we wanted to use regular expressions on EventText:

source

| parse EventText with * "resourceName=" resourceName ",

totalSlices=" totalSlices:long * releaseTime=" releaseTime:date ")" *

| valid in~ ("true", "false")

5)      If we wanted to read signin logs:

source                            

| evaluate bag_unpack(LocationDetails)

| where RiskLevelDuringSignIn == 'none'

   and TimeGenerated >= ago(7d)

| summarize Count = count() by city

| sort by Count desc

| take 5

        6) If we wanted to time bucket the log entries:

source

| where Modified > ago(7d)

| summarize count() by bin(Modified, 1h)

| render columnchart

 

        7) If we wanted to derive just the ids:

Source

| where Modified > ago(7d)

| project  Id

 

        8) If we wanted to find the equivalent of a SQL query, we could use an example as follows:

             EXPLAIN

SELECT COUNT_BIG(*) as C FROM StormEvents

Which results in

StormEvents

| summarize C = count()

| project C

 

This works even for wild card characters such as:

EXPLAIN

SELECT * from dependencies where type like “Azure%”

Which results in

dependencies

| where type startswith “Azure”

 

And for extensibility as follows:

EXPLAIN

SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies GROUP BY name

Which results in

dependencies

| summarize AvgD = avg(duration) by Name = operationName

     9)  If we wanted to process JsonPath, there are KQL Functions that process dynamic objects. For example:

datatable(input :dynamic)

[

dynamic({‘key1’: 123, ‘key2’: ‘abc’}),

dynamic({‘key1’: 456, ‘key3’: ‘fgh’}),

]

| extend result = bag_remove_keys(input, dynamic([‘key2’, ‘key4’]))

The above can also be written with json query language

| extend result = bag_remove_keys(input, dynamic([‘$.key1’]))

which results in

{‘key2’:’abc’}