Count Result on user name in CUCM CDR

[ThomasADP]

Hi all,

I'm tring to count the number of times username appear in the result ,and display count by day with one color = one user:

In the result :
on incoming calls entries, field finalCalledPartyUnicodeLoginUserID ,there is username available
on outgoing calls entries , field callingPartyUnicodeLoginUserID there is username available

how i can created a variable table based on fields above :
robert 15
mickeal 6

I start the query :

    index="cucm_cdr" OR index="cucm_cmr" sourcetype=cucm_cdr OR sourcetype=cucm_cmr NOT INTEGER NOT globalCallID_callId globalCallId_ClusterID=*(type="outgoing" OR type="incoming") 
| search rsmith OR maldo 
| top limit=20 day_of_week 

I must use | timechart sum at the end for display it as i want ?

really need help 🙂

Thanks
Thomas

[sideview]

Hi Thomas -

It's easiest to do this if you go back to the General Report page. On that page, in the "advanced" field we're going to create a new field for ourselves using the combined values of the other two fields. Type into "advanced" the following :

eval userID=mvappend(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID )

and hit return.

Now after the pulldowns refresh below, change the "over time" to say "over userID"
and click "table" to see the results as a table.

If you want to split these results by incoming/outgoing/internal etc, you can change "split by" to "type".

Feel free to post again here - I love handling these cases on the public answers site even though it's less usual, or just use the contact form on our site or email me. You guys are paying customers and now that you're doing stuff that requires the "advanced" field we can talk about other use cases and have some fun doing more advanced things.

[ThomasADP]

hi,

there is no "_" userID listed when i used this search

 `cdr_events` (type="outgoing" OR type="incoming")
  |eval userID=mvappend(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID )
  | search userID=amares OR userID=sbordea OR userID=cserban OR userID=andran OR userID=vladr
  | `get_call_id`
  | stats dc(callId) as calls by userID 

[sideview]

Interesting. OK Thanks. I see in the screenshot that there is a userID value that is blank. This is what's triggering the "VALUE" in the final report for what it's worth. Underscore and hyphen characters would do it too but here it appears to be a " " value. I can show you how to filter that out.

However I think it makes sense for us to schedule a webex to go over all of this and to get your final report working exactly the way you want it. If that sounds like a good thing just email support@sideviewapps.com and we can set that up.

[ThomasADP]

Hi,

1) CMR records store information about the quality of the streamed , that's why we separate CDR index from CMR index.
what is bad to do that ?

2) Even if 2 agents speak with one client it must be count like a call one to one . So it's ok for us.

In General Report UI, "search terms" doesn't appear, i have "other termes" ,that's why i used raw search .
there is always a difference between result VALUE in blue and the total of all agents

Regards
Thomas

[sideview]

1) There's just no reason to separate them also by index. They're already separated by sourcetype. In fact putting them into a separate index will break all of the CMR functionality built into the app. Definitely modify the CMR data input so that indexed CMR data going forward will be in the same index as the CDR data. Once the old unusable data ages out the CMR features and UI elements will work properly.
In general you use separate indexes if there are different access restrictions on the different data, by user or by role, or if there are different retention policies. Neither is the case with this data in general.

2) The methods I sent will still preserve the right counts in the cases where two agents handle a single call. However your searches will potentially overcount still - for instance a call may involve an agent "bob" and bob may appear in more than one call leg for that call. Bob will in your search get credit for multiple calls when there was only one call.

In that screenshot, I think the "VALUE" is indicating that the loginUserID field in some cases is "_". Can you run this search to check?

`cdr_events` (type="outgoing" OR type="incoming")
 |eval userID=mvappend(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID )
 | search userID=amares OR userID=sbordea OR userID=cserban OR userID=andran OR userID=vladr
 | `get_call_id`
 | stats dc(callId) as calls by userID 

And let me know if there's a "_" userID listed there. If so we'll probably want to screen that out of your results.

[ThomasADP]

Hello,

it's works ! , need just modify some details 🙂

 index="cucm_cdr" OR index="cucm_cmr" sourcetype=cucm_cdr OR sourcetype=cucm_cmr 
NOT INTEGER NOT globalCallID_callId globalCallId_ClusterID=*(type="outgoing" OR type="incoming")
|eval userID=mvappend(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID )
| search amares OR sbordea OR cserban OR andran OR vladr
| top limit=20 day_of_week

i would like to keep it like that but split by Total userID calls by day with one colors for each user, instead of total calls by day

It's possible to keep the original order of day : monday , tue, web,thu,Fri ?

PS: In public we can show all possibilities of Splunks Cisco CDR's , amazing ! 🙂

thanks
Thomas

[sideview]

A bunch of notes, and then a corrected search:

1) index=cucm_cdr OR index=cucm_cmr is really weird. I can't think of any reason you would want to put the cmr data into a different index. Hopefully that's just a typo and only one is the "real" index.

2) this search is going to overcount calls with multiple call legs and in Callmanager CDR that can be quite a lot of your calls. For instance a conference call with 10 parties will be counted as 10 separate calls. It's best to stay within the General Report UI and let it handle this sort of thing for you.

Solutions:
A) What I recommend is to go back to the General Report UI, put into the "advanced" field the expression from my earlier answer:

eval userID=mvappend(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID )

and put into the "searchterms" field the following:

amares OR sbordea OR cserban OR andran OR vladr

And then set the reporting pulldowns to Number of Calls over day_of_week split by userID

and visualize it as a column chart. You'll get the chart you need where the column for each day of the week is split up by user. Furthermore from the General Report view, the drilldowns will work, in that clicking a particular user and day will take you to a Calls over time for that particular day and that particular user... Drilldowns from the raw search interface on the other hand will dump you into raw CDR events.

B) If you prefer to use the raw search language, here is what you should use:

`cdr_events` (type="outgoing" OR type="incoming")
|eval userID=mvappend(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID )
| search userID=amares OR userID=sbordea OR userID=cserban OR userID=andran OR userID=vladr
| `get_call_id`
| chart dc(callId) over day_of_week by userID limit=50

this does the distinct count of callId instead of raw call-leg counts, and it does the userId matching a little more narrowly, and gives you the column-chart split by userId that you need.

[sideview]

Just checking in to see if you were able to get what you needed. Feel free to email also -- nick [at] sideviewapps.com

[ThomasADP]

Hi,

thanks for your reponse , i was in holiday , i test that tomorow 😉

Thomas