SWQL error in custom query if no 'order by' statement

cboyce

Hi,

I have a SWQL query that works fine in SWQL Studio but when in a custom query it errors

SELECT 'Total Servers being Monitored' as [Type], count(ApplicationID) as [Number of Servers] FROM Orion.APM.Application where name = 'Windows Update Monitoring v2'

UNION All(SELECT '# of Servers Not Reporting' as [Type], count(StatusDescription) as [Number of Servers] FROM Orion.APM.Application where name = 'Windows Update Monitoring v2' and statusDescription = 'Down')

UNION All(SELECT '# of Servers Fully Patched' as [Type], count(StatusDescription) as [Number of Servers] FROM Orion.APM.Application where name = 'Windows Update Monitoring v2' and statusDescription = 'Up')

UNION All(SELECT '# of Servers with Patches but Waiting for Patching' as [Type], count(StatusDescription) as [Number of Servers] FROM Orion.APM.Component where name like 'Available critical updates' and statusDescription = 'Warning')

UNION All(SELECT '# of Servers not Patched in the last 1 months' as [Type], count(StatusDescription) as [Number of Servers] FROM Orion.APM.Component where name like 'Days passed from last Windows Update' and statusDescription = 'Warning')

UNION All(SELECT '# of Servers not Patched more than 2 months' as [Type], count(StatusDescription) as [Number of Servers] FROM Orion.APM.Component where name like 'Days passed from last Windows Update' and statusDescription = 'Critical')

The query errors with 'There was an error processing the request' and the OrionWeb log has the following.

2019-08-26 06:40:09,773 [149] (8) ERROR SolarWinds.Orion.Web.InformationService.InformationServiceProxy - (null)  no viable alternative at input '<EOF>' in Order By clause

System.ServiceModel.FaultException`1[SolarWinds.InformationService.Contract2.InfoServiceFaultContract]: RunQuery failed, check fault information.

no viable alternative at input '<EOF>' in Order By clause (Fault Detail is equal to InfoServiceFaultContract [ SolarWinds.Data.SWQL.SWQLSyntaxException: no viable alternative at input '<EOF>' in Order By clause ---> Antlr.Runtime.NoViableAltException: A recognition error occurred.

   at SolarWinds.Data.SWQL.Parser.SWQLParser.negatedExpression()

   --- End of inner exception stack trace ---

   at SolarWinds.Data.SWQL.Parser.SWQLParser.negatedExpression()

   at SolarWinds.Data.SWQL.Parser.SWQLParser.logicalAndExpression()

   at SolarWinds.Data.SWQL.Parser.SWQLParser.logicalOrExpression()

   at SolarWinds.Data.SWQL.Parser.SWQLParser.expression()

   at SolarWinds.Data.SWQL.Parser.SWQLParser.orderElement()

   at SolarWinds.Data.SWQL.Parser.SWQLParser.orderByClause()

   at SolarWinds.Data.SWQL.Parser.SWQLParser.queryRule()

   at SolarWinds.Data.SWQL.Parser.SWQLParser.selectStatement()

   at SolarWinds.Data.SWQL.Parser.SWQLParser.statement()

   at SolarWinds.Data.SWQL.SWQLParser.Parse(String query)

   at SolarWinds.Data.Query.Engine.QueryProcessor.CompileQueryExecutionPlan(String qu...).

If I add an 'order by' statement it runs fine in the custom query, But I don't want it ordered.

Is it mandatory to have the output ordered.

Thanks

Craig Boyce

mesverrum

The way the custom query widget works it sometimes struggles with how it will handle paging in the GUI unless you tell it how to order things.

It's just how it is, its a known issue going way back, I just got into the habit of putting order by on all my custom swql widgets, whether i need them or not.