Splunk Search

How do you do an outer join of two stats searches?

zebu14
Explorer

Hello,

I am trying to do an outer join of two searches.

I have 2 server groups (Gateway="opaxvgw1" OR Gateway="opaxvgw2") and (Gateway="opaxvgw3" OR Gateway="opaxvgw4")
For each group, I list the customers by the term "Penta_source".

Then I want to extract the customers that are only present in one of the groups (and eliminate all the customers that are present in both)

Here is my search job, but it doesn't work :

index="axwaydb-prd" sourcetype=AXWAY-Stats Direction=I Gateway="opaxvgw1" OR Gateway="opaxvgw2" | stats count by Penta_source | join type=outer Penta_source [search index="axwaydb-prd" sourcetype=AXWAY-Stats Direction=I Gateway="opaxvgw3" OR Gateway="opaxvgw4" | stats count by Penta_source]

Any idea ?

Tags (2)
0 Karma
1 Solution

kmaron
Motivator

Try this: (edited)

index="axwaydb-prd" sourcetype=AXWAY-Stats Direction=I Gateway IN ("opaxvgw1", "opaxvgw2", "opaxvgw3", "opaxvgw4")
| eval Group1 = if(Gateway="opaxvgw1" OR Gateway="opaxvgw2",1,0)
| eval Group2 = if(Gateway="opaxvgw3" OR Gateway="opaxvgw4",1,0)
| stats values(Group1) as Group1 values(Group2) as Group2 count by Penta_source 
| eval GroupCheck = Group1+Group2
| where GroupCheck<2

View solution in original post

zebu14
Explorer

Just tried and it gives me a lot of customers that are present in each independant search, so NOK.

0 Karma

zebu14
Explorer

No more success with your update
It's probably even worse (2 more results : 872 at first and 874 then)

I'm supposed to obtain 126 customers (I've already done the job through Excel to compare results)

0 Karma

kmaron
Motivator

edited again. try once more.

0 Karma

kmaron
Motivator

oh! I think I see where I went wrong. my apologies.

0 Karma

kmaron
Motivator

I have edited my previous answer. see if that is more correct.

0 Karma

kmaron
Motivator

Try this: (edited)

index="axwaydb-prd" sourcetype=AXWAY-Stats Direction=I Gateway IN ("opaxvgw1", "opaxvgw2", "opaxvgw3", "opaxvgw4")
| eval Group1 = if(Gateway="opaxvgw1" OR Gateway="opaxvgw2",1,0)
| eval Group2 = if(Gateway="opaxvgw3" OR Gateway="opaxvgw4",1,0)
| stats values(Group1) as Group1 values(Group2) as Group2 count by Penta_source 
| eval GroupCheck = Group1+Group2
| where GroupCheck<2

View solution in original post

zebu14
Explorer

This update seems to do the job.
As I still obtain many results, let me check that everything is correct and I'll validate your solution ASAP.

Thanks

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!