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
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
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
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).
Thank you for your advise. Actually issue is elsewhere. Could you look into my last replay to somesoni2 ♦ (above post)?
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?
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