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

APM Reporting - Outage Duration sorted per server and outage timestamp

Hi,

We need a custom SQL Report presenting the outage duration times of last month sorted per server and outage time.
It should look exactly like the very nice template from jtimes, but this was written only for node down alerts...

Unfortunately the table structures for APM seems quite different, so it was not able for me as SQL-rookie to reverse engineer this task.
I also searched the thwack, found only one topic targeting this- but the calculation is based on availability percentage and the output is also not that meaningful as showed above...

Thanks for any help!

Find more posts tagged with

Accepted answers

qle

Ok, I attempted to modify jtimes' report to your preference. Just as there is for a down node, there is a source to calculate outage duration from the database. Create a new Advanced SQL report and insert the following

SELECT StartTime.EventTime, APM_Application.Name, Nodes.Caption, Nodes.Location, StartTime.Message, DATEDIFF(Mi, StartTime.EventTime,
(SELECT TOP 1 EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 504
AND EndTime.NetObjectType = 'AA'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
FROM Events StartTime INNER JOIN APM_Application ON StartTime.NetObjectID = APM_Application.ID INNER JOIN Nodes on Nodes.NodeID = APM_Application.NodeID
WHERE (StartTime.EventType = 505) AND (StartTime.NetObjectType = 'AA') AND
eventtime between dateadd(month, -1, getdate()) and getdate()
ORDER BY StartTime.EventTime

It includes time of outage, (application) name, (node) name, location, down event and outage duration. Let me know if this meets your needs.

All comments

qle

Sorry, I must a little slow this morning but I'm trying to understand what you're looking for here. jtimes's Outage Duration Last Month report is already sorted by server/node. Also, as you've indicated, the report applies to down nodes but how is that different from your request for outage duration? Doesn't a down node imply an outage or am I completely missing something here?

Questionario

I think he means outage duration of each application template sorted by server

herwig

good guess, Questionario - that's the point

wrote my posting at midnight, think I was not in best condition either.
I'll try to improve this request with additional information...

I found custom SQL code, doing nearly what I am searching for:

looks like this:

What's not optimal in this report compared with that from jtimes

1- Outage minutes are accumulated over month per server and application template, not listed by Trigger-time and date for the last month like in jtimes node report.
2- Outage minutes calculation is based on availability percent, as far as I understand because no other source for the calculation exists in database?
3- the minute values should be shortened to full integer values

qle

SELECT StartTime.EventTime, APM_Application.Name, Nodes.Caption, Nodes.Location, StartTime.Message, DATEDIFF(Mi, StartTime.EventTime,
(SELECT TOP 1 EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 504
AND EndTime.NetObjectType = 'AA'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
FROM Events StartTime INNER JOIN APM_Application ON StartTime.NetObjectID = APM_Application.ID INNER JOIN Nodes on Nodes.NodeID = APM_Application.NodeID
WHERE (StartTime.EventType = 505) AND (StartTime.NetObjectType = 'AA') AND
eventtime between dateadd(month, -1, getdate()) and getdate()
ORDER BY StartTime.EventTime

It includes time of outage, (application) name, (node) name, location, down event and outage duration. Let me know if this meets your needs.