How to check if a field value from an event matches the results from an ldapsearch.

[ezmo1982]

Hi,

I am looking to compare a field value against the results of an ldapsearch to check whether the value is present or not. The Use Case is basically that I want to detect if a cloud user account is created in our O365 environment, and that same user name does not exist in our on-prem Active Directory.

I have the below SPL which returns newly created users in my O365 environment. The below returns a field named "user".

index="o365_log" action=created command="Add user."

I now want to search my Active Directory domain to see if this user exists or not. If it doesnt exists in Active Directory, I want the search to detect it and output the user name.  I am using ldapsearch and searching where the userPrincipleName is the same as the value of the user field. The SPL below is what I am using:

| ldapfilter domain=default search="(userPrincipalName=$user$)" attrs="cn,userPrincipalName" 

The above SPL works, however the problem I have is that Im not sure how to combine these two lines SPL together so that it performs the check and only output' users which are not found from the ldapsearch. Can somebody help?

Thanks. 

[ezmo1982]

Anyone else got any ideas?

[isoutamo]

Hi

Probably something like this could work (I haven't test syntax)

index="o365_log" action=created command="Add user."
| stats dc by user
| fields user
| map search="ldapfilter domain=default search=\"(userPrincipalName=$user$)\" attrs=\"cn,userPrincipalName\”" 
| append [search index="o365_log" action=created command="Add user."
   | stats dc by user
   | fields user]
| stats count as uCount by user
| where uCount = 1

1. get dc users
2. do additional ldapsearch with map
3. append again this dc users from o365
4. stats to see if you have 2 entry for those
5. select those which have only 1 entry (didn't exists in AD)

r. Ismo

[ezmo1982]

Your SPL returns user's from o365 who are also in Active Directory. Also produces errors so im guessing thats why its not working correctly ...

This search uses deprecated 'stats' command syntax. This syntax implicitly translates '<function>' or '<function>()' to '<function>(*)', except for cases where the function is 'count'. Use '<function>(*)' instead.

Unable to run query 'ldapfilter domain=default search="(userPrincipalName=clen.lubbers@syncreon.com)" attrs="cn,userPrincipalName\”'.