calculating the total time an extension has spent on calls, vs idle

[sideview]

(NOTE: I am posting this on behalf of a customer of ours who asked this of us a while back)

This is in Callmanager CDR. I need to calculate a kind of "non overlapping" duration for a set of VTC Calls. I don't want just the "total duration" of calls through the extension, but rather the total clock time that the number has been on 1 or more VTC calls, vs the total clock time that it has sat idle.

I can't figure it out. For a while we thought the difference might be to use the 'duration_elapsed' field instead of 'duration' and 'duration_total' but those didn't work out.

by the way we are using the "Cisco CDR Reporting and Analytics" app for Cisco Unified Communications Manager.

[sideview]

Let me restate the question to make sure I understand - You want total clock time where this extension was on one or more calls, and the clock time when it was not on any calls.

As advanced use cases go in our app, this is actually not that bad, because the app already has a macro to do the hard part, namely calculating the concurrency. But I'm getting ahead of myself.

First, I'm afraid we can't calculate this in the default "Browse Calls" UI, Nor in the "General Report" UI.

So we'll have to construct the logic ourself by clicking the dreaded "Search" link in the app's nav bar.

Then run this search:

`cdr_events` <your search terms here to filter down to the calls you care about> 
| `get_call_concurrency` 
| `timechart_for_concurrency` 
| rename _span as span 
| eval active=if(concurrency>0,"yes","no") 
| stats sum(span) as duration by active 
| eval duration=tostring(duration,"duration")

The basic idea here, is to use those two macros to calculate and then graph concurrency over time, and then to add up the times where the concurrency is "0" vs the times when it's 1 or higher.

However - there are some nuances to be aware of.

1) By default the timechart_for_concurrency macro is going to create time buckets of a certain size. (ie 5mins vs 1 hour etc). How it picks this is that for the given timerange (eg if you're searching "last 24 hours" vs "last 60 minutes"), it will try and pick a bucket size that is a reasonable whole number of something, but that results in less than 400 total buckets.
In a mathematical sense, the smaller you make the buckets, the more accurate the final numbers are. In a practical sense if you actually make it calculate a million buckets your search will.... not perform well.

2) The 400 number may well give an accurate enough number. IF you want to mess with this magic 400 number, then instead of the timechart_for_concurrency macro, you can use the raw definition.

timechart bins=400 max(concurrency) as max_concurrency last(post_concurrency) as last_concurrency | filldown last_concurrency* | eval concurrency=coalesce(max_concurrency,last_concurrency) | fields - last_concurrency max_concurrency

and then just change the 400. What could go wrong? 😃

NOTE: the concurrency macros in the product have two flavors. The simple kind where there is no split by, and the much more complex kind where there is a "split by" field. Everything here that I'm talking about, is actually the "simple" kind believe it or not.

---

To give you some context about the 3 duration fields you mentioned --

-- duration This is the individual number of seconds on the individual call legs. So on a single leg call it's just the call duration. On a big conference call or a call with lots of transfers, it'll be a multivalue value. So sum(duration) will be extremely large for a big conference call. likewise avg(duration) across a lot of conference calls will be the average length of the individual call legs across all of the calls. NOT the average call duration.

-- duration_total the app's UI constructs this when you need it, and it's the total duration of all the individual call legs. For single leg calls, it's identical to the duration field. For big conference calls, it'll be a huge number, considerably larger than the actual "wall clock time" of the call. For calls with lots of transfers it'll be similar to the wall clock time, but generally a bit larger as call legs often overlap slightly.

-- duration_elapsed This is basically the "wall clock time". ie from the moment the earliest call leg to connect actually connected, to the moment the last call to terminate actually terminated.