21 Replies Latest reply on Sep 14, 2016 9:18 AM by xtraspecialj

    SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio

    Raul Gonzalez

      Hello World

       

      before the upgrade to NPM v12 we were using some custom queries in order to extract IOPs per process from the database and create charts and tables with that. Everything was working beautifully until the upgrade to NPM v12, since then the query is not working (error message) on any web resources, however it still works in SWQL studio.

       

      I've been troubleshooting the query and the issue is in the 'in (Select...)' statement. It works fine if you add values inside the in (example pec.componentid in ('10','20','30'...) but it doesn't with another WHERE clause.

       

       

      It would be nice if we could get a response from the development team and if they could confirm that SWQL queries that are working in SWQL studio will also be working on the web interface? Otherwise, what's the point of SWQL studio? (yes, i know that there are lots of different things you can do with it, but this is one of the main ones, at least for a lot of SW users).

       

       

      Query:

       

      SELECT pec.componentid, ( pec.avgioreadoperationspersec + pec.avgiowriteoperationspersec )
      AS avgiops, pec.timestamp, cst.componentprocessname, cst.componentprocessname + ' from ' + cst.componentname + ' on ' + n.caption
      AS fullname
      FROM orion.apm.processevidencechart pec inner join orion.apm.currentstatistics cst
      ON cst.componentid = pec.componentid inner join orion.apm.component comp
      ON comp.componentid = pec.componentid inner join orion.apm.applicationalert appl
      ON appl.id = comp.applicationid inner join orion.nodes n
      ON n.nodeid = appl.nodeid

      WHERE pec.timestamp> ${FromTime} AND pec.timestamp< ${ToTime}
      AND pec.componentid IN

       

      (

      SELECT top 10 componentid
      FROM orion.apm.processevidencechart pec inner join orion.apm.component comp
      ON comp.componentid = pec.componentid WHERE pec.timestamp> ${FromTime}
      AND pec.timestamp< ${ToTime} AND avgioreadoperationspersec IS NOT NULL
      AND avgiowriteoperationspersec IS NOT NULL AND comp.applicationid = ${applicationid}
      GROUP BY componentid
      ORDER BY avg ( avgioreadoperationspersec + avgiowriteoperationspersec )

      DESC

      )

       

      The problem also is that we use this structure in order to extract other type of information....

       

      Regards

       

      Raul

       


        • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
          tdanner

          Some of the resources modify the query rather than using it as you enter it. I'm guessing that something about these modifications changed and this is what is breaking. Without knowing the specific error you are seeing, it is hard to know what you should do to get this working again. One guess: some resources append a "WITH ROWS" clause to the query for paging. This only works if the query ends with an "ORDER BY" clause. Try adding an ORDER BY clause to your query and see if that helps.

           

          To get the specific error, look in the SWIS log. C:\ProgramData\SolarWinds\InformationService\v3\Orion.InformationService.log.

            • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
              Raul Gonzalez

              Hi

               

              thank you very much tdanner, but it didnt work. This is the information service log:

               

              016-07-19 11:58:54,299 [123] ERROR SolarWinds.InformationService.Core.InformationService - w3wp| /LM/W3SVC/2/ROOT-1-131133199080507903| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/ValidateQuery  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

              SolarWinds.Data.Query.ParserException: Cannot resolve property ExtendedData_OriginServerID

                 at SolarWinds.Data.Query.Processor.SemanticChecker.HandleNonQualifiedPropertyName(EntityExpression entityExpression)

                 at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(ColumnExpression column)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(ColumnExpression column)

                 at SolarWinds.Data.Query.Swql.ParseTreeDuplicatorNoState.Visit(SelectClause select)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SelectStatement statement)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state, String replaceStatementTagWith)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SourceExpression sourceExpression)

                 at SolarWinds.Data.Query.Swql.ParseTreeDuplicatorNoState.Visit(FromClause from)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SelectStatement statement)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state, String replaceStatementTagWith)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state)

                 at SolarWinds.Data.Query.Processor.PreProcessor.Process(SelectStatement selectStatement, PreProcessorState& preProcessorState)

                 at SolarWinds.Data.Query.Engine.QueryProcessor.ProcessInternal(String query, IQueryExecutionContext context)

                 at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query, String username, Boolean federationEnabled, Func`1 buildQueryPlan)

                 at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

               

              Any clue?

                • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                  derhally

                  I don't currently have a setup to test this, but does the query work in the resource if you remove IN expression on the where clause?

                    • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                      Raul Gonzalez

                      Hi

                       

                      it works if i removed the IN or if i leave the in but remove the select inside the IN. For example, this woukd work:

                       

                       

                      SELECT pec.componentid, ( pec.avgioreadoperationspersec + pec.avgiowriteoperationspersec )

                      AS avgiops, pec.timestamp, cst.componentprocessname, cst.componentprocessname + ' from ' + cst.componentname + ' on ' + n.caption

                      AS fullname

                      FROM orion.apm.processevidencechart pec inner join orion.apm.currentstatistics cst

                      ON cst.componentid = pec.componentid inner join orion.apm.component comp

                      ON comp.componentid = pec.componentid inner join orion.apm.applicationalert appl

                      ON appl.id = comp.applicationid inner join orion.nodes n

                      ON n.nodeid = appl.nodeid

                       

                      WHERE pec.timestamp> ${FromTime} AND pec.timestamp< ${ToTime}

                      AND pec.componentid IN

                      ('10','11','20','30')

                        • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                          derhally

                          I apologize for not actually testing this out, but does this work for you if the subquery in the where clause is converted to inner join like

                           

                          SELECT pec.componentid, ( pec.avgioreadoperationspersec + pec.avgiowriteoperationspersec )
                          AS avgiops, pec.timestamp, cst.componentprocessname, cst.componentprocessname + ' from ' + cst.componentname + ' on ' + n.caption
                          AS fullname
                          FROM orion.apm.processevidencechart pec inner join orion.apm.currentstatistics cst
                          ON cst.componentid = pec.componentid inner join orion.apm.component comp
                          ON comp.componentid = pec.componentid inner join orion.apm.applicationalert appl
                          ON appl.id = comp.applicationid inner join orion.nodes n
                          ON n.nodeid = appl.nodeid
                          inner join (
                            SELECT top 10 componentid
                            FROM orion.apm.processevidencechart pec inner join orion.apm.component comp
                            ON comp.componentid = pec.componentid
                            WHERE pec.timestamp> ${FromTime}
                              AND pec.timestamp< ${ToTime}
                              AND avgioreadoperationspersec IS NOT NULL
                              AND avgiowriteoperationspersec IS NOT NULL AND comp.applicationid = ${applicationid}
                            GROUP BY componentid
                            ORDER BY avg ( avgioreadoperationspersec + avgiowriteoperationspersec ) DESC
                          ) AS X ON pec.componentid = X.componentid
                          WHERE pec.timestamp> ${FromTime} AND pec.timestamp< ${ToTime}
                          
                            • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                              KMSigma

                              derhally - I tried that SWQL query in the SWQL Studio by commenting out the ${ToTime} and ${FromTime} (it works!) and within the Custom Chart (fails with * Query is not valid) and Custom Table (fails with * Query is not valid).

                              My Core version is: 2016.1.5300.0 (as reported by...)

                              SELECT Name, Version, IsActive
                              FROM Orion.Module
                              WHERE Name = 'Core'
                              
                              NameVersionIsActive
                              Core2016.1.5300.0True
                                • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                                  derhally

                                  after you refresh the page, can you look in the SWIS log ( C:\ProgramData\SolarWinds\InformationService\v3\Orion.InformationService.log) and post the error you see at the end .

                                   

                                  We have identified the bug and trying to identify a workaround for you.

                                    • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                                      Raul Gonzalez

                                      Hi

                                       

                                      trying with the inner join, this is the error message:

                                       

                                       

                                      2016-07-26 09:26:52,396 [83] ERROR SolarWinds.InformationService.Core.InformationService - w3wp| /LM/W3SVC/2/ROOT-1-131139939442796689| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/GetQueryResults  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

                                      System.Data.SqlClient.SqlException (0x80131904): Column 'dbo.Sites.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

                                         at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

                                         at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

                                         at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

                                         at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

                                         at System.Data.SqlClient.SqlDataReader.get_MetaData()

                                         at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

                                         at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

                                         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

                                         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

                                         at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

                                         at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

                                         at SolarWinds.InformationService.DataProviders.SqlQueryRelation.<GetEnumerator>d__8.MoveNext()

                                         at SolarWinds.Data.Query.PhysicalQueryPlan.ProviderPassThroughScanOp.<GetEnumeratorInternal>d__5.MoveNext()

                                         at SolarWinds.Data.Query.PhysicalQueryPlan.ProjectOp.<GetEnumeratorInternal>d__7.MoveNext()

                                         at SolarWinds.Data.Query.PhysicalQueryPlan.PhysicalQueryPlan.<GetEnumerator>d__4.MoveNext()

                                         at SolarWinds.InformationService.Serialization.XmlResponseSerializer..ctor(IQueryResultReader reader, Boolean includeQueryPlan, IQueryStatisticsContext statistics)

                                         at SolarWinds.InformationService.Serialization.XmlResponseSerializerSimple..ctor(IQueryResultReader reader, Boolean includeQueryPlan, IQueryStatisticsContext statistics)

                                         at SolarWinds.InformationService.Serialization.XmlSerializer.GetSerializer()

                                         at SolarWinds.InformationService.Serialization.XmlSerializer.PrepareSerialize(IQueryExpr query, ISchema schema, IQueryResultReader reader)

                                         at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

                                      ClientConnectionId:b8c307da-6a41-40b6-8806-7fd6bfaf3601

                                       

                                       

                                      With the 'IN' clause:

                                       

                                      2016-07-26 09:28:48,167 [106] ERROR SolarWinds.InformationService.Core.InformationService - w3wp| /LM/W3SVC/2/ROOT-1-131139939442796689| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/GetQueryResults  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

                                      SolarWinds.Data.Query.ParserException: Cannot resolve property ExtendedData_OriginServerID

                                         at SolarWinds.Data.Query.Processor.SemanticChecker.HandleNonQualifiedPropertyName(EntityExpression entityExpression)

                                         at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(ColumnExpression column)

                                         at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(ColumnExpression column)

                                         at SolarWinds.Data.Query.Swql.ParseTreeDuplicatorNoState.Visit(SelectClause select)

                                         at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SelectStatement statement)

                                         at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state, String replaceStatementTagWith)

                                         at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SourceExpression sourceExpression)

                                         at SolarWinds.Data.Query.Swql.ParseTreeDuplicatorNoState.Visit(FromClause from)

                                         at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SelectStatement statement)

                                         at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state, String replaceStatementTagWith)

                                         at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state)

                                         at SolarWinds.Data.Query.Processor.PreProcessor.Process(SelectStatement selectStatement, PreProcessorState& preProcessorState)

                                         at SolarWinds.Data.Query.Engine.QueryProcessor.ProcessInternal(String query, IQueryExecutionContext context)

                                         at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query, String username, Boolean federationEnabled, Func`1 buildQueryPlan)

                                         at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

                                       

                                       

                                      Thank you very much.

                                       

                                      Raul

                                      • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                                        KMSigma

                                        2016-07-27 16:59:05,655 [118] ERROR SolarWinds.InformationService.Core.InformationService - w3wp| /LM/W3SVC/1/ROOT-1-131140608029075160| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/GetQueryResults  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

                                        System.Data.SqlClient.SqlException (0x80131904): Column 'dbo.Sites.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

                                           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

                                           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

                                           at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

                                           at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

                                           at System.Data.SqlClient.SqlDataReader.get_MetaData()

                                           at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

                                           at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

                                           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

                                           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

                                           at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

                                           at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

                                           at SolarWinds.InformationService.DataProviders.SqlQueryRelation.<GetEnumerator>d__8.MoveNext()

                                           at SolarWinds.Data.Query.PhysicalQueryPlan.ProviderPassThroughScanOp.<GetEnumeratorInternal>d__5.MoveNext()

                                           at SolarWinds.Data.Query.PhysicalQueryPlan.ProjectOp.<GetEnumeratorInternal>d__7.MoveNext()

                                           at SolarWinds.Data.Query.PhysicalQueryPlan.PhysicalQueryPlan.<GetEnumerator>d__4.MoveNext()

                                           at SolarWinds.InformationService.Serialization.XmlResponseSerializer..ctor(IQueryResultReader reader, Boolean includeQueryPlan, IQueryStatisticsContext statistics)

                                           at SolarWinds.InformationService.Serialization.XmlResponseSerializerSimple..ctor(IQueryResultReader reader, Boolean includeQueryPlan, IQueryStatisticsContext statistics)

                                           at SolarWinds.InformationService.Serialization.XmlSerializer.GetSerializer()

                                           at SolarWinds.InformationService.Serialization.XmlSerializer.PrepareSerialize(IQueryExpr query, ISchema schema, IQueryResultReader reader)

                                           at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

                                        ClientConnectionId:f0d0019b-2801-4124-aedc-f64049cc6583

                                        Error Number:8120,State:1,Class:16

                                        2016-07-27 16:59:06,624 [3] ERROR SolarWinds.InformationService.DataProviders.SqlQueryRelation - w3wp| /LM/W3SVC/1/ROOT-1-131140608029075160| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/GetQueryResults  An SqlException occurred.

                                        Message: Column 'dbo.Sites.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

                                        Query:

                                        SET DATEFIRST 7;

                                        SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

                                        SELECT TOP (15) [T1].[C8] AS C1, [T1].[C9] AS C2, [T1].[C10] AS C3, [T1].[C11] AS C4, [T1].[C12] AS C5, [T1].[C7] AS C6

                                        FROM (SELECT [T2].[C13] AS C7, [T3].[ComponentID] AS C8, [T3].[AvgIOReadOperationsPerSec] + [T3].[AvgIOWriteOperationsPerSec] AS C9, [T3].[TimeStamp] AS C10, [T4].[ComponentProcessName] AS C11, [T4].[ComponentProcessName] + ' from ' + [T4].[ComponentName] + ' on ' + [T5].[Caption] AS C12

                                        FROM dbo.APM_ProcessEvidenceChart AS T3

                                        INNER JOIN dbo.APM_CurrentStatistics AS T4 ON [T4].[ComponentID] = [T3].[ComponentID]

                                        INNER JOIN

                                        (

                                        SELECT C.ID, C.Name, C.ShortName, C.ComponentType, C.ApplicationID, C.TemplateID, C.ComponentOrder, C.ApplicationItemID, CD.ComponentEvidenceType

                                                FROM dbo.APM_Component AS C

                                                INNER JOIN dbo.APM_ComponentDefinition AS CD ON C.ComponentType = CD.ComponentType

                                                LEFT JOIN dbo.APM_ComponentTemplate CT ON C.TemplateID = CT.ID

                                                WHERE ISNULL(CT.VisibilityMode, 0) = 0 OR (CT.VisibilityMode = 2 AND C.ApplicationItemID IS NOT NULL) OR CT.VisibilityMode = 3

                                        ) AS T6 ON [T6].[ID] = [T3].[ComponentID]

                                        INNER JOIN dbo.APM_ApplicationAlertsData AS T7 ON [T7].[ID] = [T6].[ApplicationID]

                                        INNER JOIN dbo.NodesData AS T5 ON [T5].[NodeID] = [T7].[NodeID]

                                        INNER JOIN (SELECT TOP (10) [T8].[ID] AS C13, [T9].[ComponentID] AS C14

                                        FROM dbo.APM_ProcessEvidenceChart AS T9

                                        LEFT JOIN dbo.Sites AS T8 ON 0 = [T8].[SiteID]

                                        INNER JOIN

                                        (

                                        SELECT C.ID, C.Name, C.ShortName, C.ComponentType, C.ApplicationID, C.TemplateID, C.ComponentOrder, C.ApplicationItemID, CD.ComponentEvidenceType

                                                FROM dbo.APM_Component AS C

                                                INNER JOIN dbo.APM_ComponentDefinition AS CD ON C.ComponentType = CD.ComponentType

                                                LEFT JOIN dbo.APM_ComponentTemplate CT ON C.TemplateID = CT.ID

                                                WHERE ISNULL(CT.VisibilityMode, 0) = 0 OR (CT.VisibilityMode = 2 AND C.ApplicationItemID IS NOT NULL) OR CT.VisibilityMode = 3

                                        ) AS T10 ON [T10].[ID] = [T9].[ComponentID]

                                        WHERE ([T9].[AvgIOReadOperationsPerSec] IS NOT NULL) AND ([T9].[AvgIOWriteOperationsPerSec] IS NOT NULL) AND [T9].[TimeStamp] > '2016-07-27T15:59:02' AND [T9].[TimeStamp] < '2016-07-27T16:59:02' AND [T10].[ApplicationID] = [T10].[ApplicationID]

                                        GROUP BY [T9].[ComponentID]

                                        ORDER BY avg([T9].[AvgIOReadOperationsPerSec] + [T9].[AvgIOWriteOperationsPerSec]) DESC) AS T2 ON [T3].[ComponentID] = [T2].[C14]

                                        WHERE [T3].[TimeStamp] > '2016-07-27T15:59:02' AND [T3].[TimeStamp] < '2016-07-27T16:59:02') AS T1

                                        2016-07-27 16:59:06,639 [3] ERROR SolarWinds.InformationService.Core.InformationService - w3wp| /LM/W3SVC/1/ROOT-1-131140608029075160| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/GetQueryResults  Exception running query:

                                        SELECT  TOP 15  [data].[componentid] AS [ComponentID],[data].[avgiops] AS [avgiops],[data].[timestamp] AS [TimeStamp],[data].[componentprocessname] AS [ComponentProcessName],[data].[fullname] AS [fullname],data.ExtendedData_OriginServerID

                                        FROM (SELECT ExtendedData_OriginServerID, pec.componentid, (pec.avgioreadoperationspersec + pec.avgiowriteoperationspersec) AS avgiops, pec.timestamp, cst.componentprocessname, ((((cst.componentprocessname + ' from ') + cst.componentname) + ' on ') + n.caption) AS fullname

                                        FROM orion.apm.processevidencechart AS pec

                                        INNER JOIN orion.apm.currentstatistics AS cst ON (cst.componentid = pec.componentid)

                                        INNER JOIN orion.apm.component AS comp ON (comp.componentid = pec.componentid)

                                        INNER JOIN orion.apm.applicationalert AS appl ON (appl.id = comp.applicationid)

                                        INNER JOIN orion.nodes AS n ON (n.nodeid = appl.nodeid)

                                        INNER JOIN

                                        (

                                        SELECT TOP 10 pec.OrionSite.ID AS ExtendedData_OriginServerID, componentid

                                        FROM orion.apm.processevidencechart AS pec

                                        INNER JOIN orion.apm.component AS comp ON (comp.componentid = pec.componentid)

                                        WHERE (((avgioreadoperationspersec IS NOT NULL) AND (avgiowriteoperationspersec IS NOT NULL)) AND (((pec.timestamp > '2016-07-27T15:59:02') AND (pec.timestamp < '2016-07-27T16:59:02')) AND (comp.applicationid = ${applicationid})))

                                        GROUP BY componentid

                                        ORDER BY avg((avgioreadoperationspersec + avgiowriteoperationspersec)) DESC

                                        ) AS X ON (pec.componentid = X.componentid)

                                        WHERE ((pec.timestamp > '2016-07-27T15:59:02') AND (pec.timestamp < '2016-07-27T16:59:02'))) AS data RETURN XML RAW

                                        2016-07-27 16:59:06,639 [3] ERROR SolarWinds.InformationService.Core.InformationService - w3wp| /LM/W3SVC/1/ROOT-1-131140608029075160| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/GetQueryResults  Exception for Operation: <s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing">

                                          <s:Header>

                                            <a:Action s:mustUnderstand="1">http://schemas.solarwinds.com/2007/08/informationservice/InformationService/QueryXml</a:Action>

                                            <a:MessageID>urn:uuid:c32367d8-7a09-4e52-a607-934f1a40ebea</a:MessageID>

                                            <a:ReplyTo>

                                              <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>

                                            </a:ReplyTo>

                                            <Impersonation xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                                              <TargetUsername>admin</TargetUsername>

                                            </Impersonation>

                                            <i18n xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                                              <Culture>en-US</Culture>

                                            </i18n>

                                            <SwisSettings xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">

                                              <AppendErrors>true</AppendErrors>

                                              <ApplicationTag>w3wp| /LM/W3SVC/1/ROOT-1-131140608029075160| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/GetQueryResults</ApplicationTag>

                                              <DataProviderTimeout>PT10M</DataProviderTimeout>

                                              <FederationEnabled>false</FederationEnabled>

                                            </SwisSettings>

                                            <IsBase64EncodingAccepted xmlns="http://schemas.solarwinds.com/2007/08/informationservice">true</IsBase64EncodingAccepted>

                                            <a:To s:mustUnderstand="1">net.tcp://localhost:17777/SolarWinds/InformationService/v3/Orion/certificate</a:To>

                                          </s:Header>

                                          <s:Body>

                                            <QueryXml xmlns="http://schemas.solarwinds.com/2007/08/informationservice">

                                              <query>SELECT  TOP 15  [data].[componentid] AS [ComponentID],[data].[avgiops] AS [avgiops],[data].[timestamp] AS [TimeStamp],[data].[componentprocessname] AS [ComponentProcessName],[data].[fullname] AS [fullname],data.ExtendedData_OriginServerID

                                        FROM (SELECT ExtendedData_OriginServerID, pec.componentid, (pec.avgioreadoperationspersec + pec.avgiowriteoperationspersec) AS avgiops, pec.timestamp, cst.componentprocessname, ((((cst.componentprocessname + ' from ') + cst.componentname) + ' on ') + n.caption) AS fullname

                                        FROM orion.apm.processevidencechart AS pec

                                        INNER JOIN orion.apm.currentstatistics AS cst ON (cst.componentid = pec.componentid)

                                        INNER JOIN orion.apm.component AS comp ON (comp.componentid = pec.componentid)

                                        INNER JOIN orion.apm.applicationalert AS appl ON (appl.id = comp.applicationid)

                                        INNER JOIN orion.nodes AS n ON (n.nodeid = appl.nodeid)

                                        INNER JOIN

                                        (

                                        SELECT TOP 10 pec.OrionSite.ID AS ExtendedData_OriginServerID, componentid

                                        FROM orion.apm.processevidencechart AS pec

                                        INNER JOIN orion.apm.component AS comp ON (comp.componentid = pec.componentid)

                                        WHERE (((avgioreadoperationspersec IS NOT NULL) AND (avgiowriteoperationspersec IS NOT NULL)) AND (((pec.timestamp &gt; '2016-07-27T15:59:02') AND (pec.timestamp &lt; '2016-07-27T16:59:02')) AND (comp.applicationid = ${applicationid})))

                                        GROUP BY componentid

                                        ORDER BY avg((avgioreadoperationspersec + avgiowriteoperationspersec)) DESC

                                        ) AS X ON (pec.componentid = X.componentid)

                                        WHERE ((pec.timestamp &gt; '2016-07-27T15:59:02') AND (pec.timestamp &lt; '2016-07-27T16:59:02'))) AS data RETURN XML RAW</query>

                                              <parameters>

                                              </parameters>

                                            </QueryXml>

                                          </s:Body>

                                        </s:Envelope>

                                        2016-07-27 16:59:06,639 [3] ERROR SolarWinds.InformationService.Core.InformationService - w3wp| /LM/W3SVC/1/ROOT-1-131140608029075160| SolarWinds.Orion.Core.Reporting.DataTableQueryCustomSWQL.Query()| /Orion/Services/DataSourceManagement.asmx/GetQueryResults  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

                                        System.Data.SqlClient.SqlException (0x80131904): Column 'dbo.Sites.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

                                           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

                                           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

                                           at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

                                           at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

                                           at System.Data.SqlClient.SqlDataReader.get_MetaData()

                                           at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

                                           at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

                                           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

                                           at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

                                           at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

                                           at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

                                           at SolarWinds.InformationService.DataProviders.SqlQueryRelation.<GetEnumerator>d__8.MoveNext()

                                           at SolarWinds.Data.Query.PhysicalQueryPlan.ProviderPassThroughScanOp.<GetEnumeratorInternal>d__5.MoveNext()

                                           at SolarWinds.Data.Query.PhysicalQueryPlan.ProjectOp.<GetEnumeratorInternal>d__7.MoveNext()

                                           at SolarWinds.Data.Query.PhysicalQueryPlan.PhysicalQueryPlan.<GetEnumerator>d__4.MoveNext()

                                           at SolarWinds.InformationService.Serialization.XmlResponseSerializer..ctor(IQueryResultReader reader, Boolean includeQueryPlan, IQueryStatisticsContext statistics)

                                           at SolarWinds.InformationService.Serialization.XmlResponseSerializerSimple..ctor(IQueryResultReader reader, Boolean includeQueryPlan, IQueryStatisticsContext statistics)

                                           at SolarWinds.InformationService.Serialization.XmlSerializer.GetSerializer()

                                           at SolarWinds.InformationService.Serialization.XmlSerializer.PrepareSerialize(IQueryExpr query, ISchema schema, IQueryResultReader reader)

                                           at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

                                        ClientConnectionId:f0d0019b-2801-4124-aedc-f64049cc6583

                                        Error Number:8120,State:1,Class:16

                          • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                            ET

                            Hi,

                            I'm wondering whether there's some support ticket to which we can bind this issue?

                             

                            Thanks

                            ET;

                            • Re: SWQL queries not working in Custom Query and Custom Reports since NPM v12 but still working in SWQL studio
                              Craig Norborg

                              Was there a resolution to this?  Just upgraded my server to NPM 12 and running into what looks like the same issue.  Even tried redoing the query a different way and still getting the "Query not valid" and no indication as to why it isn't.   Not seeing anything in my Orion.InformationService.log either, although mine is in C:\ProgramData\SolarWinds\InformationService\v3.0 rather than where specified above...