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.
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).
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.
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.