3 Replies Latest reply on Oct 14, 2008 9:01 AM by efj6408

    Report Writer Advance SQL Down Time

    efj6408 Oct 10, 2008 11:45 AM

      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?

        • Re: Report Writer Advance SQL Down Time
          kweise
          kweise Oct 14, 2008 8:18 AM (in response to efj6408)
          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:
          Outage Duration Last Month

          Hope this helps.
            • Re: Report Writer Advance SQL Down Time
              efj6408 Oct 14, 2008 8:45 AM (in response to kweise)

              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

                • Re: Report Writer Advance SQL Down Time
                  efj6408 Oct 14, 2008 9:01 AM (in response to efj6408)

                  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