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:
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.
1 of 1 people found this helpful
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.
Those are your FriendlyName entries under GroupType 'Group'
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:
Which is a representation of this:
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!
It's a little cleaner if you take this SQL and use the web-based report writer.
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.
Did you ever find a solution for nested folders? I am attempting to create the same type of report.
2 of 2 people found this helpful
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
Where ParentId = 0)
AND g1.CIDR < 30
AND g1.CIDR > 0
ORDER BY ga.AncestorGroupId, g1.ParentId, g1.Address