I am trying to display failed logins from Domain Admins. For this, I have two separated searches:
1. Failed logins for all users
2. Display a table of members of Administrators group.
I want to join those two searches so the results from search 1 are compared against a list of members from search 2. If the failing user is listed as a member of Domain Admins - display it
Failed logins for all users (more or equal to 5)
eventtype=msad-failed-user-logons (host="*")
|fields _time,signature,src_ip,src_host,src_nt_host,src_nt_domain,user,Logon_Type, Logon_Account, Source_Workstation
|`ip-to-host`
|stats count by user,src_nt_domain
|where count>=5
|sort -count
|rename user as "Username", src_nt_domain as "Domain"
Members of Administrator group
| ldapsearch domain=xxxxxxx.xxx search="(&(objectclass=group)(cn=Administrators))"|ldapgroup|table member_name, member_domain
Any help is appreciated - thanks!
You can use a subsearch to add the results of the ldapsearch to your initial search query:
eventtype=msad-failed-user-logons (host="*") [| ldapsearch domain=xxxxxxx.xxx search="(&(objectclass=group)(cn=Administrators))"|ldapgroup|table member_name, member_domain] |fields _time,signature,src_ip,src_host,src_nt_host,src_nt_domain,user,Logon_Type, Logon_Account, Source_Workstation | ip-to-host |stats count by user,src_nt_domain |where count>=5 |sort -count |rename user as "Username", src_nt_domain as "Domain"
Use the search inspector to see how this adds the data from the ldapsearch to the base search.
You can use a subsearch to add the results of the ldapsearch to your initial search query:
eventtype=msad-failed-user-logons (host="*") [| ldapsearch domain=xxxxxxx.xxx search="(&(objectclass=group)(cn=Administrators))"|ldapgroup|table member_name, member_domain] |fields _time,signature,src_ip,src_host,src_nt_host,src_nt_domain,user,Logon_Type, Logon_Account, Source_Workstation | ip-to-host |stats count by user,src_nt_domain |where count>=5 |sort -count |rename user as "Username", src_nt_domain as "Domain"
Use the search inspector to see how this adds the data from the ldapsearch to the base search.
Or you setup the second search | ldapsearch domain=xxxxxxx.xxx search="(&(objectclass=group)(cn=Administrators))"|ldapgroup|table member_name, member_domain
to feed a lookup table and run this at night and enrich the user data with the admin group membership. See the docs for more details on outputlookup
and lookups to enrich your data:
stmyers7941 - I have tried what you proposed but the search does not return results and I am sure that failed logins for admins should come up a lot. Also, I am not really sure how the job inspector works so it is hard for me to figure out what went wrong.
MuS - I will give a try to what you have proposed. I have never created any lookup tables so it will probably take me a while to get it to work.
Thank for your help guys!
I used outputlookup domain_admins.csv
to write the results of the second search into a lookup file. I confirmed that the file contains the data.
Then I setup a search eventtype=msad-failed-user-logons (host="*")
[| inputlookup domain_admins.csv]
|fields _time,signature,src_ip,src_host,src_nt_host,src_nt_domain,user,Logon_Type, Logon_Account, Source_Workstation
|ip-to-host
|stats count by user,src_nt_domain
|where count>=5
|sort -count
|rename user as "Username", src_nt_domain as "Domain"
It runs fast but still not data is presented. Any idea why?
just run this as search | inputlookup domain_admins.csv
to see what is returned from the lookup file
| inputlookup domain_admins.csv
Returns appropriate values but not in a table format. All of the results are in a string separated by spaces "XXXX1 XXXX2 XXXX XXXX3 XXXX4". If XXXX1 fails to login, would it return a match or is it trying to match the whole string "XXXX1 XXXX2 XXXX XXXX3 XXXX4" ?
I finally got it to work, I changed my second search to display top results instead of creating a table, this was able to put every the events into a table like format:
XXX1
XXX2
XXX3
and then my search number 1 had no problems finding matches. Thanks everyone for help!
| ldapsearch domain=xxxxx.xxx search="(&(objectclass=group)(cn=Administrators))" | ldapgroup | top 30 member_name showperc=f | fields - count | rename member_name as "user" | outputlookup domain_admins.csv
I need to do the same thing, but the LDAP group membership to check against will be one of many chosen as input so I'd rather do it without a lookup for the group membership, but rather as initially suggested with a subsearch. It seems like it would require using ldapfetch with ldapgroup. In the original suggestion I don't see anything that connects user fields between the AD group and the events.
In my search the event field User is equivalent to AD group member_name, so I will have the group cn as input, then I want only events where member_name=User returned. Any advice appreciated.
Check the Search Inspector to see what your inputlookup command is adding to the search to make sure it's working correctly.