Problem with CDR Phone Data

[denis_roehr]

Hello Everybody,

I have a big Problem with customers phone data (cdr´s)...

One phone-call has multiple events:

I like to merge all events together values() with an additional field with the name "callType". (for example interal, team, ....)

For example : " | stats values(origDeviceName) values(destDeviceName) values(...) by callType

I´ve tried to use stats / transaction / eventstats .... but without success...

Has anybody an idea?

[denis_roehr · Accepted solution]

I´ve found a working solution now....

in detail:

<index and fields>

| streamstats list(*) AS * by IncomingProtocolCallRef OutgoingProtocolCallRef callingPartyNumber  

| search (Splunk_Telgruppe_origCalled="Group-01" OR Splunk_Telgruppe_finalCalled="Group-01")

| sort origDeviceName

| stats count list(*) AS * by callingPartyNumber origMediaTransportAddress_Port origMediaTransportAddress_IP 

| search  origDeviceName="SBC*" 


| eval Splunk_Telgruppe_finalCalled = mvindex(mvdedup(Splunk_Telgruppe_finalCalled),0)

| eval CallTime                     = mvindex(mvdedup(CallTimeTotal),0)
| eval is_origCalled                = if(match(Splunk_Telgruppe_origCalled ,"Group-01"),1,0)     
| eval is_finalCalled               = if(match(Splunk_Telgruppe_finalCalled ,"Group-01"),2,0)   
| eval callType4                    = if(match(origDeviceName,"SBC*") AND match(destDeviceName,"SEP*") AND NOT match(origDeviceName,"CVP*"),"40","0")     
| eval callType5                    = if(match(origDeviceName,"CVP*") AND NOT match(finalCalledPartyNumber,"3333*") AND NOT match(finalCalledPartyNumber,"4444*"),3,0)

| eval helper                       = is_origCalled+is_finalCalled+callType4+callType5
| eval CallType                     = case(helper==0,"-",helper==1,"dispense",helper==2,"take up",helper==3,"own team",helper==5,"employee direct call",helper==6,"employee direct call",helper>40,"team leader direct call")
| eval callType                     = if(isNull(callType),"without call", callType)


| table CallTime CallTimeTotal CallType Splunk_Telgruppe_origCalled Splunk_Telgruppe_finalCalled  callingPartyNumber originalCalledPartyNumber finalCalledPartyNumber origDeviceName destDeviceName origCause_text destCause_text duration

It works perfectly....

Thanks

[DalJeanis]

Assuming your protocol references are unique...

your search that returns the transactions and gets a calltype on at least one of the transactions
| eval DeviceNames=mvappend(origDeviceName,destDeviceName)
| eval ProtocolRefs=mvappend(IncomingProtocolCallRef, OutgoingProtocolCallRef)
| table _time DeviceNames ProtocolRefs callType
| transaction ProtocolRefs 

[denis_roehr]

Here are a few more detailed details...

The dashboard currently displays the following data:

In the lower red field the data of the SEP connection are missing and the reason are changing IncomingProtocolCallRef IDs.

The raw data show that the SEP connection exists, but with a different incoming ID ...

Here is the complete search

index="callmanager_cdr"  sourcetype="cucm_cdr" globalCallID_callId=* cdrRecordType=1 
(NOT "INTEGER,INTEGER*" AND NOT "*cdrRecordType*")  
(callingPartyNumber="*" OR originalCalledPartyNumber ="*" OR finalCalledPartyNumber="*") destCause_text="*" type="*"              

| fields _time Splunk_Telgruppe_origCalled Splunk_Telgruppe_finalCalled IncomingProtocolCallRef OutgoingProtocolCallRef 
callingPartyNumber originalCalledPartyNumber Standort_origCalled StandortBezeichnung_origCalled finalCalledPartyNumber 
Standort_finalCalled StandortBezeichnung_finalCalled origDeviceName destDeviceName origCause_text destCause_text duration       

| eval Splunk_Telgruppe_finalCalled2 =if(isNull(Splunk_Telgruppe_finalCalled),"N/A",Splunk_Telgruppe_finalCalled)           
| sort 0 - _time    
| eval time2=strftime(_time,"%d-%m-%Y %H:%M:%S")    
| eval identHelper = mvzip(IncomingProtocolCallRef,OutgoingProtocolCallRef)           
| eval time= _time              
| makemv delim="," identHelper         
| mvexpand identHelper            

| stats count list(*) as * by identHelper         
| search count > 1  (Splunk_Telgruppe_origCalled="XXX-XXX" OR Splunk_Telgruppe_finalCalled="XXX-XXX")  origDeviceName="SBC*"       
| eval _time = mvindex(time,0)           
| eval Splunk_Telgruppe_finalCalled = mvindex(Splunk_Telgruppe_finalCalled2,count-2)    
| eval is_origCalled = if(match(Splunk_Telgruppe_origCalled ,"XXX-XXX"),1,0)   
| eval is_finalCalled = if(match(Splunk_Telgruppe_finalCalled ,"XXX-XXX"),2,0)  
| eval callType4 = if(match(origDeviceName,"SBC*") AND match(destDeviceName,"SEP*") AND NOT match(origDeviceName,"CVP*"),"40","0")    
| eval helper = is_origCalled+is_finalCalled+callType4           
| eval callType = case(helper==0,"-",helper==1,"Überlauf abgebend",helper==2,"Überlauf aufnehmend",helper==3,"eigenes Team",helper>40,"Direktanruf")       

| append [search  index="callmanager_cdr" sourcetype="cucm_cdr" globalCallID_callId=* cdrRecordType=1 (NOT "INTEGER,INTEGER*" AND NOT "*cdrRecordType*")  
(callingPartyNumber="*" OR originalCalledPartyNumber ="*" OR finalCalledPartyNumber="*") (destDeviceName = CVP* AND origDeviceName = SBC*)   destCause_text="*" type="*" 
(Splunk_Telgruppe_origCalled="XXX-XXX" OR Splunk_Telgruppe_finalCalled="XXX-XXX")      
NOT [search  index="callmanager_cdr" sourcetype="cucm_cdr" globalCallID_callId=* cdrRecordType=1   (NOT "INTEGER,INTEGER*" AND NOT "*cdrRecordType*")  
(callingPartyNumber="*" OR originalCalledPartyNumber ="*" OR finalCalledPartyNumber="*")    destCause_text="*" type="*"                

| fields _time IncomingProtocolCallRef OutgoingProtocolCallRef            
| eval identHelper = mvzip(IncomingProtocolCallRef,OutgoingProtocolCallRef)         
| eval time= _time      
| makemv delim="," identHelper          
| mvexpand identHelper             
| stats count by identHelper       
| where count > 1       
| fields identHelper      
| rename identHelper AS OutgoingProtocolCallRef    
| format] ] 

| eval callType = if(isNull(callType),"ohne Gespräch", callType) 
|  table _time time2 callType identHelper IncomingProtocolCallRef OutgoingProtocolCallRef  callingPartyNumber origDeviceName destDeviceName duration  
| search callType="*" 
| sort - _time

thanks for your help...

[denis_roehr]

Hi Dal,

Thanks for your quick reply... But I could not get the right results with the functions.

I will share more details...

Denis