Devices in a particular device collection

select * from dbo.v_FullCollectionMembership where CollectionID=’collection id’


Maintenance windows of a particular server in SCCM 2012

This query gives maintenance windows of a particular server in sccm 2012

Note : you need to provide server name in the query

select a.CollectionName , b.CollectionID, b.Name, c.StartTime , c.Duration from v_Collections a
join v_FullCollectionMembership b on ( a.SiteID = b.CollectionID )
join v_ServiceWindow c on ( a.SiteID = c.CollectionID ) where b.Name like ‘%servername%’ order by b.Name

SCCM SQL Queries

This query gives maintenance windows and collection name of each server in SCCM 2012

v_FullCollectionMembership.Name as ComputerName ,v_Collection.Name as CollectionName,
v_ServiceWindow.Description as ‘Next Maintenance Window’
from v_ServiceWindow
inner join v_FullCollectionMembership on (v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID)
inner join v_Collection on (v_Collection.CollectionID = v_FullCollectionMembership.CollectionID)
order By v_Collection.Name