Azure Data Explorer - 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. 
   
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;  
12. 
    
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!