The “Top 25 Remediations by Risk” report shows me that I have a few hundred assets that is missing a specific MS patch (with a specific KB number).
I was thinking it should be pretty easy to create a Remediation Project that shows me all the assets based on this missing patch so I can have my team work on resolving this issue.
However, its turns out that I cant find a way to create a query for a Remediation Project, that lists all the assets that are missing this patch. I thought it would be a simple task as the the report clearly can display the assets missing the patch - but I can’t build a query that does the same.
Are there any workarounds to build a query which basically says “List all assets where KB1234567 is missing”?
You can probably use something like this to pull that from the dim_solution.summary table in a custom SQL query. You can add or remove whichever columns you want, but here’s the basic idea.
SELECT ds.summary AS “Remediation Summary”,
dv.title AS “Vulnerability title”,
da.host_name AS “Hostname”,
dos.description AS “Operating System”,
da.ip_address AS “IP Address”,
date(da.last_assessed_for_vulnerabilities) AS “Last Scanned”
FROM dim_asset_vulnerability_best_solution
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_asset da USING (asset_id)
JOIN dim_solution ds USING (solution_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN fact_asset fa USING (asset_id)
WHERE ds.summary ~* ‘(KB1234567)’
ORDER BY da.host_name ASC, ds.summary ASC;
Interesting! I’ll look into that - However, I don’t know how to turn an custom SQL query into a remediation project and it does seem a little excessive for something that I would think should be readily available
Unfortunately that does not give me a useful result. As you can see on the attached screenshots, the report shows me that 276 assets does not have KB4580328, but building a query showing me Microsoft patches and vulnerability titles containing the patch ID yelds 0 assets - The vulnerability does simply not contain the name of the patch…
Is there any way we can filter by solution ID?
So for instance we need to upgrade to last version a specific software. How can we create a remediation project to display the assets which contain the software and only the upgrade to last version solution? Is there any way to filter by solution ID (for intance solution ID - jre-upgrade-latest)