How to get the maximum value from a timechart table?

[avisram]

Hi folks,

I am trying to obtain the maximum value from any cell in a table generated by a timechart search. For example, in the attached image the search string is:

index=_internal | timechart count by sourcetype

The time span automatically used is 1 day. Based on this I want to receive the single value of 70434 which occurs under the splunkd column on 4/12/16.

I can get the maximum value for each sourcetype with the following search:

index=_internal | timechart count by sourcetype | stats max(*) as *

However this doesn't get me the single value I want.

[supersleepwalke]

Here's a way that doesn't require a transpose.

<some search here>
| eval rowmax=0
| foreach columnname* 
    [ eval rowmax=if('<<FIELD>>' > rowmax, '<<FIELD>>', rowmax) ]

You're essentially brute force comparing every column to find the max.

[exmuzzy]

I would like to find not only max but min also

ndex=_internal 
 | timechart span=1h count by host
 | stats max(*) AS *."max", min(*) as *."min" | transpose

but how put min into another column?

[MuS]

Hi exmuzzy,

you can try something like this:

index=_internal 
| bin _time span=1h 
| stats count by host _time 
| stats max(*) AS *."max", min(*) as *."min" by host

cheers, MuS

[MuS · Accepted solution]

Hi avisram,

based on your example you can run this search:

index=_internal 
| timechart count by sourcetype 
| stats max(*) AS * 
| transpose 
| stats max(*) AS * 
| rename column AS sourcetype "row 1" AS count

and the result will look like this:

Hope this helps ...

cheers, MuS

[Roopaul]

Hi, I have a similar situation. But I need min,max and avg for each sourcetype. Using your query only one calculation can be done. Is there a way to include all 3 in a single table.

index=* | extract pairdelim="," kvdelim=":" | table _time,* xml,* json
| stats max(* ) AS *

| transpose
| rename column AS sourcetype "row 1" AS Max

[*xml and *json because the fields are dynamic]

[avisram]

As I only need the value I modified MuS' search to the following:

index=_internal | timechart count by sourcetype| stats max(*) as * | transpose | stats max("row 1") as value

Thanks to both of you for your responses!

[avisram]

Thanks MuS and sideview for your responses. Both of these methods give me the desired result. Is one more efficient than the other?

[MuS]

Run the options with your real life events ( and your search ) and you will see in the job inspector which one will win 😉 I reckon it's @sideview option 2

[sideview]

My money is on @sideview option 2! I love the 2-dimensional max(*) as * craziness though.

[avisram]

Actually MuS' was faster - 1.76 to 1.894 over 274,899 events.

[sideview]

Strange. It really shouldn't be. And on my system index=_internal | head 100000 | bin _time span="1h" | stats count by _time sourcetype | sort - count | head 1 is quite a lot faster than the other options. I'm glad they all work for you though. 😃

[sideview]

1) You want to use untable to turn the chart/timechart style result set into a "stats style" result set,

then you can find the maximum value along with both the time value and the relevant value of the split-by field.

Using your index=_internal example it would look like

index=_internal | timechart count by sourcetype | untable _time sourcetype count | sort - count | head 1

2) the other way to do it is to just use bin and stats instead of timechart in the first place, like so:

index=_internal | bin _time span="1h" | stats count by _time sourcetype | sort - count | head 1

but then you have to do the binning by hand with the bin command.