13 Replies Latest reply on Jul 10, 2009 9:53 AM by r0berth1

    Availability report problems

    cstaniforth

      When I create a report within Orion Report Writer 8.5 to show the average availability for nodes, it brings back the expected result as a percentage for each node. i.e;

      node 1 = 100%

      node 2 = 100%

      node 3 = 90%

      My problem is that as soon as I try and filter it to show only the nodes that have less than 100% average availability, using the following filter "Records where Availability is less than 100" its returns the following;

      node 3 = 0.00%

      It correctly shows that node 3 is the only node that has less than 100% average availability, but it no longer displays its average percentage, which I need it to show.

      Has anyone else had this problem or know of a work around to get it to work?

        • Re: Availability report problems
          r0berth1

          I had a silimar problem a while back. Can you post the SQL code from the report so i can work on a fix for you?

            • Re: Availability report problems
              cstaniforth

              Glad its not just me being dumb as a box of rocks, here is the SQL code for the report; 

              SELECT  TOP 10000 CONVERT(DateTime,
              LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
              101) AS SummaryMonth,
              Nodes.Caption AS NodeName,
              AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
              AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
              MIN(ResponseTime.MinResponseTime) AS MIN_of_Minimum_Response_Time,
              MAX(ResponseTime.MaxResponseTime) AS MAX_of_Peak_Response_Time

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


              WHERE
              ( DateTime BETWEEN 39963 AND 39992.9999884259 )
               AND 
              (
                (Nodes.DeviceUsage = 'CORE') AND
                (ResponseTime.Availability < 100)
              )


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


              ORDER BY SummaryMonth ASC, 3 ASC, 4 DESC

                • Re: Availability report problems
                  casey.schmit

                  I think I know what's going on here.  You're doing an average of the availablility's but you're filtering out any of the availability values of 100 from that average.  So you only end up averaging the values that were less than 100%. 

                  My SQL is a little rusty(and I don't have a working DB at the moment), but after discussion here with a couple folks, we think you might be able to change "ResponseTime.Availability<100" to "AVERAGE_of_Availability<100" and it might work.  If I get a working DB going before you get a chance to try it, I'll let you know.  If you get a chance to try it first, let me know how it goes.

                    • Re: Availability report problems
                      r0berth1

                      I have a working DB, i will test it and post the results.

                      • Re: Availability report problems
                        r0berth1

                        That didn't work. It is looking for a Column name and if you enter (AVG(ResponseTime.Availability) < 100) under the WHERE statement it throws an error.

                          • Re: Availability report problems
                            casey.schmit

                            Yeah, that's what I get for not trying it out first.  I warned you about my SQL. :)

                             

                            Here's a working query:

                             


                            Select * from (

                            SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
                            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

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


                            WHERE
                            ( DateTime BETWEEN 40000.5416666667 AND 40000.6666666667 )


                            GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
                            Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address


                            ORDER BY SummaryDate ASC, 4 ASC) AS X where AVERAGE_of_Availability < 100;

                             

                            That will return all the rows that have less than 100% availability, which I'm pretty sure is what you're looking for.  You'll also need to tweak the times(which are different in my query than in yours).

                              • Re: Availability report problems
                                r0berth1

                                So your querry should look like this:

                                SELECT  TOP 10000 CONVERT(DateTime,
                                LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
                                101) AS SummaryMonth,
                                Nodes.Caption AS NodeName,
                                AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
                                AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
                                MIN(ResponseTime.MinResponseTime) AS MIN_of_Minimum_Response_Time,
                                MAX(ResponseTime.MaxResponseTime) AS MAX_of_Peak_Response_Time

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


                                WHERE
                                ( DateTime BETWEEN 39963 AND 39992.9999884259 )
                                 AND 
                                (
                                  (Nodes.DeviceUsage = 'CORE')
                                )


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


                                ORDER BY SummaryMonth ASC, 4 ASC) AS X where AVERAGE_of_Availability < 100;

                      • Re: Availability report problems
                        r0berth1

                        OK, let me correct my earlier statement. I am still having the problem. When i add the filter "availability not equal to 100" a branch that had 97% availability before the filter, now shows 14.2% availability with the filter applied. I am using Orion 9.5 SP2 with all hotfixes applied.