Workstations on the network having not contacted EPO in 5 days

noEPOcontact5Days.sql

USE [build]
GO
/****** Object:  StoredProcedure [dbo].[AVRule1]    Script Date: 30/03/2016 11:45:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Michael Maher
-- Create date: 10 January 2012
-- Description:	Workstations on the network having not contacted EPO in 5 days
-- =============================================
ALTER PROCEDURE [dbo].[AVRule1]
AS

Declare @server varchar (20)
Declare @msgbody varchar (MAX)
Declare @count int
Declare @allservers varchar (MAX)

create table ##gtempreport480(hostname varchar(15),EPODATE datetime,ASSETDBDATE datetime)

Declare c Cursor For Select Distinct hostname From build.audit
Open c 

Fetch next From c into @server
 
While @@Fetch_Status=0 Begin 
    -- Checks date difference in EPO and ASSET DB check-in over one day then its a problem
	-- Only machines active on the domain over the last 5 day(s) are in scope 
	-- See dbo.compareEPOdate function
	
	print @server
	IF (dbo.compareEPODate(@server) > 1) 
		BEGIN
		insert into ##gtempreport480(hostname,EPODATE,ASSETDBDATE)
		select bld.[hostname], LEAF.[LastUpdate] AS EPODATE, 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 (NOT ( ( LEAF.[AutoID] in 
			(SELECT tagass.[LEAFNodeID]
			FROM [EPOSRV].[ePO4_EPOSRV].[dbo].[EPOTagAssignment] TAGASS
			INNER JOIN [EPOSRV].[ePO4_EPOSRV].[dbo].[EPOTag] TABLEEPOTAG ON TAGASS.[TagID] 
			= TABLEEPOTAG.[TagID]
			WHERE ( TAGASS.[TagID] = N'1' 	AND TABLEEPOTAG.[Family] = 'EPO' 
			AND ( TAGASS.[Exclude] = 0 ) ) ) ) ) ) 
			AND LEAF.Nodename = @server
		END
    Fetch next From c into @server
	
End 

Close c 
Deallocate c

DECLARE @msg varchar(MAX)
SET @msg = 'Last contact with EPO gt 5 days' + 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 = 'Last contact with EPO gt 5 days',
@query ='select * from ##gtempreport480',
@attach_query_result_as_file = 1