Windows: All workstations with antivirus software (Built-in)

[Daniel_B]

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

[iatech]

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]

Have you been able to figure out the report to show only machines with DAT version older than 7 days?

[iatech]

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.