cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Sdonovan415
Engaged Sweeper II
Hi All,

I am still learning SQL and was wondering if there is a way to create a query to be able to pull a report to list all software installed on each asset. I originally had this query but it does not list the OS.

Select
tblAssets.AssetName,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
In order to list information about the OS, either add tsysOS.OSname to your report or add another table, tblOperatingsystem, and include tblOperatingsystem.Caption in your report. The following example uses tblOperatingsystem.Caption:

Select Top 1000000 tblAssets.AssetName,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged,
tblOperatingsystem.Caption As OS
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
In order to list information about the OS, either add tsysOS.OSname to your report or add another table, tblOperatingsystem, and include tblOperatingsystem.Caption in your report. The following example uses tblOperatingsystem.Caption:

Select Top 1000000 tblAssets.AssetName,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged,
tblOperatingsystem.Caption As OS
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName