topic Re: Combine results from subsearches in Splunk Search

Combine results from subsearches

ronaldsc — Wed, 04 May 2016 12:19:19 GMT

Hello all,

I'm a newbie to Splunk so I'm hoping someone can assist me figuring out how to accomplish the following.

On a regular basis I have to search two sets of logs. I'd like to be able to knock it out in one go if possible while at the same time have the ability to display the results I need correctly.

The first search I run is:

 index=network sourcetype=analysisFiles file_accessed="zip"  | stats  count by srcip, hostname, data

Which I then gather up the srcip and run the following search to gather the user information from another set of logs:

 index=analysisFilesUserAccess srcip="src_ipFromAboveResults" | stats count by user, srcip

I figured out how to do a subsearch to pull the account information based on the results from the first search doing the following

 index=analysisFilesUserAccess * [search index=network sourcetype=analysisFiles file_accessed="zip" | fields srcip |mvcombine delim=" OR " srcip | nomv srcip | rename srcip as search]

The problem I'm running into and cant figure out is how to show results from both searches. The subsearch only returns fields related to the second search and I can't seem to table or run stats based on the fields from the first. Ideally what I'd like to be able to do is align the srcip from both searches and append the results from the first. I'd still like to be able to see the same results as running |count by srcip, hostname, data but add the username by the matching srcip from both searches.

For example current results from each search when run independently looks as follows:

First Search

  srcip        hostname     count
  192.168.1.49  hostName1       10
  192.168.1.98  hostName1       58

Second Search

  user       srcip            count
  user01       192.168.1.49       10
  user02       192.168.1.98       58

What I'd like the output to look like is one of the following two ways. Key being that I just added the user results that I got back on a per IP basis.

  srcip        hostname      user     count
  192.168.1.49  hostName1     user01   10
  192.168.1.98  hostName1     user02   58

  srcip        hostname     count
  192.168.1.49  hostName1       10
  user01
  192.168.1.98  hostName1       58
  user02

Thanks for all the help in advanced.

Re: Combine results from subsearches

jkat54 — Wed, 04 May 2016 12:26:47 GMT

How about this?:

 (index=network sourcetype=analysisFiles file_accessed="zip") OR (index=analysisFilesUserAccess) | stats  count by srcip, user, hostname, data

Re: Combine results from subsearches

ronaldsc — Wed, 04 May 2016 13:42:14 GMT

Thanks for the reply. This pull every record that's in the second index and on top of that doesn't come back with any results.

Re: Combine results from subsearches

akheraj_splunk — Wed, 04 May 2016 19:24:28 GMT

join command?

index=analysisFilesUserAccess srcip="src_ipFromAboveResults" | stats count by user, srcip | join srcip [ search index=network sourcetype=analysisFiles file_accessed="zip" | stats count as count2 by srcip, hostname, data]

Re: Combine results from subsearches

jkat54 — Wed, 04 May 2016 19:44:29 GMT

Remove the data field from the stats command. I'm trying to avoid the join command due to its limitations.

Re: Combine results from subsearches

jkat54 — Wed, 04 May 2016 19:47:02 GMT

An but I need more work to my search still...

Just use the join command as shown in the other answer for now. I'll add some eval to the search I sent and revert later.

Re: Combine results from subsearches

somesoni2 — Wed, 04 May 2016 20:17:33 GMT

Try like this

index=analysisFilesUserAccess OR (index=network sourcetype=analysisFiles file_accessed="zip" ) | stats values(hostname) as hostname values(data) as data values(user) as user  count(eval(index="analysisFilesUserAccess")) as count1  count(eval(index="network")) as count1 values(index) as index by srcip | where mvcount(index)=2 | fields - index

Update#1

(index=analysisFilesUserAccess [search index=network sourcetype=analysisFiles file_accessed="zip" | stats count by src_ip | table src_ip ] ) OR (index=network sourcetype=analysisFiles file_accessed="zip" ) | stats values(hostname) as hostname values(data) as data values(user) as user  count(eval(index="analysisFilesUserAccess")) as count1  count(eval(index="network")) as count1 values(index) as index by srcip | where mvcount(index)=2 | fields - index

Re: Combine results from subsearches

ronaldsc — Fri, 06 May 2016 14:56:02 GMT

No go as well. Maybe I'm not following but I don't understand by looking through an entire index is the recommended way to do this. Instead wouldn't it be better to just pass the specific data you want to be searched to the second search? Is there no good way to combine the results from the second search with data from the first?

When I add mvcombine delim=" OR " srcip this gives me the specific data I need to search which looks through a couple thousand records vs. doing index=analysiFileUserAccess which seems to pull through everything in the index? Sorry if I'm not following, just trying to make sure I understand how it's actually working under the hood.

Re: Combine results from subsearches

ronaldsc — Fri, 06 May 2016 15:03:03 GMT

This only works if i manually add the src_ip. I'm hoping to pass the results from the first search to the second automatically.

For example:
In my original search by doing a |mvcombine delim=" OR " srcip | nomv srcip

I get this which is in turn passed to the first search

192.168.1.49 OR 192.168.1.52 OR 192.168.1.88 OR 192.168.1.214

Which then searches for that data by running the second search index=analysisFilesUserAccess 192.168.1.49 OR 192.168.1.52 OR 192.168.1.88 OR 192.168.1.214*. Where I am stuck is how do I go about combining fields from the first result with the second. There are fields from both that I'd like to group together. The minute I add |stats or anything else to the first search it seems to kill the passing of these IP values.

Re: Combine results from subsearches

somesoni2 — Fri, 06 May 2016 15:13:49 GMT

You're correct, if you can filter the records, you should do that always. I updated the search which will do the filter and combine the results. Give that a try.

Re: Combine results from subsearches

ronaldsc — Mon, 09 May 2016 14:35:28 GMT

Thanks for the continued help, somesoni2.

When I run the search it always comes back with just results from 1 index. When I run each of the searches independently I get the results I'm looking for.

If I run the searches below they each return events (this is just your search broken out). The first search has the index of analysisFilesUserAccess and the second search has the index index of network. However, when I run both combined the results only has 1 index returned. Not sure why and this is causing no result returned.

 **(index=analysisFilesUserAccess [search index=network sourcetype=analysisFiles file_accessed="zip" | stats count by src_ip | table src_ip ] )**

 **(index=network sourcetype=analysisFiles file_accessed="zip" ) | stats values(hostname) as hostname values(data) as data

If I look at the job inspector is get the message saying verify that the fields expected by the report commands are present in the events for the two fields that are supposed to come from the network index.