Splunk Search

ID of the max value event

kvaga
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

cmerriman
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

woodcock
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

kvaga
Explorer

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

woodcock
Esteemed Legend

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

0 Karma

jkat54
SplunkTrust
SplunkTrust

Is this the answer?

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

kvaga
Explorer

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

0 Karma

cmerriman
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

kvaga
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...