Create table of max values from timechart results

[ryanholland]

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?

[martin_mueller]

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.

[martin_mueller]

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 🙂

[ryanholland]

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.

[martin_mueller]

Transpose?

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

[ryanholland]

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

[ryanholland]

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?

[ryanholland]

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

[sowings]

Have you tried the max aggregator in timechart?