4 Replies Latest reply on Jun 6, 2016 11:08 AM by zackm

    Availability reporting during business hours

    barnyboy

      I'm currently using this report...

       

      SELECT CONVERT(DateTime,

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

      101) AS SummaryMonth,

      Nodes.NodeID AS NodeID,

      Nodes.VendorIcon AS Vendor_Icon,

      Nodes.Caption AS NodeName,

      Nodes.IP_Address AS IP_Address,

      AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

       

      FROM

      Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)

       

      WHERE

      ( MONTH(DateTime) = (MONTH(GETDATE()-DAY(GETDATE())))

      AND

      YEAR(DateTime) = YEAR(GETDATE()-DAY(GETDATE())))

       

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

      Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address

       

      HAVING AVG(DailyNodeAvailability.Availability) <= 90

       

      ORDER BY SummaryMonth ASC, 4 ASC

       

      ...and it works great! However I really need this to only report daily business hours (8am to 5pm) on weekdays. I've tried tons of variations but the report keeps coming up blank. An example of a variation I made is this...

       

      DECLARE

      @LastMonth1 datetime = (SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)),

      @Lastmonth2 datetime = (SELECT DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))),

      @GetDateMinusMonth datetime;

      SET @GetDateMinusMonth = DATEADD(day, -30, GETDATE());

       

      SELECT CONVERT(DateTime,

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

      101) AS SummaryMonth,

      Nodes.NodeID AS NodeID,

      Nodes.VendorIcon AS Vendor_Icon,

      Nodes.Caption AS NodeName,

      Nodes.IP_Address AS IP_Address,

      AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

       

      FROM

      Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)

       

      WHERE

       

      ( DateTime BETWEEN @LastMonth1 AND @LastMonth2 )

      AND

      (DATEPART(weekday, DateTime) >= 2)   --Monday

          AND (DATEPART(weekday, DateTime) <= 6)   --Friday

          AND (Convert(Char,DateTime,108) >= '08:00')

          AND (Convert(Char,DateTime,108) <= '18:00')

      AND

      MONTH(DateTime) = (MONTH(@GetDateMinusMonth-DAY(@GetDateMinusMonth)))

      AND

      YEAR(DateTime) = YEAR(@GetDateMinusMonth-DAY(@GetDateMinusMonth))

       

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

      Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address

       

      ORDER BY SummaryMonth ASC, 4 ASC

       

      ...which causes the report to become completely blank. What's wrong with my logic? Any help would be greatly appreciated!

        • Re: Availability reporting during business hours
          zackm

          the problem is that the data in that view does not have hours annotated, so your limitation on the hours of the day won't work:

          2016-05-17 00:00:00.000

          2016-05-10 00:00:00.000

          2016-05-05 00:00:00.000

          2016-06-03 00:00:00.000

          2016-05-13 00:00:00.000

          Try this, pulling from the ResponseTime view (which is actually the basis of the DailyNodeAvailability view anyway)

           

          DECLARE @startOfMonth DATETIME = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
          
          SELECT
          CONVERT(CHAR(11), a.DateTime,106) SummaryMonth
          ,n.NodeID
          ,n.VendorIcon Vendor_Icon
          ,n.Caption NodeName
          ,n.IP_Address
          ,AVG(a.Availability) Average_of_Availability
          FROM Nodes n
          JOIN ResponseTime a ON a.NodeID = n.NodeID
          WHERE
            (DATETIME >= DATEADD(MONTH, -1, @startOfMonth)) AND (DATETIME < @startOfMonth)
          AND 
            (
            (DATEPART(WEEKDAY, a.DateTime) <> 1) AND (DATEPART(WEEKDAY, a.DateTime) <> 7) -- 1 Represents Sunday  and 7 Represents Saturday
            AND
            (CAST(a.DateTime AS TIME) BETWEEN '08:00' AND '17:00') -- 8:00am to 5:00pm ***Use 24h Time Here***
            )
          GROUP BY 
            CONVERT(CHAR(11), a.DateTime,106), n.Caption, n.IP_Address, n.NodeID, n.VendorIcon
          -- This is in the original report, but not the one you were testing. Basically it will limit the results to only nodes with less than or equal to 90% Average_of_Availability
          -- If you want to include it, just uncomment this line (remove the -- at the beginning)
          -- HAVING AVG(a.Availability) <= 90
          ORDER BY SummaryMonth, n.Caption
          

           

           

          Hope that helps!

           

          -ZackM

          Loop1 Systems: SolarWinds Training and Professional Services

            • Re: Availability reporting during business hours
              barnyboy

              Trying the SQL you gave in Report Writer seems to give me the daily availability each day for each node for the month, meaning I have 200+ pages of results. You say that it's the basis of the Daily Node Availability so what I need to do now is take each result and compile it into one total average for the month.

                • Re: Availability reporting during business hours
                  zackm

                  Sure thing, just remove the summary month from the results. This will give you a single average availability metric per node for the last month during business hours.

                   

                  DECLARE @startOfMonth DATETIME = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)  
                    
                  SELECT  
                  n.NodeID  
                  ,n.VendorIcon Vendor_Icon  
                  ,n.Caption NodeName  
                  ,n.IP_Address  
                  ,AVG(a.Availability) Average_of_Availability  
                  FROM Nodes n  
                  JOIN ResponseTime a ON a.NodeID = n.NodeID  
                  WHERE  
                    (DATETIME >= DATEADD(MONTH, -1, @startOfMonth)) AND (DATETIME < @startOfMonth)  
                  AND   
                    (  
                    (DATEPART(WEEKDAY, a.DateTime) <> 1) AND (DATEPART(WEEKDAY, a.DateTime) <> 7) -- 1 Represents Sunday  and 7 Represents Saturday  
                    AND  
                    (CAST(a.DateTime AS TIME) BETWEEN '08:00' AND '17:00') -- 8:00am to 5:00pm ***Use 24h Time Here***  
                    )
                  GROUP BY   
                    n.Caption, n.IP_Address, n.NodeID, n.VendorIcon  
                  -- This is in the original report, but not the one you were testing. Basically it will limit the results to only nodes with less than or equal to 90% Average_of_Availability  
                  -- If you want to include it, just uncomment this line (remove the -- at the beginning)  
                  -- HAVING AVG(a.Availability) <= 90  
                  ORDER BY n.Caption