ITMU patches released or re-released in the last 30 days
Here is a report that will show you any patches released or re-released in the last 30 days.
I dont like that it is showing old patches but it is a starting point. Maybe someone can add to it :-)
You will need to create the table and stored procedure I used in my previous ITMU reports to get the v_ms_patch_list.
SET NOCOUNT ON
(SELECT DISTINCT
ps.Bulletin AS Bulletin_No,
ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed - ps.Verified AS Unpatched
, ps.Verified as Patched,
ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed AS TWS,
ROUND((100 * (ps.Verified + .00000001)) / (.00000001 + ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed), 0) AS '% Compliant',
real_total.total as 'CR'
into #SMSITMU1
FROM (SELECT fcm.CollectionID,
pse.ID AS Bulletin,
SUM(CASE WHEN pse.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS NoStatus,
SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / 2 AS Verified,
SUM(CASE WHEN pse.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS Retrying,
SUM(CASE WHEN pse.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS PreSuccess,
SUM(CASE WHEN pse.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS Uninstalled,
SUM(CASE WHEN pse.LastStateName = 'Reboot pending' THEN 1 ELSE 0 END) AS PendReboot,
SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS Failed
FROM
v_ApplicableUpdatesSummaryEx INNER JOIN
v_GS_PatchStatusEx pse ON v_ApplicableUpdatesSummaryEx.UpdateID = pse.UpdateID RIGHT OUTER JOIN
v_FullCollectionMembership fcm ON pse.ResourceID = fcm.ResourceID
WHERE
(pse.LocaleID In ('0','9'))
AND (pse.QNumbers NOT LIKE 'None')
AND (pse.ID NOT LIKE 'None')
AND (fcm.CollectionID = @collid )
GROUP BY pse.ID
, v_ApplicableUpdatesSummaryEx.Type
, fcm.CollectionID
HAVING
(v_ApplicableUpdatesSummaryEx.Type = 'Microsoft Update')) ps
INNER JOIN
(
SELECT DISTINCT MS_ID as 'ID0'
FROM v_ms_patch_list
WHERE
--(Patch_Severity = 'critical') AND
(Date_Revised >= DATEADD([DAY], - 30, GETDATE()))
) As PatchList
ON ps.Bulletin = PatchList.ID0
CROSS JOIN
(SELECT CollectionID, COUNT(ResourceID) AS total
FROM v_FullCollectionMembership
GROUP BY CollectionID
HAVING (CollectionID = @collid )) real_total)
SELECT Name as Agency
FROM v_Collection
WHERE (CollectionID = @collid )
ORDER BY Name
Select Sum (#SMSITMU1.Patched) as 'patched'
, Sum (#SMSITMU1.TWS) As 'total'
,
ROUND((100 * (Sum (#SMSITMU1.Patched) + .00000001)) / (.00000001 + Sum (#SMSITMU1.TWS)), 0) AS '% Compliant'
From
#SMSITMU1
Select #SMSITMU1.Bulletin_No AS 'MS Number'
, #SMSITMU1.Patched As 'Patched'
, #SMSITMU1.TWS As 'Total Requested'
, #SMSITMU1.CR as 'Clients Reporting'
from #SMSITMU1
order by #SMSITMU1.Bulletin_No DESC
drop table #SMSITMU1