Need to list down minutes on NPM availability report

FormerMember

I received a request to add the total number of down minutes to a report I send out monthly for remote links. I currently send the percent up and down for the link, but I'm having some issues with the SQL statements that would take the percentage and come up with a minute figure. I understand  that the number isn't going to be exact because the data isn't coming from the alerting system, but it should be good enough for this report.

There is a thread with APM SQL code for a similar request, but all my attempts at adapting that for NPM objects have failed.

Here is my current SQL:

-----------------------------------

SELECT  TOP 10000 Nodes.Friendly_Site_Name AS Friendly_Site_Name,

Nodes.Caption AS NodeName,

AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,

AVG((100-NullIf(Availability,-2))) AS AVERAGE_of_PercentDown,

Nodes.Carrier AS Carrier 

 

FROM 

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

 

WHERE 

( DateTime BETWEEN 40573 AND 40600.9999884259 )

 AND  

(

  (Nodes.Carrier IS NOT NULL) AND 

  (Nodes.Status <> '9') AND 

  (Nodes.Owner IS NULL)

)

 

GROUP BY Nodes.Friendly_Site_Name, Nodes.Caption, Nodes.Carrier

qle

Here's a couple of suggestions:

1. Instead of using the cryptic numbers to define the window of time for the report, you might want to use dates. Therefore, your WHERE condition could look something like this:
   
   ( DateTime BETWEEN '2011--02-01' AND '2011-03-01')
   
2. With that said, you can the following column to the report
   
   ROUND(DATEDIFF(n,'2011-02-01','2011-03-01') * AVG((100-NullIf(Availability,-2))) /100,1)
   
   which essentially calculates the number of minutes between February 1 and March 1 and multiplies that by your calculation for AVERAGE_of_PercentDown. Finally, cleaned it up a little by rounding it to a single decimal.
   

Please let me know how this works out for you.

1.