NEW ITMU Reports
I’ve had a problem with the ITMU reporting for some time. The problem was associated with MS06-014. This patch was showing 10-15 times the numbers it should have been showing. I almost open a ticket with MS today because I needed to get my numbers correct because I had to make a new report that shows the overall status of the Different Departments. But since the numbers for MS06-014 are way off it was adding about a 5-10 % difference to them. So if the status showed they were at 80% they where actually 70-75% .
This is what I was seeing:
As you can see I only have 18926 clients in this report but it list 209650 requesting the patch
The problem was cause by MS releasing a language pack for each of the languages that it supports. To resolve this I added the located ID for location 0 and 9 to the ITMU reports temp table “where” statement.
As you can see this fixed the issue.
I attached my new reports incase anybody was using the old ones.
Note: you will need to change the Collection ID query to reflect your environment.
I also included my new Critical patch report. I will blog on that seporate.
The change is in red below.
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.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed AS 'Total with Status',
ROUND((100 * (ps.Verified + .00000001)) / (.00000001 + ps.Retrying + ps.PreSuccess + ps.Uninstalled + ps.PendReboot + ps.Verified + ps.NoStatus + ps.Failed), 0) AS '% Compliant',
ps.Verified, ps.NoStatus, ps.Retrying, ps.PreSuccess, ps.Uninstalled, ps.PendReboot, ps.Failed, real_total.total, ps.CollectionID
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 ID0
FROM v_GS_PATCHSTATEEX
WHERE (Language0 = 'English' Or LocaleID0 In ('0','9'))
AND ID0 <> 'none'
AND Type0 = 'Microsoft Update'
AND Severity0 = '10') 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
ORDER BY ps.Bulletin DESC