Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Adding Serial number data from NCM to NPM

I was wondering if anyone has written some SQL to take the Cisco serial number data and added it to the NPM database as I have enabled the custom field of SerialNumber in the NPM database or could you assist with a UPDATE statement that would do this

Thanks in advance

James

Find more posts tagged with

Accepted answers

njoylif

Well, that's embarrassing I lost track of my customizations.

I pulled from poller out of NPM and updated NCM with that. Interestingly, while researching this, I've found and verified [some of] 30 devices whose SN in NPM was wrong (changed Hardware or not using correct poller) - thus, I think this is the better way to go anyways.

My script has a commented section (annotated by /* comments between these */ ) that will do a select statement pulling key fields from the three tables involved to allow you to validate result set before you make the changes.

I'll update the content share to pull from Entity_Physical into NPM ( in the next few minutes ).

All comments

FormerMember

James,

We are doing something similar but we use NPM UnDP to collect the serial number from the Cisco devices.

I have a SQL job running at midnight to read the serial numbers, models and mfg and push that to the CP's in the Nodes table.

See if this makes sense..... (sorry the SQL is not better documented)

UPDATE dbo.nodes
set SerialNumber = t.status
from (
select distinct caption, status
from (
select caption, a.Status
from dbo.CustomPollerStatus A
join dbo.CustomPollerAssignment B
on b.CustomPollerAssignmentID = a.CustomPollerAssignmentID
join dbo.nodes C
on b.nodeid = c.nodeid
where (c.mfg like 'cisco%') and ( b.assignmentname like 'chassisid%')
) subquery
) as T, dbo.Nodes as T1
where t.caption = t1.caption

-- Added to get Model numbers from Cisco
UPDATE dbo.nodes
set Model = t.status
from (
select distinct caption, status
from (
select caption, a.Status
from dbo.CustomPollerStatus A
join dbo.CustomPollerAssignment B
on b.CustomPollerAssignmentID = a.CustomPollerAssignmentID
join dbo.nodes C
on b.nodeid = c.nodeid
where (c.mfg like 'cisco%') and ((b.assignmentname like 'chassismodel%')
or b.assignmentname like '%modelnumber%')
) subquery
) as T, dbo.Nodes as T1
where t.caption = t1.caption

--If you still can't get data then resort to MachineType in SNMP
UPDATE dbo.nodes
set Model = c.MachineType
from (select caption, MachineType from nodes
where (model is null or model ='')
and mfg = 'cisco') c
where (model is null or model ='')
and mfg = 'cisco'
and c.caption = nodes.caption

FormerMember

Thanks for the info, what SQL client do you use to make these types of UPDATES to the SolarWinds database

Thanks

James

FormerMember

This is done within SQL Server Studio Manager in version 2005.

Ian_Levick

Would this report be easy to adapt to HP/Juniper by substituting the name “Cisco” with the preferred vendor?

FormerMember

James,

Your original request asked for taking the serial number from NCM and posting to a NPM custom property. The following query should work.

-- In the example below my NCM database is called configmgmt552
-- My NPM database is called netperfmon
-- My CP in NPM is called SerialNumber
-- Rename your databases as required.

UPDATE nodes
set SerialNumber = t.status
from (
select distinct caption, a.status
from (
select substring(n.sysname,1,charindex('.',n.sysname)-1) as Caption,ncm.chassisid as Status
from configmgmt552.dbo.cisco_chassis ncm
join configmgmt552.dbo.nodes N
on n.nodeid = ncm.nodeid
) a
) as T, dbo.Nodes as T1
where t.caption = t1.caption

FormerMember

yes, I am trying to link the Serial in NCM to the extra field I created in NPM just for Cisco devices

I can see that in NCM in the Node database there is a Serial column already defined that contains the cisco chassis serial number. The one issue I have seen is that my instance of NCM does not have any data in the Node table for NODEID

So I was thinking of using the hostname has a mapping. So if CAPTION = SysName then use this as a match, what do you think and how would the SQL look doing it that way

Thanks for looking into this and helping out

James