SMS, SCCM, MECM, MEM, MCM & other fun acronyms

I keep a list that associates MECM client versions to a “friendly” name and use it alot during Power BI report building demos. I’ve been trying to find a good list of Win10 and beyond builds to do something similar with Windows product names but haven’t found a list I liked so decided to maintain my own.

Windows Version Name	Windows Build Number
Windows 11 22H2	10.0.22621
Windows 11 21H2	10.0.22000
Windows 10 22H2	10.0.19045
Windows 10 21H2	10.0.19044
Windows 10 21H1	10.0.19043
Windows 10 20H2	10.0.19042
Windows 10 2004	10.0.19041
Windows 10 1909	10.0.18363
Windows 10 1903	10.0.18362
Windows 10 1809	10.0.17763
Windows 10 1803	10.0.17134
Windows 10 1709	10.0.16229
Windows 10 1703	10.0.15063
Windows 10 1607	10.0.14393
Windows 10 1511	10.0.10586
Windows 10 1507	10.0.10240

We are all, or most are, in the habit of checking the ‘Windows 10‘ product when configuring a new software update point for MECM. But, since the introduction of ‘Windows 10 version 1903 and later‘ to the product list back in MECM 1906 is there still a need to check that box? And if it’s currently checked, and it probably is, is it time to uncheck it and cleanup all of that related content from the SUP catalog?

Obviously if you have one/some/many machines that are not running at least Windows 10 1903 then you may still need those updates in the catalog. But hopefully you are well past that version and the pre-1903 Windows 10 updates may no longer be needed in your environment. As with anything you should test and verify the impact before making any significant change to your environment. If you need a query to determine which updates in your SUP catalog are for the Windows 10 product only, to possibly help identify the risk, consider the query below. Adjust the isexpired, isSuperseded and nummissing at the bottom to meet your needs.

SELECT   CI_ID,   CI_UniqueID,   DateRevised,   DisplayName,   IsDeployed,   IsExpired,   IsSuperseded,   CIInformativeURL,   NumMissing,   NumNotApplicable,   NumPresent,   NumTotal,   NumUnknown,   PercentCompliant,   SeverityName FROM   dbo.fn_ListUpdateCIs(1033) AS ci WHERE       CI_ID IN (       SELECT         CI_ID       FROM         dbo.fn_ListCICategoriesAll(1033) AS SMS_CIAllCategories       WHERE         (           CategoryInstance_UniqueID = ‘Product:a3c2375d-0c8a-42f9-bce0-28333e198407’         ))     and isexpired = 0     and isSuperseded = 0     and nummissing > 0   order by DisplayName

Use the following to query the MECM database to count profiles on devices. Remove the – – comment to focus only on workstation operating systems.

select v_R_System.Name0 as ‘Computer Name’, Count(LocalPath0) as ProfileCount from v_GS_USER_PROFILE
left join v_R_System ON v_R_System.ResourceID = v_GS_USER_PROFILE.ResourceID
where LocalPath0 LIKE ‘%\users\%’
–and v_R_System.Operating_System_Name_and0 LIKE ‘%worksta%’
group by v_R_System.Name0 order by ProfileCount desc

When you configure which devices will be uploaded in the “Configure upload” tab of the CoMgmtSettingsProb properties window you can select to upload all devices managed by MECM, which is the recommended option, or you can select a specific collection of devices to upload.

If you have any collections in the site that have the “Make this collection available to assign Endpoint security policies from Microsoft Endpoint admin center” enabled

then you will get the message “The collection cannot be updated. Referenced collections are available to assign policies from Microsoft Endpoint Manager admin center“.

In order to select a collection for device upload, or even change the collection to a different one that you may have set initially, you will first need to disable the Make this collection available option for every collection that has it. If you are not sure which collections have that option enabled, run this query against the MECM database for a list:

Go thru the list in the MECM console, disable the “Make this collection available” option for each collection and then you should be able to select the collection of devices you want to upload in the “Upload specific collection” option.

consider this:

