2 Replies Latest reply on Mar 15, 2011 12:21 PM by jbehrmann

    Need to list down minutes on NPM availability report

    jbehrmann

      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

        • Re: Need to list down minutes on NPM availability report
          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.


            • Re: Need to list down minutes on NPM availability report
              jbehrmann

              I used the normal report writer interface to select "last month" for the window, so I think that is causing this SQL output to show a weird formula.

               

              I like doing it that way because I can just schedule a single report to run on the 1st of every month. It's beginning to sound like that isn't going to be an option if I want to show down minutes, which isn't the end of the world. I will try this out and report back. Thanks for the response.