2 Replies Latest reply on Jun 21, 2010 9:27 PM by cmontour

    Import Subnets (Not IP Addresses) from Spreadsheet

    cmontour

      Our organization is evaluating IPAM.  Currently our enterprise has a spreadsheet with over 1000 subnets.  I have created one custom IPAM field called "Business_Unit".  In addition I have modified our spreadsheet into the following .csv format:

       

       

      Display Name     Business_Unit         VLAN    Description

      ---------------------------------------------------------------------------------------

      10.0.200.0/24     CH-MKT                   200     Chicago Marketing VLAN

      10.254.2.48/30   HQ                                      P2P to San Jose

      ...

       

      It seems there are no import options for subnets in 1.6.  We are not opposed to a one-time SQL-based import.  Are there any options or tools to get this data mapped and imported?

        • Re: Import Subnets (Not IP Addresses) from Spreadsheet
          macnugetz

          Importing subnets from a file is something we'd like to do in a future release; we get many requests.  In the meantime, you can do it with the Engineer's Toolset.  Here is a Knowledge Base article describing how to do it.

          http://support.solarwinds.com/kbase/ProblemDetail.cfm?ID=1141

          -Craig

            • Re: Import Subnets (Not IP Addresses) from Spreadsheet
              cmontour

              Thanks Craig.  Ginned up a Ruby script that read our spreadsheet and inserted the custom fields and updated the standard fields after a batch subnet update.  This was the jist:

              require 'csv'

              # IPScope Class
              #---------------
              class IPScope
               attr_accessor :group_id, :friendly_name, :ip, :cidr, :business_unit
               attr_accessor :vlan, :comments
              end

              # DB Connect
              #------------
              def connect
               # Need to create a Windows ODBC DSN first
               dbh = DBI.connect('DBI:ODBC:***','********','********')
               return dbh
              end

              # Open and parse spreadsheet
              file = ./ip_scopes.csv

              begin
               csv_data = CSV.read(file)
              rescue => msg
               puts "Error parsing spreadsheet file: #{file}",__FILE__,__LINE__,$PROGRAM_NAME"
               exit
              end


              # Turn CSV with headers into array of hashes
              headers = csv_data.shift.map do |i|
               i.to_s
              end
              string_data = csv_data.map do |row|
               row.map do |cell|
                cell.to_s
               end
              end
              ip_scopes_aofh = string_data.map do |row|
               Hash[*headers.zip(row).flatten]
              end


              # Convert and sanitize array of hashes into array of objects
              ip_scopes = []  # Initialize array
              ip_scopes_aofh.each do |row|
               ip = IPScope.new
               ip.friendly_name = row['Display_Name']
               if ip.friendly_name.match(/(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\/(\d{1,2})/)
                ip.ip = $1
                ip.cidr = $2
               else
                puts "Failed to split IP and CIDR of scope: #{ip.friendly_name}",__FILE__,__LINE__,$PROGRAM_NAME"
                exit
               end
               ip.business_unit = row['Business_Unit']
               if row['VLAN'].match(/^\d{1,4}$/)
                ip.vlan = row['VLAN']
               else  
                ip.vlan = ""
               end
               ip.comments = row['Description']
               ip_scopes << ip
              end


              # Connect to the SolarWinds Orion database
              dbh = SWDB::connect

              # Query GroupID from SWDB and store in IPScope object
              sth = dbh.prepare("SELECT I.GroupID FROM IPAM_Group AS I WHERE I.Address = ? AND I.CIDR = CONVERT(INT,?)")
              ip_scopes.each do |i|
               sth.execute(i.ip, i.cidr)
               #sth = dbh.execute \
               # ("SELECT I.GroupID FROM IPAM_Group AS I WHERE I.Address = \'#{i.ip}\' AND I.CIDR = CONVERT(INT,\'#{i.cidr}\');")
               while row = sth.fetch do
                # Only add if a GroupID is retreived
                if row[0].to_s.match(/\d+/)
                 i.group_id = row[0]
                end
               end
              end
              sth.finish


              # Insert Attribute Data into SWDB
              sth = dbh.prepare("INSERT INTO IPAM_GroupAttrData (GroupID, Business_Unit) VALUES(?,?)")
              ip_scopes.each do |i|
               # Only process scopes with a valid GroupID
               if i.group_id.to_s.match(/^\d+$/)
                runlog.info "Inserting #{i.group_id}, #{i.business_unit} into IPAM_GroupAttrdata."
                sth.execute(i.group_id, i.business_unit)
               end
              end
              sth.finish

              sth = dbh.prepare("UPDATE IPAM_Group SET VLAN = ?, Comments = ? WHERE GroupID = ?")
              ip_scopes.each do |i|
               # Only process scopes with a valid GroupID
               if i.group_id.to_s.match(/^\d+$/)
                runlog.info "Updating IPAM_Group #{i.group_id} with #{i.vlan}, #{i.comments}."
                sth.execute(i.vlan, i.comments, i.group_id)
               end
              end
              sth.finish


              # Disconnect from SolarWinds Orion database
              dbh.disconnect

              __END__