What can I use instead of the join command for my search?

[akawacz]

Hi,

I would like to use something different instead of join

index=test STATUS=Closed 
| stats dc(ID) as TOTAL by PERIOD
| join PERIOD [search index=test  STATUS=Closed  FAIL=0 
| stats dc(ID) as MET by PERIOD

Join has been used because in the 3 lines of code, there is additional condition "FAIL=0" and based on this, I can calculate MET. At the end, I would like to have in the final result TOTAL, MET, PERIOD. Condition should be applied just for MET calculation.

Could you help me?

[justinatpnnl]

Something like this should work:

index=test STATUS=Closed
| stats dc(ID) as TOTAL, dc(eval(if(FAIL=0,ID,NULL))) as MET by PERIOD

[diogofgm]

is FAIL present in all events?

[akawacz]

yes, every enent has a flag FAIL (can be 0, 1)

[somesoni2 · Accepted solution]

Try something like this

 index=test STATUS=Closed 
 | stats dc(ID) as TOTAL by PERIOD | append [search index=test  STATUS=Closed  FAIL=0 
 | stats dc(ID) as MET by PERIOD] | stats values(*) as * by PERIOD

Another version

index=test STATUS=Closed | eval isFailed=if(FAIL=0,1,0) | dedup PERIOD, ID | stats count as TOTAL sum(isFailed) as MET by PERIOD

[akawacz]

Thank you
That work really well however can you advise me different solution. I would like to also avoid append command.

Basically I have really large data set while using join and append is really time consuming.

Thank you

[MuS]

Just pointing to an answer where you can get some ideas how it can be done http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi...