last call made by userID in CUCM CDR

[splunkuseradmin]

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.

[Richfez · Accepted solution]

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

[splunkuseradmin]

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

Thanks,
sal.