This NPM Document modifies the Out of Box Report Writer Availability Reports for month & for year.
--******************************************************************************************************************
-- Node Reliabilty (customized out of box Availability last YR from report writer)
SELECT TOP 100000
Nodes.NodeID AS NodeID,
Nodes.VendorIcon AS Vendor_Icon,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
-- between today-1-yr-ago and today (march 25th 2017)
--( DateTime BETWEEN (42792-365) AND 42792 )
DateTime > DATEADD(year,-1,GETDATE())
AND
-- restrict day time windows
(
(
-- sat before 11pm utc (- 4 hr from 3 ETZ)
( DATEPART(weekday, DateTime) = 7 AND DatePart(Hour,DateTime) < 23 )
AND
-- sun before 2am utc (- 4 from 6 ETZ)
( DATEPART(weekday, DateTime) = 1 AND DatePart(Hour,DateTime) > 2 )
)
or
(
-- betweenn monday and friday
DATEPART(weekday, DateTime) between 2 and 6
)
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address
ORDER BY 3 ASC ,SummaryMonth ASC
--******************************************************************************************************************
--******************************************************************************************************************
-- Node Reliabilty (customized out of box Availability last month)
SELECT TOP 10000
CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth,
Nodes.NodeID AS NodeID,
Nodes.VendorIcon AS Vendor_Icon,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
-- last month (march 25th 2017)
-- ( DateTime BETWEEN 42765 AND 42792.9999884259 )
datetime >= DATEADD(month,-1,GETDATE())
AND
-- restrict day time windows
(
(
-- sat before 11pm utc (- 4 hr from 3 ETZ)
( DATEPART(weekday, DateTime) = 7 AND DatePart(Hour,DateTime) < 23 )
AND
-- sun before 2am utc (- 4 from 6 ETZ)
( DATEPART(weekday, DateTime) = 1 AND DatePart(Hour,DateTime) > 2 )
)
or
(
-- betweenn monday and friday
DATEPART(weekday, DateTime) between 2 and 6
)
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address
ORDER BY SummaryMonth ASC, 4 ASC
--******************************************************************************************************************