Assets and the switch they're connected to

[Bart_M]

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

[S_B]

How can I get the showing SWITCH to be clickable ?

[esr]

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]

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]

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]

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]

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]

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]

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]

Hello, Santeroc

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

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