How can i use my global_time token in an averaging search

[tkwaller1]

Maybe a dumb question but its been making me mad, maybe im overthinking it. I have a very simple search:

index=poc channel="/event/Submission_Event"
| bucket _time span=$global_time.earliest$  
| stats count by _time
| stats avg(count) as AverageCount

I just want the avg(count) over the timerange that is selected. So if they picked 7 days it would give the 7 day average, if they picked 24h then it would give the average over the 24 hours span so i can use it in a single viz visual.

I keep getting:

Error in 'bucket' command: The value for option span (-24h@h) is invalid. When span is expressed using a sub-second unit (ds, cs, ms, us), the span value needs to be < 1 second, and 1 second must be evenly divisible by the span value.

because the token time is something like 24h@h which isnt feasible for token setting.

How can i work around this? Any ideas?

Thanks so much for the help!

[livehybrid]

Hi @tkwaller1 

I may be getting the wrong end of what you're looking for here, but it sounds like you dont need to use the bin command (I suspect this is what you meant instead of bucket command?). If you're only searching 7 days then do you want the average per day? Or the count across the 7 days?

For average per day:

index=poc channel="/event/Submission_Event"
| timechart span=1d count
| stats avg(count) as AverageCount

If you take out the span=1d then it will create determine an automatic span based on the timeframe which might be unexpected.

If you want the count across the 7 days (or whatever global time is set) then you could do:

index=poc channel="/event/Submission_Event"
| stats count as AverageCount

If Ive got the wrong end of the stick then please clarify and I can update the reply 🙂 

🌟 Did this answer help you? If so, please consider:

• Adding kudos to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[tkwaller1]

This is built in a dashboard studio dashboard. The $global_time$ token is the timerange selector on the dashboard so I cannot hardcode the search to a specific time range. So no matter what range they select it should function and give me an average.

So if its 24 hours I would expect a 24 hour average over the timespan. Same if they selected last 7 days, a single average for the timerange.

[livehybrid]

Hi @tkwaller1

Thats fine, we can make this work with the global time.

Apologies, Im still not 100% sure what you mean - " if its 24 hours I would expect a 24 hour average over the timespan"  - So - If 24hours is selected you want 1 number, is this:
A) The number of times in that 24 hours that /event/Submission_Event was hit? (Thus, count)
B) The number of times in that 24 hours, divided by the number of hours (In this case, 24?) 

If B, what would the count be divided by for time range of 5 hours? 7 days? Or 14 days? 60 days? (For example).
I'm keen to help you get to the bottom of this so please let me know and we can work out the best search to get your answer.

Thanks

[tkwaller1]

Hello
Sorry for the delay. To answer your questions:
This is what I would expect:
B) The number of times in that 24 hours, divided by the number of hours (In this case, 24?) 

Additionally:
If B, what would the count be divided by for time range of 5 hours? 7 days? Or 14 days? 60 days?

I would say that if its 5 hours it would be divided by 5 hours or 7 days or whatever is selected. I was hoping that the selector would make it easier but I didnt think about the snap-to time being a problem.

Hope that helps

Thanks again

[PickleRick]

Unfortunately, your requirement might not only be hard/impossible to implement in dashboard studio (afair simplexml dashboards allow custom js so you have much more freedom there), but is also imprecisely defined. What about a 36h search range - average daily count or hourly? Or 8 days - daily or weekly?

Anyway, from the top of my head, if you have a predefined set of time ranges you could use a list instead of time-picker and fill-in placeholders this way.

Alternative approach (but I'm not 100% sure it will work) could be to use subsearch to generate the span part of the search and within that search use addinfo to get the time range).

[tkwaller1]

Not sure why you think this is improperly defined, sounds pretty easy to me.

What about a 36h search range - total divided by 36 hours (just not 36h@h)
Or 8 days - total divided by 8 days (just not 8d@d)

It sounds like the issue REALLY lies in the pickers standard formatting of time in conjunction with dashboard studio which has less control over coding, its easier to do in XML. 

No worries, thanks for the thoughts, I'll find a way to work around this.

[PickleRick]

Why for 36 hours you want an average from 36 hour-long counts and for 192 hours you want day-long counts? Is there a threshold?

Anyway, the time picker has its purpose and it's not providing a span.

Anyway. What seems to be working in my lab is indeed generating the span with a subsearch. The trick is that you have to do the whole "span=something" string. Like this:

index=winevents
| timechart
[ | makeresults
| addinfo
| eval range=info_max_time-info_min_time
| eval span=case(range<60,"1s",range<3600,"1m",range<86400,"1h",1=1,"1d")
| eval search="span=\"".span."\""
| table search ] count

[livehybrid]

Hey @PickleRick 

I like that approach - hadnt thought of the subsearch in the timechart to achieve so bookmarking that for future ref 😉 

Not to be pedantic....I'd probably go for <90000=1h instead of <86400 because if you select "Last 24 hours" then you get slightly more than 24 hours (something like 31-03-2025 22:00:00 to 01-04-2025 22:09:12)

@tkwaller1 if you want to get a single value out of it then you could do something like the below - Ive added an appendcols to add the span info into a field so people know what its an average of.

index=_internal 
| timechart 
    [| makeresults 
    | addinfo 
    | eval range=info_max_time-info_min_time 
    | eval span=case(range<60,"1s",range<3600,"1m",range<90000,"1h",1=1,"1d") 
    | eval search="span=\"".span."\"" 
    | table search ] count partial=f 
| stats avg(count) as avgCount 
| appendcols 
    [| makeresults 
    | addinfo 
    | eval range=info_max_time-info_min_time 
    | eval span="per ".case(range<60,"1s",range<3600,"1m",range<90000,"1h",1=1,"1d") 
    | table span ]

🌟 Did this answer help you? If so, please consider:

• Adding kudos to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing.