19 Replies Latest reply on Jan 14, 2009 2:33 PM by BakerD

    Report Writer - Custom SQL

      I'm looking for some help with a custom sql report I'm trying to setup.


      I wanted to run my custom sql report on the 1st of every month and have it compile data from the previous month (or even previous 30 days). Does anyone happen to know how I could call out the previous month in the code?


       


      I know there is an option for this in the basic option but I had to customize a few things for this report.


       


      Thanks in advance! 

        • Re: Report Writer - Custom SQL
          mark wiggans

          Here is one from the Content Sharing Zone that addresses the last 30 days...

           

          Office Average Availability - Last 30 Days

            • Re: Report Writer - Custom SQL
              robguent

              Here is a good source for the different time frame selects:

              http://databaseadvisors.com/pipermail/dba-sqlserver/2003-September/001109.html

              ***snippet of my SQL report, which calculates 2hrs Utilizations for the Last Month

              -- Reporting time frame - Last Month
              -- ReportPeriod specifies for which month  the report should be generated, 1 = Last Month, 2 = Month before Last Month
              DECLARE @ReportPeriod int
                   Set @ReportPeriod=1

              SELECT 
               
              Bandwidth='1.) Utilization/120min >= 80% for Bandwidth <=2Mbit/s', 
              A_BWpre.Region,
              A_BWpre.SPID,
              A_BWpre.NodeName,
              A_BWpre.VRF,
              A_BWpre.Recv_Bandwidth,
              A_BWpre.SummaryDateTime,
              A_BWpre.AVERAGE_of_Recv_Percent_Utilization
               
              FROM
               
               (SELECT
               
                Nodes.Caption AS NodeName,
                Interfaces.VRF AS VRF,
                AVG(Case InBandwidth When 0 Then 0
                 Else (In_Averagebps/InBandwidth) * 100
                 End) AS AVERAGE_of_Recv_Percent_Utilization,
                Nodes.Region AS Region,
                Nodes.SPID AS SPID,
                Interfaces.InBandwidth AS Recv_Bandwidth,
                ---- Average over 2hrs ....*12)/12
                Convert(DateTime,Floor(Cast(DateTime as Float)*12)/12,0) AS SummaryDateTime
               
               FROM
                (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))
                INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)
               
               WHERE
                -- Last Month
                DatePart(m, DateTime) = DatePart(m, DateAdd(m, -@ReportPeriod, getdate())) AND
                DatePart(yyyy, DateTime) = DatePart(yyyy, DateAdd(m,
              -@ReportPeriod, getdate()))

                • Re: Report Writer - Custom SQL
                  robguent

                  Here an example for Last XX days

                   

                  --------- TRANSMIT ---------------

                  --VAR Definition

                  -- Reporting time frame - Last XX days
                  -- ReportPeriod specifies for how many days back the report should be generated
                  DECLARE @ReportPeriod int
                       Set @ReportPeriod=7

                  SELECT 
                   
                  Bandwidth='1.) Utilization/120min >= 80% for Bandwidth <=2Mbit/s', 
                  A_BWpre.Region,
                  A_BWpre.SPID,
                  A_BWpre.NodeName,
                  A_BWpre.VRF,
                  A_BWpre.Xmit_Bandwidth,
                  A_BWpre.SummaryDateTime,
                  A_BWpre.AVERAGE_of_Xmit_Percent_Utilization
                   
                  FROM
                   
                   (SELECT
                   
                    Nodes.Caption AS NodeName,
                    Interfaces.VRF AS VRF,
                    AVG(Case OutBandwidth When 0 Then 0
                     Else (Out_Averagebps/OutBandwidth) * 100
                     End) AS AVERAGE_of_Xmit_Percent_Utilization,
                    Nodes.Region AS Region,
                    Nodes.SPID AS SPID,
                    Interfaces.OutBandwidth AS Xmit_Bandwidth,
                    ---- Average over 2hrs ....*12)/12
                    Convert(DateTime,Floor(Cast(DateTime as Float)*12)/12,0) AS SummaryDateTime

                   
                   FROM
                    (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))
                    INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)
                   
                   WHERE
                    -- last XX days
                    ( DateTime >= DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())- @ReportPeriod,0)  ) AND
                    ( DateTime < DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)   ) AND
                   

                    • Re: Report Writer - Custom SQL
                      savell

                      Here's an SQL where statement I use for some of our monthly reports (returns date range for previous month)..

                      WHERE
                      ( DateTime >= dateadd(mm,datediff(mm,0,getdate())-1,0) AND DateTime
                      <dateadd(mm,datediff(mm,0,getdate()),0) )

                      Dave.

                      Edit: very similar to the method in the above post now I look at it!

                • Re: Report Writer - Custom SQL

                  Thanks Everyone for your help. I was able to get this working...finally.

                   

                  THANKS AGAIN!

                    • Re: Report Writer - Custom SQL
                      Craig Norborg

                      I hate replying to a post that is already solved, but I didn't see the method I use to try and figure out how the Solarwinds guys accomplish things in reports.

                      For example, in this case you want to know how to do a report for the previous month.  There are a ton of Orion reports that are for "Last Month" already and they work quite well, wouldn't it be great to know how they did it?  You can quite easily!!

                      Just open any report in Report Writer that has the feature you want in it, in this case one of the reports that has a timeframe of "Last Month".   Lets say the Availability report "Availability: Last Month" for instance.   Once you have the report opened, go to the "Report" menu and choose "Show SQL".  

                      At this point, another tab will open in the report you were just looking at that will show the exact SQL that is used to produce that report.

                      Makes it easy to learn off the professionals, a great way to get ideas on how to accomplish something!!

                        • Re: Report Writer - Custom SQL
                          savell

                          It's a good tip and something I also use. However it's unfortunately not quite that simple in some instances.

                          Have a look at how the SQL looks when Report Writer generates the report - any datetime ranges are generated at the time the report is run (i.e. an absolute date range is generated rather than a relative one).

                          So you end up with something like the following for yesterday's data....

                          WHERE
                          ( DateTime BETWEEN 39819 AND 39820 )

                          This is fine if the SQL is run today - but if the same statement is run tomorrow, you may be disappointed with the result...

                          Dave.

                            • Re: Report Writer - Custom SQL
                              rgward

                              Try these.  I got them from Support a while back.

                              Replace the generated absolute statement like...

                              WHERE
                              ( DateTime BETWEEN 39819 AND 39820 ) 

                              with...

                              For 'Last 24 Hours':

                              WHERE ColumnName >= GetDate () -1

                              For 'Last 7 Days':

                              WHERE DateTime >= GetDate () -7

                              For 'Last 30 Days':

                              WHERE DateTime >= GetDate () -30

                              For 'Last Month':

                              WHERE MONTH(ColumnName) = (MONTH(GETDATE()-DAY(GETDATE())))
                              AND YEAR(ColumnName) = YEAR(GETDATE()-DAY(GETDATE()))

                              For 'This Month':

                              WHERE MONTH(ColumnName) = (MONTH(GETDATE())) AND YEAR(ColumnName) = YEAR(GETDATE())


                              Replace 'ColumnName' with the name of the column that contains the DateTime information.  In most cases, it is DateTime, but some may vary.  You would want to check the table columns to get the appropriate column name.

                                • Re: Report Writer - Custom SQL

                                  Thanks for the suggestions but looks like I'm on another stumbling block...

                                  I am trying to pull data from the previous month but only during the hours of 8am to 5pm, Monday thru Friday.

                                  Here is what I have but no matter what I put in the DatePart section I get the same results.

                                  ________________________________________________________________

                                  WHERE
                                  DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
                                   AND 
                                  (
                                    (Interfaces.Caption LIKE '%Qwest%') OR
                                    (Interfaces.Caption LIKE '%COX-20MB%') OR
                                    (Interfaces.Caption LIKE '%One-Communications%') OR
                                    (Interfaces.Caption LIKE '%Verizon-ISP%')

                                   AND
                                  (
                                     (DatePart(Hour,DateTime) >= 8) AND
                                     (DatePart(Hour,DateTime) <= 5) AND
                                     (DATEPART(weekday, DateTime) <> 1) AND
                                     (DATEPART(weekday, DateTime) = 7))
                                  )

                                  _________________________________________________________________

                                  Anyone have any suggestions?

                                  Thanks.

                                    • Re: Report Writer - Custom SQL

                                      I think the last line of your query should say <> 7 instead of = 7.

                                        • Re: Report Writer - Custom SQL

                                          Yes, you are right. I made the change and it didn't make a difference.... It seems like the entire DATEPART section is being ignored. If I remove it the results are the same.

                                            • Re: Report Writer - Custom SQL

                                              Here is how I have specified this sort of thing in my queries.

                                               

                                                        (DATEPART(weekday, DateTime) <> 7)AND

                                                        (DATEPART(weekday, DateTime) <> 1) AND

                                                        (CONVERT(Char, DateTime, 108) > '0800') AND

                                                        (CONVERT(Char, DateTime, 108) < '1700')

                                                • Re: Report Writer - Custom SQL

                                                  Hi Sedmo,

                                                  Thanks for the suggestion, I seem to be getting different results with this but I don't believe it's correct.

                                                  What does the "108" relate to in the Convert lines?

                                                  Thanks

                                                    • Re: Report Writer - Custom SQL

                                                      108 is a type code that determines the format of the time.  In this case it returns HH:MM:SS.  Would it be possible for you to post your complete query?

                                                        • Re: Report Writer - Custom SQL

                                                          Sure, no problem. Here is what I have.

                                                           

                                                          SELECT  TOP 10000 Nodes.Caption AS NodeName,
                                                          Interfaces.Caption AS Interface_Caption,
                                                          AVG(Case InBandwidth
                                                              When 0 Then 0
                                                              Else (In_Averagebps/InBandwidth) * 100
                                                          End) AS AVERAGE_of_Recv_Percent_Utilization,
                                                          MAX(Case InBandwidth
                                                              When 0 Then 0
                                                              Else (In_Averagebps/InBandwidth) * 100
                                                          End) AS MAX_of_Recv_Percent_Utilization,
                                                          AVG(Case OutBandwidth
                                                              When 0 Then 0
                                                              Else (Out_Averagebps/OutBandwidth) * 100
                                                          End) AS AVERAGE_of_Xmit_Percent_Utilization,
                                                          MAX(Case OutBandwidth
                                                              When 0 Then 0
                                                              Else (Out_Averagebps/OutBandwidth) * 100
                                                          End) AS MAX_of_Xmit_Percent_Utilization

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


                                                          WHERE
                                                          DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
                                                           AND 
                                                          (
                                                          (Nodes.Caption LIKE '%RI-NCC-2811%') AND
                                                            (Interfaces.InterfaceName = 'Serial0/0/1') OR 
                                                          (Interfaces.Caption LIKE '%Qwest%') OR
                                                            (Interfaces.Caption LIKE '%COX-20MB%') OR
                                                            (Interfaces.Caption LIKE '%One-Communications%') OR
                                                            (Interfaces.Caption LIKE '%Verizon-ISP%') OR
                                                          (Nodes.Caption LIKE '%Cov2-3745-WAN%') AND
                                                            (Interfaces.InterfaceName = 'Multilink1') OR
                                                          (Nodes.Caption LIKE '%East-Greenwich-Warehouse%') AND
                                                            (Interfaces.InterfaceName = 'Serial1/0') OR
                                                          (Nodes.Caption LIKE '%PVD-7200%') AND
                                                            (Interfaces.InterfaceName = 'GigabitEthernet0/1') OR
                                                          (Nodes.Caption LIKE '%PVD-7200%') AND
                                                            (Interfaces.InterfaceName = 'GigabitEthernet0/2')

                                                           AND

                                                          (DATEPART(weekday, DateTime) <> 7)AND
                                                          (DATEPART(weekday, DateTime) <> 1) AND
                                                          (CONVERT(Char, DateTime, 108) > '0800') AND
                                                          (CONVERT(Char, DateTime, 108) < '1700')

                                                          )

                                                          GROUP BY Nodes.Caption, Interfaces.Caption

                                                           

                                                          Thanks again!

                                                            • Re: Report Writer - Custom SQL

                                                              I think you have some problems with the groupings in your where statement.  Try this.

                                                               

                                                              SELECT  TOP 10000 Nodes.Caption AS NodeName,
                                                              Interfaces.Caption AS Interface_Caption,
                                                              AVG(Case InBandwidth
                                                                  When 0 Then 0
                                                                  Else (In_Averagebps/InBandwidth) * 100
                                                              End) AS AVERAGE_of_Recv_Percent_Utilization,
                                                              MAX(Case InBandwidth
                                                                  When 0 Then 0
                                                                  Else (In_Averagebps/InBandwidth) * 100
                                                              End) AS MAX_of_Recv_Percent_Utilization,
                                                              AVG(Case OutBandwidth
                                                                  When 0 Then 0
                                                                  Else (Out_Averagebps/OutBandwidth) * 100
                                                              End) AS AVERAGE_of_Xmit_Percent_Utilization,
                                                              MAX(Case OutBandwidth
                                                                  When 0 Then 0
                                                                  Else (Out_Averagebps/OutBandwidth) * 100
                                                              End) AS MAX_of_Xmit_Percent_Utilization

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


                                                              WHERE
                                                              DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
                                                               AND
                                                              (
                                                               (Nodes.Caption like '%RI-NCC-2811%' and
                                                                (Interfaces.InterfaceName = 'Serial0/0/1' or
                                                                Interfaces.Caption like '%Qwest%' or
                                                                Interfaces.Caption like '%COX-20MB%' or
                                                                Interfaces.Caption like '%One-Communications%' or
                                                                Interfaces.Caption like '%Verizon-ISP%')
                                                               )
                                                              or
                                                               (Nodes.Caption like '%Cov2-3745-WAN%' and
                                                                (Interfaces.InterfaceName = 'Multilink1')
                                                               )
                                                              or
                                                               (Nodes.Caption LIKE '%East-Greenwich-Warehouse%' and
                                                                (Interfaces.InterfaceName = 'Serial1/0')
                                                               )
                                                              or
                                                               (Nodes.Caption LIKE '%PVD-7200%' and
                                                                (Interfaces.InterfaceName = 'GigabitEthernet0/1' or
                                                                 Interfaces.InterfaceName = 'GigabitEthernet0/2')
                                                               )
                                                              )
                                                              AND
                                                              (
                                                              (DATEPART(weekday, DateTime) <> 7)AND
                                                              (DATEPART(weekday, DateTime) <> 1) AND
                                                              (CONVERT(Char, DateTime, 108) > '0700') AND
                                                              (CONVERT(Char, DateTime, 108) < '1700')
                                                              )

                                                              GROUP BY Nodes.Caption, Interfaces.Caption

                                                                • Re: Report Writer - Custom SQL

                                                                  That seems to give me the correct timeframe but now it's ignoring the first section of nodes and interfaces (from RI-NCC-2811 to Verizon-ISP), only giving information fro the last three....

                                                                    • Re: Report Writer - Custom SQL

                                                                      I assumed that those interfaces where on the RI-NCC-2811 device.  If that is not the case then the grouping will need to be modified.  The way it is currently written, the interface has to match one of the five statements below.

                                                                      caption like RI-NCC-2811 and interfacename = Serial0/0/1

                                                                      caption like RI-NCC-2811 and interfaces.caption like '%Qwest%'

                                                                      caption like RI-NCC-2811 and interfaces.caption like '%COX-20MB%'

                                                                      caption like RI-NCC-2811 and interfaces.caption like '%One-Communications%'

                                                                      caption like RI-NCC-2811 and interfaces.caption like '%Verizon-ISP%'

                                            • Re: Report Writer - Custom SQL
                                              BakerD

                                              In one of my reports on the Time Frame tab I set it to last 30 days.  Then on the Filter Results tab I have two lines that narrow it down to working hours for me of 8am - 6pm.

                                               

                                              Records where Time of Day (24 hour format) is less than 08:00

                                              Records where Time of Day (24 hour format) is greater than or equal to 18:00

                                               

                                              I use this ina filter to give me WAN utilization for the last 30 days during working hours.