Note that there is no Remove option in the above. If you enable a built-in/default collection for AAD Group sync and specify an AAD Group to sync to you cannot remove the group later, you have to open a support ticket Configuration Manager Support and have them handle it. If it’s a custom collection you can freely add and remove AAD group sync objects.

SQL query here in case it disappears from Github, which is where I got it, full credit to NecroMonkey:

SELECT DISTINCT
sysr.Netbios_Name0,
ipr.Name as [Boundary],
ipr.BoundaryType as [Type],
bg.Name AS [Boundary Group]
FROM v_R_System AS sysr INNER JOIN
System_IP_Address_ARR AS ip ON ip.ItemKey = sysr.ResourceID AND ip.NumericIPAddressValue <> 0 INNER JOIN
v_RA_System_IPSubnets AS sub ON sub.ResourceID = sysr.ResourceID LEFT OUTER JOIN
v_RA_System_IPv6Prefixes AS v6 ON v6.ResourceID = sysr.ResourceID INNER JOIN
BoundaryEx AS ipr ON ipr.BoundaryType = 3 AND ip.NumericIPAddressValue BETWEEN ipr.NumericValueLow AND ipr.NumericValueHigh OR
ipr.BoundaryType = 1 AND ipr.Value = sysr.AD_Site_Name0 OR
ipr.BoundaryType = 0 AND ipr.Value = sub.IP_Subnets0 OR
ipr.BoundaryType = 2 AND ipr.Value = v6.IPv6_Prefixes0 INNER JOIN
vSMS_BoundaryGroupMembers as bgm ON ipr.BoundaryID = bgm.BoundaryID INNER JOIN
vSMS_BoundaryGroup as bg ON bgm.GroupID = bg.GroupID
GROUP BY sysr.Netbios_Name0, ipr.Name, ipr.BoundaryType, bg.Name
ORDER BY sysr.Netbios_Name0, ipr.Name, bg.Name

https://github.com/NecroMonkey/vault/blob/master/SQL/client_boundary_membership_update.sql

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

In the past when I or a customer needed to report inventory on something that isn’t in the default list of available hardware inventory items on an MECM client I would add a custom entry in hardware inventory to collect that data. The old tool RegKeytoMOF was great for that and understanding MOF syntax was helpful if you needed to tweak the code the tool generated.

Fast forward a number of years, and with the addition and steady improvements in CMPivot, I don’t know that I’ll need to perform custom hardware inventory edits as often if a customer just wants a list of machines that have/don’t have a certain item. A recent example is a customer needed to know which Windows 10 machines had TLS configured. My knowledge of TLS is limited but this seems to work:

Registrykey(‘HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\*’) | where key like ‘%TLS%’ | join OS | where (Caption like ‘%10%’) | project device, Key, Caption, Version

This CM query assumes that the UPN is being duplicated in the database but you could change it to user_name0 if that is where the duplicate entries are being found:

select user_principal_name0, count(*) total from v_r_user
group by User_Principal_Name0
having (count(user_principal_name0))>1

There are native MECM reports that show which maintenance windows a client is in but none for clients with no maintenance window defined. Consider the following queries that could be used in SRS or PowerBI

SELECT sys.Name0 AS ‘Computer Name’, sys.Operating_System_Name_and0 AS ‘Operating System’
FROM v_ServiceWindow AS sw INNER JOIN
v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID RIGHT OUTER JOIN
v_R_System AS sys ON fcm.ResourceID = sys.ResourceID
WHERE (sw.Name IS NULL) AND (sys.Client0 = 1)
ORDER BY ‘Computer Name’

and if you want to verify it’s working, remove the NULL where clause:

SELECT sys.Name0 AS ‘Computer Name’, sys.Operating_System_Name_and0 AS ‘Operating System’, sw.Name as ‘MW Name’, sw.CollectionID
FROM v_ServiceWindow AS sw INNER JOIN
v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID RIGHT OUTER JOIN
v_R_System AS sys ON fcm.ResourceID = sys.ResourceID
WHERE (sys.Client0 = 1)
ORDER BY ‘MW name’