search cancel

Custom report to display Maintenance Window with Start and End time

book

Article ID: 174248

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

This report will display the Maintenance Window with Start and End time. 

Resolution

  1. On Symantec Management console, click on Reports > All Reports. 
  2. Right click and select New > Report > SQL Report
  3. Copy and paste below mentioned SQl query under Parameterized Query and Save :

 

--Create a temp table to hold the xml data

declare @PatchTargetXML as table (Name nvarchar (1000), Guid uniqueidentifier, [Schedule] xml, tz nvarchar (100))
 
--Populate the data for maintenance windows as xml that can be more easily parsed
insert into @PatchTargetXML
SELECT 
i.Name, 
i.Guid, 
[Schedule] = CAST(i.State AS XML),
case 
when CAST (i.State as XML).value('(/item/schedule/@tz)[1]', 'nvarchar (100)') = 'Local' then 'Agent Time'
when CAST (i.State as XML).value('(/item/schedule/@tz)[1]', 'nvarchar (100)') = 'Server' then 'Server Time'
when CAST (i.State as XML).value('(/item/schedule/@tz)[1]', 'nvarchar (100)') = 'UTC' then 'Coordinate using UTC'
end as 'tz'
FROM Item i
JOIN ItemClass ic on ic.Guid = i.Guid  
WHERE ic.ClassGuid = '610e2ad7-e76e-44fb-ad6a-c6e638596325'
  
--Get the individual values and try to display them is a more usable format
select a.*, COUNT (distinct pa.ResourceGuid) as 'Computer Count'
from 
(
SELECT i.Guid, i.Name, 
[Schedule] = tar.value('(@type)[1]', 'nvarchar (20)') + ' (' + i.tz + ')', 
[MW Start Time and Duration (Day.Hour:Min:Sec)] = replace (tar.value('(@at)[1]', 'nvarchar (20)'), ' Z', '') + ' for ' + tar.value('(@duration)[1]', 'nvarchar (20)'),
case 
when tar.value('(@type)[1]', 'nvarchar (20)') = 'Weekly' 
then 'On weekdays - ' + tar.value('(@weekdays)[1]', 'nvarchar (20)')
when tar.value('(@type)[1]', 'nvarchar (20)') = 'MonthlyByWeek' 
then 'On weeks - ' + tar.value('(@weeks)[1]', 'nvarchar (20)') + ' &  Week days - ' + tar.value('(@weekdays)[1]', 'nvarchar (20)')
when tar.value('(@type)[1]', 'nvarchar (20)') = 'MonthlyByDate' 
then 'On days - ' + tar.value('(@dates)[1]', 'nvarchar (20)')
when tar.value('(@type)[1]', 'nvarchar (20)') = 'YearlyByWeek' 
then 'In Months - ' + tar.value('(@months)[1]', 'nvarchar (20)') + ' & On weeks - ' + tar.value('(@weeks)[1]', 'nvarchar (20)') + ' &  Week days - ' + tar.value('(@weekdays)[1]', 'nvarchar (20)')
when tar.value('(@type)[1]', 'nvarchar (20)') = 'YearlyByDate' 
then 'In Months - ' + tar.value('(@months)[1]', 'nvarchar (20)') + ' & On days - ' + tar.value('(@dates)[1]', 'nvarchar (20)')
else 'NA for Daily'
end as 'Days to run'
FROM @PatchTargetXML i  
cross apply [Schedule].nodes('/item/schedule/trigger') as t2(tar)
) a
left join vPolicyAppliesToResource pa on pa.PolicyGuid = a.Guid
group by a.[Days to run], a.Guid, a.[MW Start Time and Duration (Day.Hour:Min:Sec)], a.Name, a.Schedule
order by a.Name, a.Schedule