Splunk Search

How can I merge search results from two different indexes based on common fields?

driva
Path Finder

Hi everyone,

I'd be eternally grateful if someone could help point me in the right direction here. I'm trying to output a table with merged results from two different indexes. 

There are two events:

Index A:

 

time="12th April, 19:07:32", name="Bob", ip="192.168.0.45", searched="how do I tie my laces", mac="00:1B:44:11:3A:B7"

 

Index B:

 

the_time="12-04-20-190702", username="Bob", ipaddress="192.168.0.45", location="Home", macaddress="00:1B:44:11:3A:B7"

 

As you can see in both indexes there are common fields - IP address and MAC address, however the timestamps differ slightly. 

I would like to output a table that contained all of the fields e.g. Time, Name, IP address, Searched, Location, MAC Address however I'm not entirely sure how I would construct the search. 

Is there a way that I can make the table work even though the timestamp is out slightly?

Any help would be amazing, thank you!

Best wishes,

D

 

 

Labels (5)
0 Karma

gcusello
Legend

Hi @driva ,

you have only to decide which is the correlation key between the two indexes, so if e.g. you want to use the username 8also if has a different fieldname in the indexes), you could try something like this:

index=indexA OR index=indexB
| eval Name=coalesce(username,user)
| stats 
     earliest(_time) AS _time 
     values("IP Address") AS "IP Address" 
     values(Searched) AS Searched 
     values(Location) AS Location 
     values("MAC Address") AS "MAC Address" 
     BY Name 
| eval Time=strftime(_time,"%Y-%m-%d %H:%M:%S")
| table Time Name "IP address" Searched Location "MAC Address" 

If you want both the timestamps, you can use this one:

index=indexA OR index=indexB
| eval Name=coalesce(username,user)
| stats 
     earliest(_time) AS earliest
     latest(_time) AS latest
     values("IP Address") AS "IP Address" 
     values(Searched) AS Searched 
     values(Location) AS Location 
     values("MAC Address") AS "MAC Address" 
     BY Name 
| eval 
     Time_earliest=strftime(earliest,"%Y-%m-%d %H:%M:%S"),
     Time_latest=strftime(latest,"%Y-%m-%d %H:%M:%S")
| table Time_earliest Time_latest Name "IP address" Searched Location "MAC Address"

Ciao.

Giuseppe

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!