Query Collection in SCCM for members

Posted on 2014-10-23
Last Modified: 2014-12-05
Good Morning,

I'm trying to query the SCCM database to get the members of a collection. I'm trying to do this in I found a script on Microsofts website but couldn't get it to work. I also know how to connect to the SCCM database but not sure how to grab a collection and then export it to a text file or show it in a List Box. Here's the code i got from microsoft.

' Set up a connection to the local provider.
Set swbemLocator = CreateObject("WbemScripting.SWbemLocator")
Set swbemServices= swbemLocator.ConnectServer(".", "root\sms")
Set providerLoc = swbemServices.InstancesOf("SMS_ProviderLocation")

For Each Location In providerLoc
    If location.ProviderForLocalSite = True Then
        Set swbemServices = swbemLocator.ConnectServer(Location.Machine, "root\sms\site_" + Location.SiteCode)
        Exit For
    End If

Call EnumerateCollectionMembers(swbemServices)

Sub EnumerateCollectionMembers(connection)

' Set required variables.
' Note:  Values must be manually added to the queries below.

    ' The following example shows how to enumerate the members of the All Systems (SMS00001) collection.
    Query1 = "SELECT ResourceID FROM SMS_FullCollectionMembership WHERE CollectionID = 'SMS00001'" 
    ' Run query.
    Set ListOfResources1 = connection.ExecQuery(Query1, , wbemFlagForwardOnly Or wbemFlagReturnImmediately)

    ' The query returns a collection that needs to be enumerated.
    Wscript.Echo " "
    Wscript.Echo "Query: " & Query1
    For Each Resource1 In ListOfResources1     
        Wscript.Echo Resource1.ResourceID   
    ' A slower alternative is to use the SMS_CollectionMember_a association class.
    Query2 = "SELECT ResourceID FROM SMS_CollectionMember_a WHERE CollectionID = 'SMS00001'" 
    ' Run query.
    Set ListOfResources2 = connection.ExecQuery(Query2, , wbemFlagForwardOnly Or wbemFlagReturnImmediately)

    ' The query returns a collection that needs to be enumerated.
    Wscript.Echo " "
    Wscript.Echo "Query: " & Query2
    For Each Resource2 In ListOfResources2     
        Wscript.Echo Resource2.ResourceID   
    ' A further alternative is to query the members by using the actual collection class name specified in the MemberClassName property of SMS_Collection. 
    Query3 = "SELECT ResourceID FROM SMS_CM_Res_Coll_SMS00001" 
    ' Run query.
    Set ListOfResources3 = connection.ExecQuery(Query3, , wbemFlagForwardOnly Or wbemFlagReturnImmediately)

    ' The query returns a collection that needs to be enumerated.
    Wscript.Echo " "
    Wscript.Echo "Query: " & Query3
    For Each Resource3 In ListOfResources3     
        Wscript.Echo Resource3.ResourceID   

End Sub

Open in new window

Question by:AnthonySmithMCP
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 24

Accepted Solution

Nagendra Pratap Singh earned 500 total points
ID: 40402577
You can export a collection using the console if you want.

Author Comment

ID: 40406263
Thanks but that's not what I'm looking for.

Author Closing Comment

ID: 40482902
Tried but no answer

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question