How to include the time of each calculated "stats max()" in a table?

[Cuyose]

If I have a search of

search|stats max(duration) by Action

When I run the search, how can I add the time for each action's max duration event in the searched timerange within the results in its own column?

Action,max(duration),time_of_actions_max(duration)

[acharlieh]

As all the answers so far are using some form of stats I figured I would throw in a slightly different answer using dedup to show yet again that there are multiple ways to skin the proverbial Splunk cat... which one would be better however, is a really good question and worth figuring out.

your base search | dedup Action sortby -duration

This of course keeps whole events and all fields (which might not be what you want / performant for dedup), but you can use commands like table or fields to eliminate fields prior to the dedup.

[somesoni2]

Nice and simple...

[MuS]

awesome !!

[Cuyose]

So far none of these are working. Instead of getting a single Action row with the max calculated timestamp I get multiple duplicate Action rows with different max durations.

[vasildavid]

If I understand your question correctly, you are wanting to get the timestamp for the action associated with the max duration? Try using eventstats :

search | eventstats max(duration) AS max_duration by Action | where max_duration = duration | table _time,duration,Action

You might get some duplicate rows for Action if multiple events have the same max duration and Action. You can use a |dedup Action to remove those after the where clause.

[somesoni2 · Accepted solution]

Try like this

your base search | table Action duration _time  | eventstats max(duration) as max by Action | where duration=max | field - max

[Cuyose]

Hey, this one seems to work. I figured it would be easier actually, not that this isnt a clean solution. It just seems that first creating a table from the results then running stats on the table would be inefficient, but it seems to be pretty speedy!

[somesoni2]

I included the table command to limit the number of fields carried through the command. This helps if you're to run some commands before aggregation commands.

[MuS]

Hi Cuyose,

if you have a field called Action you can do it like this:

your base search goes here 
| streamstats max(duration) AS max_Action by Action, _time
| stats max(duration) AS max max(max_Action) AS max_Action by Action, _time | ...

This is un-tested, but should point you in the right direction.

Hope this helps ...

cheers, MuS

UPDATE: since you modified the question 😉

[jkat54]

... | stats max(duration) by Action, _time

does this work?