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
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.
I might try this as a last resort, but there are some organizational hurdles when trying to get the whole user base.
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 🙂
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.