Splunk Search

Correlating Data from 2 Indexes

JRamirezEnosys
Explorer

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?

0 Karma

woodcock
Esteemed Legend

Like this:

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

harishbenne2
Explorer

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?

0 Karma

cmerriman
Super Champion

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

JRamirezEnosys
Explorer

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

0 Karma

niketnilay
Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!