This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

IPAM database how to map City from IPAM_GroupAttrData table to a IP in IPAM_node table

hello,
i am trying to query the IPAM database and get a list of all IP addresses. i would also need the city for each of the IP Address. i see a column called City in  IPAM_GroupAttrData, but there is no groupID column in the IPAM_node table to map these through a join. i am wondering how i can get this information, as for sure there has to be a way. The UI shows a tree of all cities and shows IP addresses inside each of the cities. sso wondereing from where the UI is fetching data to assign a city to an IP address?

any help is greatly appreciated.

  • Because IPAM data is structured as a tree, the right way to query this will be different depending on what level your City attribute is at. You can have the City attribute on a subnet, or you can have one or more levels of supernets/groups between the group that holds the City attribute and the actual IP addresses. Could you share a screenshot to show how this is organized?

  • thanks for that explanation. agree with that.here is a screenshot. i had to blur the name. so when i click on that blurred name, on the right panel, i get a list of IP addresses
    pastedImage_0.png

  • Ok, it looks like the city is also the name of the group. Here's query that will get all of the IP addresses, their status, and the name of the subnet's group's group:

    SELECT DISTINCT TOP 1000 I.IPAddress, I.Status, G2.FriendlyName

    FROM IPAM.IPNode I

    LEFT JOIN IPAM.GroupNode G1 ON I.Subnet.ParentId=G1.GroupId

    LEFT JOIN IPAM.GroupNode G2 ON G1.ParentId=G2.GroupId

  • To be clear, in this scenario City is not currently a property of any address, it is the name of a group that someone went in and stuck all those subnets under.  Since you can layer as many groups as you want in the tree tdanner​'s query is assuming that all the subnets are 2 layers from their city.  If that is not consistent across all your cities and subnets then you are going to be in for some fun manual cleanup once you get this all into excel.

  • thats exactly what i was looking for. thank much tdanner​ ! appreciate your help.