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.

I am currently trying to write a report to show the up time of a custom property in this case it is SiteName. For each site we have a primary and backup routing system. We do not have the dependency set to show a site up if one or the other router is on.

In this case it is SiteName. For each site we have a primary and backup routing system. We do not have the dependency set to show a site up if one or the other router is on. so I am trying to manually get the data.

So far I have. The problem is in some sites the backup has been down 100% of the time but the site has been running fine with the primary router. So the 0% is making the up time of the site lower than what it should be.

SELECT  TOP 10000

Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

Nodes.IP_Address AS IP_Address,

AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,

Nodes.DeviceRole AS DeviceRole,

Nodes.SiteName AS SiteName,

CONVERT(DateTime,

LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

101) AS SummaryMonth

FROM

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

WHERE

( DateTime BETWEEN 42003 AND 42298 )

AND 

(

  (Nodes.DeviceRole = '3g backup router') OR

  (Nodes.DeviceRole = '3g primary router') OR

  (Nodes.DeviceRole = '4g primary router') OR

  (Nodes.DeviceRole = '4g backup router') OR

  (Nodes.DeviceRole = 'vsat backup router') OR

  (Nodes.DeviceRole = 'vsat primary router') OR

  (Nodes.DeviceRole = 'wan router')

)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

Nodes.NodeID, Nodes.Caption, Nodes.IP_Address, Nodes.DeviceRole, Nodes.SiteName

ORDER BY 2 ASC ,SummaryMonth ASC, SiteName