Splunk Search

How to get the maximum value from a timechart table?

avisram
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

1 Solution

MuS
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

supersleepwalke
Communicator

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.

0 Karma

exmuzzy
Explorer

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?

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

MuS
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

0 Karma

Roopaul
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

avisram
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

avisram
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

MuS
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

sideview
SplunkTrust
SplunkTrust

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

avisram
Path Finder

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

0 Karma

sideview
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

sideview
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...