Hi everyone. I am trying to create a custom report to give me a list of sites with availability less than 95% in yesterday I have selected node name, IP address and availability under fields. The challenge comes when I try to filter fields specifying records that contain only records where availability is less than 95. The result however comes out showing a lot of sites that are not in this category and every site is listed with availability of 0.00%. SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate, Nodes.Caption AS NodeName, Nodes.IP_Address AS IP_Address, Nodes.Region AS Region, Nodes.Branch_Type AS Branch_Type, AVG(ResponseTime.Availability) AS AVERAGE_of_Availability FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID) WHERE ( DateTime BETWEEN 42643 AND 42644 ) AND ( (Nodes.Branch_Type = 'My City') OR (Nodes.Branch_Type = 'My State') ) GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0), Nodes.Caption, Nodes.IP_Address, Nodes.Region, Nodes.Branch_Type ORDER BY SummaryDate ASC, 6 ASC Where Nodes custom property named “Branch_Type” fill with 'My City' and 'My State' are required nodes. Shell be very thankful, Jawwad~

This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Node Availability Report less than 95% on Yestesday.

jawwad05 over 7 years ago

Hi everyone.

I am trying to create a custom report to give me a list of sites with availability less than 95% in yesterday I have selected node name, IP address and availability under fields. The challenge comes when I try to filter fields specifying records that contain only records where availability is less than 95. The result however comes out showing a lot of sites that are not in this category and every site is listed with availability of 0.00%.

SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,

Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
Nodes.Region AS Region,
Nodes.Branch_Type AS Branch_Type,

AVG(ResponseTime.Availability) AS AVERAGE_of_Availability

FROM

Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE

( DateTime BETWEEN 42643 AND 42644 )

AND

(

(Nodes.Branch_Type = 'My City') OR

(Nodes.Branch_Type = 'My State')

)

GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),

Nodes.Caption, Nodes.IP_Address, Nodes.Region, Nodes.Branch_Type

ORDER BY SummaryDate ASC, 6 ASC

Where Nodes custom property named “Branch_Type” fill with 'My City' and 'My State' are required nodes.

Shell be very thankful,

Jawwad~