Printers used by computers

[davidc]

I discovered a report very similar to what I am searching for:
http://www.lansweeper.com/Forum/yaf_postst12389_Printer-Inventory.aspx#post44784

However, I would like to add a column with the printer's name, as retrieved for the printer asset.

My SQL skills are apparently not up to the task.

Anyone know how to join it so that the Printer's "AssetName" is available by using the tblPrinters.Portname as the "IPAddress" for the printer?

Thanks in advance

[Nick_VDB · Accepted solution]

This question was answered by email.

We added an example report that will should give back the name of the asset when the portname matches the ipaddress. However, we cannot guarantee that the report will work for all assets as the portname does not always store the exact IP Address. There are many cases where the portname has a '_1' or other characters at the end of the IP Address. If a printer is connected to multiple ports on the machine it will also give back multiple IP Addresses with different endings in the same field.

To make the report fully work it would require a lot of filtering of any type of variations that can be given back in the portname. This would require implementing advanced SQL methods for which we cannot provide support.

We added an additional report that will give back all the installed printers on any machines.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblPrinters.Caption As Printer,
  tblAssets1.AssetName As [Printer Name],
  tblPrinters.Portname,
  tblPrinters.Printjobdatatype,
  tblPrinters.Printprocessor,
  tblPrinters.Sharename,
  tblPrinters.Status,
  tblPrinters.Comment,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblPrinters.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
  Inner Join tblAssets tblAssets1 On tblPrinters.Portname = tblAssets1.IPAddress
Where tblPrinters.Printprocessor Not Like '%winprint%' And
  tblAssetCustom.State = 1
Order By tblPrinters.Portname,
  tblAssets.AssetName

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblPrinters.Caption As Printer,
  tblPrinters.Portname,
  tblPrinters.Printjobdatatype,
  tblPrinters.Printprocessor,
  tblPrinters.Sharename,
  tblPrinters.Status,
  tblPrinters.Comment,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblPrinters.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Where tblAssetCustom.State = 1
Order By tblPrinters.Portname,
  tblAssets.AssetName