5 Replies Latest reply on Jun 5, 2018 8:59 AM by mesverrum

    Need to get local Business hours reports

    patriot

      My SolarWinds polling engine is in the central time zone of the U.S., but I am monitoring devices all over the world. My challenge is that I need to run weekly reports covering LOCAL business hours (8AM to 6PM). In other words, a report for devices in Paris should include data from 8A to 6P in Paris, not 8A to 6P in Dallas.

       

      How do I do that in SQL? Can it be done directly with Manage Reports?

       

      Thank you for any guidance as I am not a SQL expert.

        • Re: Need to get local Business hours reports
          Steven Klassen

          Hey patriot - long time no chat!

           

          The way I handle this is by creating a floating point node custom property with the GMT offset for each node (e.g., -7 for PDT, -5 for CDT, and -4 for EDT). Then when I create the report, I limit the rows (in SWQL or SQL) to those where the hour is >= (dateTimeField + customPropertyUTCOffsetField) if the dateTimeField is UTC already (as it is with availability). Boom, you have a timezone aware report! If you want a hand with the particulars, give me a shout and we can hop on a GoToMeeting and chat about it.

            • Re: Need to get local Business hours reports
              uaggarwal

              can you check with the below query, if the query is getting executed directly on the database, i am able to get the output of the query, whereas if running on the application, the data is not getting fetched. :

               

               

              SELECT TOP 10

              CONVERT(DateTime,

              LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

              101) AS SummaryMonth,

              Nodes.Caption AS NodeName,

              Interfaces.InterfaceName AS Interface_Name,

              AVG(Case InBandwidth

                          When 0 Then 0

                          Else (In_Averagebps/InBandwidth) * 100

                          End) AS AVERAGE_of_Recv_Percent_Utilization,

              AVG(Case OutBandwidth

                          When 0 Then 0

                          Else (Out_Averagebps/OutBandwidth) * 100

                          End) AS AVERAGE_of_Xmit_Percent_Utilization,

              Nodes.Region AS Region,

              InterfaceTraffic.DateTime AS DateTime,

              Interfaces.InterfaceID AS InterfaceID

               

              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 43219 AND 43249.9999884259 )

              AND 

              (

                (Nodes.Region LIKE '%Networks%') AND

                (DatePart(Hour,DateTime) >= 9) AND

                (DatePart(Hour,DateTime) <= 18) AND

                (Nodes.Caption = 'reckben-ingnpip01-2953349') AND

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

                (DATEPART(weekday, DateTime) <> 7)

              )

               

               

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

              Nodes.Caption, Interfaces.InterfaceName, Nodes.Region, InterfaceTraffic.DateTime, Interfaces.InterfaceID

               

               

              ORDER BY SummaryMonth ASC

               

               

              reckben-ingnpip01-2953349