solarwinds  |  thwack
in
Search 44,679 posts and 621 resources contributed by 21,524 members or post a topic.

Already Joined? Sign in
Report Writer Advance SQL Down Time

rated by 0 users
Not Answered This post has 0 verified answers | 3 Replies | 3 Followers


4 Posts
Points 10
efj6408 posted on 10-10-2008 11:45 AM
rated by 0 users

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?

All Replies


118 Posts
Points 278
kweise replied on 10-14-2008 8:18 AM
rated by 0 users
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: http://thwack.com/files/folders/orion_custom_reports/entry16443.aspx Hope this helps.
  • | Post Points: 3

4 Posts
Points 10
efj6408 replied on 10-14-2008 8:45 AM
rated by 0 users

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

  • | Post Points: 1

4 Posts
Points 10
efj6408 replied on 10-14-2008 9:01 AM
rated by 0 users

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

 

  • | Post Points: 1
Page 1 of 1 (4 items) | RSS