Join tables to get a specific information (IPAM)

[mkaltenbrunner3]

Hey everyone,
can anyone help me with the following scenario?
How do i get the corresponding VLAN-ID, VLAN-Name of an IP-Address?

I know that different tables are available such as IPAM_Group, IPNode, etc. Now i want to know, how I can select and join different tables to get this structure back:

IP Address | MAC-Address | VLAN-ID | VLAN-Name | ...

Thanks for helping!

[sum_giais]

Here's something to start with - 😁 - Enjoy!

SELECT ip.IPAddress, ip.MAC, net.VLAN, net.DisplayName
FROM IPAM.IPNode AS ip
INNER JOIN IPAM.Subnet AS net
ON ip.SubnetId = net.SubnetId

[mkaltenbrunner3]

Thanks for your help,
this query helps me a lot, the problem is, that i don't have any table named like IPAM_Subnet ...
What should I do now?

Kind regards,
Michael

[sum_giais]

Ahh my mistake -- that's a SWQL query. I'll try to take a look at what (if any) SQL query can be done for this.

[sum_giais · Accepted solution]

Here's roughly that same query for SQL:

SELECT ip.IPAddress, ip.MAC, net.VLAN, net.FriendlyName
FROM IPAM_Node AS ip
INNER JOIN IPAM_Group AS net
ON ip.SubnetId = net.GroupId
AND net.GroupType = 8

I added in net.GroupType = 8 so it only shows subnets (grouptype 8 is subnet). 

[mkaltenbrunner3]

Thanks for helping :).