Splunk Support for Active Directory: Using LDAP search, how do I match groups, user names, and titles in a single table?

[scottrunyon]

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?

[scottrunyon · Accepted solution]

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.