Verify if users are in an Active Directory group

[Sasquatchatmars]

Hi all,

I have been trying to create a search which compares results from an index with results from an ldap search. The goal is to check if a user is not one of the groups. 

For now I have this query. 

index="summary_wineventlog" cn=Group1 OR cn=Group1 OR cn=Group3 
| append [ | ldapsearch domain="default" search="(&(objectClass=user))" attrs="sAMAccountName" | rename sAMAccountName AS user | fields user]
| regex user!="^([a-zA-Z0-9_\.-]+)\$$"
| rex field=member_name "(?<username>\S+)+"
| eval result=if(match(username, user),"Contained","Not Contained")

The eval function only shows "Not contained".  My field member_name contains every user delimited with a white space. The weird thing is that the field username only shows every first username of the field member_name. So that field would look like this. 

user1 user2 user3 user4 user5 user6 user7 user8

I also have a lookup which contains a field with the usernames but I can't add it, every time i tried it gave me an error. This was the query that I tried for that.

index="summary_wineventlog" cn=Group1 OR cn=Group2 OR cn=Group3
[| inputlookup account_status_tracker | fields user]
| regex user!="^([a-zA-Z0-9_\.-]+)\$$"
| rex field=member_name "(?<username>\S+)+"
| eval result=if(match(member_name, user),"Contained","Not Contained")

Does someone know how I could check if a user is not in one of the 3 groups with one of the two searches above? 

Thanks 

Sasquatchatmars

[richgalloway]

So the problem with this

index="summary_wineventlog" cn=Group1 OR cn=Group1 OR cn=Group3 
| append [ | ldapsearch domain="default" search="(&(objectClass=user))" attrs="sAMAccountName" | rename sAMAccountName AS user | fields user]
| regex user!="^([a-zA-Z0-9_\.-]+)\$$"
| rex field=member_name "(?<username>\S+)+"
| eval result=if(match(username, user),"Contained","Not Contained")

is the append command puts the output of ldapsearch *below* the results for the base search.  The result is no event has both the user and member_name fields so they never match and result field is always "Not Contained". 

The solution, if you want to continue using append, is to merge the two sets of results before comparing fields.  Do that using stats, provided you have a field common to both data sets on which to base the merge.

Regarding the second search,

index="summary_wineventlog" cn=Group1 OR cn=Group2 OR cn=Group3
[| inputlookup account_status_tracker | fields user]
| regex user!="^([a-zA-Z0-9_\.-]+)\$$"
| rex field=member_name "(?<username>\S+)+"
| eval result=if(match(member_name, user),"Contained","Not Contained")

The output from inputlookup will be added to the base search, resulting in a query that is not likely to produce the desired results.  Run the subsearch by itself with | format to see what I mean.

The fix for that is to modify the subsearch so it produces output that works with the base search.  You may want to add "NOT" before the subsearch so the query finds data in the summary index that is not in the lookup file.

[Sasquatchatmars]

Hi @richgalloway,

I have been trying your suggestion but I can't quite seem to make it work. Would it be possible for you to correct the searches I have made so that I will have an example for futur references? 

For the first one I tried to by renaming the member_name field to user in order to have a common field named user. Than by adding | stats values(*) as * by user which would give 

| ldapsearch domain="default" search="(&(objectClass=group)(cn=GroupName))" 
| ldapgroup 
| append 
    [ ldapsearch domain="default" search="(&(objectClass=user))" attrs="sAMAccountName" 
    | rename sAMAccountName AS user 
    | regex user!="^([a-zA-Z0-9_\.-]+)\$$" 
    | search user!="$*" 
    | fields user] 
| stats values(*) as * by user 
| eval result=if((member_name=user),"Contained","Not Contained")

By the way I started using an ldapsearch as base search so that I'm sure I will always get the updated values. 

For the second search I ran the | format command in the subsearch and uderstand want you mean. The problem is that I don't know how to modify the subsearch so that it will output normal results. Now it shows this.

( ( user="USER1" ) OR ( user="USER2" ) OR ( user="USER3" )OR ..... )

I also tried to add the NOT before the subsearch but is it possible that this can't be used with an ldapsearch? Because I tried this and it gave me an error "Script execution failed for external search command 'ldapgroup'

| ldapsearch domain="default" search="(&(objectClass=group)(cn=EV_*))" 
| ldapgroup 
NOT [| inputlookup account_status_tracker 
    | fields user] 
| rex field=member_name "(?<username>\S+)+" 
| eval result=if(match(member_name, user),"Contained","Not Contained")

I am not working with splunk since a very long time so I'm still trying to learn, sorry if I may be asking basic questions. 

Thank you,

Sasquatchatmars 

[richgalloway]

Renaming member_name to user means you no longer have a member_name field to use in if(member_name=user).  The two searches should be merged using a different common field so you preserve the member_name and user fields for comparison.

I'm not familiar with the ldapgroup command, but it appears it does not accept "NOT".  I don't know how to work around that.

[Sasquatchatmars]

Hi @richgalloway,

I have been trying to do what you say whole week-end. I came with this but nothing seems to work, I'm not getting it to work. 

| ldapsearch domain="default" search="(&(objectClass=group)(cn=GROUP_NAME))" 
| ldapgroup 
| append 
    [ ldapsearch domain="default" search="(&(objectClass=user))" attrs="sAMAccountName" 
    | rename sAMAccountName AS user 
    | fields user] 
| stats values(member_name) as member_name values(user) as user by Joinkey 
| eval result=if(match(member_name, user),"Contained","Not Contained")

Can you give me an example of how I should do it? 

Thanks,

Sasquatchatmars

[richgalloway]

You have the general idea, but I do not know your data so I can't suggest a field by which you can merge the events.