→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
davidc
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
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

View solution in original post

1 REPLY 1
Nick_VDB
Champion Sweeper III
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