Splunk Search

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

akawacz
Path Finder

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?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

justinatpnnl
Communicator

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
SplunkTrust
SplunkTrust

is FAIL present in all events?

------------
Hope I was able to help you. If so, some karma would be appreciated.
0 Karma

akawacz
Path Finder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

akawacz
Path Finder

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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...

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...