Azure Data Explorer - Kusto Query - Get Categorial Count

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

I used Kusto Query Language (KQL) for writing advanced queries for Azure Log Analytics. At first, when you will start writing queries, it would be very daunting and keeping that in mind, I thought, I should share a few of those queries which could save huge on the 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.

Kusto: 

1. DemoData   
2. | where GenerationDate >= datetime(2020-05-20) and GenerationDate <= datetime(2020-05-23)  
3. | extend Descriptions = strcat(DescriptionTitle," : ",DescriptionDetail)  
4. | summarize dcount(FeedKey) by Descriptions, FeedKey   
5. | 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!