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
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"
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
@manjunathmeti
This does not seem to work for me
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
I found the issue with the first query and its working now I think
Thanks
Welcome, I've updated answer. Please accept if it is working for you.
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]
@KailA
Thanks but no luck getting desired outcome
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
@gcusello
Thanks but this has not give me the desired outcome.
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
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