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

Up or Down TIME for a box ...

I would like to monitor Up or Down TIME (Not %) for a box ... I've found that using the % Availability and back-calculating is bad, because it is based on ping intervals .... It's better to use the actual EVENTS of UP or DOWN and calculate the time in-between the events ... Has anyone figured a way to do this ??? I would like to summarize these views on a per-box per-week or month view. .... I don't care if it's seconds or minutes or hours ... I just need a number to work with.

Basically -->
NodeName -- Month -- #MinutesOffline

That's it

So far, I have yet to get anything to work even using raw SQL. There is something that is APPROXIMATELY like what I what in the default SQL query OUTAGE DURATION ... but that query does not summarize to any days/weeks/months .. it is just each outage and how long it is!

Find more posts tagged with

Accepted answers

All comments

drosoph

UPDATE: I have created the report ... but it only shows on a PER Event Up/Down time ... In Minutes or Hours ..

How would I go about grouping on a per day/week/month basis ???

DonYonce

Andrew,
That some heavy SQL code to do that. It's quite possible, it's just complex and can be slightly different for each query depending on your data (and table joins).

The Report Writer is smart enough to generate the weekly/monthly/yearly grouping for reports created using it's report generator. It does this by creating the complex SQL for you. But, the Report Writer cannot automatically generate the required SQL statements for a custom SQL report.

So... here's what I suggest...

Use the Report Writer and create a new "Historical - Availability" report from scratch. (this report is NOT going to give you the data you need, just an example of how to do weekly/monthly grouping in SQL).

Add the Node Name and Percent Availability to the report. Then select the "Summarization" tab. Select "Summarize the results by Week". Then from the Report Writer main menu, select "Report->Show SQL" An example of the SQL statements needed to group the data weekly are shown. Now go back to the "Summarization" tab and select "Summarize the results by Month". Then select the "SQL" tab again. The monthly grouping statements (much more complex) are shown.

You can use these examples to form your own grouping for your data. The grouping should be placed in the "Select" and "Group By" sections of the SQL statement.

Here is a couple examples of the Outage Duration report grouped by Week and Month.

SQL for grouping by week number :

SELECT
DatePart(Week,EventTime),
Nodes.Caption,
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')

GROUP BY DatePart(Week,EventTime), Nodes.Caption, StartTime.EventTime, StartTime.NEtworkNode

ORDER BY StartTime.EventTime DESC

SQL for grouping by Month :

SELECT
CONVERT(DateTime,LTRIM(MONTH(EventTime)) + '/01/' + LTRIM(YEAR(EventTime)),101) AS SummaryMonth,
Nodes.Caption,
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')

GROUP BY CONVERT(DateTime,LTRIM(MONTH(EventTime)) + '/01/' + LTRIM(YEAR(EventTime)),101),
Nodes.Caption, StartTime.EventTime, StartTime.NEtworkNode

ORDER BY StartTime.EventTime DESC

There is a flaw in the above queries .... do you see it

You may not care, but, if the outage crossing your grouping boundary (outage is from Oct 31 11:00 PM to Nov 2 10:00 AM for example), the entire outage is shown in the month it started, not split into multiple months. You may not care.

Also, there may be a problem with the example query in summing up multiple outages in a single period. I did not spend any time testing that issue. But, I think I answered your original question about grouping weekly and monthly.

If none of this works for you, you may need to use nested SQL queries.

Zoran

This is SQL query for
Interfaces-outage-in minutes-monthly
please some one help me about creating Availabillity report for interfaces how to summ all outages (minutes) and give in percents for my manager ???? emergency
************************************
SELECT
CONVERT(DateTime,LTRIM(MONTH(EventTime)) + '/01/' + LTRIM(YEAR(EventTime)),101) AS SummaryMonth,
Interfaces.NodeID,
Interfaces.Caption,
Interfaces.InterfaceID,
DATEDIFF(Mi, StartTime.EventTime,
(SELECT TOP 1
EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 11
AND EndTime.NetObjectType = 'I'
AND EndTime.NetObjectID = StartTime.NetObjectID
ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Interfaces ON StartTime.NetObjectID = Interfaces.InterfaceID
WHERE (StartTime.EventType = 10) AND (StartTime.NetObjectType = 'I') AND ( EventTime BETWEEN 38410 AND 38439 )

GROUP BY CONVERT(DateTime,LTRIM(MONTH(EventTime)) + '/01/' + LTRIM(YEAR(EventTime)),101),
Interfaces.NodeID,Interfaces.Caption, Interfaces.InterfaceID, StartTime.EventTime, StartTime.NetObjectID
ORDER BY Interfaces.Caption DESC

Network_Guru

I've been requested to create similar reports for all our stores.
One of the problems using the Events for these reports is the inaccurate outage duration.

We are polling each router at 5 minute intervals. The fast polling timer is set for 4 minutes. So in the event there is an actual 10 minute outage, this would show up in the report as anywhere from a 1 to 4 minute outage.

The polling & fast polling timers have been optimized to reduce false reports, but cause inaccurate reporting.
<feature request> I'd like to be able to adjust the fast polling timer on a per node basis, so that I can tune the stores to report outage duration minutes more accurately.

-=Cheers=-
NG