In continuation of the preceding post on leveraging Kusto databases for querying, even when it involves import of data from external sources, the following Kusto queries will help with useful analysis across ITOM and ITSM landscape.

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 

 

A few queries are included for incident dashboard purposes:

-          How many incidents did customers open this week, month, year-to-date?

-          How many incidents did a team resolve and close those periods ?

-          What is the mean time to resolution?

-          What is the breakdown of incidents by category?

-          What are the largest clusters of incidents?

-          What are the bounding boxes by time or resource groups for the highest number of incidents?

-          What are the outlying incidents?

-          What are the splits for a decision tree?

-          What are clusters formed from co-occurring incidents by running them through a neural net.

Kusto queries can involve external data sources and it is easy to integrate with APIs from external services. This expands the processing and relaxes it from being inlined into the query.