Custom report to display Maintenance Window with Start and End time

Products

• IT Management Suite

Issue/Introduction

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

Environment

ITMS 8.x, 8.7.x

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}