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.

How can I leverage Solarwinds' resources to generate a report that lists network subnets per location and per network switch?

I have Solarwinds IPAM and UDT and NPM and NCM.  Each contain various parts of the above request.  If only there were a canned report for what I need.  Or, if only I knew how to generate a SQL or SWQL report that would create a table showing the following:

  1. The list of all network switches
  2. The location (site and network room) in which each switch is installed
  3. The list of VLAN's on each switch
  4. The subnet associated with each VLAN ID
  5. Optional:  The VLAN on each switch port (or the list of VLANs on a port, if its a trunk port)

Some subnets and VLANs are spanned to more than one switch, so the list of all network switches should associate to multiple rows for several VLAN's.

Alternately, a subnet / VLAN that shows up on two or switches must show all switches they're on in the output of the query.

To make it more interesting, different sites use the same VLAN ID's, but use different subnets on those VLANs.

This info is all inside Solarwinds.  But I don't know how to put it together into a useful output.

Can you help me gather this data into a SQL or SWQL generated report?

Or, is there a canned report in Solarwinds that can list this much information?  If so, what's the name of the report?

Since this looks like a fun database exercise, I hope some of you won't mind me tagging you:

aLTeReGo

Radioteacher

tomiannelli

sqlrockstar

Better still, if you have any training guides you share with your teams, for learning how to build and generate SWQL or SQL queries like this, you'll be the one teaching me to fish, instead of giving me a fish.

  • Gawd!!!!!!   I would love this! I am just not smart enough. I would have to defer to my expert mikesky​ to whip up a masterful creation.

  • Soooo tempting! So, so tempting!

  • You guys are teasing me, now!  Do you have a proven query or report to share?  I'm thinking you do--or something quite close, that could be modified?

  • No, not really. But @mikesky is a wizard at SolarWinds reporting. He's whipped up some pretty creative ones in the past. The fun is in the brainstorming sessions... and then watching him self-destruct in the process, but then only to rise like the phoenix at the end.

  • It sounds entertaining and beautiful.

    I think . . .

    ;^)

  • Oh, I so want to work on this right now...it is way more interesting me to dig into the data....

    Alas, I am swamped at the moment.

  • Most of this stuff is easy to get to, the tricky part is going to be the subnet info.

    select distinct n.Vendor, n.Caption, n.IP, n.Location, n.CustomProperties.Building , n.NodeVlans.VlanId, n.NodeVlans.VlanName
    ,case when i.Caption is not null then i.Caption
    when map.IfIndex is not null then 'Assigned to Unmonitored Interface'
    when map.IfIndex is null then 'No Interface Assigned this VLAN'
    end as InterfaceName

    from orion.Nodes n
    left join orion.NodePortInterfaceMap map on map.nodeid=n.nodevlans.NodeID
    left join orion.npm.Interfaces i on i.Index=map.IfIndex
    where n.NodeVlans.VlanId is not null

    order by vendor, caption, vlanid, interfacename

    If you have IPAM and have been diligent about populating the VLANID you could join that and get the subnet info like so

    select distinct n.Vendor, n.Caption, n.IP, n.Location, n.CustomProperties.Building , n.NodeVlans.VlanId, n.NodeVlans.VlanName
    ,case when i.Caption is not null then i.Caption
    when map.IfIndex is not null then 'Assigned to Unmonitored Interface'
    when map.IfIndex is null then 'No Interface Assigned this VLAN'
    end as InterfaceName
    , ipam.Address, ipam.CIDR

    from orion.Nodes n
    left join orion.NodePortInterfaceMap map on map.nodeid=n.nodevlans.NodeID
    left join orion.npm.Interfaces i on i.Index=map.IfIndex
    --possible solution to subnet problem
    left join ipam.GroupNode ipam on ipam.vlan=tostring(n.NodeVlans.VlanId) -- or maybe if you've been using names instead of vlan id's use ipam.vlan = n.NodeVlans.VlanName

    where n.NodeVlans.VlanId is not null

    order by vendor, caption, vlanid, interfacename

    The second query doesn't have a good solution for cases where the same vlanid is being used in different parts of the network for different subnets, but I think if that was the case you could string together some logic to compare the ipam subnet locations against the node locations or a custom property and that could work to match them up.

    pastedImage_3.png

    You could probably tweak it a touch here or there depending on your environment and I'm sure there are edge cases I haven't considered yet, but this works in my lab.

    -Marc Netterfield

        Loop1 Systems: SolarWinds Training and Professional Services

  • As far as the "teach a man to fish" part, I'm not shy to point out my signature and mention that we offer a pretty solid 4 day advanced reporting class (the 301), along with on-site or remote private engagements

    https://loop1.com/training/solarwinds-public-training/​​

    For those who are more interested self learning then I'd say the best thing to do is start with picking up a basic understanding of sql tables and joins, this is where I began that journey,

    SQL Tutorial

    Then download and install SWQL studio from the github,

    Releases · solarwinds/OrionSDK · GitHub

    Pop it open, the most important tables are mostly self explanatory, so you pick where you want to start (usually Nodes)

    pastedImage_6.png

    and then I just experiment with ideas for joining tables until you get all the pieces you want.  It surely makes it faster once you get really deeply familiar with the layout of the database, but that only comes with spending more time in there looking for things.

    -Marc Netterfield

        Loop1 Systems: SolarWinds Training and Professional Services

  • I'm excited to see this in action later today. Thank you for your thought & efforts!