SQL Query to find Maintenance windows on collections.

 

Author Nawaz Kazi

Hello EveryOne,

I had a scenario, where even if machines had proper maintenance windows and client settings assign to them, yet machines were not rebooting.

Further, on Troubleshooting, I found that there were many collections that had expired windows, so even if the machine had an active maintenance window, you are going to have a tough time on patching day.

If we search any default reports, there is only one report to check the maintenance window that too on a single machine.

Let us jump to the query which lists all collections having Maintenance Window.

SQL QUERY

select 
c.Name as 'Collection Names',
sw.Name as 'Maintenance Window Name',
sw.Duration,
CAST(sw.StartTime as date) as 'Start Date',
case
when sw.RecurrenceType ='1' Then 'None'
when sw.RecurrenceType ='2' Then 'DAILY'
when sw.RecurrenceType ='3' Then 'Weekly'
when sw.RecurrenceType ='4' Then 'MONTHLYBYWEEKDAY'
when sw.RecurrenceType ='5' Then 'MONTHLYBYDATE'
 else 'unknown' end as 'Recurrence Type',

c.Comment,sw.Description from v_ServiceWindow sw
join v_Collection c on c.CollectionID = sw.CollectionID

order by sw.StartTime

 

Now you can find all collections having an Expired maintenance window. It’s time to remove the expired maintenance window which was only one set up to run once. SQL query also share this information via “Recurrence Type” values

Conclusion: If one machine is part of multiple collections where one collection has an active maintenance window and the other one having an expired maintenance window such workstations are going to have issues.