Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Help tuning out of box Application Availability report to only business hours (M-F, 0700-1900)

Hi THWACK,

I need some assistance tuning the out of box "Application Availability - Last Month" report to only report on data from business hours (0700-1900) Monday thru Friday. This is the SQL code that comprises the report from report writer:

DECLARE @utcOffset int
DECLARE @actualMonths int
SET @utcOffset = datediff(minute, getdate(), getutcdate())
SET @actualMonths = datediff(month, 1, getdate())
SELECT
     max(APM_ApplicationStatus.[TimeStamp]) as [Month],
     Nodes.Caption,
     APM_Application.Name AS ApplicationName,
     sum(APM_ApplicationStatus.PercentAvailability * APM_ApplicationStatus.RecordCount) / sum(APM_ApplicationStatus.RecordCount) AS PercentAvailability
FROM APM_ApplicationStatus with(nolock)
INNER JOIN APM_Application with(nolock) ON APM_Application.ID = APM_ApplicationStatus.ApplicationID
INNER JOIN Nodes with(nolock) ON Nodes.NodeID = APM_Application.NodeID
WHERE APM_ApplicationStatus.[TimeStamp] >= dateadd(minute, @utcoffset, dateadd(month, @actualMonths -1, 0)) AND
          APM_ApplicationStatus.[TimeStamp] < dateadd(minute, @utcoffset, dateadd(month, @actualMonths, 0))
GROUP BY APM_Application.ID, APM_Application.Name, Nodes.Caption
ORDER BY Nodes.Caption,APM_Application.Name

I have tried the following other resources to tune this report but I have not had any success:

https://thwack.solarwinds.com/docs/DOC-188643

https://thwack.solarwinds.com/thread/60701

https://support.solarwinds.com/Success_Center/Server_Application_Monitor_(SAM)/Knowledgebase_Articles/Create_an_Application_Availability_report_with_status

https://thwack.solarwinds.com/community/solarwinds-community/geek-speak/blog/2014/02/25/filtering-for-days-of-the-week-in-orion-report-writer

It seems most of the articles I have found are mostly relevant to NPM reports not SAM focused ones. Any thoughts?

Find more posts tagged with

Accepted answers

All comments

zackm

I'd back out of the SQL script and use the web report writer to build your own, based on this style: Use Report Manager to configure business hours in web-based reports in Orion Platform - SolarWinds Worldwide, LLC. Help …

Ignore the "NPM" part, this works for any data with a timestamp in the Orion platform.