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

 

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

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

  1. 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
        1. 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 Reply