How to edit my ldapsearch query to match identify information with a lookup?

[kiran331]

Hi

I'm using the below search to get the Identity information into Splunk. Now I have a lookup(critical_users.csv) with critical users. How can I match this lookup with the query to make priority "Critical" and Category "Critical users" and watchlist="true". In the lookup, I have userPrincipalName of the users.

| ldapsearch domain="XXXXXXX" search="(&(objectClass=user)(!(objectClass=computer)))" attrs="sAMAccountName,displayName,givenName,sn,mail,telephoneNumber,manager,department,whenCreated,accountExpires, userAccountControl, distinguishedName,userPrincipalName,employeeID"
|search userAccountControl="NORMAL_ACCOUNT"
|eval suffix=""
| eval priority=case(       like(distinguishedName,"%OU=Privileged Accounts,OU=AD_REFRESH,DC=XX,DC=XXXXXX,DC=com"), "critical",
                            like(distinguishedName,"%OU=USERS,OU=Disabled Accounts,DC=XX,DC=XXXXXX,DC=com"), "critical",
                            1==1,"medium" )
 | eval category=case(       like(distinguishedName,"%OU=Privileged Accounts,OU=AD_REFRESH,DC=XX,DC=XXXXXX,DC=com"), "privileged",
                            like(distinguishedName,"%OU=USERS,OU=Disabled Accounts,DC=XX,DC=XXXXXXX,DC=com"), "disabled",
                             1==1,"normal" )
|eval startDate = strptime(whenCreated,"%Y%m%d%H%M%S.%1NZ")
| eval endDate=if(accountExpires="(never)","",accountExpires)
|eval watchlist="false"
|eval identity =  sAMAccountName +"|"+userPrincipalName+"|"+ distinguishedName+"|"+employeeID 
|table userPrincipalName identity,personalTitle,displayName,givenName,sn,suffix,mail,telephoneNumber,mobile,manager,priority,department,category,watchlist,startDate,endDate
|rename personalTitle as prefix, displayName as nick, givenName as first, sn as last, mail as email, telephoneNumber as phone, mobile as phone2, manager as managedBy, department as bunit
| outputlookup create_empty=false "users.csv"

[sundareshr · Accepted solution]

Try this

| ldapsearch domain="XXXXXXX" search="(&(objectClass=user)(!(objectClass=computer)))" attrs="sAMAccountName,displayName,givenName,sn,mail,telephoneNumber,manager,department,whenCreated,accountExpires, userAccountControl, distinguishedName,userPrincipalName,employeeID"
|search userAccountControl="NORMAL_ACCOUNT"
|eval suffix=""
| eval priority=case( like(distinguishedName,"%OU=Privileged Accounts,OU=AD_REFRESH,DC=XX,DC=XXXXXX,DC=com"), "critical",
like(distinguishedName,"%OU=USERS,OU=Disabled Accounts,DC=XX,DC=XXXXXX,DC=com"), "critical",
1==1,"medium" )
| eval category=case( like(distinguishedName,"%OU=Privileged Accounts,OU=AD_REFRESH,DC=XX,DC=XXXXXX,DC=com"), "privileged",
like(distinguishedName,"%OU=USERS,OU=Disabled Accounts,DC=XX,DC=XXXXXXX,DC=com"), "disabled",
1==1,"normal" )
|eval startDate = strptime(whenCreated,"%Y%m%d%H%M%S.%1NZ")
| eval endDate=if(accountExpires="(never)","",accountExpires)
|eval watchlist="false"
|eval identity = sAMAccountName +"|"+userPrincipalName+"|"+ distinguishedName+"|"+employeeID 
|table userPrincipalName identity,personalTitle,displayName,givenName,sn,suffix,mail,telephoneNumber,mobile,manager,priority,department,category,watchlist,startDate,endDate
|rename personalTitle as prefix, displayName as nick, givenName as first, sn as last, mail as email, telephoneNumber as phone, mobile as phone2, manager as managedBy, department as bunit
| lookup critical_users.csv userPrincipalName OUTPUT userPrincipalName AS cu 
| eval critical=if(isnotnull(cu), "Critical", critical)
| eval category=if(isnotnull(cu), "Critical Users", category)
| outputlookup create_empty=false "users.csv"

[kiran331]

It worked.Thank you!