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!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...