Hi All!
I have a base search that just reports users connected to a vpn service.
index=netvpn | stats count by user
Very simple, i then want to run those users against an ldapsearch and get their employeeType and displayName, is there anyway i can subsearch to achieve this? I have already tried:
index=netvpn sourcetype="pulse:connectsecure"
| stats count by user
[| ldapsearch domain=*obfuscated* search="(sAMAccountName=$user$)" attrs="employeeType displayName" ]
| table employeeType, displayName
But i don't get any results, am i close? Oh and the output of "user" in the base search is the sAMAccountName in Active Directory, so i shouldn't need to rename any fields.
I do also have a scheduled search running to output the base search to a csv, if it makes the process any easier!
Many thanks,
Chris
index=netvpn sourcetype="pulse:connectsecure"
| stats values(user) as users
| eval user="(".mvjoin(users," OR ").")"
| ldapsearch domain=*obfuscated* search="(sAMAccountName=$user$)" attrs="employeeType displayName"
| table employeeType, displayName
ldapsearch
works by single value.
so, use map
OR try like this.
Subsearches run first so there is no value of $user$
available. Instead, consider the map
command.
index=netvpn sourcetype="pulse:connectsecure"
| stats count by user
| map user search="| ldapsearch domain=*obfuscated* search=\"(sAMAccountName=$user$)\" attrs=\"employeeType displayName\" "
| table employeeType, displayName
Even better would be to use ldapsearch
to create a lookup file that can be accessed from other searches. Run this as scheduled search each day:
| ldapsearch domain=*obfuscated* search="(sAMAccountName=*)" attrs="employeeType displayName"
| table sAMAccountName, employeeType, displayName
| outputlookup employeeTypes.csv
Then consult the lookup file. This is far faster than reaching out to LDAP many times and will keep your admins happy.
index=netvpn sourcetype="pulse:connectsecure"
| stats count by user
| lookup employeeTypes.csv sAMAccountName
| table employeeType, displayName
Hi Rich,
Thank you for those suggestions 🙂 Agreed yes it would be more efficient to use a lookup, so i'll definitely be looking into that method. I did just try the first suggestion (to check it pulls data) and i get the error:
Error in 'map' command: Unable to find saved search 'user'.
I'll try the lookup suggestion now 🙂
The map
command failed because I flubbed the syntax. Try the updated answer.