Combined Search - Difference in two lists

[splunk219783]

This always feels exceptionally difficult to me, i'm not sure what i'm missing.

I have a list of machines, a simple CSV with a Name, Category, and Tag.  Kind of like this:

VM	Category	Tag
VM1	Backup	Friday
VM1	Datacenter	AWS
VM2	Backup	Monday
VM2	Critical	Yes
VM2	Datacenter	Azure
VM3	Critical	No
VM3	Datacenter	Azure

I want to find machines that do not have a backup Category, so in this example it would be VM3.

I've written a search to give me all Machines, and another one to give me all machines with backups.  I've written this:

index=vcenter source=*tag* | dedup VM | fields VM | search VM NOT [search index=vcenter source=*tag* Category=Backup* | dedup VM | fields VM] | table VM

I get some results but not all.

[richgalloway · Accepted solution]

You have the general idea.

Check the subsearch by running it by itself and adding "| format" on the end.  You'll see it returns a result that looks like "(VM="VM1" OR VM="VM2)"".  That string is added to the main search to complete the query so we end up with 

index=vcenter source=*tag* | dedup VM | fields VM | search VM NOT (VM="VM1" OR VM="VM2") | table VM

 There's an extra "VM" in the search command.  Removing it works in my sandbox.

[splunk219783]

Thanks Rich.  When I run the subsearch by itself with | format i do get a whole list of:

(VM="VM1") OR (VM="VM2") OR (VM="VM3")

I'm not following on where I have the extra VM in my subsearch though?

[splunk219783]

For anyone who stumbles in the future Rich was right, but I think his example was not.  I did not need VM between search and not in the second search.  The final search was:

index=vcenter source=*tag* | dedup VM | fields VM | search NOT [search index=vcenter source=*tag* Category=Backup* | fields VM]