topic Re: Correlating Data from 2 Indexes in Splunk Search

Correlating Data from 2 Indexes

JRamirezEnosys — Wed, 31 May 2017 04:32:42 GMT

Hi everyone, I've been trying to add results from 2 different indexes using search after the pipe but it doesn't seem to work.

My task is to call into 2 different indexes:

One is called Networklogs and the other is called ScanResults

There is a field on both indexes with the same information (an ip address) that I want to use as the primary key to correlate them.

On Networklogs is called srcip and on ScanResults is called hostname

From the Network logs I want the srcip and the field called app
From the ScanResults I want the hostname and a field called fqdn

I try the following query to mix and match both

index=Networklogs srcip=7.7.7.7  | search index=ScanResults hostname=7.7.7.7 | stats count by srcip app fqdn

Any advice on how to achieve this result?

Re: Correlating Data from 2 Indexes

JRamirezEnosys — Wed, 31 May 2017 05:19:16 GMT

I tried a join

index=Networklogs srcip=7.7.7.7 | rename srcip as hostname | join hostname [search index="Scanresults"   hostname="7.7.7.7"]

Still not working

Re: Correlating Data from 2 Indexes

niketn — Wed, 31 May 2017 07:53:35 GMT

@JRamirezEnosys, create a Field Alias for one of the fields for example call hostname in index Scanresult as srcip. You can do this from Settings > Fields > Field aliases. However, you would need to create Field alias based on either source, sourcetype or host (so identify sourcetype for index="Scanresults" first). Refer to Splunk documentation on how to create Field Alias (https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Addaliasestofields).

Once you have created Field Alias you can try the following search:

index="Networklogs" OR index="Scanresults" AND srcip="7.7.7.7"

PS: If possible add sourcetype for both the indexes as well. Narrow dataset in the base search through metadata fields will lead to better search performance.

Re: Correlating Data from 2 Indexes

cmerriman — Wed, 31 May 2017 11:32:45 GMT

can you try something like this:

(index=Networklogs srcip=7.7.7.7) OR (index=ScanResults hostname=7.7.7.7)|eval ipAddress=if(index="Networklogs",srcip,hostname) | stats count values(app) as app values(fqdn) as fqdn by ipAddress

Re: Correlating Data from 2 Indexes

woodcock — Wed, 31 May 2017 15:54:23 GMT

Like this:

index=Networklogs OR index=ScanResults | eval joiner=coalesce(srcip, hostname) | stats values(app) AS app values(fqdn) AS fqdn BY joiner

Re: Correlating Data from 2 Indexes

harishbenne2 — Fri, 31 Aug 2018 10:25:04 GMT

Tried this, but haven't been able to fetch the data from second index, fqdn in this case. It just shows blank field in the name. Can you refine/recheck the query and suggest a fix?