Splunk Search

ID of the max value event

Explorer

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

0 Karma
1 Solution

Super Champion

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

View solution in original post

Esteemed Legend

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

Explorer

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

Esteemed Legend

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

0 Karma

SplunkTrust
SplunkTrust

Is this the answer?

  | stats max(Duration) by ID, OperationName
0 Karma

Explorer

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

0 Karma

Super Champion

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

View solution in original post

Explorer

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!