Thwack » SolarWinds » Orion Network Performance Monitor » Report Writer Advance SQL Down Time

in More Search Options

Report Writer Advance SQL Down Time

Last post 10-14-2008 9:01 by efj6408. 3 replies.
Page 1 of 1 (4 items)
Sort Posts:

  • 10-10-2008 11:45

    • efj6408
    • Not Ranked
      •
    • Joined on 06-05-2007
    • Posts 4
    • Points 10

    Report Writer Advance SQL Down Time

    Reply

    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?
    • Post Points: 3

  • 10-14-2008 8:18 In reply to

    • kweise
    • Top 75 Contributor
      •
    • Joined on 08-22-2006
    • USA
    • Posts 79
    • Points 179

    Re: Report Writer Advance SQL Down Time

    Reply
    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

  • 10-14-2008 8:45 In reply to

    • efj6408
    • Not Ranked
      •
    • Joined on 06-05-2007
    • Posts 4
    • Points 10

    Re: Report Writer Advance SQL Down Time

    Reply

    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

  • 10-14-2008 9:01 In reply to

    • efj6408
    • Not Ranked
      •
    • Joined on 06-05-2007
    • Posts 4
    • Points 10

    Re: Report Writer Advance SQL Down Time

    Reply

    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)