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
Champion

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
Champion

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
Champion

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!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...