All Apps and Add-ons

LDAP lookup instead of filter or search

afx
Contributor

Hi,
I am trying to resolve user names within a multistage query using appends.
ldapsearch wants to be in its own query, and I have not found a way yet to make this work (see sample below).
ldapfilter is not useable as it will skip log entries for which the LDAP query does not return an entry.

index=x*auth "user NOT in sudoers"  | 
   [ | ldapsearch search="(sAMAccountName=$user$)" attrs="displayName" ] |
   lookup server.csv Server as host  OUTPUT SID |
append [search index=x*auth  "(su-l:auth): authentication failure;" |  
   [|ldapsearch search="(sAMAccountName=$ruser$)" attrs="displayName"] |
   lookup server.csv Server as host OUTPUT SID | rename ruser as user suUser as USER] | 
append [ search index=x*auth  "PAM * more authentication failures" | 
   [| ldapsearch search="(sAMAccountName=$suUser$)" attrs="displayName" ] |
   lookup server.csv Server as host OUTPUT SID | rename suUser as user  ] | 
sort _time | table  _time host SID process rhost user displayName USER

Any ideas?
thx
afx

0 Karma
1 Solution

afx
Contributor

Ok, this is the query I used (copied from the Enterprise Security docs):

|ldapsearch search="(&(objectclass=user)(!(objectClass=computer)))" attrs="userAccountControl,sAMAccountName,personalTitle,displayName,givenName,sn,mail,telephoneNumber,mobile,manager,department,whenCreated,accountExpires"
|makemv userAccountControl
|search userAccountControl="NORMAL_ACCOUNT" 
|eval suffix=""
|eval priority="medium"
|eval category="normal"
|eval watchlist="false"
|eval endDate="" 
|table sAMAccountName,personalTitle,displayName,givenName,sn,suffix,mail,telephoneNumber,mobile,manager,priority,department,category,watchlist,whenCreated,endDate

This is run as a scheduled report at 4am to create AD-Users.csv
Then I can resolve user names and departments easily with the following lookup

| lookup AD-Users.csv sAMAccountName as TargetUserName OUTPUT displayName, department
| fillnull value="N/A"

And the fillnull makes sure that empty fields do not exclude lines in the stats or table output.

cheers
afx

View solution in original post

afx
Contributor

Ok, this is the query I used (copied from the Enterprise Security docs):

|ldapsearch search="(&(objectclass=user)(!(objectClass=computer)))" attrs="userAccountControl,sAMAccountName,personalTitle,displayName,givenName,sn,mail,telephoneNumber,mobile,manager,department,whenCreated,accountExpires"
|makemv userAccountControl
|search userAccountControl="NORMAL_ACCOUNT" 
|eval suffix=""
|eval priority="medium"
|eval category="normal"
|eval watchlist="false"
|eval endDate="" 
|table sAMAccountName,personalTitle,displayName,givenName,sn,suffix,mail,telephoneNumber,mobile,manager,priority,department,category,watchlist,whenCreated,endDate

This is run as a scheduled report at 4am to create AD-Users.csv
Then I can resolve user names and departments easily with the following lookup

| lookup AD-Users.csv sAMAccountName as TargetUserName OUTPUT displayName, department
| fillnull value="N/A"

And the fillnull makes sure that empty fields do not exclude lines in the stats or table output.

cheers
afx

afx
Contributor

I don't know which ones to query in advance...
So I need the whole user base.
Found a way to get it though.
thx
afx

0 Karma

DavidHourani
Super Champion

awesome, could you please share your answer and accept it ? 🙂

0 Karma

afx
Contributor

Hi David,
I might try this as a last resort, but there are some organizational hurdles when trying to get the whole user base.
thx
afx

0 Karma

DavidHourani
Super Champion

I don't mean get the whole user base, get the exact ones you are trying to query with ldapsearch, and have them all in one lookup 🙂 that way you wont have to run append multiple times 🙂

0 Karma

DavidHourani
Super Champion

Hi @afx,

Have you considered running a daily scheduled searches for grouping together all the ldapsearch results into a single lookup file ? or even into a kv-store ?
It will reduce the number of queries you run against your AD and will make the query above and similar queries way easier to create and maintain.

Cheers,
David

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...