Close Menu
    Facebook X (Twitter) Instagram
    Monday, June 23
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»Configmgr SSRS SQL Query How to check Client is member of what collections and its Maintenance Window

    Configmgr SSRS SQL Query How to check Client is member of what collections and its Maintenance Window

    Eswar KonetiBy Eswar KonetiFebruary 05, 12:27 am1 Min Read CM2012 885 Views
    Share
    Facebook Twitter LinkedIn Reddit

     

    This is quick post on How to check Client is member of what collections and display its Maintenance window .

    Maintenance Window information for collections are stored in view called v_ServiceWindow .Here is the excel spreadsheet that I always make it as reference for any custom queries always help to find right view. http://eskonr.com/2013/10/download-sccm-configmgr-2012-r2-sql-views/ 

    You can use the below SQL Query to run it from SQL management studio or use it in SSRS Reports.

    Change the PC name in marked in bold letters.

    select fcm.CollectionId, coll.Name [Collection Name],s.Name [MW Name],s.Description,s.StartTime,s.Duration
    from dbo.v_R_System sys
    join dbo.v_FullCollectionMembership FCM on sys.ResourceID = fcm.ResourceID
    join dbo.v_Collection coll on Coll.CollectionID = fcm.CollectionID
    left join v_ServiceWindow S on s.CollectionID=fcm.CollectionID
    Where sys.Name0 ='WIN10X64-003'

    Full Details : https://social.technet.microsoft.com/Forums/en-US/3a171c53-9ca4-4ecb-9ce6-5dfca19cdadc/query-to-get-server-name-which-collection-it-is-a-member-of-and-the-associated-maintenance-window?forum=configmanagergeneral

    image

    client maintance window CM12 Collection configmgr Maintanance Window Member collections SCCM SQL SSRS
    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

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

    November 09, 10:28 am

    4 Comments

    1. Hackmuss on February 12, 2016 4:50 PM

      I can see this being a useful query, but is it possible to make it so that it prompts for you to input the machine name? That would make it a lot easier...

      Reply
      • Eswar Koneti on February 25, 2016 10:15 PM

        yes,it is possible ,i will update the blog post soon.

        Reply
        • Jay on September 28, 2016 3:47 AM

          Hi Eswar, how to get this work for 2 or more machines at once

          Reply
          • admin on September 28, 2016 9:04 AM

            You can modify the sql query to something like below and you can keep adding more machines if needed.
            Where sys.Name0 =’WIN10X64-003′ or sys.name='pc001' or sys.name0='pc002'

            Regards
            Eswar

            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.