Splunk query based on the results of another query

Question

A Splunk question...

I've found a few Google hits that I thought were going to help with this. I'm trying to perform a search for all "rows" that are returned by an outer search/query.

I am by no means a Splunk expert, not even a power user!

The outer query performs an LDAP search against Active Directory and returns a list of people with a particular group membership (e.g.: all Domain Admins or Account Operators, Etc.)

I then want to perform a search for each of the returned user names against Windows Event Logs … and return the results as one data set.

I've got the LDAP search nailed. I've got the Windows Event Log search nailed. I just need to stitch them together.

If I were coding this in a script, I'd either:

i) Enumerate relevant group members into an array. Run the event log query for users that exist in the array, e.g.: using semantics such as isin() or contains(); or ii) Enumerate the group members and perform a foreach() type loop.

So, how the #?!@ do I do this in Splunk. I've tried using the "search" command and "foreach" command, but have had no joy. I even toyed with building a lookup and tried isin(), but could not get this to work.

Example LDAP search:

| ldapsearch domain="contoso.com" search="(&(objectclass=user)(objectCategory=person)(memberOf=CN=Domain Admins,OU=MyContainer,DC=contoso,DC=com))" attrs="sAMAccountName" basedn="DC=contoso,DC=com" | eval ldapSearchUserName="contoso\\"+lower(sAMAccountName)

Example Event log search:

index="wineventlog" source="WinEventLog:Security" sourcetype="WinEventLog:Security" "LogName=Security" "EventCode=4624" earliest=-1d | rex field=Message ".*Logon Type:\s+(?<LogonType>\d+)" | eval UserName=mvindex(Security_ID, 1) | table UserName

Any thoughts, hints or guidance?

Many thanks

S

RichG RichG · Accepted Answer · 2019-05-16T00:13:13

First, let me try to clarify a few things.

Splunk returns results in a table. Rows are called 'events' and columns are called 'fields'. Most search commands work with a single event at a time.

The foreach command loops over fields within a single event. Use the map command to loop over events (this can be slow).

Splunk supports nested queries. The "inner" query is called a 'subsearch' and the "outer" query is called the "main search". Subsearches are enclosed in square brackets [] and are always executed first. The means the results of a subsearch get passed to the main search, not the other way around.

One approach to your problem is to do the ldapsearch in a subsearch and let those results become part of the main search.

index="wineventlog" source="WinEventLog:Security" sourcetype="WinEventLog:Security" "LogName=Security" "EventCode=4624" earliest=-1d 
| rex field=Message ".*Logon Type:\s+(?<LogonType>\d+)" 
| eval UserName=mvindex(Security_ID, 1) 
| search [| ldapsearch domain="contoso.com" search="(&(objectclass=user)(objectCategory=person)(memberOf=CN=Domain Admins,OU=MyContainer,DC=contoso,DC=com))" attrs="sAMAccountName" basedn="DC=contoso,DC=com" 
| eval UserName="contoso\\"+lower(sAMAccountName)|fields UserName | format]
| table UserName

Here, the LDAP search is fetching a list of users into a field called "UserName". The field name used here must match a field used in the main search or you won't get any results. The format command puts the results into (UserName=foo OR UserName=bar...) form for proper searching. That is then used to search the list of user names produced by the main search, which should produce the results you seek.

Splunk query based on the results of another query

1 Answers