Starting from the query indicated in the following article, how can the query return only the name of systems that have more than one associated maintenance window? https://www.prajwaldesai.com/find-sccm-maintenance-windows-for-a-computer/ select v_FullCollectionMembership.Name as Computername ,v_Collection.Name as CollectionName, v_ServiceWindow.Description as "Maintenance Windows Schedule" 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 Thank you for your support.

Look at the Having Sql function, this will allow to get those system having more than one maintenance window.

Query Collection : Multiple Maintenance Window assigned

Accepted answer

AllenLiu-MSFT 44,826 Microsoft Vendor

Thank you for posting in Microsoft Q&A forum.

select

 v_FullCollectionMembership.Name as Computername , COUNT(v_FullCollectionMembership.Name) as MaintenanceWindowsNumber

 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)

 group by v_FullCollectionMembership.Name

 having COUNT(v_FullCollectionMembership.Name) >= 2

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

Vid3al 96

Thank you @AllenLiu-MSFT , it works well as a report !!

I only needed it for server systems.

SELECT        dbo.v_FullCollectionMembership.Name AS Computername, COUNT(dbo.v_FullCollectionMembership.Name) AS MaintenanceWindowsNumber, dbo.v_R_System.operatingSystem0
FROM            dbo.v_ServiceWindow INNER JOIN
                         dbo.v_FullCollectionMembership ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_ServiceWindow.CollectionID INNER JOIN
                         dbo.v_Collection ON dbo.v_Collection.CollectionID = dbo.v_FullCollectionMembership.CollectionID INNER JOIN
                         dbo.v_R_System ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_R_System.ResourceID
GROUP BY dbo.v_FullCollectionMembership.Name, dbo.v_R_System.operatingSystem0
HAVING        (COUNT(dbo.v_FullCollectionMembership.Name) >= 2) AND (dbo.v_R_System.operatingSystem0 LIKE N'%server%')

Do you think you can also create a query collection?

Thank you for your support and patience.

AllenLiu-MSFT 44,826 Microsoft Vendor

Hi, @Vid3al

 SELECT        dbo.v_FullCollectionMembership.Name AS Computername, COUNT(dbo.v_FullCollectionMembership.Name) AS MaintenanceWindowsNumber, v_R_System.Operating_System_Name_and0 as OperatingSystem
FROM            dbo.v_ServiceWindow INNER JOIN
                         dbo.v_FullCollectionMembership ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_ServiceWindow.CollectionID INNER JOIN
                         dbo.v_Collection ON dbo.v_Collection.CollectionID = dbo.v_FullCollectionMembership.CollectionID INNER JOIN
                         dbo.v_R_System ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_R_System.ResourceID
GROUP BY dbo.v_FullCollectionMembership.Name, v_R_System.Operating_System_Name_and0
HAVING   (COUNT(dbo.v_FullCollectionMembership.Name) >= 2) and dbo.v_R_System.Operating_System_Name_and0 LIKE N'%server%'

Share via

Query Collection : Multiple Maintenance Window assigned

1 additional answer

Your answer