SQL query - Include first discovery date and last discovery date with Asset inventory

InsightVM SQL Queries
, ,
1

I am a SQL novice, and I’m trying to build a query that includes the first_discovered and last_discovered data from the fact_asset_discovery table into this script, could someone please explain to me how to do this because when I try to add these I get an invalid script as I conflict the from input for fad that I add with the prexisting fa from call.

SELECT
dsite.name as “Site”,
da.ip_address,
da.host_name,
da.mac_address,
dos.description as “OS”,
os.certainty_max
FROM
fact_asset AS fa
JOIN dim_asset da ON da.asset_id = fa.asset_id
JOIN (
SELECT
asset_id,
MAX(certainty) as certainty_max
FROM
dim_asset_operating_system
GROUP BY
asset_id
) os ON fa.asset_id = os.asset_id
AND os.certainty_max < 1
JOIN dim_operating_system as dos ON da.operating_system_id = dos.operating_system_id
JOIN dim_site_asset as dsa ON fa.asset_id = dsa.asset_id
JOIN dim_site as dsite ON dsa.site_id = dsite.site_id
GROUP BY
dsite.“name”,
da.ip_address,
da.host_name,
da.mac_address,
dos.description,
os.certainty_max
ORDER BY
“Site”,
da.ip_address

2

you could try it this way…

SELECT
    dsite.name AS "Site",
    da.ip_address,
    da.host_name,
    da.mac_address,
    dos.description AS "OS",
    os.certainty_max,
    fad.first_discovered,
    fad.last_discovered
FROM
    fact_asset AS fa
JOIN
    dim_asset da ON da.asset_id = fa.asset_id
JOIN (
    SELECT
        asset_id,
        MAX(certainty) AS certainty_max
    FROM
        dim_asset_operating_system
    GROUP BY
        asset_id
) os ON fa.asset_id = os.asset_id
AND os.certainty_max < 1
JOIN
    dim_operating_system AS dos ON da.operating_system_id = dos.operating_system_id
JOIN
    dim_site_asset AS dsa ON fa.asset_id = dsa.asset_id
JOIN
    dim_site AS dsite ON dsa.site_id = dsite.site_id
JOIN
    fact_asset_discovery AS fad ON fa.asset_id = fad.asset_id
GROUP BY
    dsite.name,
    da.ip_address,
    da.host_name,
    da.mac_address,
    dos.description,
    os.certainty_max,
    fad.first_discovered,
    fad.last_discovered
ORDER BY
    "Site",
    da.ip_address;