cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Bart_M
Lansweeper Alumni
List of all assets and the switch and port they are connected to.

Only shows assets:
- Connected to a switch

Sorted on:
- Asset name

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblSNMPAssetMac.AssetMacAddress,
tblAssets_1.AssetName As Switch,
tblSNMPInfo.IfIndex As Port
From tsysAssetTypes
Inner Join tblAssets On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetMacAddress
On tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Order By tblAssets.AssetName
10 REPLIES 10
S_B
Engaged Sweeper II
How can I get the showing SWITCH to be clickable ?
esr
Champion Sweeper
We work it the other way around- switches, their ports, and the assets connected to the port if any. The opposite of what switch is an asset connected.

And, yup, the speed calculation is reporting back accurate numbers of 10 / 100 / 1000


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,
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,
tblAssets1.Username,
tblAssets1.Userdomain
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,
[If]
mark_owens
Engaged Sweeper
Is there a way to get this report to tell me which switch the assets are directly connected to? This lists everything as connected to my core switch.

Bart.M wrote:
List of all assets and the switch and port they are connected to.

Only shows assets:
- Connected to a switch

Sorted on:
- Asset name

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblSNMPAssetMac.AssetMacAddress,
tblAssets_1.AssetName As Switch,
tblSNMPInfo.IfIndex As Port
From tsysAssetTypes
Inner Join tblAssets On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetMacAddress
On tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Order By tblAssets.AssetName


JohnJones
Engaged Sweeper II
I worked it out 🙂

Displays:
Asset Tag of Device | Last User to Login to device | Alias of Port (DeskCode) | Asset Type | Asset MAC address | Switch | Port

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblSNMPInfo.ifAlias,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tblSNMPAssetMac.AssetMacAddress,
tblAssets_1.AssetName As Switch,
tblSNMPInfo.IfIndex As Port
From tsysAssetTypes
Inner Join tblAssets On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetMacAddress
On tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Order By tblAssets.AssetName
JohnJones
Engaged Sweeper II
Hi all,

Please forgive my ignorance, as I'm new to LS reporting.

On the switch asset pages there is a field "Alias" which is the port description on our Dell switches.
How would we input this data to the report.

We use this as our ports are named in relation to the desk code and would like to see which assets are sitting at which desks.
santeroc
Engaged Sweeper
Well, it works fine in my office.

I'm using HP-E Aruba 2920 as switch.

I have defined the login / password in order Lansweepr would be able to access them and I have lots of informations about them in Lansweeper.

Do you have managed switch and do you know there layer level ?

It should not work properly if your switch is too "basic".
santeroc
Engaged Sweeper
Hello,

I've just tried your comment : "Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,"
and the result seems to be bad.

Try this : " Ceiling(tblSNMPInfo.IfSpeed / 1000 / 100 / 10) As Speed,"

And tell me if it's better.
TechGuy
Engaged Sweeper II
santeroc wrote:
Hello,

I've just tried your comment : "Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,"
and the result seems to be bad.

Try this : " Ceiling(tblSNMPInfo.IfSpeed / 1000 / 100 / 10) As Speed,"

And tell me if it's better.


I just tried your suggestion, but I get the same output: "This report has no results."
Would you happen to know what are the prerequisites for getting this report to yield more than no data at all?

Thank you!
TechGuy
Engaged Sweeper II
Hello, Santeroc

Is the line "Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,"

Not supposed to be "Ceiling(tblSNMPInfo.IfSpeed / 100 / 1000) As Speed," instead?