Splunk Search

## How to get sum stats from pair of values

New Member

Hi!
I am looking for help for, I think, a simple statistic but I can't figure out how to do this simply.
Here's an example of my data :

1. Customer1=A, Customer2=B
2. Customer1=A, Customer2=C
3. Customer1=B, Customer2=A

and I want spunk to count the number of event by pair of customer, like :

Pair=AB, count=2
Pair=AC, count=1

I'm sure spunk can do that really easily but all I can do is that and it's pretty ugly and duplicates the result :

eval pair1=Customer1. " / ". Customer2
eval pair2=Customer2. " / ". Customer1
eval pair=mvappend(pair1, pair2)
stats count by pair

Tags (4)
1 Solution
Motivator

Greetings @maellebrown,

``````           | makeresults | eval Customer1="A", Customer2="B"
| append [ | makeresults | eval Customer1="A", Customer2="C" ]
| append [ | makeresults | eval Customer1="B", Customer2="A" ]
| eval Customer1_sort=if(Customer1<Customer2,Customer1,Customer2),
Customer2_sort=if(Customer1<Customer2,Customer2,Customer1)
| eval CustomerPair  = Customer1_sort . " / " . Customer2_sort
| stats count by CustomerPair
``````

Output:

``````CustomerPair    count
A / B            2
A / C            1
``````
Cheers,
Jacob
SplunkTrust

Try

``````Your search
| rename COMMENT as "stats the first time to get ordered pairs"
| stats count as count1 by Customer1 Customer2

| rename COMMENT as "sort customer names into order and then combine prior stats"
| eval CustomerA=if(Customer1<=Customer2,Customer1,Customer2)
| eval CustomerB=if(Customer1<=Customer2,Customer2,Customer1)
| stats sum(count1) as count by CustomerA CustomerB
``````

gives you

``````CustomerA CustomerB count
A         B         2
A         C         1
``````
New Member

Yes thank you !

SplunkTrust

Try like this

``````your current search giving fields Customer1 and Customer2
| eval CustomerPair=mvsort(split("/".Customer1."##/".Customer2,"##"))
| nomv CustomerPair
| stats count by CustomerPair
| eval CustomerPair=replace(CustomerPair,"^\/(.+)","\1")
``````
New Member

Thanks for the answer ! ๐

Motivator

Greetings @maellebrown,

``````           | makeresults | eval Customer1="A", Customer2="B"
| append [ | makeresults | eval Customer1="A", Customer2="C" ]
| append [ | makeresults | eval Customer1="B", Customer2="A" ]
| eval Customer1_sort=if(Customer1<Customer2,Customer1,Customer2),
Customer2_sort=if(Customer1<Customer2,Customer2,Customer1)
| eval CustomerPair  = Customer1_sort . " / " . Customer2_sort
| stats count by CustomerPair
``````

Output:

``````CustomerPair    count
A / B            2
A / C            1
``````
Cheers,
Jacob
New Member

It works !!! Thanks a lot !! I knew it was easy but sometimes I'm lost with all that commands !! Thank you !

Motivator

Glad to hear it - you're welcome! Thank you for marking the answer for us and anyone who comes across this in the future.

Cheers,
Jacob