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
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...
yes,it is possible ,i will update the blog post soon.
Hi Eswar, how to get this work for 2 or more machines at once
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'