Splunk Search

How can I add where statement but only if something matches

splunker1981
Path Finder

Hello experts,

I have a search that I am trying to add a where statement to which compares fieldvalueA to fieldvalueB. where statement works great but I'd like to only hit the where statement only if a specific field does not exist. Hope that makes sense

My search that currently works looks something like this

searchHere (index=xyz sourcetype=xxx) OR (index=yyy sourcetype=tttt) 
|stats values(x) values(y) by fieldY
|where test1=test2

What I'd like to do is; if the second sourcetype does not return in the result-set then don't execute the where statement.

searchHere (index=xyz sourcetype=xxx) OR (index=yyy sourcetype=tttt) 
|stats values(x) values(y) by fieldY
|where test1=test2 if(exists(sourcetype=ttt)

Pretty sure there's a better way to do that, hoping someone can point me in the right direction.

Cheers and thanks for the help!

Tags (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

You were so close!

(index="1" AND sourcetype="1") OR (index="2" sourcetype="2")
|eval joiner=coalesce(uqIDX, buildIDX, "JUSTINCASE")
|eval sourcetype1_value=if((sourcetype="1"), coalesce(sourcetype1_value, "0_no_build"), null())
|eval sourcetype2_value=if((sourcetype="2"), coalesce(sourcetype2_value, "Manual"), null())
|stats values(sourcetype1_value), values(sourcetype2_value), values(sourcetype2_valueB), values(sourcetype2_value3) BY joiner

View solution in original post

0 Karma

woodcock
Esteemed Legend

You were so close!

(index="1" AND sourcetype="1") OR (index="2" sourcetype="2")
|eval joiner=coalesce(uqIDX, buildIDX, "JUSTINCASE")
|eval sourcetype1_value=if((sourcetype="1"), coalesce(sourcetype1_value, "0_no_build"), null())
|eval sourcetype2_value=if((sourcetype="2"), coalesce(sourcetype2_value, "Manual"), null())
|stats values(sourcetype1_value), values(sourcetype2_value), values(sourcetype2_valueB), values(sourcetype2_value3) BY joiner
0 Karma

somesoni2
Revered Legend

So if there is no data coming from sourcetype=ttt (which will be aggregated by your stats if present), then do not apply any filter? If this is correct, give this a try

searchHere (index=xyz sourcetype=xxx) OR (index=yyy sourcetype=tttt) 
 |stats values(x) values(y) values(sourcetype) as sts by fieldY
 |where test1=test2 AND isnotnull(mvfilter(match(sts,"tttt")))
0 Karma

splunker1981
Path Finder

That worked but only if both tests matched. What I'd like to do is make the where statement fail if the sourcetype doesn't exist. Might be a better way to rewrite what I am trying to do which is essentially not use a join statement and use | stats instead

0 Karma

splunker1981
Path Finder

I'm starting by searching two indexes/sourcetypes

(index=1 and sourcetype=1) OR (index=2 and sourcetype=2)

Both have a few fields in common but I need to pull values from each sourcetype specifically.

sourcetype1 and sourcetype2 have 2 fields in common which I was using to join build_idx uqIDX.

Data always exists in the build sourcetype (sourcetype1) but only if the process has made it out of phase1 will it exist in sourcetype2. When I use stats to print fields from both sourcetypes, if an event does not exist in the second sourcetype then I get zero results.

Something like the below

index=1 sourcetype=1 OR index=2 sourcetype=2
|eval joinOn=coalesce(uqIDX,buildIDX)
|eval sourcetype1_value=if(isnull(sourcetype1_value),"0_no_build",sourcetype1_value) 
|eval sourcetype2_value=if(isnull(sourcetype2_value),"Manual",sourcetype2_value) 
|stats values(sourcetype1_value), values(sourcetype2_value), values(sourcetype2_valueB), values(sourcetype2_value3) by joinOn
0 Karma
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...