Report needed with the PC and their network connection

[simonch]

Dear all,

Please let me know if there is a report where can i see for all my assets the below info:

Switch name, Switch port, Asset Name, Ip Address, Mac Address

Thanks,

Simon

[Hendrik_VE · Accepted solution]

This report should give you what you're looking for:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Mac As MACAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion,
tblAssets.Description) As OS,
LSSwitch.AssetName As [Connected To],
LSSwitch.IPAddress As [Switch IP],
tblSNMPInfo.ifName,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
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
Left Join tblAssets LSSwitch On tblSNMPInfo.AssetID = LSSwitch.AssetID
Where tblAssets.Assettype <> 66
Order By AssetType

[simonch]

Many thanks Hendrik!