Close Menu
    Facebook X (Twitter) Instagram
    Monday, May 19
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»Configmgr SSRS Report List all collections with count of client installed assigned active and Obsolete

    Configmgr SSRS Report List all collections with count of client installed assigned active and Obsolete

    Eswar KonetiBy Eswar KonetiOctober 17, 6:28 am1 Min Read CM2012 2,285 Views
    Share
    Facebook Twitter LinkedIn Reddit

    There was a question in forum list about how to get count of clients by its status (active,obsolete etc). I looked at it if there is any way to get it without report but No.

    In Configuration Manager 2007,to know the count of Computers from collection,You right click on collection,click show Count will give us the information but in Configmgr 2012,this is not possible until you develop some custom tools to get this.By default ,You can only see the total number of clients Count and members visible but not how many active,obsolete etc .

     

    image

     

    I created simple report that produces this info.

    Use the Below SQL Code to create SSRS report.

    select fcm.collectionid [Collection ID],coll.Name,
    count(case when fcm.IsActive=1 then '*' else NULL end) [Active Clients],
    count(case when fcm.IsClient= 1 then '*' else NULL end) [Client Installed],
    count(case when fcm.IsAssigned=1 then '*' else NULL end) [Assigned Clients],
    count(case when fcm.IsObsolete= 1 then '*' else NULL end) [Obsolete Clients]
    from v_FullCollectionMembership fcm,v_Collection coll
    where fcm.ResourceType='5' and fcm.CollectionId=coll.CollectionID
    group by fcm.CollectionID,coll.Name
    order by fcm.CollectionID

    Note:Change the quotes as they are not regular.

    SSRS Results:

    image

    Collections Count count of clients with assigned List all collections obsolete SCCM 2012 Status of Clients
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

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

    February 11, 9:50 pm

    Identifying devices managed by Intune but not reporting to WUfB using KQL

    November 09, 10:28 am

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 pm

    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.