|
jnelson993 -> RE: Request report query assistance (9/11/2008 9:32:25 PM)
|
I think what Garth is trying to say is :), you have there some views you aren't using...also if you want to include counts AND eliminate dupes you have to use a GROUP BY. And for what it's worth, I prefer using v_GS_PatchStateEx instead of v_GS_PatchStatusEx because it's more reliable in my opinion and you don't have to look for a series of status numbers or the absense of a status number. If it's Applicable, you need it. If it's INSTALLED then, by God, it's installed. I'm thinking something like this:
(BTW, I'm using MIN(Status0) to determine if something is applicable because there are cases with certain service packs and low priority office patches that you may have multiple patches with the same MSID, Title & QNumber, but they're actually 3 different patches for the same KB. If you pick the MIN, then they'll always show as APPLICABLE if one or more records for that patch show as applicable. If they're all INSTALLED, then MIN will always show INSTALLED...I hope that makes sense..it's patch week and I've had little sleep.)
SELECT
COUNT(DISTINCT CASE WHEN Status0 = 'Applicable' THEN resourceID END) AS [Count],
MIN(Status0) AS [Status],
ID0 as [MSID],
QNumbers0 AS [QNumber],
Title0 AS [Title],
Product0 AS [Product],
CASE Severity0
WHEN 0 THEN 'Low'
WHEN 2 THEN 'Moderate'
WHEN 6 THEN 'Important'
WHEN 8 THEN 'High'
WHEN 10 THEN 'Critical'
ELSE ''
END AS [Severity],
Severity0 AS [Severity Level]
FROM
dbo.v_GS_PatchStateEx
WHERE
type0 = 'Microsoft Update'
AND resourceID IN (SELECT ResourceID FROM dbo.v_FullCollectionMembership WHERE CollectionID = 'xxxxxxxx')
GROUP BY
ID0,
QNumbers0,
Title0,
Product0,
Severity0
HAVING
MIN(Status0) = 'Applicable'
ORDER BY
MIN(Status0),
Severity0 DESC,
ID0 DESC,
QNumber DESC
|
|
|
|
