Multiple Buckets - Possible?

[myte]

Hi all, new here, so go easy!

I have a dashboard with many panels, time picker and so on. I'm hoping that I can use multiple time buckets for event counts in one table, for example span=1h AND span=1m. At this stage I'm not bothered about events that might take longer than each span.

The result I'm looking for:

	Average count	Max count
Per hour	{results}	{results}
Per minute	{results}	{results}

I can get this to work with two separate tables but combining into one is proving to be a challenge. If at all even possible.

For example, for per hour I have:

|.... main search...

| bucket _time span=1h as perHour
| stats count as "eventsPerHour" by perHour
| stats avg(eventsPerHour) as avEventsPerHour max(eventsPerHour) as maxEventsPerHour
| eval avEventsPerHour=round(avEventsPerHour,2)
| rename avEventsPerHour as "Average count per hour" maxEventsPerHour as "Maximum count in one hour"

Which gives:

Average count per hour	Maximum count in one hour
{results}	{results}

Any pointers in the right direction much appreciated. 

[myte]

... for now I'm continuing with the separate stats queries, it seems easier to keep it as simple stats tables referring to the base search.

Cheers

[gcusello]

Hi @myte ,

as you like!

but scheduling the two searches:

|.... main search...
| bucket _time span=1h
| stats count BY _time
| stats 
     avg(count) AS AverageCount
     max(count) AS MaxCount
| eval 
     AverageCount=round(AverageCount,2),
     MaxCount=round(MaxCount,2),
     Type="Per Hour"
| collect index=my_summary

and

|.... main search...
| bucket _time span=1m
| stats count BY _time
| stats 
     avg(count) AS AverageCount
     max(count) AS MaxCount
| eval 
     AverageCount=round(AverageCount,2),
     MaxCount=round(MaxCount,2),
     Type="Per Minute"
| stats
     values(AverageCount) AS AverageCount
     values(MaxCount) AS MaxCount
     BY Type
| collect index=my_summary

and running this search when you need resuts

index=my_summary
| table Type AverageCount MaxCount

you have the same result in a single search and a quicker search.

let us know if you need more help, and, for the other people of Community, please, accept one answer.

Ciao.

Giuseppe

P.S.: Karma Points are appreciated by al the Contributors 😉

[myte]

Hi folks, thank you for the quick replies and the great suggestions.

@gcusello I like the append solution, this is great, however, like you mention it isn't the most efficient, especially if considering adding a third or even fourth row. I did consider expanding the dashboard base search in the background, creating another search id, but again, not the most efficient.

@bowesmana Not tried this way yet, will give it a go today. Will this way efficiently handle varying time windows from the time picker? Not sure how multiplying the average per minute by 60 to get an hour would be effective, perhaps it will, I can compare the different result sets I get.

As you've mentioned there's a ton of ways to achieve results in Splunk, I'm relatively new to it but it's been invaluable in analysing API xml and json logs. Having a lot of fun! Thanks again.

[bowesmana]

When dealing with time picker,  the addinfo command is your friend, as that will give you info_min_time and info_max_time that are the actual earliest and latest time ranges of the search, so you can use these to compute things.

As for avg/min, while individual counts per minute can be wildly different, if the measurement period is 2 hours and the total count is 10,000, then the avg/hour has to be 5,000 even though the 1st hour may have been 4,000 and the second hour 6,000.

Also, if the avg/min over a period of 60 minutes is 100 (i.e. a total of 6,000), then the avg/hour must be 6000, i.e. the total. 

[gcusello]

Hi @myte ,

you could try to schedule the two searches (with the frequency that meets your requirements), separately or together, saving results in a summary index.

Then you can run a very efficient search on the summary index.

Ciao.

Giuseppe

[bowesmana]

Averages are quite easy to deal with, so if you start with average per minute you can then calcuate the average per hour, which is simply average per minute * 60, so there are actually a number of ways you can do this

... search...
``` Count by 1 minute ```
| bin _time span=1m
| stats count by _time
``` Fill any gapes in the data ```
| makecontinuous _time span=1m
| fillnull count
``` Now take the max/avg/sum of PM values for 1 hour ```
| bin _time span=1h
| stats max(count) as max_pm avg(count) as avg_pm sum(count) as sum_pm by _time
``` Now do some final calcs to get total sum ```
| stats max(max_pm) as max_pm sum(sum_pm) as total max(sum_pm) as max_ph avg(sum_pm) as avg_ph
``` And this last one just calculates the final per minute, which is total divided by the number of minutes ```
| addinfo
| eval avg_pm=total / ((info_max_time - info_min_time) / 60)
| fields - info_* total
| fields *pm *ph
``` And now convert that table into your table ```
| eval "Per Minute"=mvjoin(mvappend(avg_pm, max_pm), ":")
| eval "Per Hour"=mvjoin(mvappend(avg_ph, max_ph), ":")
| fields Per*
| transpose 0
| rex field="row 1" "(?<Average>[^:]*):(?<Maximum>.*)"
| fields - "row 1"

There are always a number of ways to do things in Splunk - this is somewhat convoluted, but will probably perform well if you are dealing with a lot of data.

I am sure there is a simpler way also, but sometimes it's useful to show that almost anything is possible in Splunk.

[gcusello]

Hi @myte,

if your want the format you shared (Per hour and Per minute in different rows) you could use this solution, even if isn't so efficient:

|.... main search...
| bucket _time span=1h
| stats count BY _time
| stats 
     avg(count) AS AverageCount
     max(count) AS MaxCount
| eval 
     AverageCount=round(AverageCount,2),
     MaxCount=round(MaxCount,2),
     Type="Per Hour"
| append [
     |.... main search...
     | bucket _time span=1m
     | stats count BY _time
     | stats 
          avg(count) AS AverageCount
          max(count) AS MaxCount
     | eval 
          AverageCount=round(AverageCount,2),
          MaxCount=round(MaxCount,2),
          Type="Per Minute"
     ]
| stats
     values(AverageCount) AS AverageCount
     values(MaxCount) AS MaxCount
     BY Type

Ciao.

Giuseppe