below is my query i am working with i have a correct output of
business hours utilization using formula but i have to input manually in the formula is there a way i can do it using any input for selected
timeperiod so i dont have to do it manual.
ex., I used
| eval ... , which is correct but used540
as total minutes forbusiness hours
for the time periodyesterday
. but i need a input so if i select any othertimeperiod
i get utilization value not by entering540
total minutes(business hours
) forlast week`.
Definitely yes. It might best to send this in to us as a Support ticket -- firstname.lastname@example.org but either in parallel with that case, or after, I'll post an answer here. It's GREAT that you posted this here though. It's something we're getting asked about more and more.
taking a step back we've done a number of fairly complex utilization reports with our customers, and are working on shipping this kind of functionality either as a standalone page or with utilization stats as a part of the Browse Devices page.
Oh sorry I was waiting for you to open a ticket with us. My thought was that then we'll be familiar with your ticket history and environment and version of the app etc. Can you just quickly send this to email@example.com ? I can and certainly will post the better way to calculate utilization back here, either in parallel with your case or after.
... | eval utilization=round(100*minutes/ [|makeresults | addinfo | eval _time = info_min_time | timechart count span=1h | eval dayofweek = strftime(_time, "%a") | eval hourofday = strftime(_time, "%H") | search (NOT (dayofweek="Sat" OR dayofweek="Sun")) AND hourofday>7 AND hourofday<17 | stats count AS business_hours | eval business_minutes = 60 * business_hours], 2). "%" ...
I like this approach too, IF it can be shown safe to assume that the call concurrency of all these numbers is never >1.
If I may offer a slight improvement.
We can rely on the fact that the timechart command always outputs a hidden field called _span that represents the number of seconds in the bucket. It allows you to do the exact same business hours definition (likely saved as a macro), to have 30min granularity, also it wont have a 1 hour error during DST changes, and it tolerates the search timerange start or end falling in the middle of a day.
... | eval utilization=round(100*minutes/ [| makeresults | addinfo | transpose | rename "row 1" as value | eval _time=if(column="info_min_time" OR column="info_max_time",value,null()) | where _time>0 | timechart count span=30min | eval day_of_week=strftime(_time,"%a") | eval hour_of_day=strftime(_time,"%H") | eval is_business_hours=case((day_of_week=="Sat" OR day_of_week=="Sun"),0,(hour_of_day>7 AND hour_of_day<17),1,true(),0) | search is_business_hours=1 | stats sum(_span) as business_seconds | eval business_minutes=business_seconds/60], 2). "%" ...
thank you for replying a quick question what if i wanted to take out avg of utilization per week after whole this query ? can i do this directly after this query
|timechart span=1w avg(utilization) as utilization ??
I think that you posted this to the wrong answer, right? My answer is purely about the main question in your OP: how to automate the setting of the denominator of the first argument to the