Report computers with two antivirus software

[servicedesk]

I noticed that I have computers with two antivirus installed.

I recycled a report to list the computers that has the antivirus "A" installed, but I would like list the computers that has both antivirus installed... How should I update it?

thanks

Select Top 1000000 tblAssets.AssetID,
  tblAssets.IPAddress,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  tblSoftwareUni.SoftwarePublisher As publisher,
  tsysOS.Image As icon,
  tblADComputers.OU
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.IPAddress Like '10.162.%' And tblSoftwareUni.softwareName Like
  '%McAfee Virus and Spyware Protection Service%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  software,
  version

[servicedesk]

thanks

[Daniel_B · Accepted solution]

Then you can use this version which is simpler:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.IPAddress,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tsysOS.Image As icon,
  tblADComputers.OU
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
      tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like '%Product A%') And
  tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
      tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like '%Product B%') And
  tblAssets.IPAddress Like '10.162.%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[servicedesk]

Thanks works, but duplicates the machine names. One line to show that has the software A and other line to show that has the software B.

Is there any way to update the code without duplicate machine names? I don't really need the fields showing the software and version.

Thanks again

[Daniel_B]

The following report will list computers which have two specific software names installed:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.IPAddress,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  tblSoftwareUni.SoftwarePublisher As publisher,
  tsysOS.Image As icon,
  tblADComputers.OU
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
      tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like '%Product A%') And
  tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
      tblSoftwareUni.SoftID
  Where tblSoftwareUni.softwareName Like '%Product B%') And
  tblAssets.IPAddress Like '10.162.%' And (tblSoftwareUni.softwareName Like
    '%Product A%' Or tblSoftwareUni.softwareName Like '%Product B%') And
  tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  software,
  version