Trouble creating business hours availability report

Have you tried this?

http://www.solarwinds.com/documentation/en/flarehelp/orionplatform/default.htm#orioncoreagcreatingbusinesshoursreport.htm?Highlight=filter business hours

Does this fill the need for you?

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')

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)

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?

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

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:

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

• LinkedIN: Loop1 Systems
• Facebook: Loop1 Systems
• Twitter: @Loop1Systems

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.

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...)

Thanks, that was the final piece to this puzzle!