All Apps and Add-ons

last call made by userID in CUCM CDR

splunkuseradmin
Path Finder

Hi all,

I am trying to find out the last call made by each user in CUCM CDR.
"index=index=test_cdr"

I have came up with something (below query), but I want only there last call on what date by userID.

cdr_events
| eval userID=mvappend(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID )| stats list(callId) as callId list(userID) as userID by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID
| chart dc(callId) over userID | sort 0 dc(callId) desc | dedup userID

I would be glad if someone can help me out.
thanks,
sal.

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

Splunkuseradmin,

You may be overthinking it a bit, or maybe I'm underthinking your description... 🙂

Once you have your list of calls, which the "big stats" does (as opposed to call legs which is what you have before that line), then you should be able to just do another stats right on top of that to pull out the last call.

`cdr_events` 
| eval userID=coalesce(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID ) 
| stats last(_time) as _time last(userID) as userID by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID
| stats last(_time) as _time by userID

Notice I did have to include _time in the first one, so that we have it available in the second one.

ALSO, note that _time is a special field in that it's a unix epoch time but it displays as a pretty date/time field. This only works if you use _time - the moment you rename it it'll go to big strings of numbers like 1552942883 or whatever.

To get around that, if you rename _time, just do an eval on it to make it pretty.

`cdr_events` 
| eval userID=coalesce(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID ) 
| stats last(_time) as _time last(userID) as userID by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID
| stats last(_time) as LastCallTime by userID
| eval LastCallTime = strftime(LastCallTime, "%Y-%m-%d %H:%M:%S")

Like that.

Let me know if this was the answer you needed!

-Happy Splunking,
Rich

View solution in original post

0 Karma

Richfez
SplunkTrust
SplunkTrust

Splunkuseradmin,

You may be overthinking it a bit, or maybe I'm underthinking your description... 🙂

Once you have your list of calls, which the "big stats" does (as opposed to call legs which is what you have before that line), then you should be able to just do another stats right on top of that to pull out the last call.

`cdr_events` 
| eval userID=coalesce(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID ) 
| stats last(_time) as _time last(userID) as userID by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID
| stats last(_time) as _time by userID

Notice I did have to include _time in the first one, so that we have it available in the second one.

ALSO, note that _time is a special field in that it's a unix epoch time but it displays as a pretty date/time field. This only works if you use _time - the moment you rename it it'll go to big strings of numbers like 1552942883 or whatever.

To get around that, if you rename _time, just do an eval on it to make it pretty.

`cdr_events` 
| eval userID=coalesce(finalCalledPartyUnicodeLoginUserID, callingPartyUnicodeLoginUserID ) 
| stats last(_time) as _time last(userID) as userID by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID
| stats last(_time) as LastCallTime by userID
| eval LastCallTime = strftime(LastCallTime, "%Y-%m-%d %H:%M:%S")

Like that.

Let me know if this was the answer you needed!

-Happy Splunking,
Rich

0 Karma

splunkuseradmin
Path Finder

I juss needed 2 fields in table or stats.
by "userID" and "lastcalldate"

Thanks,
sal.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...