Creating a Report that identifies all systems with MS Office that are missing an Outlook Plugin?

[novasam]

I tried a dynamic group, but putting two filters about software that are opposite in condition breaks the Dynamic Group.

Basically the object is to Take all systems that have 'Microsoft Office%' installed, and see which of these are missing 'Cisco IronPort Email Security Plug-In'. I'm guessing because this query is a little more complicated to pull it will need to be a report if possible.

I'n guessing I would need to somehow add it to my existing query:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Cisco IronPort Email Security Plug-In%')
And tblAssets.Lastseen <> '' And tblAssets.Assettype = -1 And
tblComputersystem.Domainrole <= 1
Order By tblAssets.Domain,
tblAssets.AssetName

[novasam]

Works Perfect, thank you.

[Daniel_B · Accepted solution]

A second subquery which gets you a list of computers with "Microsoft Office%" installed should do it:

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress As IP,
  tblAssets.Lastseen
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where
    tblSoftwareUni.softwareName Like '%Cisco IronPort Email Security Plug-In%')
  And tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like 'Microsoft Office%') And
  tblAssets.Lastseen <> '' And tblAssets.Assettype = -1 And
  tblComputersystem.Domainrole <= 1
Order By tblAssets.Domain,
  tblAssets.AssetName