I create a custom table to see Vlan by ports on switch this is how to do it
- put your switch on NCM licence : Settings → Manage Node → choose your node → More Actions → Add node to NCM
- Create a report : Reports → All reports → Manage Reports → Create New report → Select Custom Table → Selection Method → Advance Database Query (SQL , SWQL) → SQL → put the query (just after ) → Add all collumn → Next → Enter a Report Title → Next → Next etc. (Report Create)
SELECT
X.NodeCaption,
X.Interface,
/* MODE */
CASE
WHEN CHARINDEX('switchport mode trunk', X.Bloc) > 0
THEN 'TRUNK'
WHEN CHARINDEX('switchport mode access', X.Bloc) > 0
THEN 'ACCESS'
ELSE NULL
END AS Mode,
/* VLAN ACCESS */
CASE
WHEN CHARINDEX('switchport access vlan ', X.Bloc) > 0
THEN TRY_CAST(
LTRIM(RTRIM(
SUBSTRING(
X.Bloc,
CHARINDEX('switchport access vlan ', X.Bloc)
+ LEN('switchport access vlan '),
CHARINDEX(
CHAR(13),
X.Bloc + CHAR(13),
CHARINDEX('switchport access vlan ', X.Bloc)
)
-
(
CHARINDEX('switchport access vlan ', X.Bloc)
+ LEN('switchport access vlan ')
)
)
))
AS INT
)
ELSE NULL
END AS AccessVlan,
/* VLAN VOICE */
CASE
WHEN CHARINDEX('switchport voice vlan ', X.Bloc) > 0
THEN TRY_CAST(
LTRIM(RTRIM(
SUBSTRING(
X.Bloc,
CHARINDEX('switchport voice vlan ', X.Bloc)
+ LEN('switchport voice vlan '),
CHARINDEX(
CHAR(13),
X.Bloc + CHAR(13),
CHARINDEX('switchport voice vlan ', X.Bloc)
)
-
(
CHARINDEX('switchport voice vlan ', X.Bloc)
+ LEN('switchport voice vlan ')
)
)
))
AS INT
)
ELSE NULL
END AS VoiceVlan,
/* VLAN NATIF */
CASE
WHEN CHARINDEX('switchport trunk native vlan ', X.Bloc) > 0
THEN TRY_CAST(
LTRIM(RTRIM(
SUBSTRING(
X.Bloc,
CHARINDEX('switchport trunk native vlan ', X.Bloc)
+ LEN('switchport trunk native vlan '),
CHARINDEX(
CHAR(13),
X.Bloc + CHAR(13),
CHARINDEX('switchport trunk native vlan ', X.Bloc)
)
-
(
CHARINDEX('switchport trunk native vlan ', X.Bloc)
+ LEN('switchport trunk native vlan ')
)
)
))
AS INT
)
ELSE NULL
END AS NativeVlan,
/* VLAN AUTORISES SUR TRUNK */
CASE
WHEN CHARINDEX('switchport trunk allowed vlan ', X.Bloc) > 0
THEN
LTRIM(RTRIM(
SUBSTRING(
X.Bloc,
CHARINDEX('switchport trunk allowed vlan ', X.Bloc)
+ LEN('switchport trunk allowed vlan '),
CHARINDEX(
CHAR(13),
X.Bloc + CHAR(13),
CHARINDEX('switchport trunk allowed vlan ', X.Bloc)
)
-
(
CHARINDEX('switchport trunk allowed vlan ', X.Bloc)
+ LEN('switchport trunk allowed vlan ')
)
)
))
ELSE NULL
END AS TrunkVlans
FROM
(
SELECT
D.NodeCaption,
D.CoreNodeId,
LTRIM(RTRIM(S.value)) AS Bloc,
/* NOM INTERFACE */
LTRIM(RTRIM(
SUBSTRING(
LTRIM(RTRIM(S.value)),
LEN('interface ') + 1,
CHARINDEX(
CHAR(13),
LTRIM(RTRIM(S.value)) + CHAR(13),
LEN('interface ') + 1
) - LEN('interface ') - 1
)
)) AS Interface
FROM
(
SELECT TOP 1
N.NodeCaption,
A.Config,
N.CoreNodeId
FROM NCM_ConfigArchive A
INNER JOIN NCM_Nodes N
ON N.NodeId = A.NodeId
WHERE N.CoreNodeId = 149
ORDER BY A.DownloadTime DESC
) D
CROSS APPLY STRING_SPLIT(
REPLACE(D.Config, 'interface ', '|interface '),
'|'
) S
WHERE LTRIM(S.value) LIKE 'interface %'
) X
WHERE
X.Interface LIKE 'FastEthernet%'
OR X.Interface LIKE 'GigabitEthernet%'
OR X.Interface LIKE 'TenGigabitEthernet%'
OR X.Interface LIKE 'TwentyFiveGigE%'
OR X.Interface LIKE 'FortyGigabitEthernet%'
OR X.Interface LIKE 'HundredGigE%'
ORDER BY X.Interface;
On your Switch Dashboard
- Customize Page → Add Widgets → custom table → Drag and drop the widget on your dashboard → Done Adding widget → Done Editing
- On Custom Table Widget → Unchack Use Current network object as Data Source → Click Load Table Configuration From Report → Select your report name → click Use button
- Edit Datasource → Change the values 149 on where clause to ${NodeID} → Update Data Source → Save
It will look like that
Switch Name | Interface | Mode | Access Vlan | Voice Vlan | Native Vlan | Trunk Vlans |
|---|
Node name | I 1 | Trunk | Null | Null | Null | 1, 2 ,3 … |
Node name | I2 | ACCESS | 5 | 12 | Null | Null |
It's done
Hope it can be usefull for someone
