Multiple Software Products not installed

[Notonyourradar]

Looking to create a report of Machines that do not Skype for Business Basic 2016, Microsoft Office Professional 2016, or Microsoft Office 365 ProPlus installed (and others). I am able to find reports on single pieces of software but can't seem to modify correctly to accommodate for multiple products. Below is what I was trying but having no luck.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tsysOS.OSname,
  tblOperatingsystem.Caption,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  tblADComputers.OU,
  tblComputersystem.SystemType
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where (tblSoftwareUni.softwareName Like 'Microsoft Office 365%' And
    tblSoftware.softwareVersion Like '16.%') Or
    (tblSoftwareUni.softwareName Like 'Microsoft Office Professional 2016%') Or
    (tblSoftwareUni.softwareName Like 'Skype For Business Basic 2016%') Or
    (tblSoftwareUni.softwareName Like 'Microsoft Office 365 ProPlus%' And
    tblSoftware.softwareVersion Like '16.%') Or
    (tblSoftwareUni.softwareName Like 'Skype voor Bedrijven Basic 2016%') Or
    (tblSoftwareUni.softwareName Like 'Office 365 16.0%') Or
    (tblSoftwareUni.softwareName Like 'Microsoft Office Professionnel Plus%' And
    tblSoftware.softwareVersion Like '16.%') Or
    (tblSoftwareUni.softwareName Like
    'Microsoft Office Professional Plus 2016%') Or
    (tblSoftwareUni.softwareName Like 'Microsoft Office 2016%' And
    tblAssetCustom.State = 1))
Order By tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tblAssets.AssetName

Any ideas?

[Esben_D]

If you want asset which do not have any of the products mentioned you will have to use And in your where clause to get the logic that the assets do not have software X and software Y and software Z.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  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 tblSoftwareUni.softwareName Not Like 'Microsoft Office 365%' And
  tblSoftwareUni.softwareName Not Like 'Microsoft Office Professional 2016%' And
  tblSoftwareUni.softwareName Not Like 'Skype For Business Basic 2016%' And
  tblSoftwareUni.softwareName Not Like 'Microsoft Office 365 ProPlus%'
  And tblSoftwareUni.softwareName Not Like 'Skype voor Bedrijven Basic 2016%'
  And tblSoftwareUni.softwareName Not Like 'Office 365 16.0%' And
  tblSoftwareUni.softwareName Not Like 'Microsoft Office Professionnal Plus%'
  And
  tblSoftwareUni.softwareName Not Like
  'Microsoft Office Professional Plus 2016%' And tblSoftwareUni.softwareName Not
  Like 'Microsoft Office 2016%' And tblSoftware.softwareVersion Not Like '16.%'
  And tblState.Statename = 'Active'
Order By tblAssets.AssetName

[Notonyourradar]

Doesn't seem to give me what I want and lists every windows asset we have - we are looking for it to report back on machines that don't have ANY of the products in the report. So if it has Skype for Business Basic, but not Office 365 it would not show on the report.

I am looking to show any machine that doesn't have ANY of these products on it.

[Esben_D]

Personally I think it is a lot easier to just use "Not like" instead of "Not in". I created a report using your where clause which should give you what you described.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  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 (tblSoftwareUni.softwareName Not Like 'Microsoft Office 365%' And
  tblSoftware.softwareVersion Not Like '16.%') Or
  (tblSoftwareUni.softwareName Not Like 'Microsoft Office Professional 2016%')
  Or
  (tblSoftwareUni.softwareName Not Like 'Skype For Business Basic 2016%') Or
  (tblSoftwareUni.softwareName Not Like 'Microsoft Office 365 ProPlus%' And
  tblSoftware.softwareVersion Not Like '16.%') Or
  (tblSoftwareUni.softwareName Not Like 'Skype voor Bedrijven Basic 2016%') Or
  (tblSoftwareUni.softwareName Not Like 'Office 365 16.0%') Or
  (tblSoftwareUni.softwareName Not Like 'Microsoft Office Professionnal Plus%'
  And tblSoftware.softwareVersion Not Like '16.%') Or
  (tblSoftwareUni.softwareName Not Like
  'Microsoft Office Professional Plus 2016%') Or
  (tblSoftwareUni.softwareName Not Like 'Microsoft Office 2016%' And
  tblState.Statename = 'Active')
Order By tblAssets.AssetName