See vuln Found Date through GUI?

Hello All!

Been in tool less than a year, learning as I go as needs arise!

Is there a way to see in the GUI when a vulnerability was first discovered on an asset (date and maybe also the scan/site/template/…)?

Searching I see related topics on extracting data from SQL and the DB but I do not have extensive experience in these backend areas.

Thanks you!
jsari

1 Like

Interesting question. I have tried to figure this out. without creating a report that specifies one scan
at a time I am not sure how to do this effectively.

ok thanks for the response

I feel like this is something we should be able to figure out.

@jennifer_linker_felman

Try this SQL, but you will need to scope it to a site, tag, or asset group. If you scope it to a scan, then it will clip the first scanned date to the scan date.

SELECT da.ip_address,
 da.host_name,
 da.sites,
 dos.description                     AS operating_system,
 dv.title                            AS vulnerability_title,
 trunc(dv.riskscore::numeric, 2)     AS risk_score,
 trunc(dv.cvss_score::numeric, 2)    AS cvss_score,
 dscan.finished                      AS scan_date,
 dsol.summary                        AS solution_summary,
 htmlToText(favi.proof)              as proof,
 favi.port,
 dp.name                             as protocol,
 trunc(fava.age_in_days::numeric, 2) AS age_in_days,
 fava.first_discovered,
 fava.most_recently_discovered

FROM fact_asset_vulnerability_finding favf

       JOIN dim_scan dscan USING (scan_id)
       JOIN dim_asset da USING (asset_id)
       JOIN dim_operating_system dos USING (operating_system_id)
       JOIN dim_vulnerability dv USING (vulnerability_id)
       JOIN dim_asset_vulnerability_best_solution davbs USING (asset_id, vulnerability_id)
       JOIN dim_solution dsol on davbs.solution_id = dsol.solution_id
       JOIN fact_asset fa USING (asset_id)
       JOIN fact_asset_vulnerability_instance favi
            on favf.asset_id = favi.asset_id and fa.last_scan_id = favi.scan_id and
 favi.vulnerability_id = favf.vulnerability_id
       JOIN dim_protocol dp USING (protocol_id)
       JOIN fact_asset_vulnerability_age fava
            on da.asset_id = fava.asset_id and favf.vulnerability_id = fava.vulnerability_id



ORDER BY da.ip_address ASC,
 dv.title ASC
3 Likes

wow thank you! let me try this out

thank you Troy! This appears to have worked for me even with my little experience with SQL :smiley:

Yeah to changing requirements :crazy_face:

The ask is to determine what vulnerabilities were present for a given asset during a specific time range.

I think is a little different than what was just helped with.

THis would be say between 01/21 and 01/27 what vulns existed in that ‘moment in time’ in the DB for xyz assets…

I think I am overcomplicating thinking this through