Custom Report: List of All Software installed only on Desktops through network.

[luisch]

So i found this code on this thread - https://www.lansweeper.com/Forum/yaf_postst6116_Custom-Report--All-Software-installed-on-Desktops.aspx#post26783

but when i use it i get these errors below

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

Code Below -

Select Distinct Top 1000000 tblComputers.Computername,
tblComputers.ComputerUnique, tblComputers.Domain, tblSoftware.softwareName,
tblSoftware.softwareVersion, tblSoftware.SoftwarePublisher,
SoftwareCount.[Number of Installations]
From tblComputers Inner Join
tblSystemEnclosure On tblComputers.Computername =
tblSystemEnclosure.Computername Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Inner Join
(Select tblSoftware.softwareName, tblSoftware.softwareVersion,
tblSoftware.SoftwarePublisher, Count(tblSoftware.ComputerName) As
[Number of Installations]
From tblSoftware Inner Join
tblSystemEnclosure On tblSystemEnclosure.Computername =
tblSoftware.ComputerName
Group By tblSoftware.softwareName, tblSoftware.softwareVersion,
tblSoftware.SoftwarePublisher, tblSystemEnclosure.ChassisTypes
Having (tblSystemEnclosure.ChassisTypes = '3') Or
(tblSystemEnclosure.ChassisTypes = '4') Or
(tblSystemEnclosure.ChassisTypes = '6')) SoftwareCount On
SoftwareCount.softwareName = tblSoftware.softwareName And
SoftwareCount.softwareVersion = tblSoftware.softwareVersion
Where (tblSystemEnclosure.ChassisTypes = '3') Or
(tblSystemEnclosure.ChassisTypes = '4') Or
(tblSystemEnclosure.ChassisTypes = '6')
Order By tblComputers.ComputerUnique, tblSoftware.softwareName,
tblSoftware.softwareVersion

[Tom_P · Accepted solution]

The report you are referring to is an older report that was created for an older version of Lansweeper. As the structure of the database was altered in recent versions, the error you are currently receiving is expected. To get a list of all software installed only on Desktops, you can make use of the following SQL:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tblPortableBattery On tblPortableBattery.AssetID = tblAssets.AssetID
Where tblState.Statename = 'Active' And   tblPortableBattery.AssetID is null
Order By tblAssets.Domain, tblAssets.AssetName, Software