Splunk Search

Create table of max values from timechart results

ryanholland
Explorer

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?

Tags (2)
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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 🙂

0 Karma

ryanholland
Explorer

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Transpose?

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

0 Karma

ryanholland
Explorer

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

0 Karma

ryanholland
Explorer

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?

0 Karma

ryanholland
Explorer

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

0 Karma

sowings
Splunk Employee
Splunk Employee

Have you tried the max aggregator in timechart?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...