"search NOT" not working - not excluding the expected results

[Kozanic]

Hi All,

Trying to figure this one out - suspect it's going to be something simple - just not sure what it is.

Have the below search:

| ldapsearch search="(&(objectClass=user)(!(objectClass=computer))(sAMAccountName=a_*))" attrs="employeeID, displayName, sAMAccountName, userAccountControl, extensionAttribute4, lastLogonTimestamp"
| search userAccountControl!=ACCOUNTDISABLE
| rename sAMAccountName as sAMAccountNameAdmin 
| rex field=_raw "sAMAccountName\":\"[Aa]_(?<sAMAccountName>[A-Za-z0-9]*)"
| search NOT 
    [| inputlookup dlm_msadAllAccounts.csv
    | table sAMAccountName]
| table sAMAccountNameAdmin displayName lastLogonTimestamp
|rename sAMAccountNameAdmin as sAMAccountName lastLogonTimestamp as "Last Login Date" displayName as "Full Name"

I have run this search almost identically using ADmon source, and the NOT part of the search works perfectly.
This search below:

index=dlm_msad eventtype=admon-user sAMAccountName="a_*" 
| dedup sAMAccountName 
| search userAccountControl!=514 userAccountControl!=66050 userAccountControl!=546 userAccountControl!=66082
| rename sAMAccountName as sAMAccountNameAdmin 
| rex field=_raw "sAMAccountName=[Aa]_(?<sAMAccountName>.*)" 
| search NOT 
    [| inputlookup dlm_msadAllAccounts.csv 
    | table sAMAccountName] 
| table sAMAccountNameAdmin displayName lastLogonTimestamp
|rename sAMAccountNameAdmin as sAMAccountName lastLogonTimestamp as "Last Login Date" displayName as "Full Name"

We recently added Splunk Supporting Add-on for Active Directory so wanted to update all the dashboard to use ldapsearch instead as the input.

Not sure why the search with ldap is not working correctly compared to the ADMon version.

Essentially though, the NOT is just not working. I'm seeing users listed in the final results that are definitely within the lookup, users which should be excluded from the final results if the NOT was working correctly.

Appreciate any assistance \ suggestions that can help figure this one out.

[Kozanic · Accepted solution]

So... after much stuffing about, I was informed about the 10k return limitation of subsearches.

As our NOT search was returning more then 10K, the overflow was impacting our final results.

But - I found this, but provided the solution to my issues:
https://answers.splunk.com/answers/207150/how-to-overcome-sub-search-limitation-only-10k-rec.html

I just came across this gem via a
co-worker. do:

 dedup Order_Number 
|search NOT [
 | inputlookup Order_Details_Lookup.csv 
 | stats  values(Order_Number) AS  Order_Number]
| table Order_Number

That will make the subsearch return a
single row with a multi-value field
containing all of the order numbers
but the individual values will get
passed along correctly into the base
search.

[somesoni2]

Can you confirm if both the searches, before search NOT command has same values for field sAMAccountName?

[Kozanic]

I have tweaked the search slightly just to confirm the matching is working correctly within the search by looking for a single user:

| search sAMAccountName
    [ | inputlookup dlm_msadAllAccounts.csv
      | table sAMAccountName
      | search sAMAccountName=kxxxxm ]

This returns 1 results as expected.

Given that matching is definitely working, and that I have the almost identical search working using the NOT with a filter on the lookup to reduce the compared records - I'm running out of ideas on why this is not working without a filter on the lookup.

[Kozanic]

Yes - I have tested that to ensure that the values match, including cut and pasting the sAMAccount name from the original Search into a new search on the lookup to confirm there is a match.

Interesting though in double checking this, I have found that some values are actually being filtered out by the NOT. If I remove the NOT, I get 140 results back - compared to 107 with. expected results though are about 5 - so a long way from where I need to be.

[somesoni2]

When you did copy paste, I assume you did it from table visualization, which truncates leading/tailing spaces from values. Can you also compare the lengh of field sAMAccountName from both searches? Since you said it works partially, my bet will be on values not matching exactly due to spaces or anything similar.

[Kozanic]

I tested the sAMAccountName on both the Search and the lookup - using eval to add a _ before and after the field value.
In both instances, there were no extra spaces in the value.

I have also just tested with len(sAMAccountName), in both lookup and search, the field is 6 characters so also matches there.

[Kozanic]

Just to add some extra info - and confusion - I'm running another version of this report with a minor tweak - which works perfectly:
Tweak is that I'm filtering disabled users - this reduces the number of results in the lookup to about 6k, rather than 20k with out the filter.

| search NOT 
    [| inputlookup dlm_msadAllAccounts.csv 
    | search userAccountControl!=ACCOUNTDISABLE*
    | table sAMAccountName] 

Are there any restrictions in relation to NOT and the number of records it can process?

[gnoellbn]

Not too sure but maybe the lookup you did has permission only for a particular app ?

[Kozanic]

Checked permissions - all good there.

Also, lookup created within the same App doing the search.