SCCM (ConfigMgr) 2012/CB – SQL query to list all servers without a maintenance window assigned

I was searching today because  I wanted to find out which of my servers that didn’t have a MW applied to it. I could only find blog posts telling me how to list computers that do have MW’s applied to them, so I fired up SQL Management Studio and put this together real quick and figured I might as well share it with the rest of the internet (those who find this anyway). The query is a subselect query, i.e. it gets the servers who are not included in the query enclosed in parentheses. Notice I have filtered ‘Operating System Name and Version’ to scope it to servers only. Just remove both those to list all computers. Also I have a non-recurring MW with a start date in the past targeted to servers, to prevent accidental deployments. This MW is also filtered out. Remove that part of the subselect query or replace it with your own.


SELECT
SYS.Name0 AS 'Computer Name',
SYS.Client0 AS 'Client Installed?',
SYS.Operating_System_Name_and0 AS 'Operating System'

FROM
dbo.v_R_System SYS

WHERE
SYS.Operating_System_Name_and0 LIKE '%server%' AND
SYS.Name0 NOT IN

(
SELECT SYS.Name0

FROM
dbo.v_ServiceWindow AS SW INNER JOIN
dbo.v_FullCollectionMembership AS FCM ON SW.CollectionID = FCM.CollectionID INNER JOIN
dbo.v_R_System SYS ON FCM.ResourceID = SYS.ResourceID

WHERE SYS.Operating_System_Name_and0 LIKE '%server%' AND SW.Name != 'Old MW'
)

ORDER BY SYS.Name0

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s