6 Replies Latest reply on Feb 12, 2016 11:06 AM by tdanner

    SWQL Studio RIGHT JOIN not working

    xtraspecialj

      I can do a regular JOIN or I can explicitly type LEFT JOIN, but if I type RIGHT JOIN or RIGHT OUTER JOIN I get

       

      "Unsupported JOIN Type "RIGHT"

       

      Yet, in the SWQL Studio "SQL Constructs Supported" section it specifically lists RIGHT OUTER JOIN as being supported.  LEFT JOIN, LEFT OUTER JOIN, and INNER JOIN do work just fine.

       

      Anybody know why this is?

        • Re: SWQL Studio RIGHT JOIN not working
          tdanner

          Can you post the full query?

            • Re: SWQL Studio RIGHT JOIN not working
              xtraspecialj

              Sure.  I figured out a different way to do it (I noticed that the AlertObjects entity has a relationship with the Node entity, so really no need for a RIGHT JOIN now, but... The documentation says RIGHT OUTER JOIN should work and it doesn't, so the question is still valid I suppose).  Here is the query I was trying, plus, a screenshot of the error can be found below that:

               

              SELECT
              ac.Name AS [Alert Name],
              '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + TOSTRING(ao.AlertObjectID) AS [_LinkFor_Alert Name],
              CASE
                WHEN asev.SevConv = 'Informational' THEN '\Orion\Images\ActiveAlerts\InformationalAlert.png'
                WHEN asev.SevConv = 'Warning' THEN '\Orion\Images\ActiveAlerts\Warning.png'
                WHEN asev.SevConv = 'Critical' THEN '\Orion\Images\ActiveAlerts\Critical.png'
                WHEN asev.SevConv = 'Serious' THEN '\Orion\Images\ActiveAlerts\Serious.png'
                WHEN asev.SevConv = 'Notice' THEN '\Orion\Images\ActiveAlerts\Notice.png'
                Else '\Orion\Images\ActiveAlerts\MoreActionsSelected.png'
                END AS [_IconFor_Alert Name],
              ao.EntityCaption AS [Alert Object],
              ao.EntityDetailsUrl AS [_LinkFor_Alert Object],
              ac.ObjectType AS [Object Type],
              ao.RelatedNodeCaption AS [Node Name],
              '\NetPerfMon\images\' + n.StatusLED AS [_IconFor_Node Name],
              ao.RelatedNodeDetailsUrl AS [_LinkFor_Node Name],
              TOSTRING(ao.LastTriggeredDateTime) AS [Triggered Time],
              CASE
                WHEN aa.Acknowledged = 1 THEN TOSTRING(aa.AcknowledgedDateTime)
                ELSE 'No'
              END AS [Acknowledged],
              CASE
                WHEN aa.Acknowledged = 1 THEN  '\Orion\Images\ActiveAlerts\Acknowliedged_icon16x16v1.png'
                ELSE '\Orion\Images\ActiveAlerts\MoreActionsSelected.png'
              END AS [_IconFor_Acknowledged]
              
              
              FROM Orion.AlertActive aa
              JOIN Orion.AlertObjects ao ON aa.AlertObjectID = ao.AlertObjectID
              JOIN Orion.AlertConfigurations ac ON ao.AlertID = ac.AlertID
              RIGHT OUTER JOIN Orion.Nodes n ON ao.RelatedNodeID = n.NodeID
              JOIN (
                SELECT
                ac2.AlertID,
                CASE
                WHEN TOSTRING(ac2.Severity) = 0 THEN 'Informational'
                WHEN TOSTRING(ac2.Severity) = 1 THEN 'Warning'
                WHEN TOSTRING(ac2.Severity) = 2 THEN 'Critical'
                WHEN TOSTRING(ac2.Severity) = 3 THEN 'Serious'
                WHEN TOSTRING(ac2.Severity) = 4 THEN 'Notice'
                Else TOSTRING(ac2.Severity)
                END AS SevConv
                FROM Orion.AlertConfigurations ac2
                ) AS asev ON ac.AlertID = asev.AlertID
              
              WHERE ac.Name LIKE '%Tier 4%'
              

               

              RIGHT JOIN gives the same error.  See the screenshot here for the error:

               

              • Re: SWQL Studio RIGHT JOIN not working
                xtraspecialj

                You get a chance to check the query out and see if there was something wrong with it, or is there an issue in SWQL?

                  • Re: SWQL Studio RIGHT JOIN not working
                    tdanner

                    SWIS only supports RIGHT JOIN for queries that it can translate entirely to a single SQL Server query. One of the entities in your query (Orion.AlertObjects) is handled by code in SWIS, so it can't send the whole query to SQL Server. SWIS's in-memory query processor can't handle the RIGHT JOIN.

                     

                    In general, queries that use RIGHT JOIN can be rearranged to use LEFT JOIN instead. I'll count this as a vote for implementing RIGHT JOIN support for the SWIS in-memory query processor.

                      • Re: SWQL Studio RIGHT JOIN not working
                        xtraspecialj

                        Awesome.  There was one time I ran it and instead of getting the error message above I got the other error it gets when you run too big of a query and it times out.  So that may explain it.  I couldn't figure out how to re-write that to be a left join at the time but now I'm thinking it could be done by joining AlertObjects to Nodes instead of Nodes to AlertObjects.  That would mean I would already have had to define Nodes earlier in the FROM statement, right?  So the FROM statement would need to start with:

                         

                        FROM Orion.Nodes n
                        LEFT OUTER JOIN Orion.AlertObjects ao ON n.NodeID = ao.RelatedNodeID
                        

                         

                        Correct?

                         

                        Thank god for entity relationships though.  I don't always use them just because they require a lot of extra typing in the SELECT statements (since you can't alias relationships), but they are great for one-offs like this.

                         

                        Thanks for your help sir.