I didn’t have much experience using SCCM collection variables until we recently made the switch to using the task sequence engine to do our monthly patch management (future blog topic). After becoming aware of the power that collection variables hold, I quickly learned they do have some drawbacks too. One of the challenges I found with using collection variables was that the unencrypted value assigned to the variable was not stored in the SQL database.
I believe the reason for masking the variable value is that collection variables are often used to supply user accounts and passwords to the Operating System Deployment (OSD) feature. In our case we are using collection variables to assign programs to machines while running the task sequence so the encrypted values just got in the way while trying to integrate the variable data into our SQL reports.
Luckily the SCCM WMI provider exposes methods to read/write collection variables directly in the WMI repository. With that in mind I wrote a PowerShell module that will read and write SCCM collection variables.
Here are the main functions:
All of the functions take two string parameters as inputs; one for the SCCM Site Server and the other for the SCCM Site Code. Most of the functions also rely on a CollectionID string parameter plus a variable name and value. The one exception is the Get-SCCMCollectionVariables function which will get all the collection variables for the site if no CollectionID is passed in.
I use this module in conjunction with another PowerShell script to retrieve all the collection variables in our SCCM site and upload it to our SQL database. From there I join the collection membership tables from SCCM and that nets me a list of resources that have the variables assigned to them…and most importantly the variable values.
Download the zip archive which contains the SCCMCollectionVariables-Commands.psm1 file; use Import-Module to load it into your PowerShell session and enjoy.
This code is provided as-is and without warranty, but please feel free to let me know if you find any bugs or have any enhancement suggestions and I’ll do my best to post back online any major revisions or updates I make.