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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

749 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