7 Replies Latest reply on Oct 25, 2019 10:25 AM by wesleykparker

    SWQL Issues with NCM.Nodes Table

    vangesseld

      Greetings Thwack,

       

      Since upgrading to Orion Core 2019.2, NCM 8.0, I've been experiencing issues with SWQL queries against the NCM.Nodes table.

       

      Any SWQL query I write in SWQL Studio or in a Custom Query widget that involves the NCM.Nodes table always seems to return 0 rows. This issue even happens in SWQL Studio when I right-click the NCM.Nodes table and do 'Generate Select Statement'.

       

      I know that my queries are valid though, as when I write the same queries using the OrionSDK, I'm able to view the results if I print them out in my script.

       

      I'm kind of at a loss here on how to troubleshoot this. I've updated SWQL Studio to the latest version and there was no change. I didn't decide to post this until I discovered the same problem seems to exist when trying to create a Custom Query widget.

       

      Any advice you guys can offer would be super helpful. Please let me know if this should be posted into the OrionSDK board instead.

       

      Thanks in advance!

       

      EDIT: I got the widget working, it doesn't like when you limit the results by using SELECT TOP #, but I still have issues with SWQL Studio it seems.

       

      Message was edited by: Chuck van Gessel

        • Re: SWQL Issues with NCM.Nodes Table
          wesleykparker

          I also recently upgraded to the same platform.

           

          I'm am having the same issue.  It seems to be related to the [dbo].NCM_Nodes (View)..

           

          When I attempt to show this in the Database manager I get the following error.

           

          SELECT TOP 1000 * FROM [dbo].[NCM_Nodes]

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

          Msg 8153, Level 0, State 1, Line 1

          Warning: Null value is eliminated by an aggregate or other SET operation.

           

           

          If I look at the (dbo.NCM_Nodes) view from SQL Studio, it works fine.

            • Re: SWQL Issues with NCM.Nodes Table
              wesleykparker

              More research shows that the issue is with the following columns in the NCM_Nodes view.  I"m going to see if I can trace back.  Maybe we have some sort of weird credential that is triggering this.

               

              --      ,[SNMPContext]

              --      ,[SNMPUsername]

              --      ,[SNMPAuthType]

              --      ,[SNMPAuthPass]

              --      ,[SNMPAuthKeyIsPass]

              --      ,[SNMPEncryptType]

              --      ,[SNMPEncryptPass]

              --      ,[SNMPEncryptKeyIsPass]

              • Re: SWQL Issues with NCM.Nodes Table
                wesleykparker

                I've located the source of the trouble.  The query to create the NCM_Nodes view has a problem.  The query runs in SQL Studio but flags for the same issue as why the Database Manager errors and displays no output.  I was able to fix the query for the View, but haven't implemented the changes in our DB until I hear back from SolarWinds Support.  I created case 00404266 to track.

                 

                I'm not sure if they flipped some global setting that is causing the problem, hence may be causing trouble with other Views in the DB, or if they just happened to change this View query and it needs to be corrected. 

                3 of 3 people found this helpful
              • Re: SWQL Issues with NCM.Nodes Table
                mdettenmeier

                Anyone find the resolution for this? I have a similar issue. The NCM.Nodes swql workd for me on the Solarwinds native site, but not through the API

                • Re: SWQL Issues with NCM.Nodes Table
                  wesleykparker

                  If anyone is feeling brave.  I rewrote the NCM.Nodes(View) query so that it doesn't trigger the failure.  I don't have a test environment, so haven't implemented it in my DB.  But I tested it in DB Manager and it runs.  I've attached it if anyone has a test environment, or is just brave.

                   

                  The only changes I made are in the two "LEFT OUTER JOIN" statements near the end.  In the current query they all end with setting all non matching names with NULL (e.g. MAX(CASE Name WHEN 'Context' THEN value ELSE NULL END) AS SNMPV3Context,).  The DB manager is complaining about the query making a small table that is one value plus a bunch of NULLs and aggregating it down to one value (it turns out it doesn't even matter if they are all NULLs, it still triggers the error).  Microsoft SQL Studio gives a warning when this happens, but doesn't fail running the query. I found that replacing the setting values to NULL with a '' (blank space)  fixes the issue, because then you are aggregating an empty field rather than a NULL field. 

                   

                   

                  SELECT        dbo.NCM_NodeProperties.NodeID, dbo.NCM_NodeProperties.CoreNodeID, dbo.Nodes.EngineID, dbo.Nodes.Caption AS NodeCaption, dbo.NCM_NodeProperties.NodeGroup, 
                                           CASE WHEN Nodes.IP_Address_Type = 'IPv4' THEN Nodes.IP_Address ELSE '' END AS AgentIP, CASE WHEN Nodes.IP_Address_Type = 'IPv6' THEN Nodes.IP_Address ELSE '' END AS AgentIPv6, 
                                           CASE WHEN Nodes.IP_Address_Type = 'IPv6' THEN 1 ELSE 0 END AS ManagedProtocol, 0 AS AgentIPSort, dbo.Nodes.DNS AS ReverseDNS, dbo.Nodes.ResponseTime, '' AS ResponseError, 
                                           CASE WHEN CAST(Nodes.Status AS int) = 26 THEN 10 ELSE CAST(Nodes.Status AS int) END AS Status, CASE CAST(Nodes.Status AS int) 
                                           WHEN 26 THEN 'Monitoring Disabled' WHEN 0 THEN 'Unknown' WHEN 1 THEN 'Up' WHEN 2 THEN 'Down' WHEN 3 THEN 'Warning' WHEN 4 THEN 'Shutdown' WHEN 5 THEN 'Testing' WHEN 6 THEN 'Dormant' WHEN
                                            7 THEN 'Not Present' WHEN 8 THEN 'Lower Layer Down' WHEN 9 THEN 'Unmanaged' WHEN 10 THEN 'Monitoring Disabled' WHEN 11 THEN 'External' WHEN 12 THEN 'Unreachable' ELSE 'Unknown' END AS StatusText,
                                            dbo.Nodes.Community, dbo.Nodes.RWCommunity AS CommunityReadWrite, dbo.Nodes.SNMPVersion AS SNMPLevel, dbo.Nodes.SysName, dbo.Nodes.Description AS SysDescr, 
                                           dbo.Nodes.Contact AS SysContact, dbo.Nodes.Location AS SysLocation, dbo.Nodes.SysObjectID AS SystemOID, dbo.Nodes.Vendor, dbo.Nodes.VendorIcon, dbo.Nodes.MachineType, dbo.Nodes.LastBoot, 
                                           dbo.Nodes.IOSImage AS OSImage, dbo.Nodes.IOSVersion AS OSVersion, 'Running,Startup' AS ConfigTypes, dbo.NCM_NodeProperties.NodeComments, dbo.Nodes.NextRediscovery AS NextDiscovery, 
                                           dbo.Nodes.NextPoll, dbo.NCM_NodeProperties.UseUserDeviceCredentials, dbo.NCM_NodeProperties.Username, dbo.NCM_NodeProperties.Password, dbo.NCM_NodeProperties.EnableLevel, 
                                           dbo.NCM_NodeProperties.EnablePassword, dbo.NCM_NodeProperties.ExecProtocol, dbo.NCM_NodeProperties.CommandProtocol, dbo.NCM_NodeProperties.TransferProtocol, 
                                           dbo.NCM_NodeProperties.EncryptionAlgorithm, dbo.NCM_NodeProperties.TelnetPort, dbo.NCM_NodeProperties.SSHPort, dbo.Nodes.AgentPort AS SNMPPort, dbo.NCM_NodeProperties.LoginStatus, 
                                           dbo.NCM_NodeProperties.UseHTTPS, dbo.NCM_NodeProperties.LastInventory, CASE WHEN isnull(RWSNMPv3.RWSNMPV3Username, '') 
                                           = '' THEN ROSNMPv3.SNMPV3Context ELSE RWSNMPv3.RWSNMPV3Context END AS SNMPContext, CASE WHEN isnull(RWSNMPv3.RWSNMPV3Username, '') 
                                           = '' THEN ROSNMPv3.SNMPV3Username ELSE RWSNMPv3.RWSNMPV3Username END AS SNMPUsername, CASE WHEN isnull(RWSNMPv3.RWSNMPV3Username, '') 
                                           = '' THEN ROSNMPv3.SNMPV3AuthMethod ELSE RWSNMPv3.RWSNMPV3AuthMethod END AS SNMPAuthType, CASE WHEN isnull(RWSNMPv3.RWSNMPV3Username, '') 
                                           = '' THEN ROSNMPv3.SNMPV3AuthKey ELSE RWSNMPv3.RWSNMPV3AuthKey END AS SNMPAuthPass, CASE WHEN isnull(RWSNMPv3.RWSNMPV3Username, '') 
                                           = '' THEN ROSNMPv3.SNMPV3AuthKeyIsPwd ELSE RWSNMPv3.RWSNMPV3AuthKeyIsPwd END AS SNMPAuthKeyIsPass, CASE WHEN isnull(RWSNMPv3.RWSNMPV3Username, '') 
                                           = '' THEN CASE ROSNMPv3.SNMPV3PrivMethod WHEN 'DES56' THEN 'DES' WHEN 'AES128' THEN 'AES' ELSE ROSNMPv3.SNMPV3PrivMethod END ELSE CASE RWSNMPv3.RWSNMPV3PrivMethod WHEN 'DES56'
                                            THEN 'DES' WHEN 'AES128' THEN 'AES' ELSE RWSNMPv3.RWSNMPV3PrivMethod END END AS SNMPEncryptType, CASE WHEN isnull(RWSNMPv3.RWSNMPV3Username, '') 
                                           = '' THEN ROSNMPv3.SNMPV3PrivKey ELSE RWSNMPv3.RWSNMPV3PrivKey END AS SNMPEncryptPass, CASE WHEN isnull(RWSNMPv3.RWSNMPV3Username, '') 
                                           = '' THEN RoSNMPv3.SNMPV3PrivKeyIsPwd ELSE RWSNMPv3.RWSNMPV3PrivKeyIsPwd END AS SNMPEncryptKeyIsPass, '' AS SNMPStatus, dbo.NCM_NodeProperties.AllowIntermediary, 
                                           0 AS EnableOrionImport, dbo.NCM_NodeProperties.UseKeybInteractiveAuth, dbo.Nodes.LastSync AS LastUpdateTime, CAST(- 2 AS datetime) AS LastRediscoveryTime, 
                                           dbo.NCM_NodeProperties.ConnectionProfile, dbo.NCM_NodeProperties.EndOfSupport, dbo.NCM_NodeProperties.EndOfSales, dbo.NCM_NodeProperties.EndOfSoftware, dbo.NCM_NodeProperties.EosEntryID, 
                                           dbo.NCM_NodeProperties.EosType, dbo.NCM_NodeProperties.EosMatchDate, dbo.NCM_NodeProperties.EosVersion, dbo.NCM_NodeProperties.EosLink, dbo.NCM_NodeProperties.EosComments, 
                                           dbo.NCM_NodeProperties.ReplacementPartNumber, dbo.Nodes.Add_IP_SLA, dbo.Nodes.AVPN_Circuit, dbo.Nodes.Branch_Compliance_Report, dbo.Nodes.City, dbo.Nodes.clean_caption, 
                                           dbo.Nodes.CLLI_Conformity, dbo.Nodes.Critical_Host, dbo.Nodes.Device_Location, dbo.Nodes.Device_Type, dbo.Nodes.Opengear_NodesID, dbo.Nodes.RESG_DEVICE, dbo.Nodes.SGT_Device, 
                                           dbo.Nodes.Site_CLLI, dbo.Nodes.site_gateway, dbo.Nodes.upgrade_device
                  FROM dbo.NCM_NodeProperties 
                  INNER JOIN  dbo.Nodes ON dbo.NCM_NodeProperties.CoreNodeID = dbo.Nodes.NodeID                  
                  LEFT OUTER JOIN
                         (
                         SELECT  dbo.NodeSettings.NodeID, 
                                 MAX(CASE Name WHEN 'Context' THEN value ELSE '' END) AS SNMPV3Context, 
                                 MAX(CASE Name WHEN 'UserName' THEN value ELSE '' END) AS SNMPV3Username, 
                                 MAX(CASE Name WHEN 'AuthenticationType' THEN value ELSE '' END) AS SNMPV3AuthMethod, 
                                 MAX(CASE Name WHEN 'AuthenticationPassword' THEN 'SWEN__' + value ELSE '' END) AS SNMPV3AuthKey,
                                 MAX(CASE Name WHEN 'AuthenticationKeyIsPassword' THEN value ELSE '' END) AS SNMPV3AuthKeyIsPwd, 
                                 MAX(CASE Name WHEN 'PrivacyType' THEN value ELSE '' END) AS SNMPV3PrivMethod, 
                                 MAX(CASE Name WHEN 'PrivacyPassword' THEN 'SWEN__' + value ELSE '' END) AS SNMPV3PrivKey, 
                                 MAX(CASE Name WHEN 'PrivacyKeyIsPassword' THEN value ELSE '' END) AS SNMPV3PrivKeyIsPwd
                         FROM dbo.CredentialProperty 
                         INNER JOIN dbo.NodeSettings ON dbo.NodeSettings.SettingValue = dbo.CredentialProperty.CredentialID
                         WHERE (dbo.NodeSettings.SettingName = 'ROSNMPCredentialID') AND (ISNUMERIC(dbo.NodeSettings.SettingValue) = 1)
                         GROUP BY dbo.NodeSettings.NodeID
                       ) AS ROSNMPv3 ON dbo.Nodes.NodeID = ROSNMPv3.NodeID 
                  LEFT OUTER JOIN
                         (SELECT dbo.NodeSettings.NodeID, 
                                 MAX(CASE Name WHEN 'Context' THEN value ELSE '' END) AS RWSNMPV3Context, 
                                 MAX(CASE Name WHEN 'UserName' THEN value ELSE '' END) AS RWSNMPV3Username, 
                                 MAX(CASE Name WHEN 'AuthenticationType' THEN value ELSE '' END) AS RWSNMPV3AuthMethod, 
                                 MAX(CASE Name WHEN 'AuthenticationPassword' THEN 'SWEN__' + value ELSE '' END) AS RWSNMPV3AuthKey, 
                                 MAX(CASE Name WHEN 'AuthenticationKeyIsPassword' THEN value ELSE '' END) AS RWSNMPV3AuthKeyIsPwd, 
                                 MAX(CASE Name WHEN 'PrivacyType' THEN value ELSE '' END) AS RWSNMPV3PrivMethod, 
                                 MAX(CASE Name WHEN 'PrivacyPassword' THEN 'SWEN__' + value ELSE '' END) AS RWSNMPV3PrivKey, 
                                 MAX(CASE Name WHEN 'PrivacyKeyIsPassword' THEN value ELSE '' END) AS RWSNMPV3PrivKeyIsPwd
                           FROM dbo.CredentialProperty 
                           INNER JOIN dbo.NodeSettings ON dbo.NodeSettings.SettingValue = dbo.CredentialProperty.CredentialID
                           WHERE (dbo.NodeSettings.SettingName = 'RWSNMPCredentialID') AND (ISNUMERIC(dbo.NodeSettings.SettingValue) = 1)
                           GROUP BY dbo.NodeSettings.NodeID
                        ) AS RWSNMPv3 ON dbo.Nodes.NodeID = RWSNMPv3.NodeID