topic Re: Search using NOT in Splunk Search

Search using NOT

Sriram — Thu, 21 May 2015 16:07:45 GMT

I have a query like this to llist thread from datasummary1 which does'nt exist in datasummary2. (something like this below). I am not getting accurate results. Any ideas where the issue is ?

if i run individual queries
datasummary1 ==> 220000
datasummary2 ==> 40000

the below query give me somewhere around 210000, where i am expecting 180000.
index=datasummary1 NOT [search index=datasummary2 | dedup ThreadId | fields ThreadId] | table ThreadId

Re: Search using NOT

richgalloway — Thu, 21 May 2015 16:25:42 GMT

Try this:

index=datasummary1 NOT [search index=datasummary2] | dedup ThreadId | table ThreadId

Re: Search using NOT

woodcock — Fri, 22 May 2015 01:49:41 GMT

Outer Join:

index=datasummary1 OR index=datasummary2 | stats dc(index) AS indices BY ThreadId | where indices==1

Re: Search using NOT

acharlieh — Fri, 22 May 2015 02:41:08 GMT

There are two possibilities that could be throwing off your numbers 1) Not all ThreadId in datasummary2 appears in datasummary1, and 2) there could be duplication in your data (this is @richgalloway 's solution).

Now to find the number of ThreadIds unique to each index, and the number of ThreadIds common to both you could use this query:

index=datasummary1 OR index=datasummary2 | stats values(index) as index by ThreadId | eval index=if(mvcount(index)==2,"both",index) | stats count by index

Alternatively if you want which ThreadIds are in each:

index=datasummary1 OR index=datasummary2 | stats values(index) as index by ThreadId | eval index=if(mvcount(index)==2,"both",index) | stats values(ThreadId) as ThreadId by index

Or a slightly alternate solution for your problem:

index=datasummary1 OR index=datasummary2 | stats values(index) as index by ThreadId | eval index=if(mvcount(index)==2,"both",index) | where index="datasummary1"

Re: Search using NOT

acharlieh — Fri, 22 May 2015 02:43:22 GMT

This will also include ThreadId values in index=datasummary2 that are not present in index=datasummary1

Re: Search using NOT

acharlieh — Fri, 22 May 2015 02:45:58 GMT

You'll want to keep the dedup within the subsearch so we're less likely to hit the subsearch limit. (in addition to the dedup outside that you've added)

Re: Search using NOT

Arun_N_007 — Fri, 22 May 2015 04:06:57 GMT

It would be better you use eventstats instead of stats because it will retain the other fields.

Re: Search using NOT

acharlieh — Fri, 22 May 2015 04:11:56 GMT

Depending on if I wanted other fields yes, but the way the question is worded leads me to believe that the goal is solely ThreadId by index in which case no need to keep around the bulk of additional fields. The original search ended with a table ThreadId which is more evidence to this assumption

Re: Search using NOT

Arun_N_007 — Fri, 22 May 2015 04:15:04 GMT

Oh my bad 🙂

Re: Search using NOT

Sriram — Fri, 22 May 2015 15:09:04 GMT

Thank you all for quick response. As acharlieh correctly pointed out that The issue appears to be subsearch result limit causing the numbers to go awry. I set a limit of 60000 (it is still ugly, but it seems to be working) and also changed the 'fields ThreadId' to 'table ThreadId' as per richgalloway suggestion.

index=datasummary1 ThreadId = "*" NOT [search index=datasummary2 | dedup ThreadId | table ThreadId | format maxresults=60000] | table ThreadId,EventDate,EventTime,status

i will explore the other suggestions mentioned in the post as my subsearch limit will definitely be more 60000 at some point.

Thanks again. You guys are awesome !