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 down times and up times on table in SQL

We need to add two columns on interface table of SQL Database as specified below:1) Interface Down time (MM.DD.YY and hh:mm:ss), 2) Interface Up time (MM.DD.YY and hh:mm:ss) ,

Then, we need to generate a monthly report to show the time difference between interface down time and up time in order to comply with SLA signed by telecom carrier.

Is there anyone who knows how to get time information into interface table columns ?Thank you. Serdar

Find more posts tagged with

Database

custom_report

Accepted answers

All comments

jtimes

This will give you node outage duration in minutes, but it can be easily re-written to provide interface outage duration

In Report Writter: File> New Report > Advanced SQL
General Tab:
Report Group: Events
Report Title: Outage Duration

Paste this into the SQL Tab:

SELECT
StartTime.EventTime,
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 = 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
eventtime between dateadd(month, -1, getdate()) and getdate()
ORDER BY StartTime.EventTime DESC

in Report Scheduler set the report to run at midnight every 30 days

serdar

Hi John,

Thank you.

We really appreciate your help.

All the best.

Serdar Esendal

serdar

Hi John,

We have a Service Level Agreement with telecom operator.

According to SLA, we need to check NodeType: 1 (Metropolitan Area) for 3 hrs outage time and we need to chek NodeType: 0 (Rural Area) for 8 hrs outage time. If the outage time is more than 3 hrs or 8 hrs on Nodes, we should generate a monthly report on a node basis and ask telecom operator to provide free service.

Can you be able to add and embed filter codes in your SQL statements ?

Thank you.

Best wishes,

Serdar

jtimes

Are NodeType: 1 and NodeType: 0 custom properties that you already have defined?

If so then on line 17

eventtime between dateadd(month, -1, getdate()) and getdate()AND Nodes.XXXXXXX = '1'

Replace the XXXXXXX with the field name of the custom property and the '1' with the value of the field '0'

"Save as" that report with a new name in the general tab, and repeat the process for the next value '1', then "Save as" again...

jtimes

Report added to the CSZ:

serdar

Hi John,

I thank you very much for your help.

We solved our problem.

Best wishes,

Serdar Esendal

serdar

Hi John,

We changed your coding a little bit to get only the nodes (1) and 180 min outagedurationminutes to be reported as follows:

SELECT
    StartTime.EventTime,
    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 = 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
eventtime between dateadd(month, -1, getdate()) and getdate() AND Nodes.NodeTipi = '1' AND 'OutageDurationInMinutes' >= '180'
ORDER BY Nodes.Caption ASC

But, we still get all the nodes to be printed. We like to print only nodes with 180 minutes down time.

It looks we are doing something wrong on your coding. Can you please help us to correct the coding ?

Thank you.

Serdar