All Apps and Add-ons

How to run an ldapsearch to find all users in an OU in Active Directory, then export all users' events to a table?

dkorlat
Explorer

I need to find all the users in a OU in Active Directory, currently I run:

| ldapsearch domain=internal.local basedn=,OU=Finance,OU=Users,DC=internal,DC=local" scope="sub" search="(objectClass=user)" 

That gets me all the users in the OU. I also export all the users event from safend to a table using:

index=safend source=Safend_DataEventsView sourcetype=dbx2 | sort by -_time | convert timeformat="%d/%m/%Y %I:%M:%S %p" ctime(_time) | replace 0 with Read, 1 with Write | eval megabytes=((FileSize/1024)/1024) | eval "File Size"= megabytes + " MB"  | table _time, User, FileName, FileExtension, ClientHost, DeviceDescription, FileOperation, "File Size" , IPAddresses, PolicyName, ClientVersion | rename _time as Time, DeviceDescription as "Device Description", FileName as "File Name", FileExtension as "File Extension", ClientHost as "From PC", FileOperation as "File Operation", PolicyName as "Policy Name", IPAddresses as "IP Address", ClientVersion as "Client Version"

I need to find a way I can first find the users using the ldapsearch and then generate a table using the above command.
When I run the LDAP command in a subsearch, no data is shown to run the second command find.

0 Karma
1 Solution

jkat54
SplunkTrust
SplunkTrust

Change userNameField to be the name of the username field in the ldap subsearch.

index=safend source=Safend_DataEventsView sourcetype=dbx2 [| ldapsearch domain=internal.local basedn="OU=Finance,OU=Users,DC=internal,DC=local" scope="sub" search="(objectClass=user)" | fields userNameField ] | sort by -_time | convert timeformat="%d/%m/%Y %I:%M:%S %p" ctime(_time) | replace 0 with Read, 1 with Write | eval megabytes=((FileSize/1024)/1024) | eval "File Size"= megabytes + " MB" | table _time, User, FileName, FileExtension, ClientHost, DeviceDescription, FileOperation, "File Size" , IPAddresses, PolicyName, ClientVersion | rename _time as Time, DeviceDescription as "Device Description", FileName as "File Name", FileExtension as "File Extension", ClientHost as "From PC", FileOperation as "File Operation", PolicyName as "Policy Name", IPAddresses as "IP Address", ClientVersion as "Client Version"

This will create a search like this:

index=safend source=Safend_DataEventsView sourcetype=dbx2 (userNameField=user1 AND userNameField=user2 AND userNameField=user3 ...) | sort by -_time | convert timeformat="%d/%m/%Y %I:%M:%S %p" ctime(_time) | replace 0 with Read, 1 with Write | eval megabytes=((FileSize/1024)/1024) | eval "File Size"= megabytes + " MB" | table _time, User, FileName, FileExtension, ClientHost, DeviceDescription, FileOperation, "File Size" , IPAddresses, PolicyName, ClientVersion | rename _time as Time, DeviceDescription as "Device Description", FileName as "File Name", FileExtension as "File Extension", ClientHost as "From PC", FileOperation as "File Operation", PolicyName as "Policy Name", IPAddresses as "IP Address", ClientVersion as "Client Version"

View solution in original post

jkat54
SplunkTrust
SplunkTrust

Change userNameField to be the name of the username field in the ldap subsearch.

index=safend source=Safend_DataEventsView sourcetype=dbx2 [| ldapsearch domain=internal.local basedn="OU=Finance,OU=Users,DC=internal,DC=local" scope="sub" search="(objectClass=user)" | fields userNameField ] | sort by -_time | convert timeformat="%d/%m/%Y %I:%M:%S %p" ctime(_time) | replace 0 with Read, 1 with Write | eval megabytes=((FileSize/1024)/1024) | eval "File Size"= megabytes + " MB" | table _time, User, FileName, FileExtension, ClientHost, DeviceDescription, FileOperation, "File Size" , IPAddresses, PolicyName, ClientVersion | rename _time as Time, DeviceDescription as "Device Description", FileName as "File Name", FileExtension as "File Extension", ClientHost as "From PC", FileOperation as "File Operation", PolicyName as "Policy Name", IPAddresses as "IP Address", ClientVersion as "Client Version"

This will create a search like this:

index=safend source=Safend_DataEventsView sourcetype=dbx2 (userNameField=user1 AND userNameField=user2 AND userNameField=user3 ...) | sort by -_time | convert timeformat="%d/%m/%Y %I:%M:%S %p" ctime(_time) | replace 0 with Read, 1 with Write | eval megabytes=((FileSize/1024)/1024) | eval "File Size"= megabytes + " MB" | table _time, User, FileName, FileExtension, ClientHost, DeviceDescription, FileOperation, "File Size" , IPAddresses, PolicyName, ClientVersion | rename _time as Time, DeviceDescription as "Device Description", FileName as "File Name", FileExtension as "File Extension", ClientHost as "From PC", FileOperation as "File Operation", PolicyName as "Policy Name", IPAddresses as "IP Address", ClientVersion as "Client Version"

dkorlat
Explorer

Thanks

This solved the problem:

index=safend source=Safend_DataEventsView sourcetype=dbx2 [| ldapsearch domain=internal.local basedn="OU=Finance,OU=Users,DC=internal,DC=local" scope="sub" search="(objectClass=user)" | eval User =lower(userPrincipalName) | fields User] | sort by -_time | convert timeformat="%d/%m/%Y %I:%M:%S %p" ctime(_time) | replace 0 with Read, 1 with Write | eval megabytes=((FileSize/1024)/1024) | eval "File Size"= megabytes + " MB" | table _time, User, FileName, FileExtension, ClientHost, DeviceDescription, FileOperation, "File Size" , IPAddresses, PolicyName, ClientVersion | rename _time as Time, DeviceDescription as "Device Description", FileName as "File Name", FileExtension as "File Extension", ClientHost as "From PC", FileOperation as "File Operation", PolicyName as "Policy Name", IPAddresses as "IP Address", ClientVersion as "Client Version"
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...