17 Replies Latest reply on Mar 25, 2011 10:37 AM by njoylif

    Adding Serial number data from NCM to NPM

    jawells

      Hi

      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

        • Re: Adding Serial number data from NCM to NPM
          sotherls

          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

            • Re: Adding Serial number data from NCM to NPM
              jawells

              Hi

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

               

              Thanks

              James

                • Re: Adding Serial number data from NCM to NPM
                  sotherls

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

                  • Re: Adding Serial number data from NCM to NPM
                    sotherls

                    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

                      • Re: Adding Serial number data from NCM to NPM
                        jawells

                        Hi

                        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

                          • Re: Adding Serial number data from NCM to NPM
                            jawells

                            Hi

                            Just looking at my NODEID data in NCM and it does not match the NODEID data in NPM, which I guess maybe is down to the fact that the devices where added into NCM manually and not via an import from NPM, would that be correct ?

                            Is it a good thing to change this, so that both these are consistant and will changing the NODEID in NCM effect the functioning of NCM

                            Thanks

                            James

                              • Re: Adding Serial number data from NCM to NPM
                                jawells

                                Hi

                                The below SQL query is where my Serial Number data is stored in the NCM database

                                SELECT ncm.SysName, ncme.Serial
                                FROM solarwinds_ncm..Nodes ncm,
                                solarwinds_ncm..Entity_Physical ncme
                                WHERE ncm.NodeID = ncme.NodeID
                                AND ncme.Serial != ''
                                AND ncme.Position = '-1'

                                So I want to be able to copy this from here to the NPM Custom property field of SerialNumber in the NPM database if that makes sense

                                James

                                • Re: Adding Serial number data from NCM to NPM
                                  sotherls

                                  No, don't make any manual changes to the nodeid's in either database. I am not sure if that what you meant but it would wreak havoc on your systesm if you do. Orion and NCM would NOT be happy.

                                  When I was looking at this I noticed that the nodeid's in both tables did not match so that is why I did the join on the 2 tables and grabbed the node name which I then compare against the Orion node table. Whew.

                                  With that said the 'substring(n.sysname,1,charindex('.',n.sysname)-1)' portion of the query strips the FQDN of the device name from sysname. I did this because our nodes are in our Orion Nodes table with the FQDN.

                                  You might be able to do a match between the Orion Nodes table and the NCM Nodes table if they are the same.

                                    • Re: Adding Serial number data from NCM to NPM
                                      njoylif

                                      Call me or shoot me an email if you like.

                                      Basically, I've created some SQL  that will copy the nodeid (custom property in NCM) to npm based on IP (or you could do this via import)  This allows me to move info back and forth.  OBVIOUSLY, this is not supported and you should create a b/u of the DB prior.

                                      some of my stuff is in content share.

                                      I did this to create a "live documentation" site via Orion (ip/mask) etc...on Orion page.

                                        • Re: Adding Serial number data from NCM to NPM
                                          njoylif

                                          the more I thought about it, the more I realized there were unnecessary steps for what you need.

                                          I used IP address matching to join the tables.  See Serial numbers from NCM entity_physical to NPM nodes - SQL script

                                          As always, back up your databases prior to any kind of customization.

                                          the script does have a commented out section that you can test the query with prior to running the full update though.

                                            • Re: Adding Serial number data from NCM to NPM
                                              jawells

                                              Hi

                                              I have one last question, the SerialNumber column in your NCM database, how was this populated, via a custom poller, as out of the box NCM Nodes tables does not have this column. I see this data in the Entity_
                                              Physical only

                                               

                                              Thanks

                                              James

                                                • Re: Adding Serial number data from NCM to NPM
                                                  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 ).

                                                    • Re: Adding Serial number data from NCM to NPM
                                                      jawells

                                                      Hi

                                                      No problem

                                                       

                                                      Just downloaded the updated SQL, cheers only issue I see is the last validation check

                                                      and npmn.SerialNumber<>cmep.Serial

                                                      this seems to prevent the match, not sure why, but I took it out and it seem to be fine, I know it does a validation check to make sure the Serial numbers in NCM Enitiy and NMP SerialNumber are not equal.

                                                      Thanks

                                                      James

                                                    • Re: Adding Serial number data from NCM to NPM
                                                      njoylif

                                                      That should populate ones that do not match.  Thus telling you which NPM.Nodes.SerialNumbers do NOT match ConfigMgmt.entity_physical.Serial.

                                                      I did this to NOT over-write the NPM SerialNumbers that are matched anyways (smaller result set).  Either way though :)