How to write SQL Query for Interfaces with IP Addresses

Version 1

    I'm learning some SQL on the way to getting more information out of SolarWinds NPM. This query pulls the information together to show the interface name, IP, subnet mask, the caption that's been entered, the description on an interface, what node it's on, what's the interface name, and some comments about the node.


    It Pulls from the following SQL tables:

    1. Interfaces
    2. NodeIPAddresses
    3. NodesData
    4. NodesCustomProperties

     

    I'm a Network Engineer by training - with a little Regex (for looking at IOS and working with BGP) ... and a SQL N00b.  I'm sure this query could be prettier, so have pity.  I used the "Database Manager" utility on the SolarWinds Orion server itself to find the tables and the data they contained.


    Thanks to:  Richard Letts for getting me started in the right direction with his helpful link Join Fundamentals

    RichardLetts

    I also found SQL Tutorial  which was helpful allowing me to play with an on-line test database, with examples and explanations.


    Here's the SQL Query:


    SELECT    i.NodeID as "NodeID", i.InterfaceName as "Int Name", i.Caption, i.InterfaceAlias as Description, i.IfName,

              nip.IPAddress as "IP Address", nip.SubnetMask as "SubnetMask",

              nd.Caption as "Node Name", 

              ncp.Comments as "Node Comments"

    From [Interfaces] as i, [NodeIPAddresses] as nip, [NodesData] as nd, [NodesCustomProperties] as ncp

    where i.NodeID = nip.NodeID

    and

    nip.NodeID = nd.NodeID

    and

    nd.NodeID = ncp.NodeID

    and

    i.InterfaceIndex=nip.InterfaceIndex


    Here's the output, with some redaction:

    chart.gif

    I hope this helps you - Pseudocyber