Substituting CDOSYS for SQLMAIL in a sp for failed logins
-
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 best 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
-
I would create the CDOSYS smtp mail as a separate SP then just change your EXEC line:
exec master.dbo.xp_sendmail @recipients = @vcMailRecipients, @subject = @vcSubject, @message = @vcMessage
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply