ID of the max value event

[kvaga]

Hello!
I have a table like this

ID, OperationName, Duration
1, oper_x, 114
2, oper_x, 117
3, oper_c, 76
4, oper_z, 87
5, oper_c, 76
6, oper_z, 128

I want to show ID and OperationName which have max Duration. For example:

ID, OperationName, Duration
2, oper_x, 117
3, oper_c, 76
6, oper_z, 128

Please help me. How can I do it? I tried ti use eventstat for max value searching grouped by OperationName. But can't show corresponding ID value

[woodcock]

Like this:

| makeresults 
| eval raw="1 oper_x 114:::2 oper_x 117:::3 oper_c 76:::4 oper_z 87:::5 oper_c 76:::6 oper_z 128" 
| makemv delim=":::" raw 
| mvexpand raw 
| rename raw AS _raw 
| rex "^(?<ID>\S+)\s+(?<OperationName>\S+)\s+(?<Duration>\S+)" 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| sort 0 - Duration 
| dedup OperationName
| sort 0 ID

[kvaga]

Thank you. It is another one good solution. Works correctly. My awards for you

[woodcock]

You can only Accept one but you can UpVote all of them.

[jkat54]

Is this the answer?

  | stats max(Duration) by ID, OperationName

[kvaga]

Unfortunately, no. Since ID is unique then you will have huge amount of pairs ID, OperationName

[cmerriman · Accepted solution]

can you try something like this:

|eventstats max(Duration) as max_opp_duration by OperationName|where max_opp_duration=Duration

this will give you two rows for oper_c, since ID 3 and 5 have the same duration. you could add min(ID) as min_opp_id to the eventstats and then add AND min_opp_id=ID to the where statement

[kvaga]

Thank you very much! It is exactly what I need!