This is Node Availability Report for Last Month during Business Days/Hours (Mon-Fri, 0900-1800hrs) with SLA Status PASS/FAIL against SLA 99.999%.
Before running the report, please create Custom Property "SLA_Node" as Floating Number and assign the targeted SLA value 99.999.
mwidjaja wrote:
The report is plug and play but before getting the report, you will need to have the SLA custom properties as mentioned in the description.
Hello,
Im trying to use this report but for some reason Im getting the SLA Status wrong:
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0));
Select SummaryMonth, NodeID, Vendor_Icon, NodeName, IP_Address, AVERAGE_of_Availability, SLA_Node, Organization, Type,
SLA From ( 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,
Nodes.SLA_Node AS SLA_Node,
Nodes.Organization AS Organization,
Nodes.Type AS Type,
CASE
WHEN AVG(ResponseTime.Availability) > Nodes.SLA_Node THEN 'PASS'
ELSE 'FAIL'
END AS SLA
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
( DateTime BETWEEN @StartDate AND @EndDate ) and Nodes.Organization = 'GHP'
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address, Nodes.SLA_Node, Nodes.Organization, Nodes.Type
) As r ORDER BY SummaryMonth ASC, 4 ASC
