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