Get location of specific SCCM device collection in Powershell

I am writing a script to export the names of all computer in a device collection to a txt file. My script works as expected but I would like to preserve the folder structure in the exported file structure. For this I need to get the location of the Device Collection.

My Question: Is there a way to get the location of a SCCM Device Collection in PowerShell?

I've stumbled across a few posts like this and this that use WMI and WQL for this, but I wasn't able to get those working in my script and I would like to do everything in PowerShell whenever possible.

$collections = (Get-CMDeviceCollection | Select -ExpandProperty "Name")

$totalCollections = $collections.length

"Number of Collections: $totalCollections"

$i = 0
foreach($name in $collections){
    ForEach-Object -Process {
        $i++
        "Writing File $i of $totalCollections"
        $SanitizedName = $name -replace '/','(slash)' -replace '\\','(backslash)' -replace ':','(colon)' -replace '\*','(asterisk)' -replace '\?','(questionmark)' -replace '"','(quote)' -replace '<','(less)' -replace '>','(more)' -replace '\|','(pipe)'
        $file = New-Item -Path "C:\Temp\exporte\$SanitizedName.txt"
        Add-Content -Path $file.FullName -Value (Get-CMCollectionMember -CollectionName $name | Select -ExpandProperty "Name")
    }
}

I would like to expand this code so that the txt files are placed in the corresponding subfolder analog to the SCCM file structure. E.g rootFolder/rooms/

I was using this module until now but wasn't able to find anything that gives me back the specific location of a collection.

Thanks in advance

Solution

I wasn't able to find a way to do this in plain PowerShell and the SCCM Module. In the end I did it like @FoxDeploy suggested. I made a SQL query select for each collection (performance isn't an issue in my case) on our SCCM database to get the folder path. I then used this to place the export file in the appropriate place.

This is my working example with some confidential lines removed

## Parameter ##
$exportLocation = [removed]
$sqlServer = [removed]
$db = [removed]

$query = "SELECT [ObjectPath] FROM [removed].[v_Collections] WHERE CollectionName ="

$SiteCode = [removed] # Site code 
$ProviderMachineName = [removed] # SMS Provider machine name

# Customizations
$initParams = @{}

# Import the ConfigurationManager.psd1 module 
if((Get-Module ConfigurationManager) -eq $null) {
    Import-Module [removed]\..\ConfigurationManager.psd1" @initParams 
}

# Connect to the site's drive if it is not already present
if((Get-PSDrive -Name $SiteCode -PSProvider CMSite -ErrorAction SilentlyContinue) -eq $null) {
    New-PSDrive -Name $SiteCode -PSProvider CMSite -Root $ProviderMachineName @initParams
}

Set-Location "$($SiteCode):\" @initParams

# get all collections and save them to an array
$collections = (Get-CMDeviceCollection | Select -ExpandProperty "Name")

# total number of collections
$totalCollections = $collections.length

# output to console
"Number of Collections: $totalCollections"

# empty output directory
Set-Location [removed]
Remove-Item $exportLocation\* -Recurse -Force
Set-Location [removed]

# loop through all collections
$i = 0
foreach($name in $collections){
    ForEach-Object -Process {
        # print progress
        $i++
        "Writing File $i of $totalCollections"
        # remove all characters, that aren't compatible with the windows file naming scheme (/\:*?"<>|)
        $SanitizedName = $name -replace '/','(slash)' -replace '\\','(backslash)' -replace ':','(colon)' -replace '\*','(asterisk)' -replace '\?','(questionmark)' -replace '"','(quote)' -replace '<','(less)' -replace '>','(more)' -replace '\|','(pipe)'
        # get members of collection
        $collectionMembers = (Get-CMCollectionMember -CollectionName $name | Select -ExpandProperty "Name")
        # write to file
        Set-Location [removed]
        $path = (Invoke-Sqlcmd -ServerInstance $sqlServer -Database $db -Query "$query '$collection'").Item("ObjectPath")
        New-Item -ItemType Directory -Force -Path "$exportLocation$path"
        $file = New-Item -Path "$exportLocation$path\$SanitizedName.txt"
        Add-Content -Path $file.FullName -Value $collectionMembers
        Set-Location [removed]
    }
}

hope this helps someone. Thanks @FoxDeploy