All Apps and Add-ons

How do I join these two searches to display failed logins from Domain Admins?

Path Finder

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!

0 Karma
1 Solution

Path Finder

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.

View solution in original post

0 Karma

Path Finder

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.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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:

Path Finder

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!

0 Karma

Path Finder

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?

0 Karma

SplunkTrust
SplunkTrust

just run this as search | inputlookup domain_admins.csv to see what is returned from the lookup file

0 Karma

Path Finder
| 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" ?

0 Karma

Path Finder

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

Communicator

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 membername, so I will have the group cn as input, then I want only events where membername=User returned. Any advice appreciated.

0 Karma

Path Finder

Check the Search Inspector to see what your inputlookup command is adding to the search to make sure it's working correctly.

http://docs.splunk.com/Documentation/Splunk/6.3.0/Knowledge/ViewsearchjobpropertieswiththeJobInspect...

0 Karma