topic How to get the maximum value from a timechart table? in Splunk Search

How to get the maximum value from a timechart table?

avisram — Wed, 13 Apr 2016 01:55:05 GMT

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.

Re: How to get the maximum value from a timechart table?

sideview — Wed, 13 Apr 2016 01:59:09 GMT

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.

Re: How to get the maximum value from a timechart table?

MuS — Wed, 13 Apr 2016 02:01:58 GMT

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

Re: How to get the maximum value from a timechart table?

avisram — Wed, 13 Apr 2016 02:08:08 GMT

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

Re: How to get the maximum value from a timechart table?

MuS — Wed, 13 Apr 2016 02:09:52 GMT

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

Re: How to get the maximum value from a timechart table?

sideview — Wed, 13 Apr 2016 02:16:26 GMT

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

Re: How to get the maximum value from a timechart table?

avisram — Wed, 13 Apr 2016 02:31:16 GMT

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

Re: How to get the maximum value from a timechart table?

avisram — Wed, 13 Apr 2016 02:41:50 GMT

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!

Re: How to get the maximum value from a timechart table?

sideview — Wed, 13 Apr 2016 07:17:50 GMT

Re: How to get the maximum value from a timechart table?

Roopaul — Sat, 24 Dec 2016 00:13:00 GMT

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.

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

Re: How to get the maximum value from a timechart table?

exmuzzy — Sun, 15 Oct 2017 06:54:25 GMT

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?

Re: How to get the maximum value from a timechart table?

MuS — Sun, 15 Oct 2017 19:50:06 GMT

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

Re: How to get the maximum value from a timechart table?

supersleepwalke — Sat, 02 May 2020 15:52:16 GMT

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.