topic Re: How do you do an outer join of two stats searches? in Splunk Search

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

zebu14 — Tue, 06 Nov 2018 13:40:01 GMT

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 ?

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

kmaron — Tue, 06 Nov 2018 14:01:21 GMT

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

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

zebu14 — Tue, 06 Nov 2018 14:05:52 GMT

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

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

kmaron — Tue, 06 Nov 2018 14:12:41 GMT

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

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

zebu14 — Tue, 06 Nov 2018 14:19:59 GMT

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)

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

kmaron — Tue, 06 Nov 2018 14:27:29 GMT

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

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

kmaron — Tue, 06 Nov 2018 14:28:25 GMT

edited again. try once more.

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

zebu14 — Tue, 06 Nov 2018 16:07:50 GMT

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