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
Store

Interface downtime report

Hi all! I am a noob in SQL and dont know how to create Interface Downtime report as Node Downtime Reports

Does anyone could assist me? I have list of interfaces with Custom property and want to know when it down, up and outage time in minutes for definite period, months?

I have found one report from here - reports - INterfaces and Nodes DownTime , but it shows me only one day's report. And I think this report is based on event. Is it possible to create report without event?

Hope for your help, Asset

Find more posts tagged with

Accepted answers

assetkanibek

Hi all! Today with my groupmate which works with SQL helped me understand the code.

So I have changed Node Downtime Reports under myself to see interfaces down/up events and duration.

rob.hock thanks for your report. I have changed it a little bit Now I want to upgrade my report to change the date between events.

Also, I have putted Uplinks condition to work with interfaces that I need. If you want you can delete this string, line.

Hope it will help others.

Interface_Down_Time_Report.OrionReport

All comments

rschroeder

Check out the process I provided here:

How to create a report displaying the Last Time Data was Transmitted or Received on a Switch Port

I use this to learn which ports on a switch can be repurposed / repatched to other devices, based on the amount of time any port on a switch has been down.

assetkanibek

rschroeder thanks for your answer, but I want to create a little bit difference report.

If you see screen below, you can see the report which shows node down and up time and outage duration. So I want to create something like this, but with interfaces. I have about 70 interfaces that I need.

I have found from [dbo].[Interfaces] Interfaces' values like: Interface name, NodeID and etc.

Could anyone tell me where to find in DB table down/up events of interfaces or where should I get information about it? If we find it I think I can create what I want.

i_like_eggs

Try this

select * from eventtypes

filter for events that interfaces can trigger then select * from events whene eventtype = interface eventid

lol bit vague but on the train on my phone and dont have access to my db to try these queries lol but when im in the office if this is unanswered by then ill help you out

assetkanibek

Who could help me to understand this part of SQL code?

StartTime.EventTime AS DownEventTime, //this is DownEventTime raw, isn't it?

(

SELECT TOP 1 EventTime //what does it mean?

FROM Events AS EndTimeTable

where EndTimeTable.EventTime >= StartTime.EventTime

AND EndTimeTable.EventType = 5

AND EndTimeTable.NetObjectType = 'N'

AND EndTimeTable.NetworkNode = StartTime.NetworkNode

AND EventTime IS NOT NULL

ORDER BY EndTimeTable.EventTime

) AS UpEventTime,

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)

) AS UpTimeTable

where outageDurationInMinutes IS NOT NULL

ORDER BY Caption ASC, DownEventTime DESC

i_like_eggs

Best way to inderstand really is to run it in little chunks so Find an eventtype in the event type table for interaces like event something like interface down then just replace

SELECT TOP 1 EventTime //what does it mean?

FROM Events AS EndTimeTable

where EndTimeTable.EventTime >= StartTime.EventTime

AND EndTimeTable.EventType = 5 --REPLACE WITH NEW INTERFACE EVENT

AND EndTimeTable.NetObjectType = 'N'-- this is looking for nodes change to I

AND EndTimeTable.NetworkNode = StartTime.NetworkNode

AND EventTime IS NOT NULL

ORDER BY EndTimeTable.EventTime

and anywhere you see eventtype = 5 just change it to the new interface eventtype ID

i_like_eggs

might be a SQL guru out here but you purpose of selecting the top 1 is so you get the top one there are going to be many events for an interface node etc and this is looking at the one at the top

assetkanibek

Hi all! Today with my groupmate which works with SQL helped me understand the code.

So I have changed Node Downtime Reports under myself to see interfaces down/up events and duration.

rob.hock thanks for your report. I have changed it a little bit Now I want to upgrade my report to change the date between events.

Also, I have putted Uplinks condition to work with interfaces that I need. If you want you can delete this string, line.

Hope it will help others.

Interface_Down_Time_Report.OrionReport

assetkanibek

Hi all! I want to change the date of report time, for example report interface down events for last 1 month of during two weeks, etc. How to change that?