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

VLAN reports

I have a all Cisco environment.

I have NPM 10.2.2 and NCM 7.0.2

The two things i need desperate in NCM are

1. Report or view showing which VLANs are on which switches. With the posibility to filter showing only switche where fx. VLAN 100 are or which VLANs are on switche which name is starting with DK

2. Report or view that shows VLAN assignment to ports again with the posibility to filter based on VLAN number.

Find more posts tagged with

cisco_vlan_report_view

Accepted answers

All comments

mipe

I hope anyone can help.

Based on the standard NCM report "Inventory / Switch Ports" (which don't work) i have created this SQL statement i REALLY would like to get into NCM as a report.

SELECT [Nodes].[NodeCaption],[Interfaces].[InterfaceDescription],[Interfaces].[InterfaceAlias],[BRIDGEPORTS].[VLANID]
FROM BridgePorts JOIN Nodes ON BridgePorts.NodeID = Nodes.NodeID JOIN Interfaces ON BridgePorts.NodeID = Interfaces.NodeID AND BridgePorts.InterfaceIndex = Interfaces.InterfaceIndex
ORDER BY [Nodes].[NodeCaption],[Interfaces].[InterfaceDescription],[Interfaces].[InterfaceAlias],[BRIDGEPORTS].[VLANID]

I have tried to copy the standard report file from "C:\Program Files\Solarwinds\Configuration Management\Reports\SwitchPorts.ConfigMgmtReport" into a new file

And then replace all the values with the parts from the SQL statement above. I can't get it to work. When i run the report in NCM i get this error. I have ofcause tried to du a full inventory many times.

"You cannot run inventory reports before running an inventory on your nodes. Please perform full inventory."

Anyone that can help creating a ConfigMgmtReport file that works ?

Here is the output of my attempts.

mipe

Not anyone that can help ?

cvachovecj

Hi Michael,

I hope to have the answer for you soon.

Regards,

Jiri

ElDeeBee

Michael,

Can you run the sql query on the database and get results?

If any of the tables, bridgeport, interfaces, or nodes, are empty, the query should fail and you get the inventory message.

If you have run a full inventory, then check the inventory settings to ensure you are requesting the bridgeport data.

cvachovecj

Michael,

your request for this new report type was logged as item no. 129830.

Regards,

Jiri

mipe

Yes, the SQL works fine when i run it on the database.

I have run full report but the build in one, don't seem to work proberly. If i filter fx. bu VLAN ID 30

I only see 1 entry. When i run my own SQL statement against the SQL database (i have based it on the build in report) i get 62 hits.

So the data is there alright.

mipe

Hi Jiri

Any news on the report ?

swbv212

The query above displays limited information resulting in only a ffour switches when there should be many more even after running an inventory. I noticed the bridgeport table in the SolarWindsNCM database only contains a few entries which I believe to part of the problem. I have all the device inventory settings enabled in NCM but still does not populate after running another inventory. Any idea wy the table won't update?

cnorborg

Hmm.. Played with this a bit, couldn't find what I wanted in the NCM tables, so I switched to NPM tables instead. This probably means to get complete reports you would need NPM to manage all interfaces that you're interested in. If you are ok with a single line per vlan, this report will work (ie: if a switchport has 5 vlans on it, it will have 5 lines in this report). Note no JOIN's in this report!!

SELECT NPIM.NodeVlan.Node.Caption, NPIM.Interface.IfName, VlanId

FROM Orion.NodePortInterfaceMap NPIM

WHERE NOT (NPIM.Interface.IfName = '') AND NOT (NPIM.NodeVlan.Node.Caption = '')

ORDER BY NPIM.NodeID, IfIndex, VlanId

This report you could easily modify to get all the ports on a given VLAN by changing the sort order.

To get this to look "neater" though (ie: all VLANS on a single line), you will have to go to full SQL I think. I know of no ways to aggregate multiple lines of a sub-query into a single column in SWQL. Here it is in SQL though...

SELECT DISTINCT ND.Caption, I.IfName,

STUFF((SELECT ',' + CAST (VlanID AS VarChar)

FROM NodePortInterfaceMap NPIM2

WHERE (NPIM2.NodeID = NPIM.NodeID) AND (NPIM2.PortID = NPIM.PortID)

FOR XML PATH('')), 1, 1, '') AS VLANS

FROM NodePortInterfaceMap NPIM

JOIN NodesData ND ON (NPIM.NodeID = ND.NodeID)

JOIN Interfaces I ON ((NPIM.NodeID = I.NodeID) AND (NPIM.IfIndex = I.InterfaceIndex))

WHERE NOT (I.IfName = '') AND NOT (ND.Caption = '')

ORDER BY ND.Caption, IfName

I can't think of a way to get this report to do all the ports on a VLAN and look good...

Let me know what you think?

vispetto

SQL query for report 1 and 2: Uncomment the VlanID and / or Caption

SELECT

NodeVlans.VlanID,

NCM_IPAddresses.IPAddress,

NodesData.Caption,

NodesData.Vendor,

NCM_IPAddresses.InterfaceIndex

FROM NodeVlans

INNER JOIN NodesData NodesData ON NodesData.NodeID = NodeVlans.NodeID

INNER JOIN NCM_NodeProperties NCM_NodeProperties ON NCM_NodeProperties.CoreNodeID = NodesData.NodeID

INNER JOIN NCM_IPAddresses NCM_IPAddresses ON NCM_IPAddresses.NodeID = NCM_NodeProperties.NodeID

WHERE Vendor = 'Cisco'

--AND VlanID = 'XXX'

--AND Caption LIKE '%XXX%'

ORDER BY Caption, InterfaceIndex ASC

VLan report.JPG