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

Answer 1

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.