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

Need to Identify MAC / IP for WAN Interfaces

Hello Everyone,

I know it has been a while but have a question for the community. I need to develop and Custom Query/Table to capture the following information from my site routers.

Every SIte Router has a WAN Interface labeled as WAN INTF. Can you help me with the SQL/SWSQL code to capture this from the Database?

HOSTNAME:

WAN INTERFACE:

MAC ADDRESS:

IP ADDRESS:

ROUTER MODEL:

SERIAL NUMBER:

Find more posts tagged with

sql_queries

Accepted answers

cnorborg

As mentioned in my last comment, the InterfaceName is rather boring and doesn't include any description you put on the interface configuration. So, try this instead.

SELECT
n.Caption AS Hostname,
n.Interfaces.InterfaceName AS Interface,
n.Interfaces.Mac AS Mac_Address,
n.IPAddress AS IP_Address,
n.MachineType AS Machine_Type
,N.HardwareHealthInfos.ServiceTag
FROM Orion.Nodes n
WHERE
n.Interfaces.Caption LIKE '%WAN INTF%'
ORDER BY
n.Caption, n.Interfaces.InterfaceName

All comments

CourtesyIT

This is a start, but not working yet.

SELECT
n.Caption AS Hostname,
i.InterfaceName AS Interface,
i.MacAddress AS Mac_Address,
n.IPAddress AS IP_Address,
n.MachineType AS Machine_Type,
n.SerialNumber AS Serial_Number
FROM Orion.Nodes n
JOIN Orion.NPM.Interfaces i ON n.NodeID = i.NodeID
WHERE i.InterfaceName LIKE '%WAN INTF%'
ORDER BY n.Caption, i.InterfaceName;

Lofstrand

Hi

This should work:

SELECT

    n.Caption AS Hostname,

    i.InterfaceName AS Interface,

    i.Mac AS Mac_Address,

    n.IPAddress AS IP_Address,

    n.MachineType AS Machine_Type

    ,N.HardwareHealthInfos.ServiceTag

FROM Orion.Nodes n

INNER JOIN Orion.NPM.Interfaces i ON n.NodeID = i.NodeID

WHERE 

    i.InterfaceName LIKE '%WAN INTF%'

ORDER BY 

    n.Caption, i.InterfaceName

If we want to be "true" tro SWQL, we don't have to JOIN in the interface table as SWIS will take care of that. So here is another example:

SELECT

    n.Caption AS Hostname,

    n.Interfaces.InterfaceName AS Interface,

    n.Interfaces.Mac AS Mac_Address,

    n.IPAddress AS IP_Address,

    n.MachineType AS Machine_Type

    ,N.HardwareHealthInfos.ServiceTag

FROM Orion.Nodes n

WHERE 

    n.Interfaces.InterfaceName LIKE '%WAN INTF%'

ORDER BY 

    n.Caption, n.Interfaces.InterfaceName

cnorborg

The second query is definitely "better", but I'm guessing for the WHERE clause that you really want to use "n.interfaces.caption" as the thing to compare against, not the actual name of the interface.

CourtesyIT

Here are my results

sample interface:

0837:	interface GigabitEthernet0/0/0
0838:	description WAN INTF

cnorborg

As mentioned in my last comment, the InterfaceName is rather boring and doesn't include any description you put on the interface configuration. So, try this instead.

CourtesyIT

Thanks for this. This will definitely help us identify WAN Interfaces for third party 802.1x verification.

CourtesyIT

@cnorborg is it possible to get the IP address of the interface vice the IP address of the host?

cnorborg

Quite possibly? I think if you add this to the SELECT section it might give you what you want?

n.Interfaces.IPAddress.IPAddress AS [Interface IP],

CourtesyIT

Note quite there. I will work with it a bit. Thanks,

SELECT
n.Caption AS Hostname,
n.Interfaces.InterfaceName AS Interface,
n.Interfaces.Mac AS Mac_Address,
n.IPAddress AS IP_Address,
n.Interfaces.IPAddress.IPAddress AS InterfaceIP
n.MachineType AS Machine_Type
,N.HardwareHealthInfos.ServiceTag
FROM Orion.Nodes n
WHERE
n.Interfaces.Caption LIKE '%WAN INTF Army%'
ORDER BY
n.Caption, n.Interfaces.InterfaceName