8 Replies Latest reply on Jul 20, 2018 6:08 AM by uaggarwal

    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

                • Re: Need to get local Business hours reports
                  mesverrum

                  When you say it is not getting fetched, is there an error message, does the report time out?

                    • Re: Need to get local Business hours reports
                      uaggarwal

                      The application goes in hung state.

                        • Re: Need to get local Business hours reports
                          mesverrum

                          Try something like setting the select to select top 10 and see if that executes, if so thats uaually a good indicator the report is taking too long to generate (default time out is 5 minutes, but it will show an error on the screen if it exceeds that normally)

                            • Re: Need to get local Business hours reports
                              uaggarwal

                              I am trying to fetch the report for only 1 node which contains only 4 interface.

                               

                              While trying to run the query, there is no results in the query that is displayed.

                               

                              i have tried waiting for 10 min for the query to execute, but no response over the application.

                                • Re: Need to get local Business hours reports
                                  mesverrum

                                  Sounds like you might want to rewrite the query, something in it is screwing the app up, add each piece in 1 by one until you understand which part is breaking it.  At a glance the order by is possibly causing the query to review the entire traffic view which could be months or years of results depending on your settings.

                                    • Re: Need to get local Business hours reports
                                      uaggarwal

                                      Hi Mesverrum,

                                       

                                      Had checked with the database team with the query, its the query with multiple functions which is taking the time to get the required output, for which either of the 2 options are available:

                                      1. Optimize the SQL query (that needs to be done by Solarwinds)

                                      2. Run the query directly on the database to get the required output.

                                       

                                      Have tried the below query on the database, and currently getting the required information from it:

                                      We had to just create a new custom property in SOlarwinds providing the timezone w.r.t the Solarwinds server time and accordingly run the below query:

                                       

                                      SELECT CONVERT(DateTime,

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

                                      101) AS SummaryMonth,

                                      Nodes.Caption AS NodeName,

                                      Interfaces.InterfaceID AS InterfaceID,

                                      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,

                                      MAX(Interfaces.OutPercentUtil) AS MAX_of_Xmit_Percent_Utilization,

                                      MAX(Interfaces.InPercentUtil) AS MAX_of_Recv_Percent_Utilization,

                                      Nodes.Application AS Application,

                                      Nodes.Approved_Site_Name AS Approved_Site_Name,

                                      Nodes.Country AS Country,

                                      Nodes.Region AS Region

                                       

                                       

                                      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 43250 AND 43279.9999884259 )

                                      AND 

                                      (

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

                                        (DatePart(Hour,DateTime) >= 9 + (CONVERT(FLOAT,  Nodes.Timezone))) AND

                                        (DatePart(Hour,DateTime) <= 18 + (CONVERT(FLOAT,  Nodes.Timezone))) 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.InterfaceID, Interfaces.InterfaceName, Nodes.Application, Nodes.Approved_Site_Name, Nodes.Country, Nodes.Region

                                       

                                       

                                       

                                       

                                      ORDER BY SummaryMonth ASC