SMS 2003 - Patch Reports - November 2006
November 2006 Patching report
Note: No Extended or office scanner updates this month
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_PatchStatus AS patch INNER JOIN
dbo.v_R_System ON patch.ResourceID = dbo.v_R_System.ResourceID
WHERE (patch.id in ('MS06-066', 'MS06-067', 'MS06-068', 'MS06-069', 'MS06-070', 'MS06-071', 'MS03-017', 'MS06-041'))
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
2006 Year in review report
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_PatchStatus 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
------------------------------------------------------------------------------
SELECT [ ], [ ], CASE Bulletin WHEN 'None' THEN Product ELSE Bulletin END AS Bulletin,
(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 CASE LocaleID WHEN '1033' THEN '' ELSE '' END AS [ ], 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_PatchStatus AS patch INNER JOIN
dbo.v_R_System ON patch.ResourceID = dbo.v_R_System.ResourceID
WHERE ((patch.title LIKE 'Office XP Service Pack 3%') OR
(Patch.Title LIKE '%889167%') OR
(Patch.Title LIKE '%89284%') OR
(Patch.Title LIKE '%904443%') OR
(Patch.Title LIKE '%905553%') OR
(Patch.Title LIKE '%905555%') OR
(Patch.Title LIKE '%90564%') OR
(Patch.Title LIKE '%90575%') OR
(Patch.Title LIKE '%90911%') OR
(Patch.Title LIKE '%908981%') OR
(Patch.Title LIKE '%911831%') OR
(Patch.Title LIKE '%911701%') OR
(Patch.Title LIKE '%917627%') OR
(Patch.Title LIKE '%911907%') OR
(Patch.Title LIKE '%914451%') OR
(Patch.Title LIKE '%9165%') OR
(Patch.Title LIKE '%9173%') OR
(Patch.Title LIKE '%914455%') or
(Patch.Title Like '%914796%') or
(Patch.Title Like '%914797%') or
(Patch.Title Like '%917150%') or
(Patch.Title Like '%917151%') or
(Patch.Title Like '%917152%') or
(Patch.Title Like '%918419%') or
(Patch.Title Like '%918420%') or
(Patch.Title Like '%918424%') or
(Patch.Title Like '%92082%') or
(Patch.Title Like '%92156%') or
(Patch.Title LIKE '%89454%') or
(Patch.Title LIKE '%MSXML%') or
(Patch.Title LIKE '%Security Update for Word%895%') OR
(Patch.Title LIKE '%2003 service Pack%')) AND (dbo.v_R_System.Operating_System_Name_and0 LIKE '%workstation%')
GROUP BY QNumbers, ID, Product, Title, LocaleID) AS ps
ORDER BY Bulletin, Product
Errors year in review
SELECT CASE WHEN ID = 'None' THEN Title ELSE ID END AS ID, QNumbers, SUM(CASE WHEN lastexecutionresult = '61686' THEN 1 ELSE 0 END)
AS '61686 - Invalid_Switch', SUM(CASE WHEN lastexecutionresult = '61482' THEN 1 ELSE 0 END) AS '61482 - STATUS MUST RESTART FIRST',
SUM(CASE WHEN lastexecutionresult = '61957' THEN 1 ELSE 0 END) AS '61957 - STATUS UPDATE ALREADY RUNNING',
SUM(CASE WHEN isNull(lastexecutionresult, '') = '' THEN 1 ELSE 0 END) AS '<Null> - No Idea',
SUM(CASE WHEN lastexecutionresult = '-532459699' THEN 1 ELSE 0 END) AS '-532459699 - Yet to figure out',
SUM(CASE WHEN lastexecutionresult = '-1073741818' THEN 1 ELSE 0 END) AS '-1073741818 - The remote procedure call was cancelled.',
SUM(CASE WHEN lastexecutionresult = '-1073741819' THEN 1 ELSE 0 END)
AS '-1073741819 - Binding Handle does not contain all required information',
SUM(CASE WHEN lastexecutionresult = '-1073740972' THEN 1 ELSE 0 END) AS '-1073740972 - Yet to figure out',
SUM(CASE WHEN lastexecutionresult = '299' THEN 1 ELSE 0 END) AS '299 - ReadProcessMemroy or WriteProcessMemory Failed',
SUM(CASE WHEN lastexecutionresult = '999' THEN 1 ELSE 0 END) AS '999 - Error performing Inpage Operation',
SUM(CASE WHEN lastexecutionresult = '1' THEN 1 ELSE 0 END) AS '1 - Incorrect Function',
SUM(CASE WHEN lastexecutionresult = '3' THEN 1 ELSE 0 END) AS '3 - Cant find path', SUM(CASE WHEN lastexecutionresult = '5' THEN 1 ELSE 0 END)
AS '5 - Access Denied', SUM(CASE WHEN lastexecutionresult = '32' THEN 1 ELSE 0 END) AS '32 - Cannot access the file',
SUM(CASE WHEN lastexecutionresult = '53' THEN 1 ELSE 0 END) AS '53 - The network path was not found.',
SUM(CASE WHEN lastexecutionresult = '59' THEN 1 ELSE 0 END) AS '59 - An unexpected network error occurred',
SUM(CASE WHEN lastexecutionresult = '64' THEN 1 ELSE 0 END) AS '64 - The specified network name is no longer available',
SUM(CASE WHEN lastexecutionresult = '1231' THEN 1 ELSE 0 END) AS '1231 - Cant find path',
SUM(CASE WHEN lastexecutionresult = '3010' THEN 1 ELSE 0 END) AS '3010 - Needs Restart',
SUM(CASE WHEN lastexecutionresult = '1603' THEN 1 ELSE 0 END) AS '1603 - Fatal Error', SUM(1) AS 'total errors'
FROM v_gs_patchstatusex AS patch
WHERE ((patch.id LIKE 'MS06-%') OR (patch.title LIKE 'Office XP Service Pack 3%') OR
(Patch.Title LIKE '%889167%') OR
(Patch.Title LIKE '%89284%') OR
(Patch.Title LIKE '%904443%') OR
(Patch.Title LIKE '%905553%') OR
(Patch.Title LIKE '%905555%') OR
(Patch.Title LIKE '%90564%') OR
(Patch.Title LIKE '%90575%') OR
(Patch.Title LIKE '%90911%') OR
(Patch.Title LIKE '%908981%') OR
(Patch.Title LIKE '%911831%') OR
(Patch.Title LIKE '%911701%') OR
(Patch.Title LIKE '%917627%') OR
(Patch.Title LIKE '%911907%') OR
(Patch.Title LIKE '%914451%') OR
(Patch.Title LIKE '%9165%') OR
(Patch.Title LIKE '%9173%') OR
(Patch.Title Like '%914796%') or
(Patch.Title Like '%914797%') or
(Patch.Title Like '%917150%') or
(Patch.Title Like '%917151%') or
(Patch.Title Like '%917152%') or
(Patch.Title Like '%918419%') or
(Patch.Title Like '%918420%') or
(Patch.Title Like '%918424%') or
(Patch.Title Like '%92082%') or
(Patch.Title Like '%92156%') or
(Patch.Title LIKE '%89454%') or
(Patch.Title LIKE '%MSXML%') or
(Patch.Title LIKE '%Security Update for Word%895%') OR
(Patch.Title LIKE '%2003 service Pack%')) AND laststatename = 'failed'
GROUP BY ID, Qnumbers, title
ORDER BY ID