Vlan Report

Hmm...  Have you looked at this resource I created that goes on a Node Details page?  Might be kind of what you're looking for.  If not, let me know what you want different...

All IP Addresses on a node - potential replacement resource

Hi Craig,

Thanks for the response.. I did see this, but I'm looking for something more like this

Router0001

VLAN TAG          Description          Network

100                    Server Vlan          1.1.1.0 /24  (or could be 1.1.1.0/255.255.255.0)

Hmmm...   More work than I want to put in I think.  Not saying it can't be done, just that I don't see a clear way to do it in a decent timeframe.   Plus it would have fairly limited usage I would think, where-as mine is useful on most devices.   Why?   The VLAN tag I think will only show up on switches, where with mine using the Interface name works on routers, switches, layer-3 switches, firewalls, etc.  And, if mine says "Gi0/0.107", I know its VLAN 107 doing a router-on-a-stick from a router.  Or, if it says "VLAN 500", that's a VLAN TAG of 500.    I don't need the actual VLAN tag to know what it should be.

I did try getting the actual VLAN info, but ran into a bunch of dead ends.   I would think it should be as easy as adding the variable "NIP.Interface.InterfacePortMaps.VLanID" to the SELECT line, but I got nothing off of that.   Tried joining it to the NodePortInterfaceMap table manually, still got nothing - the interface indexes didn't line up.  Tried joining it to hte NodeVlans table, same result on that.  I think you might be able to get it from the   UDT.VLan table, but then you first have to own UDT, then you have to add the VLAN interfaces into UDT to be managed (which I don't normally do), and if an interface wasn't managed you get null info back.

You can get the Netmask from the NodeL3RoutingData table, which you can join via the interface indexes, and have to weed out a bunch of stuff like Host Routes and some other stuff, but it doesn't tie you back to a VLAN TAG that I can see anywhere..   I'm not sure of an easy way to convert a dotted quad to a slash netmask though, could probably do it by creating a table that had all the possible dotted quad and what the slash notation was and do a simple lookup, but...

Like I said, there is probably a way to do it, I just don't see an easy path to it myself and don't see it to be that much more valuable than what I published. 

Here is the closest I got.  

SELECT NIP.Interface.Name AS Interface, NIP.IPAddress AS [IP Address], NIP.Interface.Alias AS [Description], NIP.Interface.Index, L3.RouteDestination, L3.RouteMask

FROM Orion.NodeIPAddresses NIP

JOIN Orion.NodeL3RoutingData L3 ON ((NIP.Interface.Index = L3.RouteIfIndex) AND (NIP.NodeID = L3.NodeID))

WHERE (NIP.NodeID = ${NodeID}) AND (NIP.Interface.Status = 1) AND  (L3.RouteType = 3) and NOT (L3.RouteMask = '255.255.255.255')

ORDER BY NIP.Interface.Index, NIP.Interface.Name

Hmmm...   More work than I want to put in I think.  Not saying it can't be done, just that I don't see a clear way to do it in a decent timeframe.   Plus it would have fairly limited usage I would think, where-as mine is useful on most devices.   Why?   The VLAN tag I think will only show up on switches, where with mine using the Interface name works on routers, switches, layer-3 switches, firewalls, etc.  And, if mine says "Gi0/0.107", I know its VLAN 107 doing a router-on-a-stick from a router.  Or, if it says "VLAN 500", that's a VLAN TAG of 500.    I don't need the actual VLAN tag to know what it should be.

I did try getting the actual VLAN info, but ran into a bunch of dead ends.   I would think it should be as easy as adding the variable "NIP.Interface.InterfacePortMaps.VLanID" to the SELECT line, but I got nothing off of that.   Tried joining it to the NodePortInterfaceMap table manually, still got nothing - the interface indexes didn't line up.  Tried joining it to hte NodeVlans table, same result on that.  I think you might be able to get it from the   UDT.VLan table, but then you first have to own UDT, then you have to add the VLAN interfaces into UDT to be managed (which I don't normally do), and if an interface wasn't managed you get null info back.

You can get the Netmask from the NodeL3RoutingData table, which you can join via the interface indexes, and have to weed out a bunch of stuff like Host Routes and some other stuff, but it doesn't tie you back to a VLAN TAG that I can see anywhere..   I'm not sure of an easy way to convert a dotted quad to a slash netmask though, could probably do it by creating a table that had all the possible dotted quad and what the slash notation was and do a simple lookup, but...

Like I said, there is probably a way to do it, I just don't see an easy path to it myself and don't see it to be that much more valuable than what I published. 

Here is the closest I got.  

SELECT NIP.Interface.Name AS Interface, NIP.IPAddress AS [IP Address], NIP.Interface.Alias AS [Description], NIP.Interface.Index, L3.RouteDestination, L3.RouteMask

FROM Orion.NodeIPAddresses NIP

JOIN Orion.NodeL3RoutingData L3 ON ((NIP.Interface.Index = L3.RouteIfIndex) AND (NIP.NodeID = L3.NodeID))

WHERE (NIP.NodeID = ${NodeID}) AND (NIP.Interface.Status = 1) AND  (L3.RouteType = 3) and NOT (L3.RouteMask = '255.255.255.255')

ORDER BY NIP.Interface.Index, NIP.Interface.Name