I’m having trouble getting this information. I have found a few queries with different information, the first being:
Top 25 remediation’s:
SELECT DISTINCT
ds.summary AS “Solution”,
proofAsText(ds.fix) AS “Fix”,
ds.estimate AS “Estimate”,
dv.title AS “Vulnerability Title”,
da.ip_address AS “IP Address”,
da.host_name AS “Host Name”, dacs.aggregated_credential_status_description AS “Access Level”,
round(dv.riskscore) AS “Risk Score”,
dv.severity AS “Severity”,
da.last_assessed_for_vulnerabilities AS “last discovered” FROM
fact_remediation(25, ‘riskscore DESC’) fr
JOIN dim_solution ds ON (fr.solution_id = ds.solution_id) JOIN dim_asset_vulnerability_solution davs ON (fr.solution_id = davs.solution_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_asset da USING (asset_id)
JOIN fact_asset fa USING (asset_id)
JOIN dim_aggregated_credential_status dacs using (aggregated_credential_status_id)
and the second:
Assets with UUID:
select dauid.asset_id, max(dauid.unique_id) as uuid, max(dauid.source) as source, da.ip_address, da.mac_address, da.host_name, da.sites
from dim_asset_unique_id dauid
join dim_asset da using (asset_Id)
where unique_Id in (
select unique_Id from (
select unique_Id, count()
from dim_asset_unique_id
group by unique_Id having count() >1) as dupes)
group by dauid.asset_id, da.ip_address, da.mac_address, da.host_name, da.sites
order by uuid
I need a way of combining these two to show top 25 remediation with details while also having the UUID.