How to get columns associated with max value of specific column in 30 minute time window?

[nowakgft]

Hello everyone,

I have following type of data to analyze:

timestamp	endpoint	executionTime
08:12	/products	0.3
08:20	/products	0.8
08:25	/users	0.5
08:41	/users	1.0
08:50	/products	0.7

I would like to display information about slowest endpoint in each 30 minute window, in this example it would look like:

timeWindow	timestamp	endpoint	maxExecutionTime
08:00	08:20	/products	0.8
08:30	08:41	/users	1

It's fairly easy to gather data on maximum execution time only and so I created such a query:

index = myindex | timechart span=30m max(executionTime) as maxExecutionTime

but now I have no idea how to attach endpoint called and actual timestamp. How should I do it?

[somesoni2 · Accepted solution]

Give this a try

index = myindex | bucket span=30m _time as timeWindow | eventstats max(executionTime) as max maxExecutionTime by endpoint
| where executionTime=MaxExecutionTime 
| table timeWindow _time endpoint maxExecutionTime
| convert ctime(timeWindow)

[nowakgft]

I gave it a try, changed it in two places (corrected typo in max maxExecutionTime and changed by endpoint to by timeWindow).

index = myindex | bucket span=30m _time as timeWindow | eventstats max(executionTime) as maxExecutionTime by timeWindow
| where executionTime=MaxExecutionTime 
| table timeWindow _time endpoint maxExecutionTime
| convert ctime(timeWindow)

  Now works like a charm 🙂 Thank you!