cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Vlan Report

Hi All!!

Anyone have a report for layer3 vlans by device showing vlan id and address ?

Labels (2)
4 Replies
Level 8

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
0 Kudos

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

0 Kudos

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)
0 Kudos

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

0 Kudos