SQL Adding first discovered and last discovered

I have this sql query that shows unauthenticated/authenticated with certainty but i need first discovered and last discovered added to it: I know I can use the fad.first_discovered, fad.last_discovered but can’t figure out where my from clause can go if i already have fac_asset as fa…

I also want the

SELECT dsite.“name” as “Site”, da.ip_address, da.host_name, 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,dos.description, os.certainty_max

ORDER BY “Site”, da.ip_address

Can you rephrase/format your question? I think you are asking to add the first_discovered and last_discovered fields from the fact_asset_discovery table, but its hard to tell. Also, any reason you are using fact_asset?

No reason just the way i was able to make the sql work. I think i asked my question wrong i was wanting last scan date and first discovered.

did you get this to work?

I sure did not.

SELECT
dsite.name as “Site”,
da.ip_address,
da.host_name,
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 fad ON fa.asset_id=fad.asset_id

GROUP BY dsite.name, da.ip_address, da.host_name,dos.description, os.certainty_max, fad.first_discovered, fad.last_discovered

ORDER BY “Site”, da.ip_address

1 Like

Wow thank you so much!!

@john_hartman I wanted to see if you could do something with this one. All i’m needing is it to go off the first found date as this is new column that has been added. The report is getting it mixed up with the first published date as that how it was before they added the First found. It also is not suppose to find any new vulnerabilities in between the timelines this is the middle one 45 day report there is 30 45 and 60 day what ever it finds on the 30 day report reamins there on 45 and 60 no new vulns are suppose to show up in between, and i think because the first found date is new and our sql is older its getting mixed up and adding vulns that are new.

WITH asset_count (asset_ids,asset_vuln) AS (
SELECT DISTINCT fava.asset_id, COUNT (*)
FROM fact_asset_vulnerability_age fava
JOIN dim_vulnerability dv ON fava.vulnerability_id=dv.vulnerability_id
WHERE (dv.cvss_v2_score >= 8) AND (fava.age_in_days > 45)
GROUP BY fava.asset_id
),

 owner_tags (asset_ids, asset_owner) AS (  
  SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags  
  FROM dim_tag  
     JOIN dim_tag_asset USING (tag_id)  
  WHERE tag_type = 'OWNER'  
  GROUP BY asset_id  

),

custom_tags (asset_ids,asset_tag_custom) AS (  
  SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags  
  FROM dim_tag  
     JOIN dim_tag_asset USING (tag_id)  
  WHERE tag_type = 'CUSTOM'  
  GROUP BY asset_id  

)

SELECT dag.name AS “Division”,
CASE WHEN ct.asset_tag_custom LIKE ‘%ITCS%’ then ‘ITCS’
ELSE ‘DEPT’
END AS “ITCS or Dept”,
da.host_name AS “Host Name”,
da.ip_address AS “IP Address”,
ac.asset_vuln AS “Vulnerabilities”,
ot.asset_owner AS “Asset Owner”

FROM asset_count ac
JOIN dim_asset_group_asset daga ON ac.asset_ids=daga.asset_id
JOIN dim_asset_group dag ON daga.asset_group_id=dag.asset_group_id
JOIN dim_asset da ON ac.asset_ids=da.asset_id
JOIN owner_tags ot ON ac.asset_ids=ot.asset_ids
JOIN custom_tags ct ON ac.asset_ids=ct.asset_ids

WHERE dag.name LIKE ‘D%-%’ AND dag.name NOT LIKE ‘De%’

ORDER BY dag.name ASC, ac.asset_vuln DESC

only 8 or greater of cvss score version 2. Its suppose to give a count of vulnerabilities older than 30 days 45 days and so on.