Interface Availability Report

Sorry, my previous reply was for node availability. Here is the correct code for interface availability

use netperfmon
IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.SUMTABLE'))

DROP TABLE SUMTABLE

go
SELECT
EventTime,
Interfaces.Caption,
NetObjectID,
NodeID,

DATEDIFF(MINUTE, 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
INTO SUMTABLE
FROM Events AS StartTime
INNER JOIN Interfaces ON StartTime.NetObjectID = Interfaces.InterfaceID
WHERE
(StartTime.EventType = 10)
AND (StartTime.NetObjectType = 'I')
AND (eventtime between '11/01/2008 00:00:00' and '11/30/2008 23:59:59')
GROUP BY
Interfaces.NodeID,
NetObjectID,
Interfaces.Caption,
Interfaces.InterfaceID,
StartTime.EventTime,
StartTime.NetObjectID

Select DISTINCT
Nodes.Caption AS Router_Name,
interfaces.caption,

COALESCE(CONVERT(char(10),100-Round(SUM(Convert(float,OutageDurationInMinutes)/(DATEDIFF(Minute, '09/01/2005 00:00:00', '09/30/2005 23:59:59') + 1 )*100),2)),'100') + '%' AS "Avalibility This Month",
COALESCE(SUM(OutageDurationInMinutes), 0) AS OutageTimeInMinutes
FROM (Nodes INNER JOIN Interfaces ON Interfaces.NodeID = Nodes.NodeID) LEFT JOIN SUMTABLE ON SUMTABLE.NetObjectID = Interfaces.InterfaceID

GROUP BY Nodes.Caption, interfaces.caption, nodes.department

ORDER BY Nodes.Caption

DROP TABLE SUMTABLE

All comments

You can track the interface down time (not the availability) using the events.
Just create an alert for any interface down. This will show up in the events, which you can create a report for minutes of down-time per outage.

You could then manually convert this into availability.

-=Cheers=-
NG

Thanks for your advice, NG! I'll give it shot.

quote:On this issue of interface availability, how would I create a report based on down events to calculate interface availability? Would I use a report similar to the canned Outage Duration report for nodes? Thanks

That is correct.
I haven't actually done this with interfaces, but it should work the same as the Node Outage Duration reports. You may have to customize the SQL query to do this.

-=Cheers=-
NG

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
and how it will go on web?
************************************
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

Zoran,
Did you ever get this to work?
Im very interested in the same thing.

Ross warren

This is very close.. Not quite in percentages yet.

IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[SUMTABLE]'))
DROP TABLE SUMTABLE

SELECT CONVERT(DateTime,LTRIM(MONTH(EventTime)) + '/07/' + LTRIM(YEAR(EventTime)),101) AS SummaryMonth,
EventTime,
Interfaces.Caption,
NetObjectID,

DATEDIFF(MINUTE, StartTime.EventTime,
(SELECT TOP 1 EventTime
FROM Events AS Endtime
WHERE
EndTime.EventTime > StartTime.EventTime
AND EndTime.EventType = 5001
AND EndTime.NetObjectType = 'I'
AND EndTime.NetObjectID = StartTime.NetObjectID
ORDER BY EndTime.EventTime))

AS OutageDurationInMinutes

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

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

GO

Select Caption AS Circuit_Name, SUM(OutageDurationInMinutes) AS TotalOutageTime
from SUMTABLE

GROUP BY Caption
HAVING SUM(OutageDurationInMinutes) IS NOT NULL

Interface availability based on Zoran's start.
With Percentage.
NOTE: I use the custom property Interfaces.Service = "Internet"
to define my circuits. Thanks Yahoo chart guy!

IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[SUMTABLE]'))
DROP TABLE SUMTABLE

SELECT CONVERT(DateTime,LTRIM(MONTH(EventTime)) + '/07/' + LTRIM(YEAR(EventTime)),101) AS SummaryMonth,
EventTime,
Interfaces.Caption,
NetObjectID,
NodeID,

DATEDIFF(MINUTE, StartTime.EventTime,
(SELECT TOP 1 EventTime
FROM Events AS Endtime
WHERE
EndTime.EventTime > StartTime.EventTime
AND EndTime.EventType = 5001
AND EndTime.NetObjectType = 'I'
AND EndTime.NetObjectID = StartTime.NetObjectID
ORDER BY EndTime.EventTime))

AS OutageDurationInMinutes

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

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

GO

Select Nodes.Caption, SUMTABLE.Caption AS Circuit_Name,
COnvert(char(5),Round(SUM(Convert(float,OutageDurationInMinutes)/43200*100),2)) + '%' AS "Percent unavailable Per Month"
from SUMTABLE LEFT JOIN Nodes ON Nodes.NodeID = SUMTABLE.NodeID

GROUP BY Nodes.Caption, SUMTABLE.Caption
HAVING SUM(OutageDurationInMinutes) IS NOT NULL

Im just talking to my self

Ross Warren

quote:Originally posted by rossw

Interface availability based on Zoran's start.
With Percentage.
NOTE: I use the custom property Interfaces.Service = "Internet"
to define my circuits. Thanks Yahoo chart guy!

Im just talking to my self
Ross Warren

Brilliant work Ross,
I wish I had this kind of SQL savvy!

It just goes to show the untapped potential of the Orion reporting engine.

-=Cheers=-
NG

My thanks to everyone for your feedback with this post! Very nice work Ross and Zoran too! I wish I had your skill sets to develop this kind of stuff!

A slight update:

I changed the calculation to availability, not unavailability.
Also made the output show all the interfaces.service = 'Internet'
not just the interfaces that show up in events. Got to show those
100%s!!!!..

IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[SUMTABLE]'))
DROP TABLE SUMTABLE

SELECT
EventTime,
Interfaces.Caption,
NetObjectID,
NodeID,

DATEDIFF(MINUTE, StartTime.EventTime,
(SELECT TOP 1 EventTime
FROM Events AS Endtime
WHERE
EndTime.EventTime > StartTime.EventTime
AND EndTime.EventType = 5001
AND EndTime.NetObjectType = 'I'
AND EndTime.NetObjectID = StartTime.NetObjectID
ORDER BY EndTime.EventTime))

AS OutageDurationInMinutes

INTO SUMTABLE
FROM Events AS StartTime
INNER JOIN Interfaces ON StartTime.NetObjectID = Interfaces.InterfaceID
WHERE
(StartTime.EventType = 10)
AND (StartTime.NetObjectType = 'I')
AND (Interfaces.Service = 'Internet')

GROUP BY
Interfaces.NodeID,
NetObjectID,
Interfaces.Caption,
Interfaces.InterfaceID,
StartTime.EventTime,
StartTime.NetObjectID

GO

Select DISTINCT Nodes.Caption AS Router_Name, Interfaces.Caption,
COALESCE(CONVERT(char(5),100-Round(SUM(Convert(float,OutageDurationInMinutes)/43200*100),2)),'100') + '%' AS "Avalibility This Month"
FROM (Nodes INNER JOIN Interfaces ON Interfaces.NodeID = Nodes.NodeID) LEFT JOIN SUMTABLE ON SUMTABLE.NodeID = Interfaces.NodeID
Where Interfaces.Service = 'Internet'

GROUP BY Nodes.Caption, Interfaces.Caption
ORDER BY Nodes.Caption

Ross...I attempted to set up the SQL report but Report Writer didn't like the GO statement. Can you shed any light? SQL if not my forte.

quote:Originally posted by rgward

Ross...I attempted to set up the SQL report but Report Writer didn't like the GO statement. Can you shed any light? SQL if not my forte.

Ron, I believe you must use M$ Enterprise SQL Manager to run this code.

-=Cheers=-
NG

Yes. I cant get it to run in Report Writer either.
Still working on it. Will Share when I do.

Yes SQL Enterprize manager is what I use.

Ross Warren

Thank you Gents! I'll run it thru SQL Manager.

Ross...Report Writer version will be nice too. Hope it's not too much trouble.

Ron

skaczkowski

Hmmmmmmmm...

I can't seem to get this to work, I get the following error:

Server: Msg 207, Level 16, State 3, Line 4
Invalid column name 'Service'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'SUMTABLE'.

I should be able to just run this out of SQL Query Analyzer without any DB mods, correct?

Thanks everyone!

-Steve

Steve,
You need to do a custom property called service.
Was an idea i gleamed from the Yahoo Charts thread.

Ross W

skaczkowski

Or using one of my existing groups seems to work ok too

Thanks!

-Steve

osd

Hi,

Any ideas if this would be possible querying the syslog messages? Some of our WAN outages don't involve an interface actually going down, but we get and EIGRP neighbor state change message in the syslog. I want to be able to setup a report for monthly outages that queries the syslog tables for these messages.

Cheers

Owen

charpham

I ran the SQL query from rossw (dated 8/4) and got the following

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'SUMTABLE'.

Unfortunately I am not really SQL savvy, can someone tell me what this means?

Incidently, I was using the SQL Query Analyzer..

SUMTABLE is just a temp table that gets build to aggregate data.

Mine just started to fail after upgrading to 7.8.. Must check into.

Ross Warren

skaczkowski

I ran the report once, then it failed complaining about the SUMTABLE so I nuked that table and it ran again..

Seems that it doesn't clean up correctly or something..

-Steve

Had some Errors and incorrect reporting.
My DBA and I fixed these up.

This is for September and is keyed of Interfaces.Service = 'backbone'. I have started using Interfaces.Service to help us define different reports.. Such as 'Internet' or 'backbone' or 'Customer' etc.

IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[SUMTABLE]'))

DROP TABLE SUMTABLE

go
SELECT
EventTime,
Interfaces.Caption,
NetObjectID,
NodeID,
Interfaces.Service_Type,

DATEDIFF(MINUTE, 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
INTO SUMTABLE
FROM Events AS StartTime
INNER JOIN Interfaces ON StartTime.NetObjectID = Interfaces.InterfaceID
WHERE
(StartTime.EventType = 10)
AND (StartTime.NetObjectType = 'I')
AND (Interfaces.Service = 'Backbone')
AND (eventtime between '09/01/2005 00:00:00' and '09/30/2005 23:59:59')
GROUP BY
Interfaces.NodeID,
NetObjectID,
Interfaces.Caption,
Interfaces.InterfaceID,
StartTime.EventTime,
StartTime.NetObjectID,
Interfaces.Service_Type

go

Select DISTINCT
Nodes.Caption AS Router_Name,

Interfaces.Label AS Interface,
Interfaces.Service_Type,

COALESCE(CONVERT(char(10),100-Round(SUM(Convert(float,OutageDurationInMinutes)/(DATEDIFF(Minute, '09/01/2005 00:00:00', '09/30/2005 23:59:59') + 1 )*100),2)),'100') + '%' AS "Avalibility This Month",
COALESCE(SUM(OutageDurationInMinutes), 0) AS OutageTimeInMinutes
FROM (Nodes INNER JOIN Interfaces ON Interfaces.NodeID = Nodes.NodeID) LEFT JOIN SUMTABLE ON SUMTABLE.NetObjectID = Interfaces.InterfaceID
Where Interfaces.Service = 'Backbone'
GROUP BY Nodes.Caption, Interfaces.Label, Interfaces.Service_Type

ORDER BY Nodes.Caption

DROP TABLE SUMTABLE

This still doesnt run in Report Writer.
Ugggh.

Bump...Ross, ever get this query working in Report Writer?

We dont really use report writer anymore. We have created our own clustered DB server that takes in data from or satellite modems, Remedy, Cisco call manager and Solarwinds and runs reports with Crystal Reports and Sharepoint.

The problem with my query and report writer is the use of the temp table. If some one (smarter) could re-write it without the use of the temp table it might work.

-Ross Warren

Hi all,

i'm new in this forum and i'm not skilled on SQL;

i was trying to create a report with your advanced query but i notice that works only with events that are closed inside the month and doesn't work with events that haven't an EndTime inside selected month; then i tried to insert a period that is longer then a month but nothing..

Is there anybody who can help me ?

Thanks in advance.

Prysm

How do I turn off the smiley faces in your post? I may be missing something, but there is no "object_id" in the sysobjects table, there is ID however.... I am running 8.5.1

poiuy

i am new to orion, i use v 9.1 with sql express on win 2003.

i need availability on interfaces BAD!!!

i tried using the posts in this forum, but it

not wrk much for me. Can u please give me a

step by step approach? i'd apreciate it greatly.

Follow this thread there are many examples available. Here is some more rudimentory code.

use netperfmon

SELECT Nodes.Caption AS NodeName,

nodes.department AS Dept,

Nodes.IP_Address AS IP_Address,

AVG(ResponseTime.Availability) AS AVERAGE_of_Availability

FROM

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

WHERE

( DateTime BETWEEN 39448 AND 39478 )

GROUP BY Nodes.Caption, Nodes.IP_Address,nodes.department

ORDER BY nodes.department ASC, 4 ASC

Sorry, my previous reply was for node availability. Here is the correct code for interface availability

use netperfmon
IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.SUMTABLE'))

DROP TABLE SUMTABLE

go
SELECT
EventTime,
Interfaces.Caption,
NetObjectID,
NodeID,

Select DISTINCT
Nodes.Caption AS Router_Name,
interfaces.caption,

GROUP BY Nodes.Caption, interfaces.caption, nodes.department

ORDER BY Nodes.Caption

DROP TABLE SUMTABLE