Splunk Search
Highlighted

lookup users that do not match users in search

Communicator

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

| inputlookup ADUserLDAPlist 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

0 Karma
Highlighted

Re: lookup users that do not match users in search

Legend

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

Highlighted

Re: lookup users that do not match users in search

Communicator

@gcusello

Thanks but this has not give me the desired outcome.

0 Karma
Highlighted

Re: lookup users that do not match users in search

Legend

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

0 Karma
Highlighted

Re: lookup users that do not match users in search

Communicator

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

0 Karma
Highlighted

Re: lookup users that do not match users in search

Contributor

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

Re: lookup users that do not match users in search

Communicator

@KailA

Thanks but no luck getting desired outcome

0 Karma
Highlighted

Re: lookup users that do not match users in search

Influencer

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
Highlighted

Re: lookup users that do not match users in search

Communicator

@manjunathmeti

This does not seem to work for me

0 Karma
Highlighted

Re: lookup users that do not match users in search

Influencer

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