Splunk query to take a search from one index and add a field's value from another index?

How can I write a Splunk query to take a search from one index and add a field's value from another index? I've been reading explanations that involve joins, subsearches, and coalesce, and none seem to do what I want -- even though the example is extremely simple. I am not sure what I am not understanding yet.

main-index has src field which is an IP address and a field I will restrict my results on. I will look over a short amount of time, e.g.

index="main-index" sourcetype="main-index-source" main-index-field="wildcard-restriction*" earliest=-1h | stats count by src

other-index has src_ip field which is an IP address, and has the hostname. It's DHCP leases, so I need to check a longer time frame, and return only the most recent result for a given IP address. I want to get back the hostname from src_nt_host, e.g.

index="other-index" sourcetype="other-index-sourcetype" earliest=-14d

I would like to end up with the following values:

IP address, other-index.src_nt_host, main-index.count

main-index has the smallest amount of records, if that helps for performance reasons.

Solution 1:

If I understand you correctly, you need to look at two different time ranges in two different indices,

In that case, it is most likely to be true that a join will be needed

Here's one way it can be done:

index=ndx1 sourcetype=srctp1 field1="someval" src="*" earliest=-1h
| stats count by src
| join src
    [| search index=ndx2 sourcetype=srctp2 field2="otherval" src_ip=* src_nt_host=* earliest=-14d
    | stats count by src_ip src_nt_host
    | fields - count
    | rename src_i as src ]

You may need to flip the order of the searches, depending on how many results they each return, and how long they take to run.

You may also be able to achieve what you're looking for in another manner without the use of a join, but we'd need to have some sample data to possibly give a better result