6 Replies Latest reply on Feb 20, 2015 10:31 PM by contactjt

    Custom SWQL Query Resource Help

    mharvey

      I'm working with EOC trying to use the Custom SWQL query resources to get a resource to show me all Acknowledged alerts from Orion servers.  The following works in SQL:

       

      Select

      EOC_AlertStatus.ObjectName, EOC_AlertStatus.TriggerTimeStamp,

      EOC_AlertDefinition.Name, EOC_AlertStatus.Acknowledged,

      EOC_AlertStatus.AcknowledgedTime, EOC_AlertStatus.AcknowledgedBy

       

       

      From EOC_AlertStatus

       

       

      Join EOC_AlertDefinition on EOC_AlertStatus.AlertDefID = EOC_AlertDefinition.AlertDefID

       

       

      Where EOC_AlertStatus.Acknowledged = 1

       

      But if I try to use it following the SWQL documentation I've found, trying to run it this way:

       

      SELECT

      EOC.AlertStatus.ObjectName, EOC.AlertStatus.TriggerTimeStamp,

      EOC.AlertDefinition.Name, EOC.AlertStatus.Acknowledged,

      EOC.AlertStatus.AcknowledgedTime, EOC.AlertStatus.AcknowledgedBy

       

       

      FROM EOC.AlertStatus

       

       

      INNER JOIN EOC.AlertDefinition on EOC.AlertStatus.AlertDefID = EOC.AlertDefinition.AlertDefID

       

       

      WHERE EOC.AlertStatus.Acknowledged = 1

       

      results in the resource being unable to process the request.  Anyone thoughts?

        • Re: Custom SWQL Query Resource Help
          mharvey

          Figured out the SWQL that I needed to use.  For anyone else who runs across this, here is what you'll need.

           

          SELECT q.ObjectName, q.TriggerTimeStamp, q.Name, q.AcknowledgedBy, q.AcknowledgedTime, q.Notes, q._LinkFor_ObjectName, q._IconFor_ObjectName AS [_IconFor_ObjectName], q._LinkFor_Name

          FROM

          (

          SELECT a.ObjectName, a.TriggerTimeStamp, d.Name, a.AcknowledgedBy, a.AcknowledgedTime, a.Notes, o.Website + '/Orion/View.aspx?NetObject=N%3A' + ToString(n.NodeID) AS [_LinkFor_ObjectName], '/images/Small-' + GroupStatus AS [_IconFor_ObjectName],'/Default.aspx?viewId=1004' AS [_LinkFor_Name]

          FROM EOC.AlertStatus a

          JOIN EOC.AlertDefinition d ON a.AlertDefID = d.AlertDefID

          JOIN EOC.Node n ON n.NodeID = a.ActiveObject AND a.ObjectType = 'Node' and n.OrionID = a.OrionID

          JOIN EOC.Orion o ON o.OrionID = a.OrionID

          where (Acknowledged = '1')

           

           

          UNION

          (SELECT a.ObjectName, a.TriggerTimeStamp, d.Name, a.AcknowledgedBy, a.AcknowledgedTime, a.Notes, o.Website + '/Orion/View.aspx?NetObject=I%3A' + ToString(i.InterfaceID) AS [_LinkFor_ObjectName], '/images/Small-' + s.StatusName + '.gif' AS [_IconFor_ObjectName],'/Default.aspx?viewId=1004' AS [_LinkFor_Name]

          FROM EOC.AlertStatus a

          JOIN EOC.AlertDefinition d ON a.AlertDefID = d.AlertDefID

          JOIN EOC.Interface i ON i.InterfaceID = a.ActiveObject AND a.ObjectType = 'Interface' AND i.OrionID = a.OrionID

          JOIN EOC.Orion o ON o.OrionID = a.OrionID

          JOIN EOC.StatusInfo s ON s.StatusId = i.Status

          where (a.Acknowledged = '1'))

          UNION

          (SELECT a.ObjectName, a.TriggerTimeStamp, d.Name, a.AcknowledgedBy, a.AcknowledgedTime, a.Notes, o.Website + '/Orion/View.aspx?NetObject=AM%3A' + ToString(c.ComponentID) AS [_LinkFor_ObjectName], '/images/Small-' + c.Application.Node.GroupStatus AS [_IconFor_ObjectName],'/Default.aspx?viewId=1004' AS [_LinkFor_Name]

          FROM EOC.AlertStatus a

          JOIN EOC.AlertDefinition d ON a.AlertDefID = d.AlertDefID

          JOIN EOC.APM.Component c ON c.ComponentID = a.ActiveObject AND a.ObjectType = 'APM: Component' and c.OrionID = a.OrionID

          JOIN EOC.Orion o ON o.OrionID = a.OrionID

          where (a.Acknowledged = '1'))

          ) q

          ORDER BY q.TriggerTimeStamp DESC

          1 of 1 people found this helpful