4 Replies Latest reply on Oct 1, 2013 9:43 PM by kandatihari

    NPM Reports

    kandatihari


      Hi there,

       

      I have created  a report through report writer, and the detatils are as below,

       

      Filelds : Node Name , Interface Name & Avg of Circuit utilization (Both recieved and transmitted) Sorted by Descending

      Filters - Default

      Top XX - All

      Time Frame - Last 6 Months

      Summarization - By month

      Field formating - Default

      Report Grouping - SummaryMonth

       

      As I run this run, I get the interface utilzation percentage for all the interfaces grouped by month. But I want to limit this to only  the first 10 records grouped under a month.

       

      Could you any please help with the logic.

       

      Thanks - Hari

        • Re: NPM Reports
          bsciencefiction.tv

          Change Top XX to 10

            • Re: NPM Reports
              kandatihari

              If I set the Top XX to 10, then only top 10 records for the first month are displayed and not  for rest of the months.but I want the top 10 for every month.

               

              Thanks for the reply!

                • Re: NPM Reports
                  JiriPsota

                  Create new SQL report and use this query

                   

                  SELECT

                           SummaryMonth,

                           NodeName,

                           Interface_Name,

                           AVERAGE_of_CircuitUtil_AvgRecvXmit

                  FROM (SELECT

                           ROW_NUMBER() OVER ( PARTITION BY SummaryMonth ORDER BY AVERAGE_of_CircuitUtil_AvgRecvXmit DESC ) AS 'RowNumber',

                           SummaryMonth,

                           NodeName,

                           Interface_Name,

                           AVERAGE_of_CircuitUtil_AvgRecvXmit

                        FROM (

                        SELECT  CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth,

                                Nodes.Caption AS NodeName,

                                Interfaces.InterfaceName AS Interface_Name,

                            AVG(Case

                              When InBandwidth+OutBandwidth=0 Then 0

                              When InBandwidth=0 Then

                              (Out_Averagebps/OutBandwidth) * 100

                              When OutBandwidth=0 Then

                              (In_Averagebps/InBandwidth) * 100

                              Else

                              ( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50

                              End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit

                        FROM

                         (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

                  WHERE

                  ( DateTime BETWEEN DATEADD(mm,-6,GETDATE()) AND GETDATE() )

                   

                   

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

                  Nodes.Caption, Interfaces.InterfaceName

                  ) as data

                        ) dt

                  WHERE RowNumber <= 10

                    • Re: NPM Reports
                      kandatihari

                      Thanks a bunch, That works.

                       

                      Also I am looking to generate a report to list the top 10 interfaces whose utilization is high in last month and trend of the utilization of these 10 interfaces in the previous 6 months. could you please give idea to get this done. THanks