topic Re: Create table of max values from timechart results in Splunk Search

Create table of max values from timechart results

ryanholland — Mon, 28 Sep 2020 14:27:39 GMT

I've created a time chart which successfully builds a table of the count of "src_ip" values in a 5 minute bucket. So, for the 1 hour period, I have 12 time buckets (5 min each) for each "pool" of src_ip's.

index=my_index earliest=-60m sourcetype=*dhcp* dhcp_message=DHCPACK | lookup wireless_networks network as src_ip | search ssid=mynetwork | transaction src_ip maxspan=5m  | timechart span=5m count(_raw) as leases by pool

I would instead like to end up with a table that has JUST the maximum value across all 12 buckets for each "pool". I thought I could simply pipe to a stats or streamstats or table command, but this does not seem to work. Once I have the timechart I want, how can I get a table with just the maximum of each column across all the time buckets?

Re: Create table of max values from timechart results

sowings — Tue, 30 Jul 2013 14:59:33 GMT

Have you tried the max aggregator in timechart?

Re: Create table of max values from timechart results

ryanholland — Tue, 30 Jul 2013 15:10:24 GMT

Just looked up what I think you are referring to, but not sure it's applicable. With timechart, I (obviously) get buckets for each time slice, and for each time slice, it's broken down by pool. However, for each individual pool, I want the max across ALL the time buckets. Perhaps a screen shot is worthwhile . . .

Re: Create table of max values from timechart results

ryanholland — Tue, 30 Jul 2013 15:19:27 GMT

Adding to the above comment response, here's an illustration. I currently have this:

(Sorry for the link . . . apparently I don't have enough "Splunk karma")
https://www.dropbox.com/s/yt4gc9dl87matbj/splunk_img.png

What I'd like to have as a result is:

north-A 1161
north-B 1067
south-A 925

(Ignore the fact that the first row contains the maximums. That's not always the case.)

Ideas?

Re: Create table of max values from timechart results

martin_mueller — Tue, 30 Jul 2013 15:37:02 GMT

I'll transfer your issue to something we can all run - counting _internal by sourcetype. This would be your timechart:

index=_internal | timechart count by sourcetype

As a result you get buckets with a count for each sourcetype for the specific timespan.
In order to get the maximum value per sourcetype, append this stats:

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

Then you get one row with the maximum per sourcetype.

Re: Create table of max values from timechart results

ryanholland — Tue, 30 Jul 2013 15:47:20 GMT

Awesome! This gets the data I need. Is there a way to get one COLUMN with maximum per sourcetype instead of one ROW?

Re: Create table of max values from timechart results

martin_mueller — Tue, 30 Jul 2013 15:54:59 GMT

Transpose?

Note, depending on your ultimate goal there may be more efficient ways.

Re: Create table of max values from timechart results

ryanholland — Tue, 30 Jul 2013 16:53:54 GMT

Awesome! Now I have a table with columnA=pool and columnB=highest_peak. This is great.

In a different lookup table, I have columnA=pool and columnB equal to max allowed in the pool. I'd like to determine the percentage of highest_peak / max. I don't know how to put the results against the "max" column I have in the other lookup.

Re: Create table of max values from timechart results

martin_mueller — Wed, 31 Jul 2013 07:05:08 GMT

If you append this

... | lookup lookup_name column_1 AS column_2

you'll get the other fields from the lookup joined to the other rows. A look in the docs wouldn't hurt either 🙂