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
