6 Replies Latest reply on Aug 15, 2018 4:38 PM by romang

    Need an IPAM "inventory" report

    patriot

      I am in the process of migrating IPAM from one SolarWinds instance to a new one, but I suspect that there are numerous bogus or duplicate IPs and subnets in the existing database. So, I would like to create a report that shows the current "infrastructure" of IPAM, such as any folders that were configured to group the subnets in. I can find the various IPAM tables in the database, but I have been unable to find the table and fields that contain the Group names that the subnets were assigned to. I also would want to see the Description information from the Edit screens.

       

      Can anyone help? Thank you very much in advance.

        • Re: Need an IPAM "inventory" report
          zackm

          I don't have a live IPAM in front of me right now, but give this a shot to see if it is what you're looking for:

           

          SQL:

           

          SELECT
              t.GroupType,
              g.FriendlyName,
              g.Address,
              g.AddressMask,
              g.CIDR,
              g.Comments
          FROM IPAM_Group g
          JOIN IPAM_GroupType t ON t.GroupTypeID = g.GroupType
          ORDER BY g.FriendlyName
          

           

           

          Try that out, if you need something different, please reply and I will try and work something up from a live DB.

           

          - ZackM

          http://www.loop1systems.com

            • Re: Need an IPAM "inventory" report
              patriot

              That helps a lot, but what it is still missing is the name of the Groups (folders) that one can create on the "Manage Subnets and IP Addresses" page.

               

              So, I want to be able to group the subnets by the Group I have added them to, for instance Company, State or whatever. In other words, I want to be able to reproduce the left side of the "Manage Subnets and IP Addresses" in a report.

               

              IPAM pic.jpg

                • Re: Re: Need an IPAM "inventory" report
                  zackm

                  Those are your FriendlyName entries under GroupType 'Group'

                   

                  For Instance:

                   

                  SELECT  
                      t.GroupType,  
                      g.FriendlyName,  
                      g.Address,  
                      g.AddressMask,  
                      g.CIDR,  
                      g.Comments  
                  FROM IPAM_Group g  
                  JOIN IPAM_GroupType t ON t.GroupTypeID = g.GroupType 
                  WHERE t.GroupType IN ('Group', 'Subnet', 'Supernet') 
                  ORDER BY t.GroupType, g.FriendlyName
                  

                   

                  Gives you this:

                   

                  ipam results.jpg

                   

                  Which is a representation of this:

                   

                  ipam_manage pic.jpg

                   

                   

                  However, to get tricky and show the parent/child relationships of the groups and subgroups, you need to get a little fancy:

                   

                  SELECT
                    grv1.GROUPTYPE AS 'TYPE',
                        CASE WHEN grv2.FRIENDLYNAME IS NULL
                             THEN 'NO PARENT'
                             ELSE grv2.FRIENDLYNAME
                        END AS 'PARENT NAME',
                    grv1.FRIENDLYNAME AS 'GROUP NAME',
                    grv1.ADDRESS AS 'NETWORK ADDRESS',
                    grv1.CIDR
                  FROM IPAM_GROUPREPORTVIEW grv1
                  JOIN IPAM_GROUPREPORTVIEW grv2 ON grv1.PARENTID = grv2.GROUPID
                  WHERE grv1.GROUPTYPE IN ('GROUP', 'SUBNET', 'SUPERNET','ROOT')
                  ORDER BY grv1.GROUPTYPE
                  

                   

                   

                  And here you go!

                   

                  IPAM_Groups_Report.OrionReport

                   

                  It's a little cleaner if you take this SQL and use the web-based report writer.

                   

                  -ZackM

                  http://www.loop1systems.com

                    • Re: Need an IPAM "inventory" report
                      michal.nehasil

                      This report is cool. Seems to go right direction, but works only in one level of folder hierarchy. I found our folder structure in IPAM more complicated as we do Region-Country-City-Building, sometime even Floor. Does anyone have an idea how to modify the report to cover that? I'd need report that include all parent groups for every record.

                       

                      Thanks,

                      m.

                        • Re: Need an IPAM "inventory" report
                          romang

                          Hi Michal.

                           

                          Did you ever find a solution for nested folders?  I am attempting to create the same type of report.

                            • Re: Need an IPAM "inventory" report
                              romang

                              If anyone is still interested in this thread, I put together a query that partially satisfies the initial request.  The query provides what I refer to as the "Primary Folder"  along with the direct parent folder for each subnet.  What I'm calling the Primary Folders are the second level folders, the ones directly under "IP Networks".  I imagine this query could be tweaked to provide the folders in between the parent and primary as well, but this gives me what I needed.  Hope this helps save someone some time.

                               

                              As a disclaimer, I'm sure that this isn't the cleanest code, my sql knowledge is more than a bit rusty.

                               

                              SELECT  g1.GroupID, g1.ParentID, g1.Address, g1.AddressMask, g1.CIDR, g1.FriendlyName, g1.Comments, g1.Location, ga.AncestorGroupId, ga.Distance, g2.FriendlyName as "Primary Folder", g3.FriendlyName as "Parent Folder"

                              FROM IPAM_Group g1

                              JOIN IPAM_GROUPAncestors ga ON ga.GroupID = g1.GroupID

                              JOIN IPAM_Group g2 ON  g2.GroupID = ga.AncestorGroupId

                              JOIN IPAM_Group g3 ON  g1.ParentID = g3.GroupID

                              Where ga.AncestorGroupId IN

                              (Select GroupId

                              FROM

                              IPAM_Group

                              Where ParentId = 0)

                              AND g1.CIDR < 30

                              AND g1.CIDR > 0

                              ORDER BY ga.AncestorGroupId, g1.ParentId, g1.Address

                              ;