9 Replies Latest reply on Aug 28, 2013 11:21 AM by joiellis

    Trouble with custom swql query in device view

    dmthames

      Hello,

       

      I'm attempting run a simple query (below) as a custom resource within a phone device view in vnqm, but getting "Error: A query to the SolarWinds Information Service failed"

      Any way I can get some more info on the error to determine if it's a network/firewall/permissions issue as opposed to an issue with the syntax?

      I've modified the query several different ways that work in report writer and sql studio, but none of those seem to take in the custom query request.

       

      select top 10

      voipccmphones.mac_address,
      voipccmphones.name,
      voipccmphones.description

      from orionnpm.dbo.voipccmphones

       

      Thanks!

        • Re: Trouble with custom swql query in device view
          dmthames

          As an update, I'm able to get the swql syntax to work from the swis utility as shown below, but it's still not working in the custom query. Uggh.

           

          SELECT Top 10 Description,  ID, IPAddress,  MACAddress, Name, Status

          FROM Orion.IpSla.CCMPhones

            • Re: Trouble with custom swql query in device view
              rsbomar

              I have tried without any luck to get any SWQL query to work using the custom query resource......  Does anyone have an answer?  Or can someone provide an example of a SWQL query that successfully runs from the custom query resource.

               

              Thanks

                • Re: Trouble with custom swql query in device view
                  tdanner

                  The custom query resource should work with any query that works in SWQL Studio. Have you tried something really simple like "SELECT Caption FROM Orion.Nodes" to make sure something else isn't broken?

                  • Re: Trouble with custom swql query in device view
                    tom.rybka

                    Unfortunately, there are two issues connected with your query (and we already track them as bugs):

                    1. Please, remove the TOP clause which is currently not processed correctly. However, the resource supports paging of large results and allows you to specify how many rows you wish to display on a single page.
                    2. The Description property doesn't sorting correctly (which the resource applies on the first output column automatically, if no explicit ordering appears in the query). However, there is a workaround - you can try to use ToString(Description) instead. This activates a additional processing allowing it proceed.
                      Note: You need to provide an alias for the column, as it is required by SWQL for any computed columns.

                     

                    Summarized, please try to use the following query:

                    SELECT ToString(Description) as Description,  ID, IPAddress,  MACAddress, Name, Status

                    FROM Orion.IpSla.CCMPhones

                    • Re: Trouble with custom swql query in device view
                      joiellis

                      We've been struggling with similar questions regarding Custom Resources. We wanted to create some device-specific views and list some specific data about each individual node.  So far, the ONLY resource object we've found that will actually use ${NodeID} as documented is the Custom Query that wants SWQL.   Here is a working example I just developed this afternoon.

                       

                      edit_custom_resource.PNG

                       

                      topology_pollers.PNG

                        • Re: Trouble with custom swql query in device view
                          tdanner

                          I'm afraid I don't understand your question. Is there a resource that you expected to recognize ${NodeID} that would not?

                            • Re: Trouble with custom swql query in device view
                              joiellis

                              Yes: All of them that offer a 'Filter:' field in the edit page, fail.  Either the filter is ignored completely and we get the whole table being queried, or we get no results at all.  If we use blatantly broken S(W)QL syntax, we get an error.

                               

                              I was responding to the previous question of Apr 11 asking for a working example of a SWQL query that works from a custom query.  He wanted a working example so I provided the only one I've created so far.

                               

                              So far this is the only custom resource we've found that actually works as documented, as it doesn't provide a 'Filter:' fields.  None of the other custom resources that provide a 'Filter:' in the edit page work, no matter what syntax we use... even the documented examples fail.  We gave up trying to figure it out, so I opened a support case last week and all I've gotten back is a request to 'prove it doesn't work. Send screenshots.'  So, I was creating a batch of failing screenshots and stumbled across the above workaround.

                               

                              We've already concluded that nothing created with Report Writer will work with a Filter from a Custom Resource.

                                • Re: Trouble with custom swql query in device view
                                  dmthames

                                  I haven't messed with it in a while, but I remember it being very 'hit or miss'. Kinda like some tables were available to swql requests and some were not.

                                   

                                  I ran into a separate issue relative to that specific custom resource, but have a few other things I'd like to try out. I'll update if anything interesting pops up.

                                   

                                  Thanks.

                                    • Re: Trouble with custom swql query in device view
                                      joiellis

                                      I've run into the problem you mention, dmthames, like some tables/columns available to the SDK and some not... all of those, so far, have been differences between the V2 schema and the V3 schema.  IE, all NCM tables are, as I understand, availalble only to V2 connections, but a lot of other stuff isn't available in V2, ie the column that provides switch firmware version, and the Metadata table that describes Verb parameters.  I'm in the process of expanding my perl library to automatically swtich between V2 and V3 depending upon my upper level method called.  It's a Real PITA, because we're starting to do a lot of NCM-related stuff which demands V2 to access it.

                                       

                                      We noticed early on that if you go into the SQL server directly, the format of many of the tables is different than what is documented with the SDK.  We've decided to, as much as possible, address our in-house scripting to SWQL, as a layer of insurance against SQL schema changes.