How to edit my search to omit results of a subsearch from the main search using NOT?

[lbogle]

Hello Splunkers,
I've been trying to solve this problem for a while now but I am still not able to NOT the contents of a sub-search from the main search.

Search 1:

index=asset source="/scratch/cadence_assets/AD-host-report.csv" NOT "Operating System"=*Server* | search "Operating System"=*Windows* | eval Name=lower(Name) | dedup Name | rename Name as hostname | fields + hostname,source | table hostname source

9,646 hostname results

Search 2:

index=asset source="/scratch/cadence_assets/SCEP.csv" | dedup hostname | eval hostname=lower(hostname) | fields +  hostname,source | table hostname source

6,402 hostname results

I would expect that NOT'ing 2 from 1 would result in approx. 3244 results but I'm having trouble with the syntax apparently.

Here is the joined search:

index=asset source="/scratch/cadence_assets/AD-host-report.csv" NOT "Operating System"=*Server* | search "Operating System"=*Windows* | eval Name=lower(Name) | dedup Name | rename Name as hostname | fields hostname,source | table hostname source | search NOT [search index=asset source="/scratch/cadence_assets/SCEP.csv" | dedup hostname | eval hostname=lower(hostname) | fields + hostname,source | table hostname source]

I get 9,646 results which looks like the search is only referencing the AD-host-report.csv report. I am trying to remove the results of search 2 from search one. Or perhaps maybe a table comparing the two by hostname would be the way to go....

[wpreston]

I think the problem is that you are leaving source in your sub search, which means that it will never match any of the values in your main search because the two sources will always be different, so no search results will ever be discarded.

Try removing the source field in your sub search and see if that gives you the results you're looking for.

[wpreston]

I should clarify. When I say remove source from your search, I mean remove it from the results. So your fields statement in your sub search should be

| fields hostname

instead of

| fields hostname source

Also, you don't need the table command in your sub search. I don't know if it will improve performance any by removing it, but the search should work just fine without the table command in the sub search.

[somesoni2]

Try this

index=asset (source="/scratch/cadence_assets/AD-host-report.csv" NOT "Operating System"=*Server*  "Operating System"=*Windows*) OR source="/scratch/cadence_assets/SCEP.csv" | eval Host=coalesce(lower(hostname),lower(Name)) | table Host source | stats values(source) as source by Host| where mvcount(source)=1 AND like(source,"%AD-host-report.csv") | rename Host as hostname

[lbogle]

Thanks Somesoni2,
I got back the same number of results unfortunately. I'm not sure what could be the problem. Could it be something wrong with the data source? There are some blank fields in the data but everything has a name/hostname....

[somesoni2]

Try the updated answer (needed field renaming I guess).

[lbogle]

I think also that the search needs to mandatory identify both sources (like an AND).

[lbogle]

Interesting search!
I got back 13K results which is more than I was expecting.
I like that eval hostname=coalesce(lower(hostname),lower(Name)) bit.
I'm keeping that 🙂

[lbogle]

Hi sk313. Thanks for the reply.
I can see the utility of these commands but they don't seem to work for my particular search. I have two searches, both with common hostname fields but I need exclude a specific set of events based on a field (in this case hostnames) based on source from another list. It seems like set doesn't allow you to specify which set to exclude
Set intersect sounded hopeful but it gives me the values that are common to both, but I need to find specifically which are present in search 1 after the items in search 2 have been excluded from search 1.
Does that make sense?

[Zakem]

I had this issue as well and did not find an intuitive way to do it directly, but got a roundabout workaround that worked perfectly.

Use the Set Diff command to get the mutually exclusive results, then take the intersection of those results with the set of events that you want to include.

So the generalized structure is for search 1 = results I want and search 2 = results I want removed from search 1:

| set intersect [ search 1 | field <some field> ] [ set diff [ search 1 | <some field> ] [ search 2 | field <some field> ] ]

This finds the results that are either in search 1 or search 2 but not both, then removes the results that are in search 2.

[sk314]

Have you looked at the diff command? More information at the following link:

http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/Diff

Also, since you are at it, you could use the set command if you want union, difference, or intersection of two searches.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Set