PowerShell WMI/WQL to get information from SCCM - optimisation

Question

This is a case where what I have works fine but I can't help but feel there must be a faster way to do this. I'm doubting my structure here. Do you see a faster way this could be done? The foreach takes a while since it has to query many times. The goal is to have an array that shows the computer's ($poste) collections (name and objectpath).

$poste = "p1234"

$SiteCode = "PRX"
$SiteServer = "SSX"

$ResourceID = Get-WmiObject -Namespace root\sms\site_$SiteCode -Computer $SiteServer -Class SMS_R_SYSTEM -Filter "Name='$poste'" |
              Select-Object -ExpandProperty ResourceID
$CollectionIDs = Get-WmiObject -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Class sms_fullcollectionmembership -Filter "ResourceID ='$resourceID'" |
                 Select-Object -expand CollectionID

foreach ($CollectionID in $CollectionIDs) {
    $query = @"
SELECT * FROM SMS_Collection
WHERE SMS_Collection.CollectionID='$CollectionID'
"@

[array]$CollectionNamesPath += Get-WmiObject -Namespace root\sms\site_$SiteCode -Computer $SiteServer -Query $query |
                               Select-Object name,ObjectPath |
                               Sort-Object -Property Name
} 

$CollectionNamesPath | Out-GridView

if the number of items is "large", then this `[array]$CollectionNamesPath +=` will make the loop run slower with each pass. the array `+=` does NOT add ... it creates a new one-item-larger array, copies the old to the new, inserts the new item, and then deletes the old one. as you would guess, that gets very slow as the array gets larger. the usual solution is to use `$Collection = foreach (` to put the entire set of results into the array all at one time. — Lee_Dailey, Feb 22 '19 at 14:05
Hi Lee! Good point there, I can't help but feel there must be a better way to tackle this, maybe with the SCCM CMDlets? — Rakha, Feb 22 '19 at 14:10
i don't know anything about the SCCM cmdlets ... however, one would hope they were optimized. [*grin*] i would give it a try ... — Lee_Dailey, Feb 22 '19 at 14:19
That's a fine idea, simply run the commands concurrently instead of one after the other. I'll see what I can do. — Rakha, Feb 22 '19 at 14:55
This question has already been answered. If you have a new question, please ask it by clicking the [Ask Question](//stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. — Samuel Liew, Feb 26 '19 at 01:04

score 1 · Accepted Answer · answered Feb 22 '19 at 15:21

This is likely the cleanest version of your existing code:

$poste = "p1234"
$SiteCode = "PRX"
$SiteServer = "SSX"
$ResourceID = Get-WmiObject -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Class SMS_R_SYSTEM -Filter "Name='$poste'" -Property ResourceID | Select-Object -ExpandProperty ResourceID
$CollectionIDs = Get-WmiObject -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Class sms_fullcollectionmembership -Filter "ResourceID='$resourceID'" -Property CollectionID | Select-Object -ExpandProperty CollectionID

$CollectionNamesPath = foreach ($CollectionID in $CollectionIDs) {
    $Query = "Select Name, ObjectPath From SMS_Collection Where SMS_Collection.CollectionID='$CollectionID'"
    Get-WmiObject -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Query $Query | Select-Object Name, ObjectPath | Sort-Object -Property Name
} 

$CollectionNamesPath | Out-GridView

You could also try this, though I'm not sure how well WQL supports it:

$poste = "p1234"
$SiteCode = "PRX"
$SiteServer = "SSX"
$ResourceID = Get-WmiObject -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Class SMS_R_SYSTEM -Filter "Name='$poste'" -Property ResourceID | Select-Object -ExpandProperty ResourceID
$CollectionIDs = Get-WmiObject -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Class sms_fullcollectionmembership -Filter "ResourceID='$resourceID'" -Property CollectionID | Select-Object -ExpandProperty CollectionID

$Query = "Select CollectionID, Name, ObjectPath From SMS_Collection Where" + $($($CollectionIDs | ForEach-Object { " SMS_Collection.CollectionID='$_' " }) -join 'or')

$CollectionNamesPath = Get-WmiObject -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Query $Query |
    Sort-Object -Property CollectionID, Name |
    Select-Object Name, ObjectPath

$CollectionNamesPath | Out-GridView

You may be able to do something like this:

Get-CimInstance -Namespace root\sms\site_$SiteCode -ComputerName $SiteServer -Class SMS_R_SYSTEM -Filter "Name='$poste'" -KeyOnly |
    Get-CimAssociatedInstance -ComputerName $SiteServer -ResultClassName sms_fullcollectionmembership -KeyOnly |
    Get-CimAssociatedInstance -ComputerName $SiteServer -ResultClassName SMS_Collection |
    Sort-Object -Property CollectionID, Name |
    Select-Object Name, ObjectPath

When it works it's fantastic. However, in my experience working with Get-CimAssociatedInstance is an uneven experience at best, doubly so when working on remote computers.

Or just use `-Filter` on `Get-WmiObject` without constructing some big query — Maximilian Burszley, Feb 22 '19 at 15:55
Thanks a lot for your suggestions I will try them when I have some time and report — Rakha, Feb 22 '19 at 18:29
@Bacon Bits I'm trying to collect the data with jobs so it's much faster, however i'm missing something...would you have any tips for that (check the question's EDIT) — Rakha, Feb 25 '19 at 12:53
@TheIncorrigible1 I'm not at all sure how using `-Filter` saves anything here. The value for the filter would be identical to the Where clause of the WQL. They use the same syntax. — Bacon Bits, Feb 25 '19 at 15:27
@Rakha That is a completely separate question to the one that was asked here originally. You should post that question to a different question if that's what you're trying to ask. — Bacon Bits, Feb 25 '19 at 15:30

PowerShell WMI/WQL to get information from SCCM - optimisation

1 Answers1