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