No Longer Able To Link NCM.CiscoCdp with Orion.Nodes via SWQL

Hi,

I've noticed that seemingly out of no where, a report that I have specific to CDP entries is failing. More specifically, it looks like it is failing because I am no longer able to make the appropriate join on the following:

from orion.nodes i
LEFT JOIN ncm.ciscocdp c on c.node.CoreNodeID = i.NodeID

c.node.CoreNodeID is now showing as NULL no matter what I do. I've tried instead using cirrus.ciscocdp but no luck.

Has anyone run into a similar issue, or have any advice?

Thanks!

  • It would be more helpful to show the FULL code you have.

    Your issue could be in the SELECT clause or something else. If I do this, for example:

    SELECT i.NodeID, c.CDPIndex
    from orion.nodes i
    LEFT JOIN ncm.ciscocdp c on c.node.CoreNodeID = i.NodeID

    I get results showing that the JOIN is working just fine.

  • Hi,

    Thanks for your response and taking the time to help me with this.

    Breaking this down into simpler terms without a join:

    select c.CDPIndex, c.node.CoreNodeID
    
    from ncm.ciscocdp c

    I see 6419 results, BUT only 84 of those results show a value in the CoreNodeID.

    This is the crux of the problem. If all of those results did successfully include a CoreNodeID, then there wouldn't be an issue making that join from orion.nodes.nodeid to ncm.ciscocdp.node.corenodeid.

    I know for certain that this was not the case a month before we had upgraded to 2023.4.2. I'm not sure if it's lack of support, or database corruption.

  • Again, the full code block would be much more useful than bit parts. Can you share the whole thing?

  • Below is the full code block that that previously worked, but no longer does now due to the fact that c.node.CoreNodeID shows as NULL for 95% of all results.

    SELECT i.CustomProperties.region, c.RemotePlatform,  c.RemoteDevice
    
    from orion.nodes i
    
    LEFT JOIN ncm.ciscocdp c on c.node.CoreNodeID = i.NodeID
    
    where c.RemoteDevice like '%wap%'

  • Based on that code, the only issue I see is the WHERE line, so start off by removing it altogether for testing. Secondly, the WHERE line presumes that all your Thin APs have 'WAP' in its name somewhere - correct?

    Here's what I did to test in our environment...

    Removed the region CP as we don't have that but replaced it with one of my own, and then I made the WHERE line like this:

    WHERE c.RemoteDevice NOT like 'Null' AND c.RemotePlatform LIKE '%AP%'


    I get the expected responses in our system. Optionally don't include the AND etc in the WHERE statement, and see if you get the expected results? If not, and the APs do have WAP in their name then I suggest it's time to open a ticket.

  • Thanks again for your help with my issue.

    I updated the code to include the WHERE statement that you listed above:

    SELECT c.RemotePlatform,  c.RemoteDevice
    
    from orion.nodes i
    
    LEFT JOIN ncm.ciscocdp c on c.node.CoreNodeID = i.NodeID
    
    WHERE c.RemoteDevice NOT like 'Null' AND c.RemotePlatform LIKE '%AP%'

    The above code works, but it only returns 27 results. If I were to run a similar query but without the above join, then I receive 1566 results, which is inilne with what I'm expecting based on when my original query worked prior:

    SELECT c.RemotePlatform,  c.RemoteDevice
    
    from ncm.ciscocdp c
    
    WHERE c.RemoteDevice NOT like 'Null' AND c.RemotePlatform LIKE '%AP%'

    I know that I'm repeating myself here, but the issue seems to be that from the NCM family, the CoreNodeID is basically inaccessible as it results in NULL 99% of the time. Without this, I cannot make successful joins to orion.nodes.

    I have a ticket opened. It's been transferred multiple times with next to no actual troubleshooting having been done which is very disappointing.

  • It sounds like you're experiencing the results of a known NCM inventory issue in later releases. I would suggest you open a ticket and request assistance in repairing the inventory tables.