All Apps and Add-ons

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

scottrunyon
Contributor

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?

0 Karma
1 Solution

scottrunyon
Contributor

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.

View solution in original post

0 Karma

scottrunyon
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...