Azure Data Explorer - Kusto Query - Get Categorial Count

It’s been a while since I started working on data analysis. When it comes to data analysis, it’s all about how efficiently one can filter and fetch the small set of useful data from a humongous collection.

 

I used Kusto Query Language (KQL) for writing advanced queries for Azure Log Analytics. At first, when you start writing queries, it can be very daunting and keeping that in mind, I thought, I should share a few of those queries which could save a huge amount of a beginner’s time.

 

Hence, my next few posts would be mostly based on how to achieve expected output using KQL. So, let’s get started with a simple scenario first.

 

Below is the sample data on which we are going to query,

 

GenerationDate	IngestionTime	DescriptionTitle	DescriptionDetail	FeedKey
2020-05-21 00:00:00:0000000	2020-05-25 02:00:00:0000000	Schedule Task	Read feed from server 1	acbf-uhef-4t5i-dfff
2020-05-21 00:00:00:0000000	2020-05-25 03:00:00:3000000	Schedule Task	Read feed from server 1	acbf-uhef-4t5i-dfff
2020-05-21 00:00:00:0000000	2020-05-25 03:00:00:3000000	Monitoring Task	Monitoring failed for LOC	lcbf-u78f-4p5i-dfff
2020-05-22 00:00:00:0000000	2020-05-26 02:00:00:0000000	Schedule Task	Data missing for palto	acbf-uhef-4t5i-dfff
2020-05-22 00:00:00:0000000	2020-05-26 00:09:00:0000000	Schedule Task	Read feed from server 1	acbf-uhef-4t5i-dfff
2020-05-22 00:00:00:0000000	2020-05-27 00:04:00:0000000	Failover Handling	Disk fault occurred in region R	acbf-uhef-4t5i-dfff

 

Query description

 

How to get the varied description count for each FeedKey.

 

Query

DemoData
| where GenerationDate >= datetime(2020-05-20) and GenerationDate <= datetime(2020-05-23)
| extend Descriptions = strcat(DescriptionTitle," : ",DescriptionDetail)
| summarize dcount(FeedKey) by Descriptions, FeedKey
| summarize DescriptionCount = count() by FeedKey | sort by DescriptionCount desc;

Expected output

 

FeedKey	DescriptionCount
acbf-uhef-4t5i-dfff	3
lcbf-u78f-4p5i-dfff	1

 

Happy kustoing!