cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
luisch
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Tom_P
Lansweeper Employee
Lansweeper Employee
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

View solution in original post

1 REPLY 1
Tom_P
Lansweeper Employee
Lansweeper Employee
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