Nathan Ziehnert

5 minute read

We took the opportunity during our SCCM migration (2012r2 to 1610) to clean up a lot of the “junk” that had accumulated in our environment. Instead of using the migration wizard the goal was to use the export/import features selectively to get us to a “clean” environment going forward. This for the most part worked really well - application deployment types obviously had to have new content locations set, packages had to have updated content locations, and the task sequences had to have the package/application references updated as well.

One thing that didn’t work so well - or at least wouldn’t have saved a bunch of time without some custom scripting - was the Device and User collections export and import. The main problem that we ran into is Limiting Collections are not maintained (with the exception of collections limited to built-in collections) regardless of the order in which the collections were re-imported. This was because the MOF file that is created for export includes the reference to the old collection ID, so when the collection is re-imported SCCM just assumes you want the same limiting collection. In a similar vein (and another problem that I’ll address in a future post) is that anything other than queries is also referenced by SCCM Object ID - which means that direct memberships and include/exclude collections are not maintained either.

almost made it

So, we had a couple options at this point - after importing all of the collections we could go through and manually update all of the collections (the “safe” but time consuming venture) - or we could script up and have PowerShell do all of the heavy lifting. This is where things get really interesting though…

Here’s the deal. SCCM’s PowerShell instance really doesn’t like it when you have bad data in the database… like really doesn’t like it. We tried running “Set-CMCollection” and were presented with the following lovely error:

PS PRI:\> Set-CMCollection -CollectionId "PRI00280" -LimitingCollectionId "PRI000B1" Set-CMCollection : Operation could not complete because the currently connected account does not have the required security rights to perform this operation. At line:1 char:1

Hmm. I know that this account has full permissions to the SCCM database (including SA privs on the SQL server), so I know that can’t be right. When all else fails try WMI right?

$collection = Get-WmiObject -Namespace “root\SMS\Site_PRI” -Class SMSCollection | where {$.Name -eq “COLLECTIONNAME”} $collection.LimitToCollectionName = “Windows 7 Machines” $collection.LimitToCollectionID = “PRI000B1” $collection.Put() Exception calling “Put” with “0” argument(s): “Generic failure “ At line:1 char:1

What. The. Heck?! At this point we had pretty much resolved to updating our couple hundred collections by hand, but in one last “Hail Mary” we decided to do what you should never do in SCCM… update the database manually.

WARNING… WARNING… WARNING… I DON’T KNOW HOW MANY TIMES I NEED TO SAY WARNING. THIS IS STUPID AND YOU REALLY SHOULDN’T DO THIS. ConfigMgr, IT WORKED IN MY LAB (YOU CAN WRITE THAT ON MY GRAVESTONE). PROCEED AT YOUR OWN RISK. TEST TEST TEST TEST TEST.

Okay, with the copious amount of warnings out of the way, let’s take a look at our SCCM database (I’m really trusting you to be looking at a DEV database and not your production one). Open up SQL Server Management Studio and connect to your CM_XXX database (where XXX is your site code) assuming that you used the default database name. Expand “Tables” and then find your “dbo.Collections_G” table. Right click on it and select the top 1000 rows. Locate one of your collections with the bad limiting collection.

database

Well now wouldn’t you know it… it’s referencing the old site’s collection ID. Now, since I don’t have some sort of hash table that matches old collection ID to new collection ID I’m going to go ahead and just update ALMOST all of the collections to their default “All” counterpoint collection. I say “ALMOST” because you don’t want to update the default collections and you will get some really bad circular references if you set the limiting collection of the master groups (“All Systems” and “All Users and User Groups”) to themselves.

UPDATE Collections_G
SET LimitToCollectionID='SMS00004'
WHERE CollectionType='1' AND SiteID NOT LIKE 'SMS%';

UPDATE Collections_G
SET LimitToCollectionID='SMS00001'
WHERE CollectionType='2' AND SiteID NOT LIKE 'SMS%';

FYI this will update all of your custom collections - if you have done more than just import collections (like created manual ones as well) this will update those. You could use some other filter limiters like “CollectionID > xxxxxxxx” assuming you didn’t create any collections after importing, or something similar. If you’re not sure how to target your SQL query properly, you should probably be seeking assistance or just not doing it at all.

Once the limiting collections were set properly, we were able to use the PowerShell cmdlet to update our collection IDs. Since we had a few collections - we actually just used the following script to handle the heavy lifting:

$oldSiteCode = "PR1"
$oldSiteFQDN = "oldsite.contoso.com"
$newSiteCode = "PR2"
$newSiteFQDN = "newsite.contoso.com"

Import-Module "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1" # Import the ConfigurationManager.psd1 module 
if(-not (Test-Path "$oldSiteCode`:")){
    New-PSDrive -Name $oldSiteCode -Root $oldSiteFQDN -PSProvider CMSite
}
if(-not (Test-Path "$newSiteCode`:")){
    New-PSDrive -Name $newSiteCode -Root $newSiteFQDN -PSProvider CMSite
}

Set-Location "$oldSiteCode`:"
$oldCollections = Get-CMCollection -Name *
Set-Location "$newSiteCode`:"
$newCollections = Get-CMCollection -Name *

foreach($newCollection in $newCollections){
    if(-not ($newCollection.CollectionID -contains "SMS")){
        $oldCollectionID = $oldCollections.Where({$_.Name -eq "$($newCollection.Name)"}).LimitToCollectionID
        $oldCollectionName = $oldCollections.Where({$_.CollectionID -eq "$oldCollectionID"}).Name
        $newLimitingCollection = $newCollections.Where({$_.Name -eq "$oldCollectionName"}).CollectionID
        Write-Host "Set-CMCollection -CollectionID `"$($newCollection.CollectionID)`" -LimitToCollectionId `"$newLimitingCollection`""
        Set-CMCollection -CollectionID "$($newCollection.CollectionID)" -LimitToCollectionId "$newLimitingCollection"
    }
}

This script gets a list of the new collections by ID, tries to find a match to the limiting collection by ID in the old collections, gets the name of that limiting collection, and then attempts to find a match in the new collections by name and sets that as the limiting collection. It doesn’t work if the names don’t match exactly, but it did the job for us.

Hopefully this proves to be of some use to you - or at least answers why you get errors when trying to use PowerShell or WMI to update limiting collections. As always - happy admining!

comments powered by Disqus