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

SWQL Query: Select distinct will result in an information service failed

Jump to solution

Hi, can someone tell me why, if I run this query under a "Query Resource", it will fail with an error: Error: A query to the SolarWinds Information Service failed.  However, if I remove the select "DISTINCT", it will run the query?

Can you not use the "distinct"?  I know I can run this as a custom table, but it just looks better with the query resource view.

SELECT distinct

      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=%/Interfaces/InterfaceID=%'

             THEN [I].[FullName]

          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].[Status]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[Status]

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

             THEN [AA].[Status]

          ELSE 30

       END AS [Status],

       [AE].AccountID

FROM Orion.AlertSuppression AS [AlertSup]

LEFT OUTER JOIN Orion.Nodes AS [N]

   ON [AlertSup].[EntityUri] = [N].[Uri]

LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]

   ON [AlertSup].[EntityUri] = [I].[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=%/Interfaces/InterfaceID=%'

             THEN [I].[InterfaceCaption]

          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
1 Solution

Accepted Solutions
Highlighted

Re: SWQL Query: Select distinct will result in an information service failed

Jump to solution

Assuming your query works in SWQL studio but not on the web console then try throwing an "order by" at the end.  The way the web console parses certain commands requires you to specify the sorting it should use.

- Marc Netterfield, Github

View solution in original post

0 Kudos
1 Reply
Highlighted

Re: SWQL Query: Select distinct will result in an information service failed

Jump to solution

Assuming your query works in SWQL studio but not on the web console then try throwing an "order by" at the end.  The way the web console parses certain commands requires you to specify the sorting it should use.

- Marc Netterfield, Github

View solution in original post

0 Kudos