Here’s a nice piece of PowerShell that will quickly tell you what collections a device is a member of.
Replace siteserver, sitecode and hostname with the relevant details. Hostname is obviously the name of the device you want to find collection membership for.
$Collections = (Get-WmiObject -ComputerName siteserver -Namespace root/SMS/site_sitecode -Query "SELECT SMS_Collection.* FROM SMS_FullCollectionMembership, SMS_Collection where name = 'hostname' and SMS_FullCollectionMembership.CollectionID = SMS_Collection.CollectionID").Name
Write-Host $Collections
SCCMentor - Paul Winstanley 
Hey- I made a little tweak to add a hostname variable so I don’t have to change the script every time:
$hostname = Read-Host “What device do you want to find collections for?”
$Collections = (Get-WmiObject -ComputerName siteserver -Namespace root/SMS/our_3_letter_site_code -Query “SELECT SMS_Collection.* FROM SMS_FullCollectionMembership, SMS_Collection where name = ‘$hostname’ and SMS_FullCollectionMembership.CollectionID = SMS_Collection.CollectionID”).Name
Write-Host $Collections
That said, I’m having an issue where it’s telling me that my namespace is invalid. I’ve tried putting in both the name and FQDN of the site server, but I’m getting:
Get-WmiObject : Invalid namespace “root/SMS/our_3_letter_site_code”
At line:2 char:17
+ $Collections = (Get-WmiObject -ComputerName name_or_FQDN_of_primary_sccm_server -Namespac …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-WmiObject], ManagementException
+ FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand
Any ideas?
Thanks.
Looks like a typo -Namespace root/SMS/site_sitecode should read -Namespace root\SMS\site_sitecode. Let me know for sure and I can update the page. Cheers Paul
Actually, I figured it out after posting (isn’t that always the way?)
I needed to put
\root\SMS\site_*this_is_my_3_letter_sitecode*
I was not prepending “site_” to my site code.
Thanks for responding!
This works perfectly for me…thx. I do have one question. How do I get the output to display the results as a column so it’s much easier to read?
Thank you…
Lex – just remove ‘write-host’ from the end 🙂
Lex, try changing the last line to:
$collections | sort-object
Thanks .. it was working fine. I am able to get ‘Collection Name’ and ‘Collection Id’.
but do you have any idea how to get ‘Package Name’ as well?? which is associated with Collection Name/ID?
Not sure what you mean.
Thanks Paul – this is great! Appreciate you sharing this valuable PowerShell snippet (I was afraid this would end up being a SQL Server maneuver, but this code saved me from that). Thanks again!
How would you do the same for included collection? I mean instead of computer you would like to get all the collections that it is included as member.
can you please share the full script which is working well
Does it error?
Thanks for the article. This has helped me tremendously!