
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-13-2017 09:07 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-16-2017 11:56 AM
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
1 REPLY 1

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-16-2017 11:56 AM
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
