Create report with assetname and software version of 2 application

[pbrinkhoff]

Hi,

I'm looking for a report with the following fields:
| AssetName | Google Chrome installed? | Google Chrome version | Notepad++ installed? | Notepad++ Version |

Who can help?

[ASismey · Accepted solution]

Hi , This should be a good starting point for you : 

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Case
    When Google.AssetID Is Not Null Then 'Yes'
    Else 'No'
  End As [Google Chrome Installed],
  Google.softwareVersion As [Google Chrome Ver],
  Case
    When Notepad.AssetID Is Not Null Then 'Yes'
    Else 'No'
  End As [Notepad++ Installed],
  Notepad.softwareVersion As [Notepad Ver]
From tblassets
  Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Chrome%') Google On
      Google.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.softwareVersion,
      tblSoftwareUni.softwareName,
      tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Notepad++%') Notepad On
      Notepad.AssetID = tblassets.AssetID
Where tblassetcustom.State = 1

[pbrinkhoff]

that works! thanks!