SQL example - Patch Tuesday

This example query returns the vulnerabilities from the latest Microsoft Patch Tuesday.

Start / End Date times will need to be updated monthly.

sql
1
SELECT ds.name AS site, da.ip_address, da.host_name, dv.title AS vulnerability_title, dos.description AS operating_system, dos.cpe
2
3
FROM fact_asset_vulnerability_finding favf
4
5
JOIN dim_asset da USING (asset_id)
6
7
JOIN dim_operating_system dos USING (operating_system_id)
8
9
JOIN dim_vulnerability dv USING (vulnerability_id)
10
11
JOIN dim_site_asset dsa USING (asset_id)
12
13
JOIN dim_site ds USING (site_id)
14
15
WHERE dv.title LIKE 'Microsoft CVE%'
16
17
AND dv.date_published BETWEEN '2017-04-01 00:00:00' AND '2017-04-30 11:59:59'
18
19
ORDER BY da.ip_address ASC, dv.title ASC