Create job to send alert using sp_send_dbmail when table value greater than x

Question

PROBLEM

The SMScounter.Counter is used to accumulate usage from a service. The service has a programmatic cap of 1000. I would like to alert users when this cap is breached.

TABLE CREATE

CREATE TABLE [dbo].[SMSCounter](
  [Counter] [int] NOT NULL,
  [CounterDate] [datetime] NOT NULL )

EXAMPLE DATA

380, 2012-09-10 00:00:00.000
177, 2012-10-14 00:00:00.000
999, 2012-11-16 00:00:00.000

JOB SCRIPT

The script below is my attempt to create a scheduled job that will check every hour if the SMScounter.Counter is greater than 950.

USE DATABASEname
select (Counter) as CHECKVALUE from SMSCounter where smscounter.CounterDate  = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)    
IF CHECKVALUE > 950

BEGIN

EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near', 
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =
    'USE DATABASEname
     (select * from SMSCounter where CounterDate = GETDATE())'

END

QUESTIONS

1. How can I format GETDATE to examine dd/mm/yy and ignore the time part when reading SMScounter.CounterDate? (script above edited , works now).

2. How can I get the IF statement to work to compare CHECKVALUE to 950?

I'm using SQL Server 2008 standard edition.

Answer 1

You can use convert (date, GETDATE()) to get just the 'date' part:

select GETDATE(), convert (date, GETDATE())
----------------------- ----------
2012-11-16 08:58:20.750 2012-11-16

And if you're only ever going to be comparing 'date' parts, it might be worth changing the datatype in your table to DATE, to save having to convert each time you query it.

For an IF statement you need to pass in a value to be checked, and then wrap the code to be run within BEGIN and END statements. Here's an example using the sysobjects table:

declare @cnt integer
select @cnt=COUNT(1) from sysobjects

if @cnt>50
begin
  select 'hello'
end

which will say hello if you've more than 50 objects in your DB

So in your case:

USE DATABASEname
go
declare @counter integer

select @counter=MAX([Counter]) from SMSCounter where convert(date,CounterDate) = convert(date,GETDATE())

IF @counter > 950
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'scott@mydomain.com',
  @body='1000 Daily SMS cap near',
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =  '(select * from DATABASEname.dbo.SMSCounter where convert(date,CounterDate) = convert)(Date,GETDATE()))'
END

I've added a MAX function to your check query, as this will only return a single row containing the maximum value for that day, otherwise it'll return a random value from all the matching rows. If you're overwriting the day's value each time you write to the table, then it's superfluous.

Answer 2

The solution is not bad, but I would like to introduce you to a more real time one. Instead of just checking the SMS counter every hour, wouldn't you better know when it first happened?

I would do the following:

• add a new column to your table - Alert bit not null default 0 - which will hold info if the customer was alerted about the high number of SMS. This is needed so that the customer isn't alerted for every insert/update > 950, but only for the first one.

• add a trigger on your table (after insert and update) that in case the value for the Counter is bigger than 950 will start the alert (set the alert column to 1 and send email).

The trigger would look like the following:

ALTER TRIGGER dbo.RaiseAlert
    ON  dbo.SMSCounter
AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    declare
        @cnt int,
        @today DATE = cast(GETDATE() as DATE),
        @alert bit;

    select @cnt = n.[Counter], @alert = alert
    from inserted n
    where n.CounterDate = @today;

    if @cnt > 950 and @alert = 0
    begin
        update SMSCounter
        set Alert = 1
        from SMSCounter
        where CounterDate = @today;

        declare @subject varchar(1000);
        select @subject = 'The sms counter is near!! It''s value is: ' + CAST(@cnt as varchar);

        EXEC msdb.dbo.sp_send_dbmail @profile_name='SQLAlerts',
            @recipients = 'xxx@xxx.com',
            @subject = @subject,
            @body = 'blablabla';
    end;
END
GO

A simple update would raise it and send the email:

update SMSCounter
set [Counter] = 988
where CounterDate = '2012-11-16 00:00:00.000'

Answer 3

I could not get this to work with the @query parameter so I used HTML instead

The script Audits Users logging in using "sa" or service accounts

IF OBJECT_ID ('AuditSqlLogins') IS NOT NULL DROP TABLE AuditSqlLogins 
GO

CREATE TABLE AuditSqlLogins (
    [Counter] [int] IDENTITY(1,1) NOT NULL,
    [Login_name] [varchar](20) NULL,
    [host_name] [varchar](20) NULL,
    [login_time] [datetime] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[AuditSqlLogins]
    (login_time, login_name, host_name )

    SELECT login_time, login_name, host_name 
    FROM sys.dm_exec_sessions 
    WHERE host_name not like '%APP%'  -- excludeds Apllication server logins
    and host_name not like '%WWW%'    -- excludeds Web server logins
    and database_id = 5               -- Specify the database you want to audit
    and login_name = 'Appadmin'       -- specify the account you want audited
    or login_name = 'sa'
    ORDER BY host_name desc

    DELETE FROM AuditSqlLogins
    WHERE host_name is null

DECLARE @counter integer
SELECT @counter = Counter FROM AuditSqlLogins                         

IF (@counter > 0)
BEGIN 

DECLARE @xml NVARCHAR(MAX)

SET @xml = CAST ( ( SELECT 
          td = login_name  , '',
          td = host_name, '',
          td = login_time,''

FROM AuditSqlLogins 

FOR XML PATH('tr'), TYPE 
              ) AS NVARCHAR(MAX) )
       DECLARE @body  nvarchar(max);
       SET @body ='
<html>
<body>
<head>
<style>
table {
  border-collapse: collapse;
}
table, th, td {
  border: 1px solid #ea5685;
}
</style>
</head>
<H3>Audit Appadmin and SA Login Event : (Server Name)</H3>  

<table border="1" cellspacing="0" border-spacing="0" style ="text-align:center"> 
<tr style="height:15px; background:#ea8686">
<th border-spacing="0" style="padding:5px 15px;">login_name</th>
<th border-spacing="0" style="padding:5px 15px;">host_name</th>
<th border-spacing="0" style="padding:5px 15px;">login_time</th>
</tr>
'     
SET @body = @body+@xml+'</table><body>'

EXEC msdb.dbo.sp_send_dbmail
@recipients='youremail@yourwork.com', 
       @subject = 'Audit itsadmin and SA Login Event',
       @body = @body,
       @body_format = 'HTML' ,
       @profile_name='Your DB Mail profile'        
END
DROP TABLE AuditSqlLogins