0 Replies Latest reply on Feb 14, 2013 4:42 PM by bsciencefiction.tv

    Help with SWQL...I am missing something here

    bsciencefiction.tv

      This is a modified report I found on Thwack that works great as a reprot.  I am trying to convert it for a SWQL view and am running into issues.  Any thoughts on what I am missing?

      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.WanTechnology like 'mpls' and Nodes.DeviceType like 'router' and (Nodes.Entity_Type like 'backoffice' or Nodes.Entity_Type like 'branch' or Nodes.Entity_Type like 'callcenter' or Nodes.Entity_Type like 'metrocampus'))

      Order by OutageDurationInMinutes DESC