Query Dump

I’m used to putting queries in OneNote or a Notepad file and then forget where I put them, what I called them, etc. So now I’ll start putting them here and maybe you’ll also find them useful.

Computer Restart Pending:

SELECT Name, ClientState
FROM v_CombinedDeviceResources
where ClientState is not NULL

anything that isn’t a 0 means there is a pending restart on the computer. This data updates fast, it does not wait for an inventory cycle to get collected. So, run this query in a large/busy environment and I would expect the data to change significantly throughout the day.

Category/Product selections in the SUP configuration:

SELECT v_UpdateCategoryInstances.CategoryTypeName, SCCM_Ext.vex_LocalizedCategoryInstances.CategoryInstanceName
FROM v_UpdateCategoryInstances INNER JOIN
SCCM_Ext.vex_LocalizedCategoryInstances ON v_UpdateCategoryInstances.CategoryInstanceID = SCCM_Ext.vex_LocalizedCategoryInstances.CategoryInstanceID
where v_UpdateCategoryInstances.IsSubscribed = ‘1’
order by CategoryTypeName, categoryinstancename

This query shows what products and classifications have been selected in the SUP configuration of the ConfigMgr site. If you need to see what is available, not what is selected, set it to False aka 0