All Apps and Add-ons

Finding and reporting on callers getting bounced around and hanging up in the general voicemail box

sideview
SplunkTrust
SplunkTrust

[this is posted and paraphrased from a customer who submitted this as a question to sideview support ]

We use Cisco Contact Center and of course UCM, but we need to do reporting on some complex call flow that is technically all happening outside of UCCX.

What is going on, is a lot of customers don't actually call the main numbers they're supposed to - instead they call one of about a dozen other DID numbers, which are different desks and stations in different buildings, and those people then forward the call on to someone who can help.

The problem is a) this is all happening outside of UCCX so management can't see it, and b) the calls often really don't end well. People get transferred around a lot and then end up transferred to the general voicemail where they get angry and hang up without leaving a message.

What we'd like to do, is find all the calls where a customer a) calls into one of these 12 numbers , b) they get transferred more than say 3 times, c) they end up on the general voicemail box, and they go on-hook to terminate the call.

Ideally we'd like to also do chart and graph things about those calls too.

0 Karma

sideview
SplunkTrust
SplunkTrust

Yes absolutely. At a high level, the useful pieces are

1) the transfers field, which is integer valued, and which basically represents the number of individual call legs in the call where the termination cause code indicated a transfer.

2) the on_hook_party field, which as you might expect, has the value of either "caller" or "recipient", and indicates who of the two went on hook to end the final leg of the call.

3) terminatingCalledPartyNumber This is as you might guess, the called party number, from the very last leg of the call.

Note - all 3 of these fields are added/calculated by the app's conf and its UI, and are not fields present in the core CDR format. (Back on the homepage there's that table going through all the 250 or so fields available, and you can find more info on these fields there as well as some sample reports)

Putting it all together,

1) Using the app's own interfaces, ie clicking your way to things.
Go to Browse Calls,
set call types to just "incoming"
in the "misc search terms" box type

on_hook_party="caller" transfers>3 terminatingCalledPartyNumber="<GENERAL VOICEMAIL BOX EXTENSION>"

hit return. You'll see a list of (fairly terrifying) calls.

Note that there's a little "graph calls over time" link on the right hand side. Click that and it will roll up all these filtering arguments and kick you over into the Reporting side of the product. From there you can change the reporting pulldowns to things like
chart the of over callingPartyAreaCode by transfers

and by doing lots of permutations like this, you might spot some interesting patterns. You might also see for that matter, if a lot of these callingPartyNumbers have had the same thing happen to them many times.... which would be variously

over

2) Using the Search Langauge directly
This is really, really, not for the faint of heart. But you could do this in the search language manually Of course you still need to be within the app for some of these pieces to be defined.

And the great great benefit of course, is that the app automatically handles all the logic why this query needs to be so crazy complicated....

`cdr_events`
| sort 0 - dateTimeConnect 
| stats min(_time) as _time list(callingPartyNumber) as callingPartyNumber list(dateTimeConnect) as dateTimeConnect list(dateTimeDisconnect) as dateTimeDisconnect first(_time) as detailLatest min(dateTimeConnect) as dateTimeConnectFirst max(dateTimeDisconnect) as dateTimeDisconnectLast list(eventtype) as eventtype list(finalCalledPartyNumber) as finalCalledPartyNumber list(on_hook_party) as on_hook_party list(originalCalledPartyNumber) as originalCalledPartyNumber first(finalCalledPartyNumber) as terminatingCalledPartyNumber sum(transfers) as transfers by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID | eval duration_elapsed=if(dateTimeConnectFirst==0,0,dateTimeDisconnectLast - dateTimeConnectFirst) | fields - dateTimeDisconnectLast dateTimeConnectFirst 
| search on_hook_party="caller" transfers>3 terminatingCalledPartyNumber="<GENERAL VOICEMAIL BOX EXTENSION>" ( eventtype="incoming_call" ) 
| sort 0 - _time 
| fields _time callingPartyNumber dateTimeConnect dateTimeDisconnect detailLatest duration_elapsed eventtype finalCalledPartyNumber on_hook_party originalCalledPartyNumber terminatingCalledPartyNumber transfers
 `id_fields`

It could however be optimized quite a lot with a subsearch that narrows it down to just calls that have had at least one call leg hitting the general voicemail extension, like so:

`cdr_events` [ search `cdr_events` finalCalledPartyNumber="<GENERAL VOICEMAIL BOX EXTENSION>" | stats count by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID | fields - count] 
| sort 0 - dateTimeConnect 
| stats min(_time) as _time list(callingPartyNumber) as callingPartyNumber list(dateTimeConnect) as dateTimeConnect list(dateTimeDisconnect) as dateTimeDisconnect first(_time) as detailLatest min(dateTimeConnect) as dateTimeConnectFirst max(dateTimeDisconnect) as dateTimeDisconnectLast list(eventtype) as eventtype list(finalCalledPartyNumber) as finalCalledPartyNumber list(on_hook_party) as on_hook_party list(originalCalledPartyNumber) as originalCalledPartyNumber first(finalCalledPartyNumber) as terminatingCalledPartyNumber sum(transfers) as transfers by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID | eval duration_elapsed=if(dateTimeConnectFirst==0,0,dateTimeDisconnectLast - dateTimeConnectFirst) | fields - dateTimeDisconnectLast dateTimeConnectFirst 
| search on_hook_party="caller" transfers>1 terminatingCalledPartyNumber="6131361016" ( eventtype="incoming_call" ) 
| sort 0 - _time 
| fields _time callingPartyNumber dateTimeConnect dateTimeDisconnect detailLatest duration_elapsed eventtype finalCalledPartyNumber on_hook_party originalCalledPartyNumber terminatingCalledPartyNumber transfers
 `id_fields`
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...