All Apps and Add-ons

need to get a input for reports

Path Finder

hi all
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 used540as total minutes forbusiness hoursfor the time periodyesterday. but i need a input so if i select any othertimeperiodi get utilization value not by entering540foryesterdayor2700total minutes(business hours) forlast week`.

1 Solution

Esteemed Legend

Like this:

... | 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). "%" ...

View solution in original post

SplunkTrust
SplunkTrust

Well there are a few moving parts in your search that you don't actually use. While this is fine, for the purpose of clarity let's strip those out, just so we can focus on the stuff your report is using.

Also there's some mvappend() + null() syntax that is problematic so let's fix that.

`cdr_events` ( globalCallId_ClusterID=ABC-Corp )
(callingPartySubgroup="Global_Rm" OR originalCalledPartySubgroup="Global_Rm" OR finalCalledPartySubgroup="Global_Rm") dest_device_type="hardphone" OR orig_device_type="hardphone" duration>0
| eval number=mvappend(if(callingPartySubgroup="Global_Rm",callingPartyNumber,""), if(originalCalledPartySubgroup="Global_Rm", originalCalledPartyNumber,""), if (finalCalledPartySubgroup="Global_Rm", finalCalledPartyNumber, ""))
| mvexpand number
| search number=*
| 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
| fields - day_of_week hour_of_day is_business_hours
| stats dc(callId) as calls sum(duration) as seconds by number
| eval minutes=round(seconds/60,2)
| eval utilization=round(100*minutes/540,2). "%"
| lookup groups number OUTPUT name number group subgroup
| fields name number subgroup calls minutes utilization

So OK. Next up, at least in our eyes, is that this is calculating total call duration. Which, if the numbers can only ever be associated with one call leg at any one moment, is probably fine to use as the basis for a utilization report. On the other extreme if this number can be associated with more than one concurrent leg, this SPL is going to be a bad measure of utilization, possibly a REALLY super bad one. For example using this approach to calculate gateway utilization wouldn't work at all. what you need instead is seconds where concurrent calls is >0, over total seconds.

It's easy enough to tell! Let's calculate the actual max concurrency of these numbers for all seconds in the timerange, split by our "number" field. Here we're borrowing some very complex macros that ship in the Cisco CDR app.

`cdr_events` ( globalCallId_ClusterID=ABC-Corp )
(callingPartySubgroup="Global_Rm" OR originalCalledPartySubgroup="Global_Rm" OR finalCalledPartySubgroup="Global_Rm") dest_device_type="hardphone" OR orig_device_type="hardphone" duration>0
| eval number=mvappend(if(callingPartySubgroup="Global_Rm",callingPartyNumber,""), if(originalCalledPartySubgroup="Global_Rm", originalCalledPartyNumber,""), if (finalCalledPartySubgroup="Global_Rm", finalCalledPartyNumber, ""))
| mvexpand number
| search number=*
| `get_call_concurrency(number)`
| `timechart_for_concurrency(number,400)`

Run that, and it'll give you a chart of the max concurrencies over time, for all the numbers.

-- IF there are only 10 or fewer values of "number" here, then just look at that chart and it'll tell you whether any of them ever are on more than one call leg concurrently.

-- However if there are lots of values of "number", uh. the linechart visualization isn't gonna really do it. It's too long a story to write out "what then" here, but email us to open a ticket. (For one thing you can remove the last timechart and replace with | sort 0 - concurrency but the rows are a little odd at that point because there's one row for each call start and one row for each call end)

But let's review. "active" is all the time buckets where max(concurrency) is 1 or higher, and "inactive" is the time buckets where max(concurrency) is exactly zero.

So.... we don't even care whether or not the numbers can be on more than one call. Right here we're pretty close to calculating utilization.
Granted.. there are only 400 time buckets so its not very precise... ...WHAT IF WE ADD MORE BUCKETS.

Say instead of 400 timebuckets we do 50,000 of them. This doesn't make Splunk very happy but it will do it.

And then after that, we can just narrow to business hours, for each number count active buckets and inactive buckets, and we're done. This will work great for timeranges of days and I think a week or two. Past a certain point unfortunately 50,000 isn't really enough granularity though.

But heres the search:

`cdr_events` ( globalCallId_ClusterID=ABC-Corp )
(callingPartySubgroup="Global_Rm" OR originalCalledPartySubgroup="Global_Rm" OR finalCalledPartySubgroup="Global_Rm") dest_device_type="hardphone" OR orig_device_type="hardphone" duration>0
| eval number=mvappend(if(callingPartySubgroup="Global_Rm",callingPartyNumber,""), if(originalCalledPartySubgroup="Global_Rm", originalCalledPartyNumber,""), if (finalCalledPartySubgroup="Global_Rm", finalCalledPartyNumber, ""))
| mvexpand number
| search number=*
| `get_call_concurrency(number)`
| `timechart_for_concurrency(number,50000)`
| 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
| fields - day_of_week hour_of_day is_business_hours
| untable _time number active
| eval minutes=round(seconds/60,2)
| eval active=if(active>1,1,active)
| stats count(eval(active=1)) as active count(eval(active=0)) as inactive by number
| eval utilization=100*active/(active+inactive)

Esteemed Legend

Like this:

... | 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). "%" ...

View solution in original post

SplunkTrust
SplunkTrust

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). "%" ...

Esteemed Legend

No fair "liking" without UpVoting!

0 Karma

SplunkTrust
SplunkTrust

haha. Hey it's high praise! ok fine I gave up an upvote. 😃 despite the little flaws i had to fix in it. 😛

Esteemed Legend

You get an UpVote and you get an UpVote... EVERYBODY gets an UpVote!!

0 Karma

Path Finder

thanks for the answer it helped 🙂
can you please help find a way to get avg(utilization) per week after getting utilization, thanks.
|timechart span=1w avg(utilization) as utilization

0 Karma

Esteemed Legend

I am not sure what you mean but post another question and spell it out clearly.

0 Karma

Path Finder

sure thanks will make a new question for that.
for current question the solution you gave is perfect but only works for search time picker change what if i use earliest="" latest"" in query, it is still doing the search time picker calculation. not for earliest="" and latest="" time-range.

0 Karma

Path Finder

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

0 Karma

Esteemed Legend

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

0 Karma

SplunkTrust
SplunkTrust

Definitely yes. It might best to send this in to us as a Support ticket -- support@sideviewapps.com 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.

0 Karma

Path Finder

any answer with this ?

0 Karma

SplunkTrust
SplunkTrust

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 support@sideviewapps.com ? I can and certainly will post the better way to calculate utilization back here, either in parallel with your case or after.

0 Karma