Report Writer Advance SQL Down Time

I am trying to use report writer to generate circuit availiblity. I need the time the node went down and came back up, then the total of the duration for that period. As it stands I have been working in advanced SQL to develop the report. I am now at a point where I am no longer making any progress. So far this is what I have:

SELECT Nodes.NodeID AS NodeID,
Events.EventTime AS Event_Time,
Nodes.VendorIcon AS Vendor_Icon,
Nodes.SysName AS System_Name,
( STR(Events.EventType) + '.gif') AS EventTypeIcon,
Events.Message AS Message

FROM
Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

WHERE
( EventTime BETWEEN 39690 AND 39719 )
AND
(
(Events.EventType = 10) OR
(Events.EventType = 11)
)

Is it possible to achieve what I am after?

Find more posts tagged with

orion_8.5_report_writer_sql

Accepted answers

All comments

kweise

I found a report in the Content Sharing Zone that generates a report similar to what you are trying to do. Here is the link:

Hope this helps.

efj6408

I have tried this but I get the error-

SQL Error:

Line 1:Incorrect syntax near'<'.

I have tried stripping out the html code, but that changes the error code.

I am open to suggestions. I have used the querry that I shared in my earlier post and used excel to minipulate the data which is tedious.

Progress!! Now I need to figure out the total down time as the calculation is wrong. It shows postive and negitive numbers.

SELECT
    StartTime.EventTime,
    Nodes.Caption,
    Nodes.Location,
    StartTime.Message,
    DATEDIFF(Mi, StartTime.EventTime,
    (SELECT TOP 1
        EventTime
        FROM Events AS Endtime
        WHERE EndTime.EventTime BETWEEN 39690 AND 39719.9999884259 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.SysName LIKE '%Vendor1%') OR
(Nodes.SysName LIKE '%Vendor2%')
)
ORDER BY StartTime.EventTime

efj6408

Progress!! Now I need to figure out the total down time as the calculation is wrong. It shows postive and negitive numbers.