cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Carpa
Engaged Sweeper
Is it possible to create a new report such as the Software Overview one that only shows software installed on servers?

So OS would be "Windows Server ..." or maybe via a description in AD where it starts with "Server - Description of role"

Thanks in advance
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
nickt wrote:
When I paste this into a new report I get the following message.

Invalid SELECT statement. Unknown object name: "tblComputers".: Unexpected token "tblComputers" at line 0, column -1

Any help would be appreciated.

This thread is very old. The report no longer works in the most recent Lansweeper release, as the database tables have changed. A sample 5.X report can be seen below. If you are interested in building or modifying reports, we would recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  • Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Order By tblAssets.AssetName,
software,
version

View solution in original post

3 REPLIES 3
Susan_A
Lansweeper Alumni
nickt wrote:
When I paste this into a new report I get the following message.

Invalid SELECT statement. Unknown object name: "tblComputers".: Unexpected token "tblComputers" at line 0, column -1

Any help would be appreciated.

This thread is very old. The report no longer works in the most recent Lansweeper release, as the database tables have changed. A sample 5.X report can be seen below. If you are interested in building or modifying reports, we would recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  • Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Order By tblAssets.AssetName,
software,
version
nickt
Engaged Sweeper
When I paste this into a new report I get the following message.

Invalid SELECT statement. Unknown object name: "tblComputers".: Unexpected token "tblComputers" at line 0, column -1

Any help would be appreciated.
Hemoco
Lansweeper Alumni
We would recommend including server names in your report. If you were to generate a report similar to the Software Overview, you would not be able to link the software names to the names of just servers who have that software installed.

Please try the report below.
Select tblComputers.Computername, tblComputers.Domain, tblComputers.Computer,
tblComputers.Description, tblComputers.Firstseen, tblComputers.Lastseen,
tblSoftware.softwareName, tblSoftware.softwareVersion,
tblSoftware.SoftwarePublisher, tblSoftware.Lastchanged,
SoftwareCount.[Total Installations]
From tblComputers Inner Join
tblComputersystem On tblComputers.Computername =
tblComputersystem.Computername Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
(Select Count(tblSoftware.ComputerName) As [Total Installations],
tblSoftware.softwareName, tblSoftware.softwareVersion,
tblSoftware.SoftwarePublisher
From tblSoftware Inner Join
tblComputersystem On tblComputersystem.Computername =
tblSoftware.ComputerName
Where tblComputersystem.Domainrole > 1
Group By tblSoftware.softwareName, tblSoftware.softwareVersion,
tblSoftware.SoftwarePublisher) SoftwareCount On SoftwareCount.softwareName
= tblSoftware.softwareName And SoftwareCount.softwareVersion =
tblSoftware.softwareVersion And SoftwareCount.SoftwarePublisher =
tblSoftware.SoftwarePublisher
Where tblComputersystem.Domainrole > 1
Order By tblSoftware.softwareName, tblSoftware.softwareVersion,
tblComputers.ComputerUnique

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options.