For some reason, there is no integration to link NCM nodes and IPAM records - despite people requesting this for years. The following is my quick solution to this (I want to make this neater, but this was my 30m solution)
The result of this is a link that appears under custom properties on the IPAM detail screen, and an additional column at the end of the IP Address View list - that has the link text NCM Node (for some reason the link text is not used on the IPAM IP Address details screen)
1. Add custom IPAM field NCM_Node as shown:
2.Create the SQL integration procedure with this script (note references to SolarWindsOrion database - you may need to change):
create proc dbo.sp_IPAM_NCM_Integrate
as
--This creates/keeps updated
--clear data where their is no related node
UPDATE solarwindsOrion.dbo.IPAM_NodeAttrData
set NCM_Node = null
from solarwindsOrion.dbo.IPAM_NodeAttrData cus
inner join solarwindsOrion.dbo.IPAM_Node ipm
on cus.IPNodeId = ipm.IPNodeId
LEFT join solarwindsOrion.dbo.Nodes n
on n.IP_Address = ipm.IPAddress
where NodeID is null
--updated change records
UPDATE solarwindsOrion.dbo.IPAM_NodeAttrData
Set NCM_Node = n.Caption + '|/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + cast(n.NodeID as varchar(15))
from solarwindsOrion.dbo.IPAM_NodeAttrData cus
inner join solarwindsOrion.dbo.IPAM_Node ipm
on cus.IPNodeId = ipm.IPNodeId
inner join solarwindsOrion.dbo.Nodes n
on n.IP_Address = ipm.IPAddress
where NodeID is not null
and NCM_Node NOT LIKE '%' + cast(n.NodeID as varchar(15))
--insert new recordcs
insert into solarwindsOrion.dbo.IPAM_NodeAttrData
select ipm.IPNodeId, n.Caption + '|/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + cast(n.NodeID as varchar(15))
from solarwindsOrion.dbo.IPAM_Node ipm
inner join solarwindsOrion.dbo.Nodes n
on n.IP_Address = ipm.IPAddress
left join solarwindsOrion.dbo.IPAM_NodeAttrData cus
on cus.IPNodeId = ipm.IPNodeId
where cus.IPNodeId is null
3. create a job that runs every 4 hours (assuming you have the default IPAM 4 hourly IP rescan set) to execute the above procedure via the GUI or a script like this:
USE [msdb]
GO
/****** Object: Job [SW_4Hourly] Script Date: 31/10/2018 9:56:05 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 31/10/2018 9:56:05 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SW_4Hourly',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [IPAM_NCM_Integrate] Script Date: 31/10/2018 9:56:05 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'IPAM_NCM_Integrate',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC [dbo].[sp_IPAM_NCM_Integrate]',
@database_name=N'SolarWindsAux',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'4-hourl',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=4,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20181031,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'1204d8cc-6b94-42b8-a06e-81fbfc24d3d5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
4. Create a Custom HTML element on the IPAM IP Address Details view. This is responsible for converting the plain text value for NCM_NODE into a working link. Set it to Synchronous load, and put the following code in it:
<script id="custom-script-NodeLink">
$NodeFind= $( "div[sw-widget-defaulttitle='IP Address Custom Properties'] > div > table > tbody > tr > td[class='Property'] > span[columnname='NCM_Node']" )
$linkSplit = $NodeFind[0].innerText.split("|")
if ($linkSplit.length > 1) {
$linkText = $linkSplit[0];
$linkHref = $linkSplit[1]
}else{
$linkText = "Link";
$linkHref = $linkSplit[0];
}
$NodeFind[0].innerHTML = "<a href='" + $linkHref + "'>" + $linkText + "</a>";
//hide this Custom HTML - optional
//document.getElementById('custom-script-NodeLink').parentNode.parentNode.parentNode.parentNode.style.visibility="hidden"
</script>
If you want all visible trace of the Custom HTML element to disappear after the code has run, un-comment the last line of Javascript.
Updates
- 2019-04-29: Changed SQL code slightly, to add in the node caption. Added Javascript to convert what is now plain-text into a working link.
- If upgrading from the previous version, run the following code after changes - it will clear the node link data, so that it will all be updated to include the node caption (if this is not done, then the name of the node link will just be 'link')
UPDATE solarwindsOrion.dbo.IPAM_NodeAttrData Set NCM_Node = null
