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
  • 2
LVL 23

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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

How to record audio from input sources to your PC – connected devices, connected preamp to record vinyl discs, streaming media, that play through your audio card: Vista, Windows 7, Windows 8, Windows 8.1 and Windows 10 – both 32 bit & 64.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

856 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