Need to pull a report from 5.22

[Sdonovan415]

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

[Daniel_B · Accepted solution]

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