Post
Ask Question

Azure Data Explorer - Kusto Query - Get Min/Max Within Each Category Filter

In continuation of 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;  

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

  13. minIngestionTimes | join kind=innerunique maxIngestionTimes on FeedKey, DescriptionTitle, DescriptionDetail  
  14. | extend Description = strcat(DescriptionTitle," : ", DescriptionDetail)  
  15. | project FeedKey, Description, MinIngestTime, MaxIngestTime, GenerationDate,  
  16. | 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! 


Similar Articles