lookup users that do not match users in search

[nathanluke86]

I have created a search to match search results for users to users in a lookup:

| inputlookup AD_User_LDAP_list append=true where OU IN ("staff", "contractors") cn!=DEL cn!=Qual*
| fields sAMAccountName
|eval matchfield=sAMAccountName
|join matchfield
[search index="windows_events" sourcetype=XmlWinEventLog source=XmlWinEventLog:Security host=dc* action=success user = . NOT user=.da NOT user=.sa NOT user=.fnpa NOT user=.fpa (EventCode=4624 OR EventCode=4634 )
|eval matchfield = user]
| table user sAMAccountName

What I am trying to accomplish now is to table users that are not matched with the lookup field.

lookup field has 261 users
search has found 208 users

I want to display the 53 users that were not matched from the lookup field sAMAccountName

TIA

[woodcock]

I have created a search to match search results for users to users in a lookup:

index="windows_events" AND sourcetype="XmlWinEventLog" AND source="XmlWinEventLog:Security" AND host="dc*" AND action="success" AND user = "*.*" AND NOT user="*.da" AND NOT user="*.sa" AND NOT user="*.fnpa" AND NOT user="*.fpa" AND (EventCode="4624" OR EventCode="4634") 
| rename user AS joiner
| fieldsjoiner sAMAccountName
| eval which="AD"
| inputlookup append=true AD_User_LDAP_list where OU IN ("staff", "contractors") AND cn!="DEL" AND cn!="Qual*"
| fields sAMAccountName
| eval joiner = coalesce(joiner, sAMAccountName)
| fields joiner
| eval which = coalesce(which, "LOOKUP")
| stats dc(which) AS which_count values(which) AS whiches values(*) AS * BY joiner
| where which_count=1 AND whiches="AD"

[manjunathmeti]

hi @nathanluke86,

You can try this:

| inputlookup AD_User_LDAP_list where OU IN ("staff", "contractors") cn!=DEL cn!=Qual* NOT 
    [ search index="windows_events" sourcetype=XmlWinEventLog source=XmlWinEventLog:Security host=dc* action=success user = . NOT user=.da NOT user=.sa NOT user=.fnpa NOT user=.fpa (EventCode=4624 OR EventCode=4634 ) 
    | stats count by user 
    | eval sAMAccountName = user
    | fields sAMAccountName
    | format] 
| table sAMAccountName

[nathanluke86]

@manjunathmeti

This does not seem to work for me

[manjunathmeti]

Try this:

| inputlookup AD_User_LDAP_list where OU IN ("staff", "contractors") cn!=DEL cn!=Qual* NOT 
     [ search index="windows_events" sourcetype=XmlWinEventLog source=XmlWinEventLog:Security host=dc* action=success user = . NOT user=.da NOT user=.sa NOT user=.fnpa NOT user=.fpa (EventCode=4624 OR EventCode=4634 ) 
     | stats count by user 
     | eval sAMAccountName = user
     | fields sAMAccountName 
     | format] 
 | table sAMAccountName

[nathanluke86]

I found the issue with the first query and its working now I think

Thanks

[manjunathmeti]

Welcome, I've updated answer. Please accept if it is working for you.

[KailA]

Hello,

I think you can try something like that :

index="windows_events" sourcetype=XmlWinEventLog source=XmlWinEventLog:Security host=dc* action=success user = . NOT user=.da NOT user=.sa NOT user=.fnpa NOT user=.fpa (EventCode=4624 OR EventCode=4634 )
| where NOT [| inputlookup AD_User_LDAP_list 
| search OU IN ("staff","contractors") AND cn!="DEL" AND cn!="Qual*"
| eval user = sAMAccountName
| fields user]

[nathanluke86]

@KailA

Thanks but no luck getting desired outcome

[gcusello]

Hi @nathanluke86,
probabbly the problem is related to the limit of 50,000 events in subsearches.
You should have a different approach:

index="windows_events" sourcetype=XmlWinEventLog source=XmlWinEventLog:Security host=dc* action=success user = . NOT user=.da NOT user=.sa NOT user=.fnpa NOT user=.fpa (EventCode=4624 OR EventCode=4634 )
| eval matchfield = lower(user)
| stats count BY matchfield 
| append [ 
     | inputlookup AD_User_LDAP_list append=true WHERE OU IN ("staff", "contractors") cn!=DEL cn!=Qual*
     | eval matchfield = lower(sAMAccountName), count=0
     | fields matchfield count
     ]
| stats sum(count) AS total BY matchfield 
| where total=0

In this way you have the list of the users in the lookup that didn't logged in to the sistem.

Ciao.
Giuseppe

[nathanluke86]

@gcusello

Thanks but this has not give me the desired outcome.

[gcusello]

Hi @nathanluke86,
what's you outcome?
remeber that you search has two problems: there's a limit of 50,000 results in subsearches and join is a very slow command to use only if there isn't any other solution!

Ciao.
Giuseppe

[nathanluke86]

https://answers.splunk.com/answers/562185/compare-search-results-with-a-lookup-table-and-ide-1.html

This answer I found has gave me the desired outcome

Thanks for your help