Close Menu
    Facebook X (Twitter) Instagram
    Monday, June 23
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»Collections»SCCM report computers member of which collection

    SCCM report computers member of which collection

    Eswar KonetiBy Eswar KonetiJuly 01, 11:15 am1 Min Read Collections 10,385 Views
    Share
    Facebook Twitter LinkedIn Reddit

     

    There are cases,where a computer might be added to different collection and it would be difficult to find out the computer is member of which collections.Try this report to list all the collection the computer member of.

    select a.CollectionId, b.Name from dbo.v_R_System r
    join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID
    join dbo.v_Collection b on b.CollectionID = a.CollectionID
    Where R.Name0 =@machine

    Prompt for machine:

    select Name0 from v_R_System

    If you want to try for user member of What collection ? below is the SQL report.

    select
    a.CollectionId,
    b.Name
    from
    dbo.v_R_User R
    join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID
    join dbo.v_Collection b on b.CollectionID = a.CollectionID
    Where
    R.User_Name0 =@UserName

    Prompt for User:

    select User_Name0 from v_R_User

    collection member of computer member of which collections Configmgr 2007 configmgr report member of member of sub collection SCCM SCCM 2007 SCCM Reports SMS SQL User member of What collections which collection members
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    Investigating Co-Management Issues with Windows Endpoints in SCCM/Intune

    October 26, 10:45 pm

    7 Comments

    1. Nawaz kazi on July 28, 2014 4:34 PM

      OK. Thanks. will check it now and see how it goes.

      Reply
    2. Nawaz kazi on July 13, 2014 4:18 PM

      Hi eswar,
      Is it possible to pass multiple machines to check if they are part of how many collection.

      Thanks mate.

      Reply
      • Eswar Koneti on July 14, 2014 9:29 AM

        yes,you can do that as well..just as many prompts you want in the report like where R.Name0 =@machine1 or R.Name0 =@machine2 or R.Name0 =@machine3
        and create as many prompts what you used in Report.that should pull all the collections for the respective machines but that would be like mess.if one collection will be part of 30 collections and for 2 pcs,you get 60 rows ,so you total see pcs*number of collections in report that will be lenghty report instead of looking for one that we usually pick.

        Reply
    3. Navneet Singh on November 27, 2013 8:14 PM

      Hello,

      I try running this query in my test LAB but it is not showing any information. It is showing blank report.

      Reply
      • Eswar Koneti on December 7, 2013 9:01 AM

        is the computer member of any collections ? this is very simple report without any complications.

        Reply
    4. Joe on June 24, 2013 8:40 AM

      Hi,

      Can you execute those commands from Custom Queries on SCCM 2012?

      Many Thanks

      Reply
      • Eswar Koneti on June 25, 2013 3:03 PM

        You can.Use Report Builder to create custom reports using the same quires.

        Reply

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.