SCCM query to count missing and installed updates

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
February 19th, 2015 12:04pm

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?





Free Windows Admin Tool Kit Click here and download it now
February 19th, 2015 9:46pm

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
February 20th, 2015 12:46am

I'm using SCCM 2012 R2.
I modified your query and added some stuff to it:

There is probably a neater/cleaner way to do this, but this works.

select
    CS.Name0 AS 'Computer',
	CS.UserName0 AS 'Last User', 
	ws.lasthwscan as 'Last HW scan',
	MAX(os.LastBootUpTime0) AS 'Last Reboot (not accurate)',
	MAX(MP_ALL.Patches_Missing) AS 'Total',
	MAX(MP_CRITICAL.Patches_Missing) AS 'Critical',
	MAX(MP_SERVICEPACKS.Patches_Missing) AS 'Service Packs',
	MAX(MP_SECURITY.Patches_Missing) AS 'Security Updates',
	MAX(MP_ROLLUPS.Patches_Missing) AS 'Update Rollups',
	MAX(MP_UPDATES.Patches_Missing) AS 'Updates',
	MAX(MP_FEATURES.Patches_Missing) AS 'Feature Packs',
	MAX(MP_DEFINITIONS.Patches_Missing) AS 'Definition Updates',
	MAX(MP_DRIVERS.Patches_Missing) AS 'Drivers',
	MAX(MP_TOOLS.Patches_Missing) AS 'Tools',
	MAX(MP_APPLICATION.Patches_Missing) AS 'Application',
	MAX(System_OU_Name0) AS 'Machine OU',
	MAX(OS.Caption0) AS 'Win OS',
	MAX(OS.CSDVersion0) AS 'Service Pack',
	MAX(OS.InstallDate0) AS 'Image Date',
	MAX(OS.TotalVisibleMemorySize0) AS 'RAM MB',
	CS.Manufacturer0 AS 'Manufacturer',
	CS.Model0 AS 'Model'
	

from
    v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM  CS
	on CS.ResourceID = UCS.ResourceID
left join v_gs_workstation_status ws 
	on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM 
	on FCM.resourceid = CS.resourceid
	
left join v_RA_System_SystemOUName SOU
	on SOU.ResourceID = CS.ResourceID
left join v_GS_Operating_System OS	
	on OS.ResourceID = CS.ResourceID

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) MP_ALL ON MP_ALL.resourceid = CS.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Critical Updates'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_CRITICAL ON CS.resourceid = MP_CRITICAL.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Definition Updates'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_DEFINITIONS ON CS.resourceid = MP_DEFINITIONS.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Drivers'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_DRIVERS ON CS.resourceid = MP_DRIVERS.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Feature Packs'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_FEATURES ON CS.resourceid = MP_FEATURES.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Security Updates'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_SECURITY ON CS.resourceid = MP_SECURITY.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Service Packs'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_SERVICEPACKS ON CS.resourceid = MP_SERVICEPACKS.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Tools'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_TOOLS ON CS.resourceid = MP_TOOLS.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Update Rollups'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_ROLLUPS ON CS.resourceid = MP_ROLLUPS.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Updates'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_UPDATES ON CS.resourceid = MP_UPDATES.resourceid

LEFT JOIN (
	select
		CS.resourceid,
		sum(case when UCS.status=2 then 1 else 0 end) As 'Patches_Missing'
	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' 
	and catinfo2.CategoryInstanceName = 'Applications'
	Group by
		CS.resourceid,
		ws.lasthwscan,
		FCM.collectionID 
) AS MP_APPLICATION ON CS.resourceid = MP_APPLICATION.resourceid


	
WHERE
    UCS.Status = '2' 
and FCM.collectionid = 'SMS00001' 

Group by
	CS.resourceid,
	CS.Name0,
	CS.UserName0, 
	CS.Manufacturer0,
	CS.Model0,
	ws.lasthwscan,
	FCM.collectionID
Order by
	MAX(MP_ALL.Patches_Missing) DESC,
	CS.Name0,
	ws.lasthwscan,
	FCM.collectionID

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 7:50pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics