8 Replies Latest reply on May 27, 2016 3:35 PM by pheonixnyte

    Import data for IP Address Groups

    jdjames24x7

      Hi!

       

      I have close to 40 sites with multiple IP ranges at each site. Does anyone know if there is a way to import this data to configure the IP Address Groups in NTA so I  do not need to manually enter 150+ IP ranges into the NTA configuration?

       

      Thanks in advance for any help!

       

      James

        • Re: Import data for IP Address Groups
          timchampion

          Is this available yet?

            • Re: Import data for IP Address Groups
              michal.hrncirik

              we may have a workaround for this, let me talk to our engineering and I'll come back with the result.

              • Re: Re: Import data for IP Address Groups
                jasonmargison

                I was able to do this with a crazy excel spreadsheet and SQL queries.  I have 701 IP groups with well over 1000 subnets defined.  NPM 11.5RC2 and NTA4.1, but this also worked with NTA4.03

                 

                I'm going to do a poor job of explaining this but hopefully it can get some people moving in the right direction.  I was hesitant to post this but people here have been asking about this for years.

                 

                 

                You have two tables in SQL you need to modify.  dbo.NetflowIPGroups and dbo.NetflowIPGroupRanges.  Populate dbo.NetflowIPGroups first.  In excel I have a sheet that creates the body of an SQL INSERT INTO query using the =CONCATENATE() function.  Start with the first unused IPGroupID and go up.

                img1.jpg

                Now go into SQL Server Management Studio and create a query on your solarwinds database:

                 

                SET IDENTITY_INSERT dbo.NetflowIPGroups ON

                INSERT INTO dbo.NetflowIPGroups (Name, IPGroupID, Enabled) VALUES

                <Insert **** from your spreadsheet>

                SET IDENTITY_INSERT dbo.NetflowIPGroups OFF

                 

                Now you need to populate the NetflowIPGroupRanges table.  You need to have the start IP and end IP of each network.  Sparing the details, I calculated the end IP by adding the inverse netmask to the network number.  Then I used text to columns with "." as the deliminator to get each octet into it's own column.

                 

                Convert the start and end IP to decimal using =CONCATENATE()

                img2.jpg

                 

                You can use VLOOKUP() to find the IPGroupID for a particular label.

                 

                Use concatenate again to create the body of the sql query.

                img3.jpg

                 

                Now go into SQL Server Management Studio and create another query:

                 

                INSERT INTO dbo.NetflowIPGroupRanges (IPGroupID, Low, High) VALUES
                <insert **** from your spreadsheet>

                 

                At this point if you did everything right, you should be able to go to NTA Settings, IP Groups, and click apply.  A few weeks later the IP Group update will be finished.

                 

                Here is a sample query to add some IP groups.

                 

                SET IDENTITY_INSERT dbo.NetflowIPGroups ON

                INSERT INTO dbo.NetflowIPGroups (Name, IPGroupID, Enabled) VALUES

                ('Test',3, 1),

                ('Test2',4, 1),

                ('Test3',5, 1)

                SET IDENTITY_INSERT dbo.NetflowIPGroups OFF


                 

                INSERT INTO dbo.NetflowIPGroupRanges (IPGroupID, Low, High) VALUES

                  (3,192168251040,192168251071)

                ,  (3,10023246000,10023246255)

                ,  (4,10036177000,10036177003)

                ,  (5,10036227000,10036227255)

                 

                1 of 1 people found this helpful
              • Re: Import data for IP Address Groups
                usmcjohn

                Flawless  So simply you would think they could take your query and build it in to an import job.

                • Re: Import data for IP Address Groups
                  sacosgrove

                  Were you being serious when you say, "... A few weeks later the IP Group update will be finished." ?

                   

                  I have performed the necessary steps and verified the data is stored in the database correctly.  However, I do no see any of these groups listed in the web console.