2 Replies Latest reply on Jul 23, 2015 2:09 AM by storniga

    Merge existing data (gathered from DHCP servers) with data from excel imports

    storniga

      Hi,

      we started using the SolarWinds Orion IPAM and already integrated our DHCP servers. All subnets configured on our DHCP Servers are now in SolarWinds including all information we get from dhcp.

      But we also have an xls-documentation of our subnets that contains information that can not be obtained by SolarWinds via DHCP Servers.

      What we would like to do now is Import the Excel sheet, and for the subnets that already exist due to dhcp Integration we want to keep the existing information and add the information from Excel to the existing subnets.

      To be more specific:

      We have displayname, address, and so on of a subnet in solarwinds ipam. We have the same subnet in Excel with columns Display Name, address, ..., and also the columns for category, site and vrf (custom fields in ipam already created).

      We want These three attributes from Excel imported and merged together with the corresponding subnet in solarwinds withoud overwriting any of the obtained dhcp-information. But we can't figure out how.

      Is there a solution to do this?

        • Re: Merge existing data (gathered from DHCP servers) with data from excel imports
          muralikvp

          I don't know why the subnet information is not imported in your IPAM.Having some workaround for this issue.

           

          [IPAM_GroupAttrData] is the table  where subnet custom fields has been stored in IPAM.

           

          Create an csv with format of groupid , customfileds2

           

          For getting groupid for particular subnet try to run the below query in sql management studio for each subnet

          select GroupId from IPAM_Group where GroupType=8 and Address =<your subnet address>

           

          Finally your csv must look like attached format(csvFormat.png). It should not have headers.To make it simple i have given two columns. That means having two custom fields in IPAM

           

           

           

          CSV format

          csvFormat.PNG

           

          Try to copy the csv file into c:\test.csv solarwinds database installed location  then run the below query in sql management studio

           

          BULK

          INSERT [IPAM_GroupAttrData]

          FROM 'c:\test.csv'

          WITH

          (

          FIELDTERMINATOR = ',',

          ROWTERMINATOR = '\n'

          )

          GO

          --Check the content of the table.

          SELECT *

          FROM [IPAM_GroupAttrData]

          GO

          GO

           

           

          Sample Data after import of CSV

          Sample__SQL_data.png

          Finally your subnet custom fields will be getting populated in IPAM