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)
... View more
