3 Replies Latest reply on Feb 7, 2017 5:11 AM by gangadhar.k

    Unsupported query by Orion SDK

    gangadhar.k

      Hi,

       

      We had tried to execute following query in Orion SDK to fetch report in below format :

       

      Unsupported Query :

      select (Select count(*) from NodesData) as Total,(Select count(*) from NodesData where Status=1) as Up,(Select count(*) from NodesData where Status=2) as Down,(Select count(*) from NodesData where Status=14) as Critical,(Select count(*) from NodesData where Status=3) as Warning,(Select count(*) from NodesData where Status=9) as Unmanaged

       

      Query Output :

            

      TotalUpDownCriticalWarningUnmanaged
      29762912340129

       

      If we try to execute above query, it compiles with following error :

       

      2016-08-19 13:40:47,523 [99] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio  Exception running query:

      select (Select count(*) from NodesData) as Total,(Select count(*) from NodesData where Status=1) as Up,(Select count(*) from NodesData where Status=2) as Down,(Select count(*) from NodesData where Status=14) as Critical,(Select count(*) from NodesData where Status=3) as Warning,(Select count(*) from NodesData where Status=9) as Unmanaged RETURN XML RAW

      2016-08-19 13:40:47,570 [99] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio  Exception for Operation: <s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">

        <s:Header>

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

          <a:MessageID>urn:uuid:d6ade743-fe91-4125-b85f-d0cd938d1cd2</a:MessageID>

          <a:ReplyTo>

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

          </a:ReplyTo>

          <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>SWQL Studio</ApplicationTag>

            <DataProviderTimeout>PT30S</DataProviderTimeout>

          </SwisSettings>

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

          <a:To s:mustUnderstand="1">net.tcp://IP address:17777/SolarWinds/InformationService/v3/Orion/ssl</a:To>

          <o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">

            <u:Timestamp u:Id="_0">

              <u:Created>2016-08-19T08:10:47.273Z</u:Created>

              <u:Expires>2016-08-19T08:15:47.273Z</u:Expires>

            </u:Timestamp>

            <c:SecurityContextToken u:Id="uuid-cfe6a0af-500d-4039-b122-eaedf7fd5e3c-35929" xmlns:c="http://schemas.xmlsoap.org/ws/2005/02/sc">

              <c:Identifier>urn:uuid:3f451289-b4bc-4c42-b546-cd618b9e4eda</c:Identifier>

            </c:SecurityContextToken>

            <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">

              <SignedInfo>

                <CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">

                </CanonicalizationMethod>

                <SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#hmac-sha1">

                </SignatureMethod>

                <Reference URI="#_0">

                  <Transforms>

                    <Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">

                    </Transform>

                  </Transforms>

                  <DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1">

                  </DigestMethod>

                  <DigestValue>+WGqDArof8MXMizkWXcFsgAYg1E=</DigestValue>

                </Reference>

              </SignedInfo>

              <SignatureValue>/4W440MDISBsLU5c82haD7alXFk=</SignatureValue>

              <KeyInfo>

                <o:SecurityTokenReference>

                  <o:Reference URI="#uuid-cfe6a0af-500d-4039-b122-eaedf7fd5e3c-35929">

                  </o:Reference>

                </o:SecurityTokenReference>

              </KeyInfo>

            </Signature>

          </o:Security>

        </s:Header>

        <s:Body>

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

            <query>select (Select count(*) from NodesData) as Total,(Select count(*) from NodesData where Status=1) as Up,(Select count(*) from NodesData where Status=2) as Down,(Select count(*) from NodesData where Status=14) as Critical,(Select count(*) from NodesData where Status=3) as Warning,(Select count(*) from NodesData where Status=9) as Unmanaged RETURN XML RAW</query>

            <parameters>

            </parameters>

          </QueryXml>

        </s:Body>

      </s:Envelope>

      2016-08-19 13:40:47,601 [99] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

      SolarWinds.Data.SWQL.SWQLSyntaxException: mismatched input 'RETURN' expecting 'FROM' ---> Antlr.Runtime.MismatchedTokenException: A recognition error occurred.

         at SolarWinds.Data.SWQL.Parser.SWQLParser.MismatchIsUnwantedToken(IIntStream input, Int32 ttype)

         at Antlr.Runtime.BaseRecognizer.RecoverFromMismatchedToken(IIntStream input, Int32 ttype, BitSet follow)

         at Antlr.Runtime.BaseRecognizer.Match(IIntStream input, Int32 ttype, BitSet follow)

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

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

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

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

         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.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)

        • Re: Unsupported query by Orion SDK
          equalswql

          Hello Ganga,

           

          Looking at the tables you referenced, this looks like an SQL Query. If you are using the Orion SDK, please try expanding the different entries (I would start with {} ORION) which will give you all the nested tables, then you can look at Orion.Nodes for example. SWQL does not allow the use of * for all fields, you have to specific the fields you would like to select. I would take a look at the Orion SDK wiki to understand how to format your query properly. In the meantime, here is your query updated in SWQL format:

           

           

          SELECT

          (Select count(NodeID) as n from Orion.Nodes) as Total,

          (Select count(NodeID) as n from Orion.Nodes where Status=1) as Up,

          (Select count(NodeID) as n from Orion.Nodes where Status=2) as Down,

          (Select count(NodeID) as n from Orion.Nodes where Status=14) as Critical,

          (Select count(NodeID) as n from Orion.Nodes where Status=3) as Warning,

          (Select count(NodeID) as n from Orion.Nodes where Status=9) as Unmanaged

           

          FROM Orion.Nodes

           

          notice that in each count, you have to name the column inside the expression (renamed as n) in order for it to be accepted.

           

          Thank you,

           

          =SWQL

          1 of 1 people found this helpful
          • Re: Unsupported query by Orion SDK
            gangadhar.k

            Hi,

             

            We have executed above query and found following results :

            Is it possible to make these numbers clickable, so that we can see which are these devices ?