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

Include Switch Name on Interface Report

Can anyone point me at the variable that will pull the switch\router name to an interface report? I can pull NodeID but that's as far as I have been able to get. I want to display the associated machine on the report so it's not just a random lonely interface.

Find more posts tagged with

reports_in_npm

interface

Accepted answers

stuartwhyte

ah ok, you're just querying the interfaces table. Create a new advanced SQL report and paste this:

select interfaces.NodeID,nodes.Caption,interfaces.InterfaceName, interfaces.Status, interfaces.StatusLED, interfaces.InterfaceLastChange,

convert(varchar(10), (DATEDIFF(d,InterfaceLastChange,GETDATE()))) + ' Days ' +

convert(varchar(10), (DATEDIFF(hh,InterfaceLastChange,GETDATE())% 24 )) + ' Hours '+

convert(varchar(10), (DATEDIFF(mi,InterfaceLastChange,GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'

from interfaces

inner join nodes on (interfaces.nodeid = nodes.nodeid)

where interfaces.StatusLED = 'down.gif'

All comments

stuartwhyte

From memory it's node.caption...

masonstryker

No luck there.

masonstryker

I should elaborate. There is a 'caption' but that shows the user friendly name of the interface, not the machine it is on.

stuartwhyte

What type of report are you using? Can you show the sql tab and post that here?

masonstryker

select NodeID,Caption,InterfaceName, Status, StatusLED, InterfaceLastChange,
convert(varchar(10), (DATEDIFF(d,InterfaceLastChange,GETDATE()))) + ' Days ' +
convert(varchar(10), (DATEDIFF(hh,InterfaceLastChange,GETDATE())% 24 )) + ' Hours '+
convert(varchar(10), (DATEDIFF(mi,InterfaceLastChange,GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'
from interfaces
where StatusLED = 'down.gif'

adatole

You may want this instead:

select Nodes.NodeID, Nodes.Caption as Node, Interfaces.Caption as Interface, Interfaces.Status, interfaces.StatusLED

//more fields here if you want

from interfaces

join nodes on interfaces.nodeid = nodes.nodeid

But the upshot is that, if you don't have SQL Management Studio set up, you really should. It's the best way to get familiar with the Solarwinds schema and what all those pesky fields are!

stuartwhyte

ah ok, you're just querying the interfaces table. Create a new advanced SQL report and paste this:

select interfaces.NodeID,nodes.Caption,interfaces.InterfaceName, interfaces.Status, interfaces.StatusLED, interfaces.InterfaceLastChange,

convert(varchar(10), (DATEDIFF(d,InterfaceLastChange,GETDATE()))) + ' Days ' +

convert(varchar(10), (DATEDIFF(hh,InterfaceLastChange,GETDATE())% 24 )) + ' Hours '+

convert(varchar(10), (DATEDIFF(mi,InterfaceLastChange,GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'

from interfaces

inner join nodes on (interfaces.nodeid = nodes.nodeid)

where interfaces.StatusLED = 'down.gif'

masonstryker

Yeah I realized I probably needed to pull from another table as I was staring at the screen. Thanks guys. I wish there was an authoritative guide on just the tables/variables. Everyone keeps pushing the Admin manual down my throat but searching through a HUGE manual for what should be a simple variable listing is futile sometimes.

You know, I'm sure people would pay a couple bucks for a simple document that lists the main tables and variables with a simple description of what the variable is. *hint hint*

stuartwhyte

When you say "variables" you mean the field headings in each table and what they really contain? Ie nodes.caption = node name, interfaces.fullname = interfaces description? That kind of thing, or deeper info on how to build custom queries?

masonstryker

I'm thinking initially just what you sent me a breakdown by:

Table.Variable.Description

How to build custom queries would be something I'd pay more than a couple bucks for.

masonstryker

Thanks adatole! I'll install that now. Trying to use the build in database management has proven challenging.

adatole

I've asked directly and SolarWinds considers their table schema to be "intellectual property" and therefore won't publish it. Same with the OIDS they poll on a server for CPU, disk, etc.

I've wanted to do it myself (document and publish the schema) for a while. Hopefully in the next few months I'll be able to pull it together. Along with teaching Treif, my pet pig, to perform in the next airshow.

stuartwhyte

I actually prefer the built in database manager.

To get an idea of the things available, I start with running a "get everything" query:

SELECT * from [tablename - ie nodes]

this will bring back the whole table, so might take a bit of time on a big database.

Then when you need info from two tables (nodes and interfaces) you need to join them with a common criteria (NodeID is always a good start)

Select nodes.*, Interfaces.*

from nodes

Left join interfaces on (nodes.nodeID = Interfaces.nodeid)

then you can start filtering on a specific field using WHERE

WHERE nodes.status <> 1

will return any nodes not up

That's a starting point for you

masonstryker

So stuartwhyte, you seem to know your way around a query, and I learn very fast. Tell me, in the last code that you sent me. I want to eliminate the node ID and sort on downtime from longest to shortest. Can you show me how you would do that? I wish I could send you points or something for helping me out

stuartwhyte

Kind of

Firstly, remove Interfaces.nodeID from the first line. First problem solved.

The next problem - sorting. To sort you use the clause:

Order by [column number] [order] so:

order by 6 desc

and here's the but - When ordering numbers, the first number is where the order is taken from, so 97 is higher than 615, which is higher than 28 which is higher that 134. I'm sure someone smarter than me could handle this and teach me something, which would be great

Anyhow, here's the code:

select nodes.Caption,interfaces.InterfaceName, interfaces.Status, interfaces.StatusLED, interfaces.InterfaceLastChange,

convert(varchar(10), (DATEDIFF(d,InterfaceLastChange,GETDATE()))) + ' Days ' +

convert(varchar(10), (DATEDIFF(hh,InterfaceLastChange,GETDATE())% 24 )) + ' Hours '+

convert(varchar(10), (DATEDIFF(mi,InterfaceLastChange,GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'

from interfaces

inner join nodes on (interfaces.nodeid = nodes.nodeid)

where nodes.StatusLED = 'down.gif'

order by 6 desc