Splunk Search

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

Contributor

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....

Tags (3)
0 Karma

Motivator

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.

0 Karma

Motivator

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.

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma

Contributor

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....

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

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

0 Karma

Contributor

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 🙂

0 Karma

Contributor

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?

0 Karma

Builder

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

0 Karma