→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
kosta
Engaged Sweeper II
Hi,

Today I can see the switches and export them.
I can also enter a switch and see the configured ports showing data such as: Name,interface number,alias,speed, mac address and asset name of the network device connected to the port, but I cannot export it.

We sometimes have electrical outage and for some reason Vlans may change to default ones, which in turn affects network printers, workstations etc..

It would be great to have an Exportable Report that will show the entire switches in the organization along with their configured ports with an added VLAN data column (today it doesn't show vlan data).

Even if I could export the data of a single switch, I would need to repeat the procedure about 20 times.

With a single exportable report containing the above, I would be able to export the data when needed and resolve network device issues quickly.

Thank you in advanced.
3 REPLIES 3
kosta
Engaged Sweeper II
Thank you very much for the solutions.

Both of them work as expected.
Hendrik_VE
Champion Sweeper III
I use the following (SQL Server) report which also shows me the vendor of the connected device when the MAC address is detected (if you still have table tsysMacVendor) and is color-coded based on the port status. Is very usefull to detect 'rogue' devices.

+1 for VLAN data.

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.IfDescription As Name,
tblSNMPIfTypes.IfTypename As Type,
case
when tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
else 'Down'
End As Admin,
tblSNMPInfo.IfMTU As MTU,
Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
tblSNMPInfo.IfIPAddress As IP,
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As SwitchportMAC,
tblSNMPAssetMac.AssetMacAddress As AssetMAC,
Case
When tblSNMPAssetMac.AssetMacAddress is Null Then 'No MAC detected'
When tsysMacVendor.Vendor Is Null Then 'MAC detected but Vendor not found'
Else tsysMacVendor.Vendor
End As AssetVendor,
tsysAssetTypes_1.AssetTypeIcon16 As icon2,
tblAssets1.AssetName As deviceassetname,
tblAssets1.Username,
tblAssets1.Userdomain,
tblSNMPInfo.Vlan,
Case
when tblSNMPInfo.IfAdminstatus = 1
Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
Left Join tsysMacVendor On
tsysMacVendor.MAC = SubString(tblSNMPAssetMac.AssetMacAddress, 1, 2) +
SubString(tblSNMPAssetMac.AssetMacAddress, 4, 2) +
SubString(tblSNMPAssetMac.AssetMacAddress, 7, 2)
Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetMacAddress.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename =
'switch' Or tsysAssetTypes.AssetTypename = 'router')
Order By tblAssets.AssetName,
[If]
JacobH
Champion Sweeper III
I modified 'Switch: Ports' report:



Select Top 1000000 aaa.AssetID,
aaa.icon,
aaa.AssetName,
aaa.IfDescription,
aaa.[If],
aaa.Type,
aaa.Admin,
aaa.MTU,
aaa.Speed,
aaa.[IP Address],
aaa.Mask,
aaa.Portname,
aaa.Vlan,
aaa.MacAddress,
tsysAssetTypes_1.AssetTypeIcon16 As icon2,
aaa.deviceassetid,
tblAssets.AssetName As deviceassetname,
aaa.AssetMacAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From (Select Top 1000000 tblAssets_1.AssetID,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets_1.AssetName,
tblSNMPInfo.IfIndex As [If],
tblSNMPIfTypes.IfTypename As Type,
Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
Else 'Testing'
End As Admin,
tblSNMPInfo.IfMTU As MTU,
Case
When (tblSNMPInfo.IfSpeed Is Null Or tblSNMPInfo.IfSpeed = 0) Then ''
When tblSNMPInfo.IfSpeed > 999999999 Then
Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000000000 As
float)) + 'Gbit'
When tblSNMPInfo.IfSpeed > 999999 Then
Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000000 As float))
+ 'Mbit'
Else Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000 As float))
+ 'Kbit'
End As Speed,
tblSNMPInfo.IfIPAddress As [IP Address],
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MacAddress,
tblSNMPAssetMac.AssetMacAddress,
tblSNMPInfo.Portname,
tblSNMPInfo.IfDescription,
tblSNMPInfo.Vlan,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Inner Join tblAssets As tblAssets_1 On tblSNMPInfo.AssetID =
tblAssets_1.AssetID
Inner Join tsysAssetTypes On tblAssets_1.Assettype =
tsysAssetTypes.AssetType
Left Outer Join tblSNMPIfTypes On tblSNMPInfo.IfType =
tblSNMPIfTypes.IfType
Left Outer Join (tblAssetMacAddress
Right Outer Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress) On tblSNMPInfo.IfIndex =
tblSNMPAssetMac.IfIndex And tblSNMPInfo.AssetID =
tblSNMPAssetMac.AssetID
Where tblAssets_1.AssetID Is Not Null And tblAssets_1.Assettype = 6
Order By tblAssets_1.AssetName,
[If]) As aaa
Left Outer Join tblAssets On aaa.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets.Assettype =
tsysAssetTypes_1.AssetType
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID