cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

SWQL Issues with NCM.Nodes Table

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

0 Kudos
9 Replies

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

I created a new view in my DB called NCM.Nodes_test.  I made my changes and then re-added the DB to the Database manager application.  My test view runs fine. 

Product Manager
Product Manager

Hi Wesley

Having updated the view are your SWQL queries now returning the expected results?

0 Kudos

Well,  my case is still open so I haven't gone "nuclear" yet.  I'm not a DBA, so I'm not super comfortable changing out things with the DB.  I know that my test view runs in the Database Manager, so I assume it would fix the real view if I were to modify it.  But, since that would be a fix that would get undone any time I ran the configuration wizard, I'm waiting to see if support is going to give up before I make any changes. 

0 Kudos
Level 9

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

0 Kudos

My ticket is still open. They say their lab environment doesn't have the same issue.  They asked me to re-run the installation wizard.  This did not fix our issue.  Waiting for them on having someone look at our installation.

0 Kudos

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.

0 Kudos

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. 

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]

0 Kudos