Software Overview on Servers

[Carpa]

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

[Susan_A · Accepted solution]

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]

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]

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.