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

Need to see which custom client settings are deployed to which collections? and the same for antimalware policies?  Also if the policy is not deployed to a collection it will show NULL for the collection name.  enjoy.

client settings:

SELECT vSMS_ClientSettingsAssignments.Name AS CollectionName, vSMS_AntimalwareSettings.Name AS EndPointPolicyName
FROM vSMS_ClientSettingsAssignments right JOIN
vSMS_AntimalwareSettings ON vSMS_ClientSettingsAssignments.ClientSettingsID = vSMS_AntimalwareSettings.ID

antimalware policy:

SELECT vSMS_ClientSettingsAssignments.Name AS CollectionName, vSMS_ClientSettings.Name AS ClientPolicyName
FROM vSMS_ClientSettingsAssignments right JOIN
vSMS_ClientSettings ON vSMS_ClientSettingsAssignments.ClientSettingsID = vSMS_ClientSettings.ID

Dumps a list of SCCM security scopes with descriptions via Powershell.  Replace computername and site_BBB with your site data.  enjoy.

Get-WmiObject -computername SCCM -Namespace “root\sms\site_BBB” -Class SMS_SecuredCategory | format-list -Property categoryname, categorydescription

If you need to determine which of your ConfigMgr packages, applications or update deployment packages has the “enable for on-demand distribution” option enabled, this query will help.  Run it in SQL studio against the ConfigMgr database.  You can adjust the where statement to also look for other configuration settings, like where the persist in cache option is set.  Enjoy.

SELECT  pkg.*,

   (PkgFlags&0x01000000)/0x01000000 AS PKG_DO_NOT_DOWNLOAD,
   (PkgFlags&0x02000000)/0x02000000 AS PKG_PERSIST_IN_CACHE,
   (PkgFlags&0x04000000)/0x04000000 AS PKG_USE_BINARY_DELTA_REP,
   (PkgFlags&0x10000000)/0x10000000 AS PKG_NO_PACKAGE,
   (PkgFlags&0x20000000)/0x20000000 AS PKG_USE_SPECIAL_MIF,
   (PkgFlags&0x40000000)/0x40000000 AS PKG_DISTRIBUTE_ON_DEMAND
 FROM   dbo.v_Package pkg  
 
 where ((PkgFlags&0x40000000)/0x40000000)  = 1

You can individually delete MPs from the SCOM console or you can delete multiples via Powershell with this command.  Replace msdtc in my example with the name/partial name of the MPs you want to mass delete.

get-scommanagementpack | ?{ $_.name -match “msdtc” } | remove-scommanagementpack