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

Availability reporting during business hours

barnyboy

I'm currently using this report...

SELECT CONVERT(DateTime,

LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

101) AS SummaryMonth,

Nodes.NodeID AS NodeID,

Nodes.VendorIcon AS Vendor_Icon,

Nodes.Caption AS NodeName,

Nodes.IP_Address AS IP_Address,

AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

FROM

Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)

WHERE

( MONTH(DateTime) = (MONTH(GETDATE()-DAY(GETDATE())))

AND

YEAR(DateTime) = YEAR(GETDATE()-DAY(GETDATE())))

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address

HAVING AVG(DailyNodeAvailability.Availability) <= 90

ORDER BY SummaryMonth ASC, 4 ASC

...and it works great! However I really need this to only report daily business hours (8am to 5pm) on weekdays. I've tried tons of variations but the report keeps coming up blank. An example of a variation I made is this...

DECLARE

@LastMonth1 datetime = (SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)),

@Lastmonth2 datetime = (SELECT DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))),

@GetDateMinusMonth datetime;

SET @GetDateMinusMonth = DATEADD(day, -30, GETDATE());

SELECT CONVERT(DateTime,

LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

101) AS SummaryMonth,

Nodes.NodeID AS NodeID,

Nodes.VendorIcon AS Vendor_Icon,

Nodes.Caption AS NodeName,

Nodes.IP_Address AS IP_Address,

AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

FROM

Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)

WHERE

( DateTime BETWEEN @LastMonth1 AND @LastMonth2 )

AND

(DATEPART(weekday, DateTime) >= 2) --Monday

AND (DATEPART(weekday, DateTime) <= 6) --Friday

AND (Convert(Char,DateTime,108) >= '08:00')

AND (Convert(Char,DateTime,108) <= '18:00')

AND

MONTH(DateTime) = (MONTH(@GetDateMinusMonth-DAY(@GetDateMinusMonth)))

AND

YEAR(DateTime) = YEAR(@GetDateMinusMonth-DAY(@GetDateMinusMonth))

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address

ORDER BY SummaryMonth ASC, 4 ASC

...which causes the report to become completely blank. What's wrong with my logic? Any help would be greatly appreciated!

Find more posts tagged with

Accepted answers

zackm

Sure thing, just remove the summary month from the results. This will give you a single average availability metric per node for the last month during business hours.

DECLARE @startOfMonth DATETIME = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)    SELECT  n.NodeID  ,n.VendorIcon Vendor_Icon  ,n.Caption NodeName  ,n.IP_Address  ,AVG(a.Availability) Average_of_Availability  FROM Nodes n  JOIN ResponseTime a ON a.NodeID = n.NodeID  WHERE    (DATETIME >= DATEADD(MONTH, -1, @startOfMonth)) AND (DATETIME < @startOfMonth)  AND     (    (DATEPART(WEEKDAY, a.DateTime) <> 1) AND (DATEPART(WEEKDAY, a.DateTime) <> 7) -- 1 Represents Sunday  and 7 Represents Saturday    AND    (CAST(a.DateTime AS TIME) BETWEEN '08:00' AND '17:00') -- 8:00am to 5:00pm ***Use 24h Time Here***    )GROUP BY     n.Caption, n.IP_Address, n.NodeID, n.VendorIcon  -- This is in the original report, but not the one you were testing. Basically it will limit the results to only nodes with less than or equal to 90% Average_of_Availability  -- If you want to include it, just uncomment this line (remove the -- at the beginning)  -- HAVING AVG(a.Availability) <= 90  ORDER BY n.Caption

All comments

zackm

the problem is that the data in that view does not have hours annotated, so your limitation on the hours of the day won't work:

2016-05-17 00:00:00.000
2016-05-10 00:00:00.000
2016-05-05 00:00:00.000
2016-06-03 00:00:00.000
2016-05-13 00:00:00.000

Try this, pulling from the ResponseTime view (which is actually the basis of the DailyNodeAvailability view anyway)

DECLARE @startOfMonth DATETIME = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)SELECTCONVERT(CHAR(11), a.DateTime,106) SummaryMonth,n.NodeID,n.VendorIcon Vendor_Icon,n.Caption NodeName,n.IP_Address,AVG(a.Availability) Average_of_AvailabilityFROM Nodes nJOIN ResponseTime a ON a.NodeID = n.NodeIDWHERE  (DATETIME >= DATEADD(MONTH, -1, @startOfMonth)) AND (DATETIME < @startOfMonth)AND   (  (DATEPART(WEEKDAY, a.DateTime) <> 1) AND (DATEPART(WEEKDAY, a.DateTime) <> 7) -- 1 Represents Sunday  and 7 Represents Saturday  AND  (CAST(a.DateTime AS TIME) BETWEEN '08:00' AND '17:00') -- 8:00am to 5:00pm ***Use 24h Time Here***  )GROUP BY   CONVERT(CHAR(11), a.DateTime,106), n.Caption, n.IP_Address, n.NodeID, n.VendorIcon-- This is in the original report, but not the one you were testing. Basically it will limit the results to only nodes with less than or equal to 90% Average_of_Availability-- If you want to include it, just uncomment this line (remove the -- at the beginning)-- HAVING AVG(a.Availability) <= 90ORDER BY SummaryMonth, n.Caption

Hope that helps!

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

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

s.elmrabet

Hi,

Have a look here Trouble creating business hours availability report

Regards.

Salah

barnyboy

Trying the SQL you gave in Report Writer seems to give me the daily availability each day for each node for the month, meaning I have 200+ pages of results. You say that it's the basis of the Daily Node Availability so what I need to do now is take each result and compile it into one total average for the month.

zackm

Sure thing, just remove the summary month from the results. This will give you a single average availability metric per node for the last month during business hours.

DECLARE @startOfMonth DATETIME = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)    SELECT  n.NodeID  ,n.VendorIcon Vendor_Icon  ,n.Caption NodeName  ,n.IP_Address  ,AVG(a.Availability) Average_of_Availability  FROM Nodes n  JOIN ResponseTime a ON a.NodeID = n.NodeID  WHERE    (DATETIME >= DATEADD(MONTH, -1, @startOfMonth)) AND (DATETIME < @startOfMonth)  AND     (    (DATEPART(WEEKDAY, a.DateTime) <> 1) AND (DATEPART(WEEKDAY, a.DateTime) <> 7) -- 1 Represents Sunday  and 7 Represents Saturday    AND    (CAST(a.DateTime AS TIME) BETWEEN '08:00' AND '17:00') -- 8:00am to 5:00pm ***Use 24h Time Here***    )GROUP BY     n.Caption, n.IP_Address, n.NodeID, n.VendorIcon  -- This is in the original report, but not the one you were testing. Basically it will limit the results to only nodes with less than or equal to 90% Average_of_Availability  -- If you want to include it, just uncomment this line (remove the -- at the beginning)  -- HAVING AVG(a.Availability) <= 90  ORDER BY n.Caption