‎03-09-2021 05:02 PM
‎03-10-2021 09:06 PM
Select Top 1000000 Switch.AssetID,
Switch.icon,
Switch.AssetName,
Switch.[If],
Switch.ifName,
Switch.ifAlias,
Switch.IfDescription,
Switch.Type,
Switch.Admin,
Switch.MTU,
Switch.Speed,
Switch.[IP Address],
Switch.Mask,
Switch.Portname,
Switch.Vlan,
Switch.MacAddress,
tsysAssetTypes1.AssetTypeIcon16 As icon2,
Switch.deviceassetid,
tblAssets.AssetName As deviceassetname,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
Switch.AssetMacAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From (Select Top 1000000 tblAssets1.AssetID,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets1.AssetName,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.ifName,
tblSNMPInfo.ifAlias,
tblSNMPInfo.IfDescription,
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.Vlan,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Left Join tblAssets As tblAssets1 On tblSNMPInfo.AssetID =
tblAssets1.AssetID
Left Join tsysAssetTypes On tblAssets1.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 tsysAssetTypes.AssetTypename = 'Switch'
Order By tblAssets1.AssetName,
[If]) As Switch
Left Outer Join tblAssets On Switch.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes1 On tblAssets.Assettype =
tsysAssetTypes1.AssetType
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
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 Interface,
tblSNMPInfo.IfDescription As Name,
tblSNMPIfTypes.IfTypename As Type,
tblSNMPInfo.IfAdminstatus As Admin,
tblSNMPInfo.IfMTU As MTU,
Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
tblSNMPInfo.IfIPAddress As IP,
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MAC,
tblAssets1.AssetName As Asset
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 tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetMacAddress.AssetID
Where tsysAssetTypes.AssetTypename = 'switch'
Order By tblAssets.AssetName,
Interface
Select Top 1000000 Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End As 'Interface Status',
count(*) As 'Number of ports'
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 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') And tblSNMPIfTypes.IfTypename Like '%Ethernet%'
Group By Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End
‎03-11-2021 01:54 PM
rader wrote:
Lansweeper put a default one in called "Switch: Ports" that might help.
Here's what I have created as well. I also created one for a pie chart to be displayed next to Switch Ports Query.
1) Switch Ports Query:
Select Top 1000000 Switch.AssetID,
Switch.icon,
Switch.AssetName,
Switch.[If],
Switch.ifName,
Switch.ifAlias,
Switch.IfDescription,
Switch.Type,
Switch.Admin,
Switch.MTU,
Switch.Speed,
Switch.[IP Address],
Switch.Mask,
Switch.Portname,
Switch.Vlan,
Switch.MacAddress,
tsysAssetTypes1.AssetTypeIcon16 As icon2,
Switch.deviceassetid,
tblAssets.AssetName As deviceassetname,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
Switch.AssetMacAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From (Select Top 1000000 tblAssets1.AssetID,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets1.AssetName,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.ifName,
tblSNMPInfo.ifAlias,
tblSNMPInfo.IfDescription,
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.Vlan,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Left Join tblAssets As tblAssets1 On tblSNMPInfo.AssetID =
tblAssets1.AssetID
Left Join tsysAssetTypes On tblAssets1.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 tsysAssetTypes.AssetTypename = 'Switch'
Order By tblAssets1.AssetName,
[If]) As Switch
Left Outer Join tblAssets On Switch.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes1 On tblAssets.Assettype =
tsysAssetTypes1.AssetType
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
2) Switch Connections:
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 Interface,
tblSNMPInfo.IfDescription As Name,
tblSNMPIfTypes.IfTypename As Type,
tblSNMPInfo.IfAdminstatus As Admin,
tblSNMPInfo.IfMTU As MTU,
Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
tblSNMPInfo.IfIPAddress As IP,
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MAC,
tblAssets1.AssetName As Asset
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 tblAssetMacAddress On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Left Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetMacAddress.AssetID
Where tsysAssetTypes.AssetTypename = 'switch'
Order By tblAssets.AssetName,
Interface
3) Chart: Switch Ports in Use
Select Top 1000000 Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End As 'Interface Status',
count(*) As 'Number of ports'
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 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') And tblSNMPIfTypes.IfTypename Like '%Ethernet%'
Group By Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End
Hopefully you can find what you need in here. Good luck.
Randy
‎03-11-2021 06:24 PM
Thanks Randy, your reports are very helpful and I'm going to use them for other efforts. They don't quite get me to where I need to be with this report though. The "Switch: Ports" report you are talking about is actually the one I'm referring to in the thread title. I feel like I'm right there, but I'm missing something with one of my joins.
‎03-10-2021 06:09 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now