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
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?
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
@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%’