I'm trying to join 2 queries together in a table
From the Active Directory App, i took this query:
eventtype=msad-account-lockout | eval actor=if(EventCode==4767 OR EventCode==671,src_user,src_host) | eval DateTime=strftime(_time, "%c") | table DateTime,signature,user,actor | rename signature as "Action",user as User, actor as "Change On/By"
Then i wanted to get the phone numbers from ldap to call the users so i tried to append this in between with no luck.
| ldapsearch domain=mydomain* search="(&(objectClass=user)(sAMAccountName=myuser))" attrs="dn,mobile,telephoneNumber"*
Errors:
Error in 'ldapsearch' command: This command must be the first command of a search.
can anyone assist? both return results when run independently. the end goal is to setup a real time alert for the lockouts with phone numbers in the emails.
thanks
You need to use ldapfilter instead.
Go into the SA-ldapsearch app and look at the documentation - it's similar to ldapsearch, but does searches against the event pipeline. You will need to add in src_nt_domain, so something like this should work:
eventtype=msad-account-lockout | eval actor=if(EventCode==4767 OR EventCode=671,src_user,src_host) | ldapfilter domain=$src_nt_domain$ search=(&(objectClass=user)(sAMAccountName=$user$))" attrs="mobile,telephoneNumber" | table _time,signature,user,actor,mobile,telephoneNumber | rename signature as "Action", user as "Locked User", actor as "Changed By", mobile as "Cell", telephoneNumber as "Phone"
Enjoy!
You need to use ldapfilter instead.
Go into the SA-ldapsearch app and look at the documentation - it's similar to ldapsearch, but does searches against the event pipeline. You will need to add in src_nt_domain, so something like this should work:
eventtype=msad-account-lockout | eval actor=if(EventCode==4767 OR EventCode=671,src_user,src_host) | ldapfilter domain=$src_nt_domain$ search=(&(objectClass=user)(sAMAccountName=$user$))" attrs="mobile,telephoneNumber" | table _time,signature,user,actor,mobile,telephoneNumber | rename signature as "Action", user as "Locked User", actor as "Changed By", mobile as "Cell", telephoneNumber as "Phone"
Enjoy!
nevermind, i see my mistake.
additional note: i was getting peridoic emails with no numbers, i noticed that the active directory app doesn't seem to pick up src_nt_domain from 2008 r2 domain controller events properly. i hard-coded my domain in the ldap search as i only have 1.
thanks for you help again.
that worked out perfect, missing one " right after search=
can you explain $src_nt_domain$ & $user$, i would've assumed
ldapfilter domain=mydomain search="(&(objectClass=user)(sAMAccountName=src_user))"