cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Need an IPAM "inventory" report

Jump to solution

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.

Tags (2)
1 Solution
Level 15

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

View solution in original post

0 Kudos
6 Replies
Level 15

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

0 Kudos
Level 12

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

Level 15

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

View solution in original post

0 Kudos

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.

0 Kudos
Level 8

Hi Michal.

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

0 Kudos
Level 8

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

;