cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
servicedesk
Champion Sweeper II
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

4 REPLIES 4
servicedesk
Champion Sweeper II
thanks
Daniel_B
Lansweeper Alumni
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
Champion Sweeper II
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
Lansweeper Alumni
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