Splunk Search
Highlighted

How to get the maximum value from a timechart table?

Path Finder

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.

alt text

Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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:

alt text

Hope this helps ...

cheers, MuS

View solution in original post

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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. 😃

0 Karma
Highlighted

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

Path Finder

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!

0 Karma
Highlighted

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

Explorer

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]

0 Karma