I have been tasked to provide a report on missed microsoft patches. I am sure there is a way using an sql query, but I just don’t know where to start. Has someone got a query already that they would be willing to share?
I will really appreciate.
Something like SCCM would probably provide a more accurate report, but this query is a decent start:
microsoft_patch_vulnerabilities AS (
SELECT vulnerability_id, title, date_published
WHERE title LIKE '%MS__-%'
SELECT da.host_name, da.ip_address, vuln.title AS "Missing Microsoft Patch", vuln.date_published AS "Patch release date: ", now()::date - vuln.date_published::date AS "Days ago as patch released", da.sites
JOIN microsoft_patch_vulnerabilities vuln USING (vulnerability_id)
JOIN dim_asset da USING (asset_id)
ORDER BY title ASC, ip_address
Actually it looks like since 2017 Microsoft has changed their vulnerability reporting format, so this would need to be modified to handle the new format as well.
Another option is to use the “Microsoft Patch” category in the vulnerability scope section when generating a report - Vulnerability Remediation Projects in InsightVM | Rapid7 | Rapid7 Blog