Splunk Search

lookup users that do not match users in search

nathanluke86
Communicator

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

0 Karma

woodcock
Esteemed Legend

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

manjunathmeti
SplunkTrust
SplunkTrust

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
Communicator

@manjunathmeti

This does not seem to work for me

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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

nathanluke86
Communicator

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

Thanks

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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

0 Karma

KailA
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

nathanluke86
Communicator

@KailA

Thanks but no luck getting desired outcome

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Communicator

@gcusello

Thanks but this has not give me the desired outcome.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

nathanluke86
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...