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?
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
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
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
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...
Hi Dal,
Thanks for your quick reply... But I could not get the right results with the functions.
I will share more details...
Denis