/* Admin_Report_Notification */
DECLARE @tab char(1)
SET @tab = CHAR(9)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Report DBMail Profile',
@recipients = '[email protected]',
@subject = 'dude, email report',
@body = 'Please see the attached query result for:
Use kimconnect
select * from table_a a (nolock)
join table_b b
on a.userid = b.userid
where a.status <> 4
and a.recorddeleted = 0',
@execute_query_database = 'kimconnect',
@query = N'SET NOCOUNT ON
select
LoginName,
FirstName,
MiddleName,
LastName,
Status = CASE
WHEN Status = 1 THEN ''ACTIVE''
WHEN Status = 2 THEN ''LOCKED''
WHEN Status = 3 THEN ''DISABLED''
ELSE ''DELETED''
END,
convert(varchar, PasswordExpirationDateUTC,120) as PasswordExpirationDateUTC,
convert(varchar, LastLoginDateUTC,120) as LastLoginDateUTC,
convert(varchar, LastLoginFailureDateUTC,120) as LastLoginFailureDateUTC,
ConsecutiveLoginFailures,
ChangePasswordRequired,
RecordDeleted,
ChangeLoginNameCount,
convert(varchar, UpdateDateUTC,120) as UpdateDateUTC,
convert(varchar, LastPasswordChangeDateTimeUTC,120) as LastPasswordChangeDateTimeUTC,
Role
from dbo.table_c a
join dbo.table_d b
on a.userid = b.userid
where a.status <> 4
and a.recorddeleted = 0;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Admin Report.csv',
@query_result_separator=@tab,
@query_result_no_padding=1
Categories:
