Network Printer Report with Gateway

[jeremyhutson]

I use the below, and it gets me the printers I need.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.Printedpages,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tblAssets.Mac,
tblAssetCustom.Custom1
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename = 'printer' And tblAssetCustom.State = 1


But when I add the tblNetwork. I get no results.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.Printedpages,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tblAssets.Mac,
tblAssetCustom.Custom1
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tsysAssetTypes.AssetTypename = 'printer' And tblAssetCustom.State = 1

[Esben_D]

Port information is not stored in the Network table. The network table contains information about the attributes and behaviors of a network adapter. It only stores Windows computer data pulled from the Win32_NetworkAdapterConfiguration WMI class.

Port information for SNMP devices is stored in tblSNMPInfo

If you're looking for specific information like this, I would recommend using our database documentation, you can find an explanation on where to find it here: https://www.lansweeper.com/Forum/yaf_postsm38296_Lansweeper-database-dictionary.aspx


The report below should work for you. You can always rearrange some columns since I just added the most common ones at the end.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.Printedpages,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tblAssetCustom.Custom1,
  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
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
  Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
Where tsysAssetTypes.AssetTypename = 'printer' And tblAssetCustom.State = 1