This SWQL query used to work but as some point in the last 3 weeks it has stopped working. We haven't updated Orion we are on SolarWinds Platform, NPM, NCM: 2023.4.2.
SELECT
N.NodeID,
N.CoreNodeID,
N.NodeCaption,
N.AgentIP,
N.Vendor,
N.MachineType,
CASE WHEN J.JnxContentsPartNo = '740-028288' THEN 'PWR-MX80-AC-S'
WHEN J.JnxContentsPartNo = '740-029712' THEN 'PWR-MX80-DC-S'
ELSE N.EntityPhysical.EntityName
END AS FRU_Model,
CASE WHEN N.EntityPhysical.EntityDescription IS NULL THEN J.JnxContentsDescr
ELSE N.EntityPhysical.EntityDescription
END AS PEM_Slot,
CASE WHEN N.EntityPhysical.Serial IS NULL THEN J.JnxContentsSerialNo
ELSE N.EntityPhysical.Serial
END AS Serial_Number,
CASE WHEN N.EntityPhysical.Model IS NULL THEN J.JnxContentsPartNo
ELSE N.EntityPhysical.Model
END AS Model_Number,
N.Address,
N.City,
N.LATA
FROM NCM.Nodes N
LEFT JOIN NCM.EntityPhysicalJuniper J
ON N.NodeID = J.NodeID AND (N.MachineType LIKE '%MX80%' OR N.MachineType LIKE '%MX5-T%')
WHERE N.EntityPhysical.EntityDescription LIKE 'PEM%' OR J.JnxContentsDescr LIKE '%PEM%'
That gives us something like this to pull power supply info for our inventory reporting/spares/break/fix, etc
I try troubleshooting in SWQL studio and I can no longer get CoreNodeID from the pre joined NodeProperties table?
I dug around in SQL and it looks like somehow the NodeID for the JuniperEntities each row has a unique NCMNodeID and I'm guessing that is screwing up our query but I'm confused how this worked for at least a year now and then it just randomly stopped working without any change to the environment. The NCM NodeID and the JuniperEntities NodeID used to have to match for my old query to work.
I also don't see how I can get this working with SWQL again either since I can't find a way to tie the NCMNodeID to a CoreNodeID. I can rebuild it in SQL but we also have this same type of report for FPCs, SFPs, MPCs, etc so its a good amount of effort.
Am I missing something or is this something weird going on?
