6 Replies Latest reply on May 19, 2016 11:06 AM by kevcor

    Trouble creating business hours availability report

    kevcor

      I'm following the instructions here Business hours reporting in the Web Based Report Writer but am having some trouble.  I hope the community can help me out.

       

      I need to create a group availability report that only considers business hour (M-F, 6-6).  The guide I've linked above has me working in Report Writer (which I've never used before) and SQL (which I'm not familiar with).  I'm happy to learn these things but I need some help.  I started with the Groups: Historica - Daily Group Availability report and added my fields and filters.  The SQL is below.

       

      SELECT  TOP 10000 Containers_ContainerAvailability.GroupPercentAvailability AS Group_Availability,
      Containers_AlertsAndReportsData.GroupName AS Group_Name,
      Containers_ContainerAvailability.DateTime AS DateTime 
      
      
      FROM 
      Containers_AlertsAndReportsData INNER JOIN Containers_ContainerAvailability ON (Containers_AlertsAndReportsData.GroupID = Containers_ContainerAvailability.GroupID)
      
      
      
      
      WHERE 
      ( DateTime BETWEEN 42474 AND 42505 )
       AND  
      (
        (DATEPART(weekday, DateTime) >= 2) AND 
        (DATEPART(weekday, DateTime) <= 6) AND 
        (DatePart(Hour,DateTime) >= 6) AND 
        (DatePart(Hour,DateTime) <= 18) AND 
        (Containers_AlertsAndReportsData.GroupName = 'ImageNow system')
      )
      
      
      
      
      
      
      ORDER BY 3 ASC
      

       

      This seems to work but I have two problems with it:

       

      1 - I want a daily average but it returns hourly.

      2 - It returns each hour twice.

       

      Capture.JPG

       

      How can I adjust this so it only has one entry for each day?  I want this to be a monthly report that is broken down by day.

       

      Thanks!

          • Re: Trouble creating business hours availability report
            kevcor

            That helps.  Thanks!  But I'm still not quite getting what I need.

             

            Following the document you referenced, I was able to filter by business days but business hours is not working right.  When I select Time of Day is greater than or equal to 6:00 and less than or equal to 17:59, I don't get any data.

             

              (Convert(Char,DateTime,108) >= '6:00') AND
              (Convert(Char,DateTime,108) <= '17:59')
            

             

            TimeofDay.JPG

             

            When I change the time filter to Hour is greater than or equal to 6 and less than or equal to 17, I get data returned starting at 4/18 through the end of the month but I want 4/1 through 4/29 (last month business days).

             

              (DatePart(Hour,DateTime) >= 6) AND 
              (DatePart(Hour,DateTime) <= 17)
            

             

            Hour.JPG

             

            When I remove the time filters, I get all the business days for last month.  Playing with the filters further, I discovered that if I leave Hour is greater than or equal to 6 and remove Hour is less than or equal to 17, I get all of the business days for last month but this is not good because I'm still reporting on afterhours (6pm-12am) periods.  Why would the filter for the end of business hours cause this?

             

            AllDays.JPG

             

            I hope someone can shed some light on this for me.

          • Re: Trouble creating business hours availability report
            zackm

            Try this in the web report writer using a SQL datasource:

             

            --Here we are declaring our variables for our reporting time period
            DECLARE @startDate DATETIME = (GETDATE()-7) -- 7 days ago
            DECLARE @endDate DATETIME = GETDATE() -- Now
            
            
            SELECT
            car.GroupName
            car.GroupName ' Group Name'
            ,cav.DateTime 'Date'
            ,avg(cav.Availability) 'Average Availability'
            FROM Containers_AlertsAndReportsData car 
            JOIN 
            (
              SELECT
              AVG(GroupPercentAvailability) 'Availability'
              ,CAST(DateTime AS Date) 'DateTime'
              ,GroupID
              FROM Containers_ContainerAvailability
              --This is where we call the variables we declared at the top
              WHERE DateTime BETWEEN @startDate AND @endDate
              --This is where we ignore weekends and limit the results between 6am - 6pm
              AND  
              (  
               (DATEPART(weekday, DateTime) NOT IN (1,7)) AND     
               (DATEPART(Hour,DateTime) >= 6) AND
               (DATEPART(Hour,DateTime) <= 18)   
              )
              GROUP BY CAST(DateTime AS Date), GroupID
            ) cav ON car.GroupID = cav.GroupID
            --This is where we can limit the results to only a specific group (optional)
            WHERE car.GroupName = 'Active Directory'
            GROUP BY car.GroupName, cav.DateTime
            ORDER BY cav.DateTime, car.GroupName
            

             

            Sample Results:

            Group NameDateAverage Availability
            Active Directory2016-05-12100
            Active Directory2016-05-13100
            Active Directory2016-05-1697
            Active Directory2016-05-17100
            Active Directory2016-05-18100

             

             

            -ZackM

            Loop1 Systems: SolarWinds Training and Professional Services

              • Re: Trouble creating business hours availability report
                kevcor

                Thanks for that.  Your day and time filters look similar to mine so that makes me feel better that I'm doing something right.

                 

                I think I've discovered the root of my problem though.  I believe after one month, hourly availability data is no longer available which is why I've been getting results that start one month prior to the day I run the query.  Today I ran the query and got hourly results starting 4/19 whereas yesterday I got results starting 4/18.  I guess running this query for a report on the first of every month should get me around that problem.

                  • Re: Trouble creating business hours availability report
                    zackm

                    oh, yeah if you're looking for the last month you're going to lose granularity after 30 days by default. My initial report was looking at the last 7 days

                     

                    if you want to do something like that, try this:

                     

                    First, extend your hourly retention interval in your settings to 31 days

                     

                    then, try this query:

                     

                    --Here we are declaring a variable to use for our reporting time period
                    DECLARE @startOfCurrentMonth DATETIME = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
                      
                    SELECT  
                    car.GroupName  
                    car.GroupName ' Group Name'  
                    ,cav.DateTime 'Date'  
                    ,avg(cav.Availability) 'Average Availability'  
                    FROM Containers_AlertsAndReportsData car   
                    JOIN   
                    (  
                      SELECT  
                      AVG(GroupPercentAvailability) 'Availability'  
                      ,CAST(DateTime AS Date) 'DateTime'  
                      ,GroupID  
                      FROM Containers_ContainerAvailability  
                      --This is where we call the variables we declared at the top
                      --This will result in results gathered from the last month (From the 1st of the month to the last day of the month)
                      WHERE (DateTime >= DATEADD(MONTH, -1, @startOfCurrentMonth)) and (DATETIME < @startOfCurrentMonth)
                      --This is where we ignore weekends and limit the results between 6am - 6pm  
                      AND    
                      (    
                       (DATEPART(weekday, DateTime) NOT IN (1,7)) AND       
                       (DATEPART(Hour,DateTime) >= 6) AND  
                       (DATEPART(Hour,DateTime) <= 18)     
                      )  
                      GROUP BY CAST(DateTime AS Date), GroupID  
                    ) cav ON car.GroupID = cav.GroupID  
                    --This is where we can limit the results to only a specific group (optional)  
                    WHERE car.GroupName = 'Active Directory'  
                    GROUP BY car.GroupName, cav.DateTime  
                    ORDER BY cav.DateTime, car.GroupName  
                    

                     

                    Then run that on the 1st of the month. With the new retention period you will always have accurate results for the last 1 month (which is a lot better than the last 30 days...)

                    1 of 1 people found this helpful