How to get switch name, model and serial # of all switches (including stack)

damon_2021

Hi,

I am trying to get a list of all Cisco switches with their name, model, and serial #. I tried various things but was not able to get the desired results.

I see some data in [dbo].[NPM_SwitchStackMember] and tried to join with [dbo].[Nodes] but couldn't get it to work.

Any help with the query or better report would be appreciated.

Orion Version: 2024.1.1

I already used below report but it only shows stacks and I need to join it with the non stacks switches so I have a full report:

thwack.solarwinds.com/.../3396

Here is SWQL:

SELECT [StackMember].SwitchStack.Nodes.Caption AS [Stack Name]
, [StackMember].SwitchStack.Nodes.VendorInfo.Icon AS [Vendor Icon]
, [StackMember].SwitchStack.Nodes.DetailsUrl AS [Stack Url]
, [StackMember].DisplayName AS [Member Name]
, [StackMember].Model AS [Hardware]
, [StackMember].SerialNumber AS [Serial Number]
, [StackMember].SoftwareImage AS [Software Image]
, [StackMember].RoleDescription AS [Member Type]
-- The below is for sort order (can be omitted if you like)
, CASE [StackMember].RoleDescription
WHEN 'Master' THEN 1
WHEN 'Standby' THEN 2
ELSE 3 -- 'Member' and others
END AS [SortOrder]
FROM Orion.NPM.SwitchStackMember AS [StackMember]
ORDER BY [StackMember].SwitchStack.DisplayName

Find more posts tagged with

Reports

orion

SWQL

orionsdk

sdk

Accepted answers

wluther

Sorry, I just re-read what you said, and it looks like you are wanting to pull all nodes, and the SwitchStack info. I'm sure there are better ways to do it, but a quick and easy way is to UNION the SwitchStack SWQL above with a normal SWQL with node data which is not in the SwitchStack results. That should give you data from both tables and remove duplicates.

SELECT
DisplayName,
SwitchStack.SwitchStackMember.MacAddress,
SwitchStack.SwitchStackMember.Model,
SwitchStack.SwitchStackMember.SoftwareImage,
SwitchStack.SwitchStackMember.SerialNumber,
SwitchStack.SwitchStackMember.RoleDescription,
SwitchStack.SwitchStackMember.StateDescription,
LastSync
FROM Orion.NPM.SwitchStack

UNION

(SELECT
Caption,
'' AS [MacAddress],
MachineType AS [Model],
IOSVersion AS [SoftwareImage],
'' AS [SerialNumber],
'' AS [RoleDescription],
StatusDescription AS [StateDescription],
LastSync AS [LastSync]
FROM Orion.Nodes
WHERE Caption NOT IN (SELECT DisplayName FROM Orion.NPM.SwitchStack))

All comments

wluther

I'm not sure about what is required for a SQL query to the DB, but this SWQL query might point you in the right direction. You can use the implicit joins with SWQL, and should be able to filter out however you'd like by adding a WHERE and whatever you need there.

SELECT
DisplayName,
SwitchStack.SwitchStackMember.MacAddress,
SwitchStack.SwitchStackMember.Model,
SwitchStack.SwitchStackMember.SoftwareImage,
SwitchStack.SwitchStackMember.SerialNumber,
SwitchStack.SwitchStackMember.RoleDescription,
SwitchStack.SwitchStackMember.StateDescription,
LastSync
FROM Orion.NPM.SwitchStack

wluther

SELECT
DisplayName,
SwitchStack.SwitchStackMember.MacAddress,
SwitchStack.SwitchStackMember.Model,
SwitchStack.SwitchStackMember.SoftwareImage,
SwitchStack.SwitchStackMember.SerialNumber,
SwitchStack.SwitchStackMember.RoleDescription,
SwitchStack.SwitchStackMember.StateDescription,
LastSync
FROM Orion.NPM.SwitchStack

UNION

(SELECT
Caption,
'' AS [MacAddress],
MachineType AS [Model],
IOSVersion AS [SoftwareImage],
'' AS [SerialNumber],
'' AS [RoleDescription],
StatusDescription AS [StateDescription],
LastSync AS [LastSync]
FROM Orion.Nodes
WHERE Caption NOT IN (SELECT DisplayName FROM Orion.NPM.SwitchStack))

damon_2021

Our versions seem different and do not have the same structure as yours.

NPM_SwitchStack does not have nested SwitchStackMember etc. Also, there is no 'DisplayName' under both. SwitchStackMember contains NodeId and memberID along with other info.

For example, from 1st block, this works:

SELECT
NPM_SwitchStackMember.MacAddress,
NPM_SwitchStackMember.Model,
NPM_SwitchStackMember.SoftwareImage,
NPM_SwitchStackMember.SerialNumber
FROM [dbo].[NPM_SwitchStackMember]

All other info is not there.

From the second block, this works:

SELECT
Caption,
'' AS [MacAddress],
MachineType AS [Model],
IOSVersion AS [SoftwareImage],
'' AS [SerialNumber],
'' AS [RoleDescription],
StatusDescription AS [StateDescription],
LastSync AS [LastSync]
FROM [dbo].[Nodes]

MAC, Serial, and RoleDes are empty.

I already have a report working to display stacks. Is it possible to expand it to include the non-stacks as well in the same report?

Source: thwack.solarwinds.com/.../3396

adam.beedell

You're using SQL, he's using SWQL, SWQL is better for this stuff long term because the SQL will break if SW change the database between patches

Aside from that, the first 2 columns are easy, the last one can very based on vendors and equipment your environment uses (and sometimes what counts to the audience)