cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 10

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

Jump to solution

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="https://thwack.solarwinds.com/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.

0 Kudos
1 Solution

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.

View solution in original post

0 Kudos
3 Replies
Level 19

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.

0 Kudos

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="https://thwack.solarwinds.com/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%'

0 Kudos

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.

View solution in original post

0 Kudos