Display a SCCM Collection Variable and its Value

Purpose: Query SCCM Collection Variables and Values in MS SQL Reporting services (SQL)

There is a suprize waiting for you if you thought you could add variables and values into your collections and query the results using Microsoft SQL Report. Finding the correct attributes from the SQL tables is straightforward, but that is not the real problem.

Query Collection Variables and Values (SQL)

The following query will return what we were looking for, but the variable values are actually encrypted! This is perhaps because values usually include passwords transferred during OS deployments. You cannot find out what the values are using SQL! So if you were looking to add collection variable values into your reports, you cannot do that.

1
2
3
4
5
SELECT
ParameterName = V.name,
ParameterValue = v.value
FROM v_CollectionVariable V
INNER JOIN v_collection C ON v.collectionid = c.collectionid
SELECT
ParameterName = V.name,
ParameterValue = v.value
FROM v_CollectionVariable V
INNER JOIN v_collection C ON v.collectionid = c.collectionid

Result:

Query Collection Variables and Values (WQL)

However, if you dont need to add them as part of a report, but would like to get them listed for another reason, you can use the SCCM Admin Console WQL queries. Create the following query and the result will be as the following.

SELECT * FROM SMS_Collection

Result: Now you see if your device collection has any variables at all (Yes/No)

SELECT * FROM SMS_CollectionSettings

Result: However, for some reason the variables are not listed in this view, the value is empty even if we know there to be a variable!

So sadly, I could not find a way to display the Collection Variables in SCCM Management Console, luckily there is still one way.

Query Collection Variables and Values (Powershell)

Lets first get the same views that we tried from the SCCM Management Console (for comparison)

Ok, so the values are empty here too, and its not an array or anything, the collection variable value is actually null here! But since SCCM 2012 SP1 we have additional powershell CMDLETs at our disposal, and one of them is now an aswer to our problem.

Set-CMDeviceCollectionVariable
Remove-CMDeviceCollectionVariable
New-CMDeviceCollectionVariable
Get-CMDeviceCollectionVariable

Additional information can be returned by get-help (cmdlet sccm command)

We finally got the unencrypted result of the SCCM Device Collection Variable, not by using SQL, not by using WQL, but by using a powershell CMDLET!

Leave a Reply

Your email address will not be published. Required fields are marked *


nine + nine =

Post Navigation