OK, so discovered the below:
26 = critical
27 = Definition Updates
28 = Drivers
29 = Feature Packs
30 = Security Updates
31 = Service Packs
32 = Tools
33 = Update Rollups
34 = Updates
Also, the below query returns a list of total updates required for each machine.
select
CS.Name0 [Server Name],
case
when
(sum(case
when UCS.status=2
then 1 else 0
end))>0 then
('Needs '+(cast(sum(case
when UCS.status=2
then 1 else 0
end)as
varchar(10))+
' Patches'))
else
'Good Client'
end
as 'Status',
ws.lasthwscan
as 'Last HW scan',
FCM.collectionID--,
from
v_UpdateComplianceStatus UCS
left
outer join dbo.v_GS_COMPUTER_SYSTEM CS
on CS.ResourceID
= UCS.ResourceID
join v_CICategories_All catall2
on catall2.CI_ID=UCS.CI_ID
join v_CategoryInfo catinfo2
on catall2.CategoryInstance_UniqueID
= catinfo2.CategoryInstance_UniqueID
and catinfo2.CategoryTypeName='UpdateClassification'
left
join v_gs_workstation_status ws
on ws.resourceid=CS.resourceid
left
join v_fullcollectionmembership FCM
on FCM.resourceid=CS.resourceid
WHERE
UCS.Status
= '2'
and FCM.collectionid
= 'SMS00001'
Group
by
CS.Name0,
ws.lasthwscan,
FCM.collectionID
Order
by
CS.Name0,
ws.lasthwscan,
FCM.collectionID
If I add the following, I get the total 'critical' updates required by each machine:
WHERE
catinfo2.CategoryInstanceID
= 26 AND
UCS.Status
= '2'
and FCM.collectionid
= 'SMS00001'
Changing the value '26' to one of the others listed above returns that classification.
Anyone know how I can incorporate "WHERE catinfo2.CategoryInstanceID = xx"
to list each update classification?
-
Edited by
amaru96
Friday, February 20, 2015 12:02 AM
Other recent topics
