cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
Old name: Workstation: All workstations with Anti-virus software (Built-in)

The below report will list all workstations with antivirus software.

The report will only list assets that meet all of the following criteria:
  • The asset state is set to "active".
  • The asset has been successfully scanned at least once.
  • The asset is a Windows asset.
  • The asset is a workstation.
  • The antivirus software is recognized by the Security Center on the client or listed under Software\Anti-Virus Settings in Lansweeper.
  • The asset has antivirus software detected.

Select Top 1000000 AntiVirus.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,
AntiVirus.Software,
AntiVirus.Version,
AntiVirus.Enabled,
AntiVirus.UpToDate,
AntiVirus.RetrievedFrom
From ((Select SoftwareComparison.AssetID As AssetID,
SoftwareComparison.Software As Software,
SoftwareComparison.Version As Version,
'Software Comparison' As RetrievedFrom,
'' As Enabled,
'' As UpToDate
From (Select tblSoftware.AssetID As AssetID,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) As SoftwareComparison)
Union
(Select tblAntivirus.AssetID As AssetID,
tblAntivirus.DisplayName As Software,
Null As Version,
'WMI' As RetrievedFrom,
Case When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes' Else 'No'
End As Enabled,
Case When tblAntivirus.productUpToDate = 1 Then 'Yes' Else 'No'
End As UpToDate
From tblAntivirus)) AntiVirus
Inner Join tblAssetCustom On AntiVirus.AssetID = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = AntiVirus.AssetID
Inner Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblComputersystem.Domainrole =
tblDomainroles.Domainrole
Inner Join tblState On tblAssetCustom.State = tblState.State
Where tblDomainroles.Domainrolename In ('Stand-alone Workstation',
'Member Workstation') And tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName,
AntiVirus.Software
3 REPLIES 3
iatech
Engaged Sweeper II
I did finally. 🙂

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery.Value As [McAfee Dat Date],
SubQuery.Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join (Select Top 1000000 tblRegistry.Value,
Count(tblRegistry.AssetID) As Count
From tblRegistry
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblRegistry.AssetID
Where tblAssetCustom.State = 1 And
tblRegistry.Regkey =
'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\McAfee\AVEngine' And
tblRegistry.Valuename = 'AVDatDate'
Group By tblRegistry.Value,
tblRegistry.Regkey,
tblRegistry.Valuename) SubQuery On SubQuery.Value = tblRegistry.Value
Inner Join tblAntivirus On tblAssets.AssetID = tblAntivirus.AssetID
Where SubQuery.Count > 1 And tblAssetCustom.State = 1 And GetDate() -
SubQuery.Value > 7
Order By [McAfee Dat Date],
tblAssets.Domain,
tblAssets.AssetName
mdotadmin
Engaged Sweeper
Have you been able to figure out the report to show only machines with DAT version older than 7 days?
iatech
Engaged Sweeper II
Good morning.

Is there a way to modify this report or create a new one so that I can find all machines that have a DAT version older than 7 days from the current date? We run McAfee on our network so the registry key I want to pull is HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\McAfee\AVEngine\AVDatDate. Any help would be appreciated.