Report combining asset information and specific software

[flemming_jul]

Can you please help create a report that adds information about one or more software on the assetlist?
The report "Computer: System configuration overview" is a good detailed report, but sometimes I am also asked to report on which McAfee, Microsoft Office and/or Adobe reader version is installed on these machines. Is this something that you can help with?
/Flemming

[Daniel_B · Accepted solution]

This can be done with subqueries. Please find an example report below. Instructions for running reports can be found here. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  
• Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.Image As icon,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblAssets.Username,
  tblAssets.Userdomain,
  Case When tblAssets.AssetID In (Select tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
        tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'McAfee%') Then 'installed'
    Else '---' End As [Mc Afee],
  Case When tblAssets.AssetID In (Select tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
        tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'Microsoft Office % 20%') Then
    'installed' Else '---' End As [MS Office],
  Case When tblAssets.AssetID In (Select tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
        tblSoftware.softID
    Where tblSoftwareUni.softwareName Like 'Adobe Reader%') Then 'installed'
    Else '---' End As [Adobe Reader],
  tblAssets.Memory,
  Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
  numeric) As nvarchar) As [Disk size],
  tblAssets.NrProcessors As #CPU,
  tblAssets.Processor,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber As Serial,
  tblAssets.IPAddress As [IP Address],
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By tblAssets.AssetID