myITforum.com : Annual Patch Status Report

I ran across this report from Shaun Cassells; it lists all the updates labeled with a prefix of MS06 and displays their status and percentage compliant clients. This is a great overview report; but I found it was not setup to leverage the data coming from ITMU. I made a simple mod to the report and here it is; using ITMU data.

This report will be used time and time again; since it is soooo easy to modify for 2005, 2007 & so on.

SELECT Bulletin, QNumber, Product, (100 * Verified / (Retrying + PreSuccess + Uninstalled + PendReboot + Verified + NoStatus + Failed)) AS '% Compliant',
Retrying + PreSuccess + Uninstalled + PendReboot + Verified + NoStatus + Failed AS Total, Verified, NoStatus, Retrying, PreSuccess, Uninstalled,
PendReboot, Failed, Issue
FROM (SELECT QNumbers AS QNumber, ID AS Bulletin, Product, Title AS Issue, LocaleID AS Locale,
SUM(CASE WHEN patch.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS NoStatus,
SUM(CASE WHEN patch.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS Verified,
SUM(CASE WHEN patch.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS Retrying,
SUM(CASE WHEN patch.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS PreSuccess,
SUM(CASE WHEN patch.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS Uninstalled,
SUM(CASE WHEN patch.LastStateName = 'Reboot pending' THEN 1 ELSE 0 END) AS PendReboot,
SUM(CASE WHEN patch.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS Failed
FROM v_GS_PatchStatusEx AS patch INNER JOIN
dbo.v_R_System ON patch.ResourceID = dbo.v_R_System.ResourceID
WHERE ((patch.id LIKE 'MS06-%') AND (dbo.v_R_System.Operating_System_Name_and0 LIKE '%workstation%'))
GROUP BY QNumbers, ID, Product, Title, LocaleID) AS ps
ORDER BY Bulletin DESC, product

Here is a link to Shawn's origonal article which contains a few other reports you find of use. http://www.myitforum.com/articles/20/view.asp?id=9485


		myITforum.com ©2010 \| Legal \| Privacy