Hello everyone.
I want to generate two custom reports, all about Remediation Plan.
1- The first report will have to be grouped by the [Remediation Solution and the hosts that requires such a solution] since the normal Remediation Plan report groups them by host, I will have multiple duplicate solutions. If I can make such custom report I will remove any duplicated solutions.
2- The Second report, i want to do it the same as the one before but with the ability to exclude any solution that has the terms [ Upgrade or Update ]. Basically I wan to exclude solutions that are related to an App or OS old versions that requires updates.
Am not sure how I can create these types of report or if its even possible. Any insight on this is much apperciated.
Thank you
1 Like
Not sure if you ever got this worked out on your own or not but this can certainly be done through SQL.
Just uncomment the WHERE line to filter out the Upgrade type solutions
SELECT
htmltotext(ds.fix) AS solution,
STRING_AGG(DISTINCT(ds.url), ', ' Order by ds.url) as solution_url,
STRING_AGG(DISTINCT(da.ip_address), ', ' Order by da.ip_address) AS ip_address,
STRING_AGG(DISTINCT(dv.title), ', ' Order by dv.title) AS vulnerability_description
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da ON favf.asset_id = da.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN dim_vulnerability_solution dvs ON favf.vulnerability_id = dvs.vulnerability_id
JOIN dim_solution ds ON dvs.solution_id = ds.solution_id
--Use this to filter out upgrades
--WHERE ds.fix NOT ILIKE '%Upgrade%'
GROUP BY ds.fix
ORDER by ds.fix DESC