Using the Splunk Supporting Add-on for Active Directory, I have been tasked to find out which users are assigned to specific groups. I can get a table showing the "Common Name" of the users in each group -
|ldapsearch domain=default search="(objectClass=group)"|table cn,distinguishedName
|ldapgroup|rex field=member_dn "CN=(?P\w*\s\w*)"| table cn,UserName | rename cn AS "Group"
Results of the search looks like this
Group UserName
IT Support Fred Flintstone
[blank] Barney Rubble
.
.
Security Thomas Magnum
[blank] Frank Cannon
I then run the following search to get the title of the user -
|ldapsearch domain=default search="(&(objectClass=user)(!(objectclass=computer)))" | dedup cn title | table cn title | rename cn AS UserName, title AS Title
Search results look like this -
UserName Title
Fred Flintstone Computer Analyst
Barney Rubble Senior Computer Analyst
Thomas Magnum Security Guard
Frank Cannon Security Manager
I what to have a table that combines the searches to look like this -
Group UserName Title
IT Support Fred Flintstone Computer Analyst
[blank] Barney Rubble Senior Computer Analyst
.
.
Security Thomas Magnum Security Guard
[blank] Frank Cannon Security Manager
I have tried join, append, appendcols and cannot get the items to line up correctly. What am I missing?
I ended up having to break the search into 2 parts, the first one creating a lookup CSV file that matched the UserName to Title
|ldapsearch domain=default search="(&(objectClass=user)(!(objectclass=computer)))"
| dedup cn title
| table cn title
| rename cn AS UserName, title AS Title
| outputlookup ldaptitletouser.csv
I then used this file to do a lookup to match up the UserName in the Group search -
|ldapsearch domain=default search="(objectClass=group)"
|table cn, member
| rex field=member "CN=(?P\w*\s\w*)"
| rename cn AS "Group"
| table Group, UserName
| lookup ldaptitletouser.csv UserName OUTPUT Title
| table Group UserName Title
Hopefully someone else can use this.
I ended up having to break the search into 2 parts, the first one creating a lookup CSV file that matched the UserName to Title
|ldapsearch domain=default search="(&(objectClass=user)(!(objectclass=computer)))"
| dedup cn title
| table cn title
| rename cn AS UserName, title AS Title
| outputlookup ldaptitletouser.csv
I then used this file to do a lookup to match up the UserName in the Group search -
|ldapsearch domain=default search="(objectClass=group)"
|table cn, member
| rex field=member "CN=(?P\w*\s\w*)"
| rename cn AS "Group"
| table Group, UserName
| lookup ldaptitletouser.csv UserName OUTPUT Title
| table Group UserName Title
Hopefully someone else can use this.