SAM availability Report on business hours/days only

dunklio

Hi All,

I've checked and I can see that this has been discussed by others but I've been unable to get their solutions to work for me, I'm a bit of a newbie to SolarWinds but my organisation wants to use it to report on our application estate for availability.

What's great is there is a report out of the box that does 90% of what we want it to do but it's currently not filtering based on our business hours, 8AM to 6PM Monday to Friday.

The report code is this:

SELECT
ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0) AS Month,
Nodes.Caption,
Nodes.DetailsUrl AS NodesDetailsUrl,
Application.Name AS ApplicationName,
Application.DetailsUrl AS ApplicationDetailsUrl,
SUM(ApplicationStatus.PercentAvailability * ApplicationStatus.RecordCount) / SUM(ApplicationStatus.RecordCount) AS PercentAvailability
FROM Orion.APM.ApplicationStatus ApplicationStatus
INNER JOIN Orion.APM.Application Application ON Application.ApplicationID = ApplicationStatus.ApplicationID
INNER JOIN Orion.Nodes ON Nodes.NodeID = Application.NodeID
WHERE ApplicationStatus.TimeStamp >= ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0)) AND
ApplicationStatus.TimeStamp < ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()), 0))
GROUP BY Application.ApplicationID, Application.Name, Nodes.Caption, Nodes.DetailsUrl, Application.DetailsUrl
ORDER BY Nodes.Caption, Application.Name

How would I filter out the dates and times I don't need? I'm sorry again if this has been answered somewhere else. 

Hoping you awesome peeps can help! Long time lurker here

cahunt

Apologies, I am playing catch up still from being away from things for a week. I was too quick on that last segment, but this one should help

AND (

(Weekday(ApplicationStatus.TimeStamp) >= 1) AND

(Weekday(ApplicationStatus.TimeStamp) <= 5) AND

(hour(ApplicationStatus.TimeStamp) >= '8') AND

(hour(ApplicationStatus.TimeStamp) <= '18')

)

Let me know how that goes please.