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

**URL:** <https://discuss.rapid7.com/t/sql-query-include-first-discovery-date-and-last-discovery-date-with-asset-inventory/39705>\
**Category:** SQL Queries\
**Tags:** InsightVM, sql, queries\
**Created:** [July 11, 2024, 6:35pm UTC](https://discuss.rapid7.com/t/sql-query-include-first-discovery-date-and-last-discovery-date-with-asset-inventory/39705 "2024-07-11T18:35:25Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![llrfarley](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/l/f04885/32.png) [@llrfarley](https://discuss.rapid7.com/u/llrfarley)\
**Post date:** [July 11, 2024, 6:35pm UTC](https://discuss.rapid7.com/t/sql-query-include-first-discovery-date-and-last-discovery-date-with-asset-inventory/39705/1 "2024-07-11T18:35:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bradpjaxx](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/bradpjaxx/32/2411_2.png) [@bradpjaxx](https://discuss.rapid7.com/u/bradpjaxx)\
**Post date:** [July 30, 2024, 6:34pm UTC](https://discuss.rapid7.com/t/sql-query-include-first-discovery-date-and-last-discovery-date-with-asset-inventory/39705/2 "2024-07-30T18:34:55Z")

</div>

you could try it this way…

```auto
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;

```