Inputlookup and sourcetype NOT not working

[willadams]

I have 2 searches that I am trying to derive information on. The first is an inputlookup that is derived from a powershell script that gets information from AD. The second is a sourcetype that is information pulled from a database (in this instance a client that is either installed or not). The purpose of my query is to identify machines that ARE in the inputlookup and if it is NOT in the sourcetype to tell me about it (in this instance I am just doing a stats to keep track of the number that meet this condition).

However I am running into a problem where the NOT statement is not working.

The following query was written

• | inputlookup active_servers | search NOT [search sourcetype=sccm:resource | fields name] | stats dc(name)

This returns values as expected. However the value is wrong. Breaking my search up into its 2 components

• | inputlookup active_servers | search name=PERSVR01

This returns a result as expected. This tells me that PERSVR01 is an active server.

Doing a search in my sourcetype

• sourcetype=sccm:resource name=PERSVR01

returns a result showing that the server exists in that sourcetype.

So therefore I have confirmed that

1) the server exists in the inputlookup
2) the server exists in the sourcetype

What I need my query to tell me is "if the server is in active_servers; and is NOT in the sourcetype return a result". The original query should do this but doesn't.

Any thoughts?

[willadams]

I found the issue. The sourcetype I was using didn't appear to have the correct data. Also found out that I needed to dedup on the subsearch due to return result limitation of subsearch.

[tiagofbmm]

Hey

Can you try using the return command:

| inputlookup active_servers | search NOT [search sourcetype=sccm:resource | fields name | return name] | stats dc(name)

[willadams]

Unfortunately no this doesn't work. The original output generates a value of 25 (for example). Running this query then extends this number to over 1400.