cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mbatescny
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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)

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
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)

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now