All Apps and Add-ons

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

ttchorz
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

stmyers7941
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

stmyers7941
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.

0 Karma

MuS
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:

ttchorz
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

ttchorz
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

MuS
SplunkTrust
SplunkTrust

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

0 Karma

ttchorz
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

ttchorz
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

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

0 Karma

stmyers7941
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...