Find Outdated DAT files

outDateDATFiles.sql

USE [build]
GO
/****** Object:  StoredProcedure [dbo].[AVRule3]    Script Date: 30/03/2016 11:45:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================================
-- Author:		Michael Maher
-- Create date: 12th January 2012
-- Description:	Find Outdated DAT files - criteria described below
-- ================================================================
ALTER PROCEDURE [dbo].[AVRule3]
	AS
BEGIN

create table ##gtempreport481(Nodename varchar(15),EPODATE 
	datetime,datver nvarchar (10), productversion nvarchar (14), 
	enginever nvarchar(10), hotfix int,
	ASSETDBDATE datetime)
	
	insert into ##gtempreport481(Nodename,EPODATE,datver,
	 productversion, enginever, hotfix,	ASSETDBDATE)

	select leaf.NodeName, 
	leaf.[LastUpdate] AS EPODATE, 
	virusscan.[datver], 
	propsview.[productversion], 
	virusscan.[enginever], 
	virusscan.[hotfix],
	bld.[updatedtime] AS ASSETDBDATE

	from [EPOSRV].[ePO4_EPOSRV].[dbo].[EPOLeafNode] LEAF

	left join 
	[EPOSRV].[ePO4_EPOSRV].[dbo].[EPOProdPropsView_EPOAGENT] PROPSVIEW on 
	leaf.[AutoID] = propsview.[LeafNodeID]

	left join [EPOSRV].[ePO4_EPOSRV].[dbo].[EPOProdPropsView_VIRUSCAN] VIRUSSCAN on 
	leaf.[AutoID] = virusscan.[LeafNodeID]

	left join [EPOSRV].[ePO4_EPOSRV].[dbo].[EPOComputerProperties] COMP on 
	LEAF.[AutoID] = COMP.[ParentID] 

	left join build.Audit BLD on 
	LEAF.[NodeName] = BLD.[Hostname] collate SQL_Latin1_General_CP1_CI_AS


	-- Where the dat version is older than the 5th most recent revision AND
	-- the system had been active on the domain in the previous 5 days AND
	-- ignore NULLs as they are covered in a different report

	where virusscan.[datver] < 
	(select TOP 1 * FROM -- Extract the 1st value only 
	(SELECT DISTINCT TOP (5) datver -- 5 most recent records in ASC order
	from [EPOSRV].[ePO4_EPOSRV].[dbo].[EPOProdPropsView_VIRUSCAN] 
	WHERE ISNUMERIC (datver) = 1 ORDER BY DATVER DESC) temp ORDER BY DATVER ASC)

	AND DATEDIFF(Day, BLD.[updatedtime], GETDATE())< 5 

	AND LEAF.[LASTUPDATE] IS NOT NULL ORDER BY virusscan.[datver] 

END

DECLARE @msg varchar(MAX)
SET @msg = 'Where the dat version is older than the 5th most recent revision AND' + CHAR(13)+ CHAR(13)
+ 'the system had been active on the domain in the previous 5 days AND' + CHAR(13)+ CHAR(13)
+ 'ignore NULLs as they are covered in a different report' + CHAR(13)+ CHAR(13)
+ 'See attached for more detail.' + CHAR(13)+ CHAR(13)
+ 'Regards,' + CHAR(13)+ CHAR(13)
+ 'Michael'

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'services_account',
@recipients = N'michael.maher@contoso.com; test@contoso.com',
@subject = 'Old DAT files',
@query ='select * from ##gtempreport481',
@attach_query_result_as_file = 1