Report: Assets that have Software A installed but not Software B

[MD2Tech]

So I found this piece of code in the Report Center... but I wanted to know how it could be modified to report not only on the missing software but also on software installed? So I need it to report Assets that have SessionWorks Judge Edition installed but not Microsoft SQL Server 2014 (x64) or vise versa.

What I'm trying to determine are assets that could possibly be out of date with the latest SQL software because SessionWorks requires that SQL server be installed and I want to know which Assets have older versions of SQL.

Missing Software Code

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%YourSoftware%') And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName

[MD2Tech]

Awesome it works! Thanks!

[Bruce_B · Accepted solution]

You can use the report below to list assets that don't have software 1 but do have software 2. You can change the highlighted text to fit your requirements.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Software 1%') And
  tblAssetCustom.State = 1 And tblSoftwareUni.softwareName Like '%Software 2%'
Order By tblAssets.Domain,
  tblAssets.AssetName