Get Last Login Date from EPO

getLastLoginDatefromEPO.sql

USE [build]
GO
/****** Object:  StoredProcedure [dbo].[EPOLastLogon2]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================================
-- Author:		Michael Maher
-- Create date: 12th June 2014
-- Description:	Find Last Login from McAfee
-- ================================================================
CREATE PROCEDURE [dbo].[EPOLastLogon2]
	AS
BEGIN

create table ##gtempreport0918(
	Nodename varchar(100),
	EPODATE datetime, 
	EPOUSERNAME varchar (1000),
	ASSETSERIAL varchar (1000),
	assetLoggedOn varchar(1000), 
	ASSETDBDATE datetime)
	
	insert into ##gtempreport0918(
	Nodename,
	EPODATE, 
	EPOUSERNAME, 
	ASSETSERIAL, 
	assetLoggedOn, 
	ASSETDBDATE)

	select leaf.NodeName, 
	leaf.[LastUpdate] AS EPODATE, 
	COMP.[username] AS EPOUSERNAME,
	bld.[serialno] AS ASSETSERIAL,
	bld.loggedon AS ASSETLOGGEDON,
	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

END

DECLARE @msg varchar(MAX)
SET @msg = 'EPO Last Logged on date' + CHAR(13)+ CHAR(13)
+ 'Regards,' + CHAR(13)+ CHAR(13)
+ 'Michael'

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'service_account',
@recipients = N'michael.maher@contoso.com',
@body = @msg,  
@subject = 'McAfee EPO Timestamp',
@query ='select * from ##gtempreport0918',
@attach_query_result_as_file = 1