Software List Not Tracked in a Software License List

[Jay29]

Hi, I am trying to list all installed software by asset minus software listed in a software license list. This works now with only one license, but if I add another license the report will remove the software from both licenses. Do I need a subquery? How do i exclude the license query from the software query?

Backstory, I am trying to create standard builds based on asset groups and want to exclude software listed in the license list that includes hardware specifics. I can see this potentially expanding to other license lists to categorize software, as authorized and unauthorized do not allow granular control.

License query

Select Top 1000000 tblSublicenses.softwareName
From tblLicenses
  Inner Join tblSublicenses On tblLicenses.LicenseidID =
    tblSublicenses.LicenseidID
Where tblLicenses.softwareName = 'Do Not Track'

Software query

Select Top 1000000 SubString(tblAssets.AssetName, 1, 3) As AssetGroup,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname As OS,
  tblSoftwareUni.softwareName
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tsysOS.OSname = 'Win 10' Or tsysOS.OSname = 'Win 7')
Order By tblAssets.AssetName

Software query that will not work once another license is added

Select Top 1000000 SubString(tblAssets.AssetName, 1, 3) As AssetGroup,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname As OS,
  tblSoftwareUni.softwareName,
  tblLicenses.softwareName As softwareName1
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tblSublicenses On tblSoftwareUni.softwareName =
    tblSublicenses.softwareName
  Left Join tblLicenses On tblLicenses.LicenseidID = tblSublicenses.LicenseidID
Where (tsysOS.OSname = 'Win 10' Or tsysOS.OSname = 'Win 7') And
  tblSublicenses.softwareName Is Null
Order By tblAssets.AssetName

Thank you