Friday, October 9, 2020

Azure Log Analytics - 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!

No comments:

Post a Comment