Hi guys, I'm looking for a couple of queries. Hopefully someone can help me out.

The first, which is probably simpler; a query which will give me the count of missing updates and the count of installed updates for every machine that has 1 ore more update missing. I am not interested in the number of updates installed on a machine that doesn't have any updates missing. Would also be good to know when the machine last reported in.

So something like this:

The second, I assume more complicated one; a query that will give me the count of missing updates but broken down into their classification (ie. critical, service packs, updates...) and a count of the installed updates.

So something like this:

Is this possible?

• Edited by amaru96 Thursday, February 19, 2015 9:28 AM

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
• Proposed as answer by Garth JonesMVP, Moderator Saturday, February 28, 2015 3:35 PM