Report listing all device adapters plus showing if specific software is installed

[skz000]

Hello,

I've been using this report to get all mac addresses of device for all existing adapters

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tblassets.Username,
  tsysOS.OSname,
  tblassets.IPAddress,
  tblNetwork.Description,
  tblNetwork.MACaddress,
  tblassets.Lastseen,
  tblADComputers.OU
From tblassets
  Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
  Inner Join tblNetwork On tblassets.AssetID = tblNetwork.AssetID
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
Where tblNetwork.Description Like '%' And tblassetcustom.State = 1

But I would love to see if specific software is installed in the same report as well.

Is there a way to integrate that it would show if e.g. "Firefox" is installed in the same report for that device. If it's not installed the column should just be blank as I still need adapters MAC addresses (computers can't be removed from this list - main focus are adapters MAC addresses)

Is it possible to achieve this ?

[ASismey · Accepted solution]

This should get you started : 

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tblassets.Username,
  tsysOS.OSname,
  tblassets.IPAddress,
  tblNetwork.Description,
  tblNetwork.MACaddress,
  tblassets.Lastseen,
  tblADComputers.OU,
  Case
    When FireFox.AssetID Is Not Null Then 'Yes'
    Else 'No'
  End As [firefor Installed]
From tblassets
  Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
  Inner Join tblNetwork On tblassets.AssetID = tblNetwork.AssetID
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.softwareName
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Mozilla Firefox%') FireFox On
      FireFox.AssetID = tblassets.AssetID
Where tblNetwork.Description Like '%' And tblassetcustom.State = 1

[skz000]

Thank You! this is exactly what I needed.