6 Replies Latest reply on Jan 13, 2011 3:17 PM by njoylif

    Defining Times in Reports other than the Pre-programmed times

    mhh351

      I ahve just written a report for a wide range of interfaces (by Device). This report is filtering on "Last Month".

      What I also need to report is by the week (week 1,2,3,4 of the month.) and also the last 2, 3 or 4 days of the month.

      This would need to run every month without any intervention for a month change.

      The weekly could be a summary of all days within that week. The last "days" report should show each day.

      How can I customize my SQL query to reflect those needs?

       

      Thank you for your help.

      Mark

        • Re: Defining Times in Reports other than the Pre-programmed times
          Andy McBride

          Looks like what you need are some datetime qualifiers for the query. I believe the only way you will be able to do this is by inserting the proper qualifiers into a custom SQL report. That said - I'm no expert on datetime stuff.

          Can one of our Report Gurus or a community member with experience in custome datetime jump in?

          Andy

          • Re: Defining Times in Reports other than the Pre-programmed times
            sotherls

            Mark,

            Can you provide a sample report or query that you are using? I have done something similar for nodes but need to see what you are looking for to see if I can adapt it.

            • Re: Defining Times in Reports other than the Pre-programmed times
              mhh351

              Guys, Thanks for trying to assist.

               

               



              Here is a shortened copy of a report write SQL where I am getting the Last Month statistics.


              That is a good thing. Additionally, I would like to get any given set of days, such as month end days and any one week. I would write the reports to reflect EACH week.


              Further, if I could get this on graphs, it would be even better! Reports will do for now.


              Thanks for the guru help!

              SELECT  TOP 10000 CONVERT(DateTime,
              LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
              101) AS SummaryMonth,
              Nodes.NodeID AS NodeID,
              Interfaces.InterfaceID AS InterfaceID,
              Nodes.Caption AS NodeName,
              Interfaces.InterfaceName AS Interface_Name,
              AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
              MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
              AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
              MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps

              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 40481 AND 40510.9999884259 )
               AND 
              (
                (
                 (Nodes.Caption LIKE '%namehere%') AND
                 (
                  (Interfaces.InterfaceName = 'FastEthernet0/1'))) OR
                (
                 (Nodes.Caption LIKE '%anothername%') AND
                 (
                  (Interfaces.InterfaceName = 'Serial0/0/0:0.777')))
              )


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


              ORDER BY SummaryMonth ASC, 4 ASC, 5 ASC