1 Reply Latest reply on May 2, 2017 10:01 PM by mesverrum

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

    timt

      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%'