How do I include fields from a subsearch to the main search results table?

[mcm10285]

I have a search with one subsearch, that looks like this.

sourcetype=sourcetype1 <search string> [search sourcetype=sourcetype2 <search string>|fields + src_ip dst_ip message|stats values(dst_ip) AS Network_Address]   | fields + hostname Network_Address | search hostname!="*Unknown*" OR hostname!="-"|convert ctime(_time) AS timestamp | stats values(Network_Address) AS dst_ip values(hostname) AS Hostname values(src_ip) AS SourceIP values(message) AS Message count by timestamp

What the whole search should do is get the dst_ip address from the subsearch, input the values into the main search, then main search gets the hostname of the dst_ip values. After the searching, it should be tabled to display the fields "timestamp" "src_ip" "dst_ip" "hostname" "message"

The fields or values for the fields "src_ip" and "message" are not picked up and rendered in the main search table. How do I get those fields included?

[aweitzman]

By the time you get to the end of your subsearch, all you have is one field called Network_Address that contains a single multivalued entry of all of the dst_ip values that show up in your subsearch results. Your transforming stats command washed all the other fields away. The only way to get src_ip and message at that point is to get them from the main search.

In general the point of a subsearch is generate a search filter phrase to apply to your main search. It doesn't get you any more information than you can get from your main search, so all you'd be able to get there are the dst_ip values:

[search sourcetype=sourcetype2 <search string> | table dst_ip | dedup dst_ip | rename dst_ip as Network_Address]

But that doesn't get you your src_ip and message fields.

It seems like what you really want is to have your main search be from sourcetype2, since that has all of your data except for hostname. Then, you could use either a lookup (ideal) or a join (less ideal) to get what you want. If you had a lookup set up that mapped your dst_ip to your hostname called addrtohostname, it would look something like this:

sourcetype=sourcetype2 <search string> 
| lookup addrtohostname dst_ip OUTPUT hostname 
| ... 
| table timestamp src_ip dst_ip hostname message

Read this: http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Addfieldsfromexternaldatasources?r=sear... to learn how you might generate such a lookup automatically.

Alternatively you could use a join. These tend to be a lot slower, so I wouldn't recommend it, but they do work:

sourcetype=sourcetype2 <search string> 
| join dst_ip [search sourcetype=sourcetype1 <search string> | table Network_Address, hostname | rename Network_Address as dst_ip]
| table timestamp src_ip dst_ip hostname message

[mcm10285]

Thanks for the response aweitzman. I was hoping that someone was able to pull up some complex search that can return the desired results.

I had the same thoughts of flipping the main and sub searches around. However doing that would make the correlation different as the event I'm looking into starts off with sourcetype2. The lookup idea is something that can be explored, but it is possible it will be a dynamic lookup.