2 Replies Latest reply on Jan 22, 2014 9:40 AM by scottfraley

    Weird / unexpected errors in SWQL Studio 1.8

    scottfraley

      I'm trying to come up with a query that will show me some performance data from the Orion.NPM.InterfaceTraffic table. So, I connect to the server using the "Orion v3" server type [so that it will use the Soap interface/channel @ 17777]. Then, when I run the following query

       

      SELECT TOP 100 IFT.InterfaceID, Sum(IFT.InAveragebps) AS SumOfInAverageBPS

      FROM

        Orion.NPM.InterfaceTraffic IFT

      WHERE

        IFT.DateTime >= '2014/01/21' AND IFT.NodeID = '712'

      --GROUP BY                                            commented out because I simply cannot seem to get a GROUP BY to exec without getting one of the error messages I'm bringing up in this post.

      --  IFT.InterfaceID --Averagebps

      ORDER BY

        IFT.DateTime

       

      ...and I get the following error.

      ---------------------------

      SWQL Studio

      ---------------------------

      The communication object, System.ServiceModel.Security.SecuritySessionClientSettings`1+ClientSecurityDuplexSessionChannel[System.ServiceModel.Channels.IDuplexSessionChannel], cannot be used for communication because it is in the Faulted state.

      ---------------------------

      OK  

      ---------------------------

       

      Of course my first thought is, ok, fine, cycle the dang thing and lets move on. Apparently SWQL Studio isn't capable of that. So, I restart and connect setting the Server Type to "Orion (v3) over HTTPS" [so that it will use the REST interface / channel @ 17778]. Then I exec the same query, but get a different (and still apparently NOTHING TO DO WITH my query being ok or bad) error.. as follows;

       

      ---------------------------

      SWQL Studio

      ---------------------------

      An error occurred while receiving the HTTP response to https://lsdorion:17778/SolarWinds/InformationService/v3/OrionBasic. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details.

      ---------------------------

       

      I guess the question is, why can't SWQL Studio simply tell me I've got a bad query? And further, is there some document, or section of this forum that shows/has examples of some complex queries so that perhaps I could extrapolate and get my query(s) to work?

       

      Finally, while playing with all this, I thought hey, why don't I save my query to a file so I can load it later and won't lose it! So I did. But when I try to open said file, I get a nice little dialog entitled "NullReferenceException" (an exception which I'm sure us C# devs are all to familiar with) and telling me that an "Object reference not set to an instance of an object." When I saved the file (named "Orion.NPM.InterfaceTraffic InAveragebps query.swql"), SWQL Studio didn't add any extension, so after trying to open the file without an extension, I got the same error, so that's when I decided to try sticking the .swql extension on it. Unfortunately that did not help with the null ref exception.

       

      Any help / thoughts appreciated.

       

       

      -Scott Fraley

        • Re: Weird / unexpected errors in SWQL Studio 1.8
          Jan Pelousek

          Hello Scott,

           

          I'm not sure about usecase of your query (used aggregation function doesn't make much sense to me for such report), however this should work for you:

           

          SELECT TOP 100 IFT.InterfaceID, Sum(IFT.InAveragebps) AS SumOfInAverageBPS

          FROM

            Orion.NPM.InterfaceTraffic IFT

          WHERE

            IFT.DateTime >= '2014/01/21' AND IFT.NodeID = '712'

          GROUP BY IFT.InterfaceID, IFT.DateTime

          ORDER BY

            IFT.DateTime

           

          The reason is simple. You can't mix aggregate functions with other not-aggregated columns without usage of them in GROUP BY statement. Good examples are e.g. here .  The error is returned directly from SQL server, not failing at the SWIS endpoint. More informations about the error you can always find in the Orion.InformationService.log (default location for SWISv3 logs is c:\Users\All Users\Solarwinds\InformationService\v3.0\).

          I hope it helped.

           

          Regards,

           

          Honza

            • Re: Weird / unexpected errors in SWQL Studio 1.8
              scottfraley

              Honza,

               

              First, I will admit to plenty of ignorance of which columns do/contain what, but if my queries aren't "failing at the SWIS endpoint," as you put it, then why do I keep getting errors that make it look like that? Why not a more helpful "bad query at <problem_word_or_token>" kind of message?

               

              Anyhoo, that query does work, so thanks for that. I was just experimenting really, trying to see what would and wouldn't work because while we have 'documentation' that shows the schema's to the 80jillion tables ya'll have, there isn't much discussion as to which do what in the grand scheme of things.

               

              Will definitely be checking out group by examples you pointed me to. (Maybe a refresher isn't a bad idea!)    Although, I am surprised about the DateTime field in the Group By NOT being in the select. I'd have sworn that anything in the GB HAD to be in the SELECT.

               

               

              Thanks,

              Scott