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
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.
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.
Thanks MuS and sideview for your responses. Both of these methods give me the desired result. Is one more efficient than the other?
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
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. 😃
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!
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 *
| rename column AS sourcetype "row 1" AS Max
[*xml and *json because the fields are dynamic]