Splunk Search

Why am I getting different results if I add a where condition in my join subsearch?

Path Finder

Hi,

Could you help me understand why, if I do not add the WHERE condition in join section, I will get a different result if I have that condition there? (highlighted part). Join should excluded this extra rows. Option with WHERE condition is correct. I checked this in a different way.

index=test STATUS="C"Flag1=0 | Where (A="1" OR B="2")| stats dc(ID) AS TOTAL by MONTH | join MONTH [search index=test STATUS="C" Flag=0 Flag2=0 Where (A="1" OR B="2")| stats dc(ID) AS TOTAL by MONTH | stats dc(IM_ID) AS Total2 by MONTH]

Thank you

0 Karma
1 Solution

Esteemed Legend

The answer to "why" it is "wrong" is because subsearches have inescapable (and very small) limits which are engaged without indication. I cannot say this enough; never use join (or more generally, subsearches) unless you absolutely have to. Try this:

index=test STATUS="C" | eval Type=case((A=1 OR B=2) AND Flag1=0, "TOTAL1", Flag=0 AND Flag2=0, "TOTAL2", true(), "OTHER") | stats dc(ID) BY MONTH TXT Type

View solution in original post

0 Karma

Esteemed Legend

The answer to "why" it is "wrong" is because subsearches have inescapable (and very small) limits which are engaged without indication. I cannot say this enough; never use join (or more generally, subsearches) unless you absolutely have to. Try this:

index=test STATUS="C" | eval Type=case((A=1 OR B=2) AND Flag1=0, "TOTAL1", Flag=0 AND Flag2=0, "TOTAL2", true(), "OTHER") | stats dc(ID) BY MONTH TXT Type

View solution in original post

0 Karma

Esteemed Legend

Because you are missing a pipe ( | ) character before the highlighted where command so instead of interpreting as a command, Splunk is treating it as additional text to be searched for to match (and probably not finding any).

0 Karma

Path Finder

Thank you for your advise. Actually issue is elsewhere. Could you look into my last replay to somesoni2 ♦ (above post)?

0 Karma

SplunkTrust
SplunkTrust

You're joining by MONTH field and the condition in WHERE clause is a totally different field. Not sure how you expect Splunk to have artificial intelligence and do the filtering. It may work fine, based on your data, but the comparison is not correct. Did you try to run both query separately and compared results manually?

0 Karma

Path Finder

Hi,

I have figured my mistake but I still do not know why first calculated is affected by condition from the join (FLAG2)

I have rebuild a bit my search. Where condtion is ok because those filed exist in the backend (this thing is not such important)

index=test STATUS="C"Flag1=0
| Where (A="1" OR B="2")
| stats dc(ID) AS TOTAL1 by MONTH, TXT
| join MONTH TXT [search index=test STATUS="C" Flag=0 Flag2=0
| stats dc(ID) AS TOTAL2 by MONTH TXT]

TOTAL2- is showing correct result. Meaning just result which appear in both sets. Also take into account condtion -FLAG2=0

however
TOTAL1- is showing all data from just from first data set but also take into account FLAG2=0. Why does it take into account FLAG2 and just showing result from first data set(deducted by filter FLAG2)

Thank you

0 Karma