1 Reply Latest reply on Jan 12, 2017 7:30 PM by ryan.davis26

    swql full outer join?

    ryan.davis26

      Is a full outer join possible in swql?

       

      (Full disclosure, I'm probably a C-student in  SQL intermediate class on a good day...)

      Trying to get at alert history for purposes of alert integration with another tool.  The following query is exactly what I need but unfortunately in SWQL, it doesn't appear to support FULL OUTER JOINs

       

      SELECT        AlertHistory.AlertHistoryID, AlertHistory.AlertActiveID, AlertHistory.EventType AS EventTypeID, 
                               CASE AlertHistory.[EventType] WHEN 0 THEN 'Triggered' WHEN 1 THEN 'Reset' WHEN 2 THEN 'Acknowledged' WHEN 3 THEN 'Note' WHEN 4 THEN 'AddedToIncident' WHEN 5 THEN 'ActionFailed' WHEN 6 THEN
                                'ActionSucceeded' WHEN 7 THEN 'Unacknowledge' WHEN 8 THEN 'Cleared' ELSE 'Unknown' END AS EventType, AlertObjects.RelatedNodeCaption, AlertObjects.EntityNetObjectId, AlertConfigurations.Severity, 
                               AlertHistory.Message, AlertHistory.TimeStamp, AlertObjects.AlertID, AlertStatusView.AlertMessage, AlertHistory.AlertObjectID, AlertConfigurations.ObjectType, AlertObjects.EntityCaption
      FROM            AlertConfigurations INNER JOIN
                               AlertObjects ON AlertConfigurations.AlertID = AlertObjects.AlertID FULL OUTER JOIN
                               AlertStatusView ON AlertObjects.AlertObjectID = AlertStatusView.AlertObjectID FULL OUTER JOIN
                               AlertHistory ON AlertObjects.AlertObjectID = AlertHistory.AlertObjectID
      WHERE        (AlertHistory.EventType = 0) OR
                               (AlertHistory.EventType = 1)
      ORDER BY AlertHistory.AlertHistoryID DESC
      
        • Re: swql full outer join?
          ryan.davis26

          I think I found a different way to do it, basically what I was after was the alerthistoryview through SWQL but that view is not exposed through SWQL so what I ended up doing was using SQL management studio, taking a peek at the design of the AlertHistoryView and grabbing the query out of that and modifying for my needs which does work in SWQL!

           

          SELECT        TOP (3000) AH.AlertHistoryID, ACo.AlertID, AH.TimeStamp, 
                                   CASE AH.[EventType] WHEN 0 THEN 'Triggered' WHEN 1 THEN 'Reset' WHEN 2 THEN 'Acknowledged' WHEN 3 THEN 'Note' WHEN 4 THEN 'AddedToIncident' WHEN 5 THEN 'ActionFailed' WHEN 6 THEN 'ActionSucceeded'
                                    WHEN 7 THEN 'Unacknowledge' WHEN 8 THEN 'Cleared' ELSE 'Unknown' END AS EventTypeWord, ACo.Name, ACo.ObjectType, ACo.Enabled AS AlertEnabled, AH.Message, AH.AlertActiveID, AH.AlertObjectID, 
                                   AC.ActionTypeID, AC.Title, AO.RealEntityType, AO.TriggeredCount, AO.EntityNetObjectId, AO.EntityCaption, AO.EntityDetailsUrl, AO.RelatedNodeCaption, AO.RelatedNodeDetailsUrl, AO.RelatedNodeId, 
                                   AO.LastTriggeredDateTime, AO.AlertNote, ACo.AlertRefID, ACo.Severity, AH.EventType
          FROM            AlertHistory AS AH INNER JOIN
                                   AlertObjects AS AO ON AH.AlertObjectID = AO.AlertObjectID INNER JOIN
                                   AlertConfigurations AS ACo ON ACo.AlertID = AO.AlertID LEFT OUTER JOIN
                                   Actions AS AC ON AH.ActionID = AC.ActionID
          WHERE        (AH.EventType = 0) OR
                                   (AH.EventType = 1)
          ORDER BY AH.AlertHistoryID DESC