Attached is a SQL job that copies the interface IPs/subnetmasks from the NCM database to the NPM interfaces table to custom properties.
You will have to create those custom properties on the interfaces table before this works.
This allows the web page to display interface IP/mask as below using ${<columnname>}
*****PLEASE ENSURE you BACK UP YOUR DB prior to any work and you either know your way around SQL or work with your DB Admin...sections of code in /* .... */ are comments/test queries to ensure you are getting the actaul stuff you want...
here is the SQL::::
This job will:
1. sync NetPerfMon.NodeID over to NCM.OrionNodeID based on IP devices address
2. in NCM, copy OrionNodeID from Nodes table to Interfaces table
3. sync NetPerfMon.InterfaceID over to NCM Interfaces.InterfaceID based on NodeID/OrionNodeID and InterfaceDescription
4. sync from NCM to NetPerfMon the IP/MASKs for Interfaces
STEP 1
UPDATE NCM.dbo.Nodes
SET NCM.dbo.Nodes.OrionNodeID=n.NodeID
--Select c.NodeID as CNode, n.NodeID as NNode, c.AgentIP as CIP, n.IP_Address as NIP, c.NodeCaption as CHost, n.HName as NHost
FROM NetPerfMon.dbo.Nodes AS n JOIN NCM.dbo.Nodes AS c ON n.IP_Address = c.AgentIP
Where
n.IP_Address=c.AgentIP
STEP 2
USE NCM
UPDATE Interfaces
SET Interfaces.OrionNodeID=Nodes.OrionNodeID
FROM Nodes JOIN Interfaces ON Nodes.NodeID=Interfaces.NodeID
STEP 3
UPDATE NCM.dbo.Interfaces
SET NCM.dbo.Interfaces.OrionInterfaceID=n.InterfaceID
/*
Select c.OrionNodeID as CNode, n.NodeID as INode,
c.OrionInterfaceID as CIntID, n.InterfaceID as NIntID,
c.InterfaceIndex as CIndex, n.InterfaceIndex as NIndex,
c.InterfaceDescription as CIntDesc, n.InterfaceName as NIntName
*/
FROM NetPerfMon.dbo.Interfaces AS n JOIN NCM.dbo.Interfaces AS c ON n.NodeID = c.OrionNodeID
Where
n.NodeID = c.OrionNodeID AND n.InterfaceIndex=c.InterfaceIndex
STEP 4
/*
Select ci.InterfaceID as CIntID, n.InterfaceID as NIntID,
ci.InterfaceDescription as CIntDesc, n.InterfaceName as NIntName,
cip.IPAddress, cip.SubnetMask
FROM NetPerfMon.dbo.Interfaces AS n JOIN NCM.dbo.Interfaces AS ci ON n.NodeID = ci.OrionNodeID
JOIN NCN.dbo.IPAddresses AS cip ON cip.InterfaceID=ci.InterfaceID
Where
n.NodeID = ci.OrionNodeID AND n.InterfaceName=ci.InterfaceDescription
#
# Actually Sets it.
#
*/
UPDATE NetPerfMon.dbo.Interfaces
SET
NetPerfMon.dbo.Interfaces.InterfaceIP=cip.IPAddress,
NetPerfMon.dbo.Interfaces.InterfaceMask=cip.SubnetMask
FROM NetPerfMon.dbo.Interfaces AS n JOIN NCM.dbo.Interfaces AS ci ON n.NodeID = ci.OrionNodeID
JOIN NCM.dbo.IPAddresses AS cip ON cip.InterfaceID=ci.InterfaceID
Where
n.NodeID = ci.OrionNodeID AND n.InterfaceName=ci.InterfaceDescription
