Difference between users

[JandrevdM]

Good day,

I am trying to get a dashboard up and going to easily find the difference between two users groups. I get my information pulled from AD into splunk and then if user1 has a group that user2 doesnt have then I can easily compare two users to see what is missing. Example users in the same department typically require the same access but one might have more privileges and that is what I want to see.

So my search works fine, only problem is it only gives me the group difference and now I cant see who has that group in order to add it to the user that doesnt have the group.

I want to add the user next to the group:
example

group	user
G-Google	user1
G-Splunk	user2

| set diff
    [ search index=db_assets sourcetype=assets_ad_users $user1$
    | dedup displayName sAMAccountName memberOf
    | makemv delim="," memberOf
    | mvexpand memberOf
    | rex field=memberOf "CN=(?<Group>[^,]+)"
    | where Group!=""
    | table Group ]
    [ search index=db_assets sourcetype=assets_ad_users $user2$
    | dedup displayName sAMAccountName memberOf
    | makemv delim="," memberOf
    | mvexpand memberOf
    | rex field=memberOf "CN=(?<Group>[^,]+)"
    | where Group!=""
    | table Group ]

[bowesmana]

@JandrevdM as your search is doing the same search twice just with a different user, you'd be better off just doing a single search and splitting by user, e.g. - similar to your existing search

index=db_assets sourcetype=assets_ad_users ($user1$ OR $user2$) 
| dedup displayName sAMAccountName memberOf 
| makemv delim="," memberOf 
| mvexpand memberOf 
| rex field=memberOf "CN=(?<Group>[^,]+)" 
| where Group!="" 
| stats values(Group) as Groups by user

which will give you a user column and then a multivalue field with the list of groups

If you then want to automatically show the differences between the two users, you can following that with

| transpose 0 header_field=user
| eval UniqueU1=mvmap(User1, if(User1!=User2,User1,null()))
| eval UniqueU2=mvmap(User2, if(User2!=User1,User2,null()))
| eval Common=mvmap(User1, if(User1=User2,User1,null()))

and it will give you a list of groups unique to user 1, user 2 and the common groups.

However, your existing search could be more efficiently done with

index=db_assets sourcetype=assets_ad_users ($user1$ OR $user2$) 
| fields displayName sAMAccountName memberOf
| stats latest(*) as * by user
| eval memberOf=split(memberOf,",")
| rex field=memberOf max_match=0 "CN=(?<Group>.+)"
| fields - memberOf

If you really want a row by row breakdown of groups, you can do the base search and then just do this

| chart count over Group by user
| foreach * [ eval <<FIELD>>=if("<<FIELD>>"="Group", <<FIELD>>, if('<<FIELD>>'=1, "Member", "Missing")) ]

which will tell you Membership status of each group per user

[richgalloway]

That is the nature of the set diff command - it will tell there's a difference, but doesn't say what it is.  See https://docs.splunk.com/Documentation/Splunk/9.3.2/SearchReference/Set

An alternative would be to count the members of each group and show those with only one member.

| multisearch
    [ search index=db_assets sourcetype=assets_ad_users $user1$
    | dedup displayName sAMAccountName memberOf
    | makemv delim="," memberOf
    | mvexpand memberOf
    | rex field=memberOf "CN=(?<Group>[^,]+)"
    | where Group!=""
    | eval User=$user1$
    | table Group User ]
    [ search index=db_assets sourcetype=assets_ad_users $user2$
    | dedup displayName sAMAccountName memberOf
    | makemv delim="," memberOf
    | mvexpand memberOf
    | rex field=memberOf "CN=(?<Group>[^,]+)"
    | eval User=$user2$
    | where Group!=""
    | table Group User ]
| stats values(User) as Users by Group
| where mvcount(Users)=1