topic Finding Unique Pairs of Data in Interchangeable Fields in Splunk Search

Finding Unique Pairs of Data in Interchangeable Fields

lboro_garyp — Wed, 01 Nov 2017 16:25:06 GMT

Hi folks, I'm parsing Cisco Callmanager call detail records in our splunk system and I'd like to see which pairs of telephone numbers have the most calls between them, but here's the tricky bit: I don't care who called who, I want to aggregate calls from A->B and B->A into one counter and list the top 10 pairs of callers who make the most calls to each other.

The code below is giving me a nice list of top calling pairs at the moment, but A->B and B->A are listed as two distinct pairs, how do I aggregate them?

index=cucm | stats count by callingPartyNumber,finalCalledPartyNumber |sort by -count

Re: Finding Unique Pairs of Data in Interchangeable Fields

somesoni2 — Wed, 01 Nov 2017 16:45:37 GMT

Give this a try

index=cucm 
| eval callParticipents=mvsort(split(callingPartyNumber."#".finalCalledPartyNumber,"#") | nomv callParticipents| stats count by callParticipents |sort by -count

Re: Finding Unique Pairs of Data in Interchangeable Fields

lboro_garyp — Wed, 01 Nov 2017 18:07:04 GMT

Brilliant! It was missing a bracket but did the trick once I popped it back in 🙂

The most important part seems to be

eval callParticipents=mvsort(split(callingPartyNumber."#".finalCalledPartyNumber,"#"))

...but I can't quite figure it out, could you explain it, please? I get that we're creating a new multivalue field to work with called callParticipents for each event in the timeframe. I guess it doesn't matter which order the values go into the field A/B is the same as B/A for the purposes of a multivalue field, right? I just can't grok how callParticipents is built from that line

edit

Never mind, I sat down and read the docs on split and the mv commands and I get it now. Thanks so much!