Tuesday, October 13, 2020

Azure Log Analytics - Kusto Query - Get Min/Max Within Each Category Filter

In continuation to my previous post on Get Categorial Count, this time let’s get our hands dirty with one more query related to filter criteria for date time field.

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:3500000

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-21 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:

For each unique combination of FeedKey and Description, find the maximum and minimum Ingestion time

Kusto query:

  1. let fact = DemoData  
  2. where GenerationDate == datetime(2020-05-21)  
  3. | summarize dcount(FeedKey) by DescriptionTitle, DescriptionDetail, FeedKey, GenerationDate; 
  4.  
  5. let minIngestionTimes = fact | join kind=leftouter DemoData on FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate  
  6. | project FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate, IngestionTime 
  7. | summarize MinIngestTime = arg_min(IngestionTime,*) by FeedKey, DescriptionTitle, DescriptionDetail;  

  8. let maxIngestionTimes = fact | join kind=leftouter DemoData on FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate  
  9. | project FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate, IngestionTime
  10. | summarize MaxIngestTime = arg_max(IngestionTime,*) by FeedKey, DescriptionTitle, DescriptionDetail;  

  11. minIngestionTimes | join kind=innerunique maxIngestionTimes on FeedKey, DescriptionTitle, DescriptionDetail  
  12. | extend Description = strcat(DescriptionTitle," : ", DescriptionDetail)  
  13. | project FeedKey, Description, MinIngestTime, MaxIngestTime, GenerationDate,  
  14. | sort by FeedKey  

Expected output

FeedKey

Description

MinIngestTime

MaxIngestTime

GenerationDate

acbf-uhef-4t5i-dfff

Schedule Task : Read feed from server 1

2020-05-25 02:00:00:0000000

2020-05-25 03:00:00:3500000

2020-05-21 00:00:00:0000000

lcbf-u78f-4p5i-dfff

Monitoring Task : Monitoring failed for LOC

2020-05-25 03:00:00:3000000

2020-05-25 03:00:00:3000000

2020-05-21 00:00:00:0000000

acbf-uhef-4t5i-dfff

Schedule Task : Data missing for palto

2020-05-26 02:00:00:0000000

2020-05-26 02:00:00:0000000

2020-05-21 00:00:00:0000000

 Happy kustoing!

No comments:

Post a Comment