topic Re: Syntax for subsearches for using NOT function btw 2 savedsearches in Splunk Search

Syntax for subsearches for using NOT function btw 2 savedsearches

pradeep0802 — Wed, 17 Apr 2013 10:08:18 GMT

Hi Guys,

I have here 2 savedsearches, now i want to do a left outer join between both of them.
I'm using the following query:
| savedsearch "saved1" NOT [| savedsearch "saved2" | dedup accid | fields accid]

There seems to be a problem with the syntax.
Or is it not possible to use it with SavedSearch??

Please Help.
Thanks!!

Re: Syntax for subsearches for using NOT function btw 2 savedsearches

Ayn — Wed, 17 Apr 2013 10:51:07 GMT

The subsearch's results will be expanded to something like

((accid="accid1") OR (accid="accid2") OR ... )

Which is incorrect syntax for the subsearch command. You probably want to use it with the search command.

| savedsearch "saved1" | search NOT [| savedsearch "saved2" | dedup accid | fields accid]

Re: Syntax for subsearches for using NOT function btw 2 savedsearches

pradeep0802 — Wed, 17 Apr 2013 11:17:25 GMT

if i use
| search NOT
the resulted output is wrong

o\p saved1 is 20 rows and saved2 is 14 rows, there are 12 rows common in both. what we need is 8 rows from saved1 that are not present in saved2.. But using | search NOT gives result similar to outer join. ie 20 rows of saved1

what we are trying to achieve is similar to NOT IN clause in sql.

Any suggestion where we be going wrong..

Re: Syntax for subsearches for using NOT function btw 2 savedsearches

alacercogitatus — Wed, 17 Apr 2013 12:36:19 GMT

I get around the limitation of |search NOT [subsearch] by putting the NOT in the subsearch.

This forces the query returned to use the NOT.

Re: Syntax for subsearches for using NOT function btw 2 savedsearches

pradeep0802 — Thu, 18 Apr 2013 04:56:21 GMT

gives matching rows from both savedsearches.

but we need other unmatched rows from saved1.

any suggestion..? or any other way we can achieve this.