Using CDOSYS instead of SQLMAIL in a sp for failed logins

[jlrsov]

SQL Server mag had a script from Canadian GIlles Despaties that audits failed logins intelligently and emails the results in SQLMAIL. My problem is that SQLMAIL is not used in our SQL Servers, and I was wondering how to insert CDOSYS instead in the following code so that the CDOSYS utility is used rather than SQLMAIL?



/* ************************* Failed Login Implementation ****************************

Assuming that SQLMail is successfully installed, you need to ensure of the following, :
1. Increase the number of error logs to keep (default is 6, new value is 25)
2. Change the Audit level to Failed Login only
** This requires a services re-start to enable parts 1 and 2.
3. Create a Failed Login Management Stored Procedure with the following script.
4. Create a Job that will launch this procedure
5. Create a Failed Login Alert that will launch the Job
************************** End Failed Login Implementation ********************** */

use msdb
go

CREATE PROCEDURE dbo.usp_Failed_Logins
@iDaysAgo int, @iDateDiff int, @iInstances int, @vcMailRecipients varchar(255)
AS

declare @vcMessage varchar(255)
declare @vcSubject varchar(255)

if not exists (select 1 from sysobjects where name = 'tblAlertMonitoring' and type = 'U')
begin
CREATE TABLE dbo.tblAlertMonitoring
(uid int IDENTITY(1,1),
id int,
name sysname,
LastOccurrenceDateTime datetime,
last_occurrence_date int,
last_occurrence_time int
)
end

-- Keep the monitoring table small
delete from dbo.tblAlertMonitoring
where LastOccurrenceDateTime < dateadd (dd, (-@iDaysAgo)*4, getdate())

-- Add the latest alert for processing
insert dbo.tblAlertMonitoring (id, name, LastOccurrenceDateTime, last_occurrence_date, last_occurrence_time)
select distinct id,
name,
convert(datetime,
case last_occurrence_date
when 0 then '1900-01-01'
else substring(convert(char(8), last_occurrence_date), 1, 4) + '-' + substring(convert(char(8), last_occurrence_date), 5, 2) + '-' + substring(convert(char(8), last_occurrence_date), 7, 2)
end
+ ' ' +
case len(convert(varchar(6), last_occurrence_time))
when 1 then '00:00:0' + substring(convert(varchar(6), last_occurrence_time), 1, 1)
when 2 then '00:00:' + substring(convert(varchar(6), last_occurrence_time), 1, 2)
when 3 then '00:0' + substring(convert(varchar(6), last_occurrence_time), 1, 1) + ':' + substring(convert(varchar(6), last_occurrence_time), 2, 2)
when 4 then '00:' + substring(convert(varchar(6), last_occurrence_time), 1, 2) + ':' + substring(convert(varchar(6), last_occurrence_time), 3, 2)
when 5 then '0' + substring(convert(varchar(6), last_occurrence_time), 1, 1) + ':' + substring(convert(varchar(6), last_occurrence_time), 2, 2) + ':' + substring(convert(varchar(6), last_occurrence_time), 4, 1)
when 6 then substring(convert(varchar(6), last_occurrence_time), 1, 2) + ':' + substring(convert(varchar(6), last_occurrence_time), 3, 2) + ':' + substring(convert(varchar(6), last_occurrence_time), 5, 2)
end
)
as LastOccurrenceDateTime, last_occurrence_date, last_occurrence_time

from dbo.sysalerts

where name like '%Logins%'


if (select count(*)
from (
select distinct id, name, LastOccurrenceDateTime
from dbo.tblAlertMonitoring
where LastOccurrenceDateTime > dateadd(s, -@iDateDiff, getdate())
) tmpTbl) >= @iInstances

BEGIN
SET @vcSubject = @@ServerName + ': High Failed Logins instances'
SET @vcMessage = 'Please check the event logs for the user login that attempts to login.'
exec master.dbo.xp_sendmail @recipients = @vcMailRecipients, @subject = @vcSubject, @message = @vcMessage
END

GO




Thanks,

Jim Ruddy