Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

How to write SQL Query for Interfaces with IP Addresses

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:

Interfaces
NodeIPAddresses
NodesData
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:

I hope this helps you - Pseudocyber

Find more posts tagged with

interface

ip address

sql query

Accepted answers

All comments

pseudocyber

I wrote a great article! 5 stars!

AlexSoul

ha-ha ... you reminded me about this: My Name Is Alex Soul And I Freaking Rock!

well done! I learned in the past that you do not become an expert - you claim it!

5 stars *****

cnorborg

Now we just have to get you writing SWQL too! Here is your query in SWQL. Notice I'm only referencing 1 table instead of 3.

@Anyone from Solarwinds! - This is the first time I've run across this, but the SWQL version of the "IPAddress" table I'm referring to below doesn't have "SubnetMask" as a field in it, so it's missing from this report. As I said I've never run across it in my SWQL adventures - anyone with more experience know if this is common? Are there many missing fields from SQL to SWQL or is this particular one a fluke?

SELECT I.NodeID, I.InterfaceName AS [Int Name], I.Caption, I.InterfaceAlias AS Description, I.ifName, I.IPAddress.IPAddress AS [IP Address],

I.Node.Caption AS [Node Name], I.Node.CustomProperties.Comments AS [Node Comments]

FROM Orion.NPM.Interfaces I

pseudocyber

Thanks Craig - is all the information contained on one table? You don't have to do a join?

cnorborg

Caught your attention huh? Remember, this is the same database you work off of with SQL, your just going through the SWQL, which is part of the SDK to get to it. So the database on the back-end is still all separate tables and such. They did something I believe they call creating relationships between the tables to make things easier for us. There are other neat things about SWQL too that are beneficial. It is NOT SQL though, there are some things you just cannot do in SWQL that you can in SQL and visa-versa. Its fun to play with and you can embed the results directly into a webpage very easily. Just choose a custom resource of "Custom Query" and put your SWQL query in there.

While you could embed a SQL report from the old report writer in this way, those would take forever to load while the SWQL stuff loads fast!

Download the SDK and have some fun!!

AlexSoul

I have page with 20+ SQL reports on it - it does take time, but it loads them all pretty quick. I am not sure how exactly SWQL works, but the bottom line is - data is stored in SQL server. Tell me if I am wrong... even if it looks like it is one table - at the back of it you will still have SQL query being performed which will join required tables for you automatically.

The real issue with SQL unfortunately is compatibility - for example in latest NPM 11.5 the database schema has been changed a lot... so that it has resulted in a lot of work for me to re-build and adopt SQL scripts for it to work. I bet as we go further we will see more and more database schema changes. This forces me to step back and start learning SDK ... finally I think SWQL is more resilient to upgrades and updates than SQL, although SQL is much easier to learn as you have full Internet of articles about it.

cnorborg

Yes, one of the "Benefits of SWIS" is that you have "Insulation from Database Schema Changes", exactly the point your asking about.

Other benefits listed include not having to have database credentials, it uses your Orion creds. I also like that it respects account limitations for what nodes/interfaces a user can access. There are other benefits to using it too..

How it actually works? That I'm not 100% sure of, I would imagine something akin to what you are postulating in that it probably performs a SQL query on the backend. I'm pretty sure however that the "relationships" are somehow guaranteed to be optimized in some sort of fashion that makes it easy for them to do this. You can still do joins in SWQL, but the relationships eliminates that need in many, many cases.

All I know is I've been having fun with it and based on your question, figured you would too!!

sean.martinez

I added a cast to order the IP Address in sequential order.

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

ORDER BY CAST(PARSENAME(nip.IPAddress, 4) AS INT),

CAST(PARSENAME(nip.IPAddress, 3) AS INT),

CAST(PARSENAME(nip.IPAddress, 2) AS INT),

CAST(PARSENAME(nip.IPAddress, 1) AS INT)

cnorborg

But Grasshopper, don't forget those fields for which you do not display. Quite often the masters have already performed duties to make things easier on themselves, of which you - the student - should take advantage of... Instead of all those CAST's why don't you just order by the IPAddressN field? I will have to say using the CASTs was quite inventive, but rather inefficient!!

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

ORDER BY IPAddressN