Hi All!!
Anyone have a report for layer3 vlans by device showing vlan id and address ?
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)
105 User Vlan 2.2.2.0/24 or 2.2.2.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
I know this is a few years old however. I was able to work with this to produce close to what people have been asking for. The blow wont list the VLAN but will list all L3 connections per device.
Asset Management process was coming up with 1000's of IPs that were "not in our Assets Manage system" But this is due to these IPs not being actual "Management IPs" but rather the VLANS / Subnet IPs / Other L3 purposes.
Below will essentially give your the VLAN / L3's IPs and what node they are part of. So you will have the Actual Managed IP in solarwinds IP + Name + the VLAN/L3 IP address. You will have a row for each VLan/L3... I also have a filter in place below.
SELECT n.Interface.Name AS Interface, n.IPAddress AS [IP Address Vlans], n.Interface.Alias AS [Description], n.Interface.Index, L3.RouteDestination, L3.RouteMask, n.Node.NodeName, n.Node.IPAddress, n.Node.CustomProperties.Zones FROM Orion.NodeIPAddresses n JOIN Orion.NodeL3RoutingData L3 ON (n.NodeID = L3.NodeID) WHERE ((n.NodeID = ${NodeID}) AND (n.Interface.Status = 1) AND (L3.RouteType = 3) and NOT (L3.RouteMask = '255.255.255.255')) AND NOT ((n.Node.CustomProperties.Zones LIKE '%DEMO123%') or (n.Node.CustomProperties.Zones LIKE '%DEMO123%')) ORDER BY n.Interface.Index, n.Interface.Name
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.