Workstations on the network having not contacted EPO in 30 days

noEPOcontact30Days.sql

USE [build]
GO
/****** Object:  StoredProcedure [dbo].[AVRule5] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Michael Maher
-- Create date: 25 August 2015
-- Description:	Workstations on the network having not contacted EPO in 30 days
-- =============================================
ALTER PROCEDURE [dbo].[AVRule5]
AS

create table ##grtemp250815(hostname varchar(15),EPODATE datetime)
BEGIN
		insert into ##grtemp250815 (hostname,EPODATE)
		 Select leaf.[Nodename], leaf.[LastUpdate]
			from [EPOSRV].[ePO4_EPOSRV].[dbo].[EPOLeafNode] LEAF 
			where DATEDIFF(Day, leaf.[LastUpdate], GETDATE()) > 30
END  
	

DECLARE @msg varchar(MAX)
SET @msg = 'Last contact with EPO gt 30 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'helpdesk@contoso.com; michael.maher@contoso.com ',
@body = @msg,  
@subject = 'Last contact with EPO gt 30 days',
@query = 'Select * from ##grtemp250815',
@attach_query_result_as_file = 1