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

[zebu14]

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 ?

[zebu14]

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

[zebu14]

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)

[kmaron]

edited again. try once more.

[kmaron]

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

[kmaron]

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

[kmaron · Accepted solution]

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

[zebu14]

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