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

[driva]

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

[gcusello]

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