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

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.

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.