cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

SAM availability Report on business hours/days only

Jump to solution

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 🙂

 

0 Kudos
1 Solution

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.

View solution in original post

6 Replies
Level 17

Add the Following;

 

AND (

  (DATEPART(weekday, ApplicationStatus.TimeStamp) >= 2) AND

  (DATEPART(weekday, ApplicationStatus.TimeStamp) <= 6) AND

  (Convert(Char,ApplicationStatus.TimeStamp,108) >= '08:00') AND

  (Convert(Char,ApplicationStatus.TimeStamp,108) <= '18:00')

)

 

 

-CharlesH

Loop1 Systems: SolarWinds Training and Professional Services

0 Kudos

Super sorry, just a bump on this?

Could never get it to work 😞 @cahunt 

0 Kudos

Hi there!

Hope someone can help?

 

0 Kudos

Thanks for responding!

I've tried to add it in after the last AND clause but couldn't get it to work:

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)) AND (
(DATEPART(weekday, ApplicationStatus.TimeStamp) >= 2) AND
(DATEPART(weekday, ApplicationStatus.TimeStamp) <= 6) AND
(Convert(Char,ApplicationStatus.TimeStamp,108) >= '08:00') AND
(Convert(Char,ApplicationStatus.TimeStamp,108) <= '18:00')
)
GROUP BY Application.ApplicationID, Application.Name, Nodes.Caption, Nodes.DetailsUrl, Application.DetailsUrl
ORDER BY Nodes.Caption, Application.Name

 

Have I done it wrong? Missed something?

0 Kudos

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.

View solution in original post

That's done the trick!

Thank you, I see where I went wrong now.

 

0 Kudos