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

let fact = DemoData
| where GenerationDate == datetime(2020-05-21)
| summarize dcount(FeedKey) by DescriptionTitle, DescriptionDetail, FeedKey, GenerationDate;
let minIngestionTimes = fact | join kind=leftouter DemoData on FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate
| project FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate, IngestionTime
| summarize MinIngestTime = arg_min(IngestionTime,*) by FeedKey, DescriptionTitle, DescriptionDetail;
let maxIngestionTimes = fact | join kind=leftouter DemoData on FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate
| project FeedKey, DescriptionTitle, DescriptionDetail, GenerationDate, IngestionTime
| summarize MaxIngestTime = arg_max(IngestionTime,*) by FeedKey, DescriptionTitle, DescriptionDetail;
minIngestionTimes | join kind=innerunique maxIngestionTimes on FeedKey, DescriptionTitle, DescriptionDetail
| extend Description = strcat(DescriptionTitle," : ", DescriptionDetail)
| project FeedKey, Description, MinIngestTime, MaxIngestTime, GenerationDate,
| 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!