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”?
Try the filter:
vulnerability.category = Microsoft Patch
That shows me all the assets with any missing MS patch. I would like a list of all asset missing a specific patch.
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”
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;
OK then add:
vulnerability.title contains KB1234567
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
But I’ll give it a try!
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…
I just checked in in my environment.
If you want to search for KB IDs it’s probably best to use the search function at the top.
This will give you a list of vulnerabilities connected to this KB:
For the use is the query builder I found out it’s best to use the alternate IDs filter and search for the number only (without the “KB”)
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)
Perhaps you can query all assets based on the particular CVE-ID you’re looking for. That should bring all tagged assets.
or find the solution id’s associated with your systems (assuming you have workstations, servers, etc. and query based on that.
Best luck to you, OP.