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 report for outages displayed as minutes and not a percentage

Hi All

Tried searching for the solution within forums but no joy

I need to create/manipulate an availability report to provide the total downtime of devices reported in minutes per month and not as a total percentage.

Can i tweak an exisiting report or create a new one from scratch?

Help much appreciated - thanks.

Find more posts tagged with

Accepted answers

All comments

FormerMember

We use the following...

SELECT
    StartTime.EventTime,
    Nodes.Caption,
    StartTime.Message,
    DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetworkNode = StartTime.NetworkNode
        ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
month(eventtime) = month(getdate())
ORDER BY Nodes.Caption ASC

nicksmith

Thanks sotherls

Yes, i require something similar as in total outage duration of a single device in minutes per month.

I see your report displays a single event and the time recordrd against this single event. Do you have a sum total for each device per month?

FormerMember

Try this....

Select NodeCaption, IsNull(Sum(DownTime),0) As DownTime From (
SELECT
      Nodes.Caption As NodeCaption,
DATEDIFF(Mi, StartTime.EventTime,
      (SELECT TOP 1
          EventTime
          FROM Events AS Endtime
          WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
              AND EndTime.NetObjectType = 'N'
              AND EndTime.NetworkNode = StartTime.NetworkNode
          ORDER BY EndTime.EventTime)) AS Downtime
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')
AND eventtime between dateadd(month, -1, getdate()) and getdate()
) dt
Group By NodeCaption
Order By NodeCaption
Select NodeCaption, IsNull(Sum(DownTime),0) As DownTime From (
SELECT
      Nodes.Caption As NodeCaption,
DATEDIFF(Mi, StartTime.EventTime,
      (SELECT TOP 1
          EventTime
          FROM Events AS Endtime
          WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
              AND EndTime.NetObjectType = 'N'
              AND EndTime.NetworkNode = StartTime.NetworkNode
          ORDER BY EndTime.EventTime)) AS Downtime
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')
AND eventtime between dateadd(month, -1, getdate()) and getdate()
) dt
Group By NodeCaption
Order By NodeCaption

nicksmith

sotherls - many thanks, we are getting there with your help.

I am basically trying to replicate one of the default availability reports that Whats Up Gold spits out. I am currently migrating boxes from WUG to Orion and the report would be a customer requirement. The report I am trying to emulate is below.

r0berth1

This is what I use to disply availability stats to upper management. Can you help me figure out how to add a column after the % Available colum that shows minuites down for the same time span?

SELECT TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
Nodes.Branch AS Branch

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN 39751 AND 39780.9999884259 )
AND
(
(Nodes."Type" = 'ATM')
)

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

ORDER BY SummaryMonth ASC, 3 ASC

r0berth1

That worked, thanks for the help

l.mendoza

Hello, Sotheris.

Good day!

I've seen the scripts you have created. Can we create a report that would show both uptime and downtime availability reports in minutes?

Appreciate your kind response.

Thank you.

Leny