SQL query for missed microsoft patches

InsightVM SQL Queries
1

Hello all

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.

2

Something like SCCM would probably provide a more accurate report, but this query is a decent start:

WITH
microsoft_patch_vulnerabilities AS (
	SELECT vulnerability_id, title, date_published
	FROM dim_vulnerability
	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
FROM fact_asset_vulnerability_finding
	JOIN microsoft_patch_vulnerabilities vuln USING (vulnerability_id)
	JOIN dim_asset da USING (asset_id)
ORDER BY title ASC, ip_address
3

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

4

Thanks sir

5

Hi,

Would it be possible to join and pull back tag data as well for each asset it returns??