Add Schedule column to v_servicewindow
With the SQL view vSMSServiceWindow, there are a column Schedules. This column allows you to see exactly what day Maintenance Window is scheduled for. The correspond supported SQL view is vServiceWindow. However, that column isn’t listed without it being listed there is no way to determine what day an MW applies too.
isnull(SWin.Name,'No Maintenance Windows') as 'SWinName',
isnull(SWin.IsGMT,'') as 'SWinIsGMT',
SWin.StartTime as 'SWinStartTime',
isnull(SWin.Duration,0) as 'SWinDuration',
when isnull(SWin.ServiceWindowType, 0) = 0 then 'No Maintenance Windows'
when SWin.ServiceWindowType=1 then 'All Programs Service Window'
when SWin.ServiceWindowType=2 then 'Program Service Window'
when SWin.ServiceWindowType=3 then 'Reboot Required Service Window'
when SWin.ServiceWindowType=4 then 'Software Update Service Window'
when SWin.ServiceWindowType=5 then 'OSD Service Window'
when SWin.ServiceWindowType=6 then 'Corresponds to non-working hours'
end as 'SWinType'
left outer join dbo.v_ServiceWindow SWin on Col.CollectionID = SWin.CollectionID
Below are the results of the query above.
All Friday 0 2019-01-09 00:00:00.000 1439 All Programs Service Window
All Monday 0 2019-01-09 00:00:00.000 1439 All Programs Service Window
All Saturday 0 2019-01-09 00:00:00.000 1439 All Programs Service Window
All the time except for Sunday between 00:00 - 04:00 0 2019-01-09 04:00:00.000 1199 All Programs Service Window
All Thursday 0 2019-01-09 00:00:00.000 1439 All Programs Service Window
All Tuesday 0 2019-01-09 00:00:00.000 1439 All Programs Service Window
All Wednesday 0 2019-01-09 00:00:00.000 1439 All Programs Service Window
Exactly what day does the 4 row happen on? Are you sure? There is no way to tell from the SQL view.
So why is this information important? Knowing the exact details of MW helps when troubleshooting. This also allows for a dashboards/reports to be created so that CM administrators can quickly look to see if there are any problems allowing them to quickly isolate what problems they might have or better yet eliminate that as the likely issue. This will also allow CM admin to meet and validate they are meeting this KPIs for deployments.
To ramp up, this view, even more, translate all of the schedule items to their respective values.