Splunk Search

When comparing to 2 lookup files, is there way to remove records/rows when there's a match?

marceloalejandr
Path Finder

We have 2 inputlookup files, 1 with All-users and another with Disabled-users.   Is there a way to remove the records from the All-users inputlookup file if the user matches/exists in the Disabled-users file and/or if needed generate a new outputlookup file with the new results?   Both files have the same field name, sAMAccountName.    We've tried dedup and append=f, with no luck so far.  We also tried uniq which I think should've only returned unique records, but unfortunately could not get it to work.

Thanks in advanced for your help.  

Labels (1)
0 Karma

PickleRick
Ultra Champion

If disabled users are a subset of all users

| inputlookup allusers
| fields sAMAccountName
| append
[ | lookup disabledusers
| fields sAMAccountName ]
| stats count by sAMAccountName
| where count=1

If you have two sets which can have overlapping values but each also can have unique values you can also use another technique

| inputlookup allusers
| fields sAMAccountName
| eval selector=1
| append
[ | lookup disabledusers
| fields sAMAccountName
| eval selector=2 ]
| stats sum(selector) as selector by sAMAccountName

Now you can see by the selector field if the value was in either of your sets (values 1 or 2 respectively) or both of them (3).

0 Karma

marceloalejandr
Path Finder

Rick, 

Thanks for your post.  Unfortunately your suggestion did not work.    

Perhaps this issue can be addressed at the initial ldapsearch instead of trying find the unique records comparing 2 outputlookup files.  The goal is to get a list of only enabled users that are in a specific Security Group FGPP (Fine Grained Password Policy) in AD.    Here is the ldapsearch we're running and unfortunately it is returning DISABLED users as well.    
| ldapsearch domain=".com" search="(&(objectClass=group)(CN=*FGPP*)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))"
| ldapgroup
| table cn, member_dn, member_name
| mvexpand member_name
| rename member_name AS sAMAccountName
| table sAMAccountName

We've also tried this syntax but it did not return anything:
| ldapsearch domain=".com" search="(&(objectClass=group)(CN=*FGPP*)(&(objectClass=user)(userAccountControl:1.2.840.113556.1.4.803:=512)(!(userAccountControl:1.2.840.113556.1.4.803:=2))))"
....
....

We suspect the issue could be that the ldapsearch contains 2 objectClass (group and user) because if run the ldapsearch separately for each, it works.  If we can find a single ldapsearch SPL to return only the ENABLED users of the users assigned to the FGPP group, then we would not need to run another SPL to compare the users in the FGPP users against  the list of disabled users and eliminate the need for running the second SPL. 

Any thoughts?  Thanks.

0 Karma

PickleRick
Ultra Champion

The ldapsearch approach is a proper one I think. It's better to create a good set at the start than trying to filter it later. Unfortunately I'm not that proficient with AD LDAP attributes to hint how to look for what you want.

0 Karma
Get Updates on the Splunk Community!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...