Windows: All servers with Antivirus (Built-in)

[Daniel_B]

Old name: Server: All servers with Anti-virus (Built-in)

The report below lists Windows servers with Antivirus software.

The report will only list assets that meet all of the following criteria:

• The computer's state is set to "active".
  
• The computer has been successfully scanned at least once.
  
• The asset is a Windows server.
  
• The asset has antivirus software installed.
  
• The Antivirus software is listed as Antivirus software within Lansweeper under Software\Anti-Virus Settings.
  

Select Top 1000000 AntiVirusUpToDate.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  AntiVirusUpToDate.Software,
  AntiVirusUpToDate.Version,
  AntiVirusUpToDate.Enabled,
  AntiVirusUpToDate.Uptodate,
  AntiVirusUpToDate.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)) As AntiVirusUpToDate
  Inner Join tblAssetCustom On AntiVirusUpToDate.assetid = tblAssetCustom.AssetID
  Inner Join tblAssets On tblAssets.AssetID = AntiVirusUpToDate.AssetId
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
 Inner Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
 Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
    tblComputersystem.Domainrole
Where tblState.Statename = 'Active' And tblDomainroles.Domainrolename In
  ('Stand-alone server', 'Member server', 'Primary domain controller',
  'Backup domain controller')
Order By tblAssets.Domain,
  tblAssets.AssetName