Splunk Search

How to get sum stats from pair of values

maellebrown
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

Please help!

0 Karma
1 Solution

jacobpevans
Motivator

Greetings @maellebrown,

Please try this run-anywhere example:

           | 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

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.

View solution in original post

0 Karma

DalJeanis
Legend

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
0 Karma

maellebrown
New Member

Yes thank you !

0 Karma

somesoni2
Revered Legend

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")

maellebrown
New Member

Thanks for the answer ! 🙂

0 Karma

jacobpevans
Motivator

Greetings @maellebrown,

Please try this run-anywhere example:

           | 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

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma

maellebrown
New Member

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

0 Karma

jacobpevans
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

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...