3 Replies Latest reply on Apr 28, 2017 2:17 PM by tdanner

    SQWL Query is valid on the SDK, but showing invalid as SW datasource

    timt

      Hi,


      When I run this query on SWQL, validated, however when I try to add this same swql query as a custom resource, it's showing an invalid query.  I believe it may have to do with LEFT OUTER JOIN Orion.NodesCustomProperties AS cp ON AlertSup.id = cp.nodeid

       

       

      SELECT DISTINCT

             EntityUri, SuppressFrom, SuppressUntil, ToLocal(SuppressFrom) AS [LocalSuppressFrom], ToLocal([SuppressUntil]) AS [LocalSuppressUntil],

             CASE

                WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

                   THEN N.Caption

                WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

                   THEN AA.FullyQualifiedName

                ELSE 'SomethingElse'

             END AS Element,

             CASE

                WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

                   THEN N.DetailsUrl

                WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

                   THEN AA.DetailsUrl

                ELSE '<a href="/Orion/SummaryView.aspx">SomethingElse</a>'

             END AS DetailsUrl,

             CASE

                WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

                   THEN N.Status

                WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

                   THEN AA.Status

                ELSE 30

             END AS Status,

             AE.AccountID, N.ipaddress, cp.os_type, cp.os_environment, cp.os_admin, cp.site_name, cp.site_server_room_row, cp.site_server_room_rak

      FROM Orion.AlertSuppression AS AlertSup

      LEFT OUTER JOIN Orion.Nodes AS N ON AlertSup.EntityUri = N.Uri

      LEFT OUTER JOIN Orion.APM.Application AS AA ON AlertSup.EntityUri = AA.Uri

      LEFT OUTER JOIN Orion.AuditingEvents AS AE ON AE.AuditEventMessage LIKE CONCAT('%', CASE

                WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

                   THEN N.NodeName

                WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

                   THEN AA.Name

                ELSE 'Wrong'

             END, '%') AND [EntityUri] LIKE CONCAT('%=', AE.NetObjectID)

      INNER JOIN Orion.AuditingActionTypes AS AT ON AE.ActionTypeID = AT.ActionTypeID

      LEFT OUTER JOIN Orion.NodesCustomProperties AS cp ON AlertSup.id = cp.nodeid

      WHERE [AT].ActionTypeDisplayName LIKE '%mute%'

       

      Thank you.

        • Re: SQWL Query is valid on the SDK, but showing invalid as SW datasource
          tdanner

          The Custom SWQL Resource adds a "WITH ROWS" clause to queries to do paging of the result set. The problem is that in SWQL this clause is only valid after an ORDER BY clause, since paging only makes sense when the order of the result set has been specified. Try adding an ORDER BY clause (like "ORDER BY EntityUri") to the end of the query and see if that fixes it.

            • Re: SQWL Query is valid on the SDK, but showing invalid as SW datasource
              timt

              That did not fix it.

               

              However query will work if I take out the custom properties.  My question would be... how can I add some orion.nodescustomproperties into this query?

               

              SELECT DISTINCT

                     EntityUri, SuppressFrom, SuppressUntil, ToLocal(SuppressFrom) AS [LocalSuppressFrom], ToLocal([SuppressUntil]) AS [LocalSuppressUntil],

                     CASE

                        WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

                           THEN N.Caption

                        WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

                           THEN AA.FullyQualifiedName

                        ELSE 'SomethingElse'

                     END AS Element,

                     CASE

                        WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

                           THEN N.DetailsUrl

                        WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

                           THEN AA.DetailsUrl

                        ELSE '<a href="/Orion/SummaryView.aspx">SomethingElse</a>'

                     END AS DetailsUrl,

                     CASE

                        WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

                           THEN N.Status

                        WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

                           THEN AA.Status

                        ELSE 30

                     END AS Status,

                     AE.AccountID, N.ipaddress

              FROM Orion.AlertSuppression AS AlertSup

               

               

              LEFT OUTER JOIN Orion.Nodes AS N ON AlertSup.EntityUri = N.Uri

              LEFT OUTER JOIN Orion.APM.Application AS AA ON AlertSup.EntityUri = AA.Uri

              LEFT OUTER JOIN Orion.AuditingEvents AS AE ON AE.AuditEventMessage LIKE CONCAT('%', CASE

                        WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

                           THEN N.NodeName

                        WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

                           THEN AA.Name

                        ELSE 'Wrong'

                     END, '%') AND [EntityUri] LIKE CONCAT('%=', AE.NetObjectID)

              INNER JOIN Orion.AuditingActionTypes AS AT ON AE.ActionTypeID = AT.ActionTypeID

               

               

              WHERE [AT].ActionTypeDisplayName LIKE '%mute%'

                • Re: SQWL Query is valid on the SDK, but showing invalid as SW datasource
                  tdanner

                  In your query at the top of this thread, you have this line:

                   

                  LEFT OUTER JOIN Orion.NodesCustomProperties AS cp ON AlertSup.id = cp.nodeid

                   

                  This is no good because the Orion.AlertSuppression.ID property is not related to a NodeID. It's just the id number of that alert suppression record.

                   

                  You have already joined Orion.Nodes into the query with the alias "N". So you should be able to get to node custom properties via navigation properties like "N.CustomProperties.Whatever" without another explicit join.