Search using NOT

[Sriram]

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

[acharlieh · Accepted solution]

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"

[Sriram]

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 !

[acharlieh]

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

[Arun_N_007]

Oh my bad 🙂

[Arun_N_007]

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

[woodcock]

Outer Join:

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

[acharlieh]

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

[richgalloway]

Try this:

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

[acharlieh]

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)