cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
zharklm
Engaged Sweeper
Hi All,

I have seen many NAV/SAV report requests, some cover what people need, some don't. Thought I would share what I have created as it does everything I need.

Required scanned files are VPC32.exe, using the below reports you will be able to tell machines that do not have it installed, use that list to add another path to the file if the machine missing it has it installed under a different path. ie 64 bit. Use %programfiles% to help also.

Required registry scan at current version is HKLM\SOFTWARE\Symantec\SharedDefs\DEFWATCH_10

NOTE: I have just copied and pasted my views to get the meat of the code, you will need to change the code to create the views, if you don't know enough sql for that the info would be elsewhere on these forums.

NOTE: I don't have enough clients to worry about the reports killing anything, if you have a lot of clients you should and test as required.

Below are the reports:

Symantec AV not installed:

USE [lansweeperdb]
GO
/****** Object: View [dbo].[web30repNoNAVCorp] Script Date: 01/21/2010 13:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[web30repNoNAVCorp]
AS
SELECT DISTINCT TOP 100 PERCENT
dbo.tblfileversions.Computername AS Computer,
dbo.tblComputers.Username,
dbo.tblComputers.Domain
from dbo.tblfileversions inner join dbo.tblcomputers on
dbo.tblfileversions.ComputerName = dbo.tblcomputers.ComputerName
where dbo.tblfileversions.FilePathFull like '%vpc32%'
and dbo.tblfileversions.computername not in (
select dbo.tblfileversions.computername
from dbo.tblfileversions
where dbo.tblfileversions.FilePathFull like '%vpc32%'
and dbo.tblfileversions.found = 1
)


Symantec AV Versions:

USE [lansweeperdb]
GO
/****** Object: View [dbo].[web30repNAVCorpVer] Script Date: 01/21/2010 13:32:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[web30repNAVCorpVer]
AS
SELECT TOP 100 PERCENT
dbo.tblfileversions.Computername AS Computer,
dbo.tblfileversions.FileVersion,
dbo.tblComputers.Username,
dbo.tblComputers.Domain
from dbo.tblfileversions inner join dbo.tblcomputers on
dbo.tblfileversions.ComputerName = dbo.tblcomputers.ComputerName
where dbo.tblfileversions.FilePathFull like '%vpc32%' and dbo.tblfileversions.found = 1


Symantec AV old version (use method in old definition report to automate, I thought not needed here as it does not change as frequently as the def versions do):

USE [lansweeperdb]
GO
/****** Object: View [dbo].[web30repNAVCorpOldVer] Script Date: 01/21/2010 13:33:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[web30repNAVCorpOldVer]
AS
SELECT TOP 100 PERCENT
dbo.tblfileversions.Computername AS Computer,
dbo.tblfileversions.FileVersion,
dbo.tblComputers.Username,
dbo.tblComputers.Domain
from dbo.tblfileversions inner join dbo.tblcomputers on
dbo.tblfileversions.ComputerName = dbo.tblcomputers.ComputerName
where dbo.tblfileversions.FilePathFull like '%vpc32%'
and dbo.tblfileversions.found = 1
and (not (dbo.tblfileversions.fileversion like '10.1.7.7000'))
order by dbo.tblfileversions.fileversion


Symantec AV definition version:

USE [lansweeperdb]
GO
/****** Object: View [dbo].[web30repNAVCorpDefVer] Script Date: 01/21/2010 13:36:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[web30repNAVCorpDefVer]
AS
SELECT TOP 100 PERCENT
dbo.tblregistry.Computername AS Computer,
RIGHT (dbo.tblregistry.value, 12) AS DefVer,
dbo.tblComputers.Username,
dbo.tblComputers.Domain
from dbo.tblregistry inner join dbo.tblcomputers on
dbo.tblregistry.Computername = dbo.tblcomputers.Computername
where dbo.tblregistry.valuename like '%defwatch%'


Symantec AV old definitions:

USE [lansweeperdb]
GO
/****** Object: View [dbo].[web30repNAVCorpDefOldVer] Script Date: 01/21/2010 13:38:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[web30repNAVCorpDefOldVer]
AS
SELECT TOP 100 PERCENT
dbo.tblregistry.Computername AS Computer,
RIGHT (dbo.tblregistry.value, 12) AS DefVer,
dbo.tblComputers.Username,
dbo.tblComputers.Domain
from dbo.tblregistry inner join dbo.tblcomputers on
dbo.tblregistry.Computername = dbo.tblcomputers.Computername
where dbo.tblregistry.valuename like '%defwatch%'
and
RIGHT (dbo.tblregistry.value, 12) not in
( select max((RIGHT (dbo.tblregistry.value, 12)))
from dbo.tblregistry
where dbo.tblregistry.valuename like '%defwatch%')


Enjoy.
1 REPLY 1
kh-vince
Moderator
Moderator

Hello everyone,
Due to the age of this post, it has been archived. Please feel free to start a new post if you wish to continue to discuss this topic.
Thank you for understanding.