Symantec Report

[mbatescny]

Looking for a way to create a report that shows me all machines that still have symantec antivirus installed on them. Not sure how do build a custom report under reports.

[Susan_A · Accepted solution]

I've posted a report below that lists the information you are after. You can run this report by following these instructions. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 unioned.assetid,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Lastseen,
  tsysOS.Image As icon,
  unioned.software,
  unioned.version,
  unioned.security_center As [Windows security center]
From ((Select a.assetid As assetid,
    a.software As software,
    a.version As version,
    b.software As security_center
  From (Select tblSoftware.AssetID As assetid,
      tblSoftwareUni.softwareName As software,
      tblSoftware.softwareVersion As version,
      tsysantivirus.Software As pattern
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
      Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
        tsysantivirus.Software) a
    Left Join (Select tblAntivirus.AssetID As assetid,
      tblAntivirus.DisplayName As software,
      Null As version,
      tsysantivirus.Software As pattern
    From tblAntivirus
      Inner Join tsysantivirus
        On tblAntivirus.DisplayName Like tsysantivirus.Software) b
      On a.assetid = b.assetid And a.pattern Like b.pattern)
  Union
  (Select tblAntivirus.AssetID As assetid,
    Null software,
    Null As version,
    tblAntivirus.DisplayName As security_center
  From tblAntivirus
  Where tblAntivirus.AntivirusId Not In (Select tblAntivirus.AntivirusId
    From tblAntivirus Join tsysantivirus On tblAntivirus.DisplayName Like
        tsysantivirus.Software))) unioned
  Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
  Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (unioned.software Like '%symantec%' And tblAssetCustom.State = 1) Or
  (unioned.security_center Like '%symantec%' And tblAssetCustom.State = 1)