Cisco CDR Max Calls per minute

[jed5]

So I'm new to Splunk, and I'm not a query search guru by any means. I've got it installed, have the sideview app installed, have it indexing my CDR records from Call Manager, and am able to run searches against that data.

The problem that I am having (maybe I'm just blind, but have yet to see it), is I would like to get the total number of active calls for a given time frame from a particular gateway or set of gateways. So far, all I have been able to pull from Splunk is a grand total of events per minute, but I need it to show that at 3:48 there were 48 active calls, instead of 372 events found with that gateway listing.

[sideview]

Back in February of this year (2014), and with quite a lot of customer feedback behind it, the Splunk for Cisco CDR app launched 3.1 with a new reporting tool called "Concurrent calls and Gateway Utilization". I forgot to come back here at that point and post this comment, but it's been a big success with our customers and I think it will hit your nail on the head. Take another look at the app if you can.

[jed5]

Nick, I would welcome that change, an easy tool to view concurrent call utilization over time for a gateway would be fantastic, we could really use that for accurate billing, trending, and forecasting for migrating offices off their existing service to SIP trunking, which we will then need to monitor as well to ensure appropriate utilization of calls based on calling party number partition or the like.

I saw that search you mentioned earlier, but it still wasn't giving the accurate counts I was looking for. For instance, our Austin office shows 47 events on thursday at 1pm over their gateway, but they only have a single PRI, so there's no way they had that many concurrent calls at 1pm. Also the search parameters only included CDR records, not both, and just the gateway in question, more concerned with the count at this moment than the quality of the call.

My company is looking at purchasing a larger splunk setup (more than just CDR) with Nick's CDR app too, but we need to get canned searches and a dashboard setup so that even the bean counters can pull meaningful information at a glance. CDR was just the least amount of data to poll that would payoff quickly.

Foster, I appreciate the input, the problem is that I'm looking for specifics at one interval, but still need the whole as well, so the full day's report, with accurate active concurrent calls at minute X, not just minute X's details. Don't know how clear that is... 😕

[sideview]

OK. I'll work on some searches and a view for reporting for "active calls", and I'll take a look at the one I have in there. Shoot me an email so I have your email address and if it's OK I might send you some searches to try out, to get your feedback.

[foster1]

I've put a request in to purchase Nick's app, and it's going through the corporate beaucracy so--possibly by this time next year--we'll have it in place 😐

In the meantime, without the assistance of sideviewapps... to get a count of calls happening at a specific time, you just need to count CDR records with dateTimeConnect < 3:48 and dateTimeDisconnect > 3:48

I use this for a quick count and list of calls at a specific time. Copy and paste this into your search window:

[your--index--or--sourcetype--here]

| convert ctime(dateTimeConnect), ctime(dateTimeDisconnect), ctime(dateTimeOrigination)

| search dateTimeConnect < "10/28/2011 09:05" AND dateTimeDisconnect > "10/28/2011 09:05"

| table dateTimeOrigination dateTimeConnect dateTimeDisconnect

[sideview]

Totally fair. So the only practical problem is the overcounting, which you can solve by tacking on a stats count <other_stats_as_necessary> by globalCallID_CallId, callManagerId

[foster1]

Before you find anything else wrong--yes, you'll need to filter for blank records, gateway, etc. I was trying to give jed5 direction and options rather than writing a complete solution in my response.

[foster1]

True. Although the only time this won't work is when our time range spans a year-change. Since even "10/27/2011 23:40" < "10/28/2011 01:24" is true in alphanumeric comparison.

And yes, the time range picker needs to be big enough to include all records that might potentially cross that instant of time in question.

Again, quick and dirty and without the benefit of helper apps...

[sideview]

Actually, there's yet another problem with this search (sorry!) the "<" operator in your search clause will be working lexicographically, meaning in alphabetical order. This wont work how you might expect. Instead you want to either use the TimeRangePicker pulldown's 'custom time' features to enter that exact time. (or you could use an online converter to find the epochtime value for the times and put earliest= latest= into the initial search string. Email me with any questions at all.

[foster1]

Since dateTimeConnect and dateTimeDisconnect only show up in CMR/cdrRecordType=1 records, by default CDR records are not counted. I actually anticipate this might undercount some, depending on conference calls and transfers but then there is some debate about how those should be counted for concurrency. Either way, this way counts it consistently--and consistency is basis of accurate reporting.

As I mentioned about, without the availability of something like sideviewapps, the above query gets the job done.

[sideview]

Also this depends a bit on the timescale but note that truly counting 'active' calls means you have to account for calls that started before the time window and ended after the time window. At long time scales this can probably be safely discarded, and the global count of globalCallID_CallId, callManagerId works fine. However at shorter time scales like in the minutes, if you want an accurate answer you'll have to use things like concurrency to really do it right.

[sideview]

This will overcount though. One call will generally have one CMR and one CDR but they can have more than one. Calls with multiple parties and transfers for instance will have quite a lot of records generated. Note carefully what the app does to use stats .... by globalCallID_CallId, callManagerId, because that's the secret. You can run a report in the app and then click the "see full search syntax" link, and then click the 'expand all macros' link.

[sideview]

It sounds kind of like you're using my app just for the indexing but not for the reporting?

At any rate in the app's report builder it's actually not even possible to graph the raw count of total 'events', because you're right they're pretty meaningless. Instead, the report view, as well as the other views in the app, rolls all the CDR and CMR rows up into unique calls so you're reporting on rows that make sense. And if you leave the y-axis fields set to the defaults it just counts the number of calls, which I think is what you're looking for.

That said, if you want to run searches in the default search interfaces, so you can see what's really going on in the search language you can do that too. Use the 'Build Report' page to graph the number of calls, and you can enter the searchterms to narrow down to your one gateway or multiple gateways in the 'search terms' field, and then after it runs you can click the 'see raw search syntax' link and it'll take you to a more power-user view where you're exposed to the full search syntax.

My apologies if I'm misreading this. Also you can always email me directly at nick@sideviewapps.com or we can set up a phone call or webex.

UPDATE: I belatedly realized that you're of course talking about 'active' calls over time, the solution to which involves using the concurrency command.

http://splunk-base.splunk.com/answers/13214/cdr-calculate-number-of-active-calls-at-any-give-time

Although the Cisco CDR App's homepage has a little demo where it uses the concurrency command to show this, I haven't yet built a real tool complete with a full set of search fields. I think I'll add this for the next version though. Let me know if that would be a welcome change, and feel free to reach out for help about the syntax with concurrency