cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jwalton4th
Engaged Sweeper
I'm trying to create a report that will tell me what systems have Windows Defender installed as anti-virus and ONLY Windows Defender. I have hundreds of systems that I am moving over from one anti-virus vendor to another. We have many that list both Windows Defender and another anti-virus product. I do understand that you can clean WMI to list the correct and only active anti-virus but this requires reboots across the board and not something we want to do between now and the next maintenance/patch cycle. Below is what I tried but it still showed systems with both Windows Defender and the other products on them.

Also, in this report, how do I create the links from the Asset to the Asset Page within the report? Thank you!

Select Top 1000000 tblassets.AssetID As AssetID1,
tblassets.AssetName As AssetName1,
tsysassettypes.AssetTypename As AssetTypename1,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress As IPAddress1,
tblassets.Lastseen As Lastseen1,
tblassets.Lasttried As Lasttried1,
tblAntivirus.AntivirusId,
tblAntivirus.DisplayName,
tblAntivirus.AssetID,
tblAntivirus.ProductState,
tblAntivirus.LastChanged,
tblAntivirus.onAccessScanningEnabled,
tblAntivirus.productUpToDate
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblAntivirus On tblassets.AssetID = tblAntivirus.AssetID
Where tblAntivirus.DisplayName Like '%Windows Defender%' And
tblAntivirus.DisplayName Not Like '%Sophos%' And
tblAntivirus.DisplayName Not Like '%Sentinel%' And tblassetcustom.State = 1


EDIT:

As an afterthought, I have a built in report that lists workstations without anti-virus installed. Ideally, I would like to add to it that any system with ONLY Windows Defender installed for anti-virus, would show in the report. Here is that query:

Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.StartIP <=
tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Inner Join tsysantivirus On
tblSoftwareUni.softwareName Like tsysantivirus.Software) And
tblAssets.AssetID Not In (Select tblAntivirus.AssetID
From tblAntivirus) And tblComputersystem.Domainrole <= 1 And
tblAssetCustom.State = 1
Order By tblAssets.AssetName
2 REPLIES 2
Andy_Sismey
Champion Sweeper III
Hi, Give this a try , I wrote it very quickly so may need a tweak or two ?

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
Case
When AV.AssetID Is Not Null Then 'Yes'
Else 'No'
End As AV,
AV.DisplayName
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Left Join (Select tblAntivirus.AssetID,
tblAntivirus.DisplayName
From lansweeperdb.dbo.tblAntivirus
Where tblAntivirus.DisplayName = 'Windows Defender') AV On
tblassets.AssetID = AV.AssetID
Where tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.State = 1
Order By AV
THANK YOU! It wasn't 100% what I wanted but it was enough so that I can modify it to my needs. Appreciate your help!

Andy.S wrote:
Hi, Give this a try , I wrote it very quickly so may need a tweak or two ?

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
Case
When AV.AssetID Is Not Null Then 'Yes'
Else 'No'
End As AV,
AV.DisplayName
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Left Join (Select tblAntivirus.AssetID,
tblAntivirus.DisplayName
From lansweeperdb.dbo.tblAntivirus
Where tblAntivirus.DisplayName = 'Windows Defender') AV On
tblassets.AssetID = AV.AssetID
Where tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.State = 1
Order By AV