In today's fast-paced business world, data-driven decision-making is crucial. However, accessing and analyzing data can be a tedious and time-consuming task, especially for database administrators (DBAs) and business people who rely heavily on data to drive their decisions.
To address this problem, a database alert system has been developed that can automate data monitoring and alert users via email. The database alert system is designed to help users save time and effort by automating data monitoring.
Users can store SQL queries that they want to monitor, and the system will run these queries on a selected data and frequency. If the system finds any results for the query, it will alert the user via email.
To build a database alert system, you need to follow a few simple steps.
First, you need to set up the database and tables to store the queries, users, and other information. Any database management system (DBMS) can be used, such as MySQL, PostgreSQL, or SQL Server.
Next, you need to create a user interface that allows users to input the queries and select the data and frequency. This user interface can be created using any programming language, such as Python, PHP, or JavaScript.
Once the user inputs the queries and selects the data and frequency, the system executes the queries and checks for any results. If it finds any results, it sends an email alert to the user. To implement the query execution and alert system, Python and SQL can be used.
Finally, you need to test the system to ensure that it is working correctly. You can test the system by inputting some test queries and selecting a short frequency to check if the system alerts you via email.
The database alert system has many benefits for DBAs and business people. For DBAs, the system can help them monitor their application metrics and track their business processes without having to manually check multiple dashboards and run SQL queries. For business people, the system can help them keep track of their business operations and make data-driven decisions.
In conclusion, the database alert system is a simple yet effective solution for automating data monitoring and alerting users via email. It can help DBAs and business people save time and effort by automating their data monitoring and making it easier for them to access the data they need. The system can be built using any DBMS and programming language, and it has many benefits for businesses looking to streamline their data monitoring processes.
Follow the below steps to do alert system in SQL Server.
- First create SQL Profile for sending email from SQL Server.
- Create metadata table for storing meta info regards alert system 9like queries, email id,Frequency etc,.. in SQL server.
- Insert data into metadata table
- Create SQL Server Procedure to send mail
- Test procedure
- #6 Create Job and schedule
#1 First create SQL Profile for sending email from SQL Server.
click the below link to crate and configure the profile for send email in SQL Server.
Create profile in SQL Server.
#2 Create metadata table in SQL server
create table [dbo].[AlertSystem]
(
[ID] numeric identity(1,1)
,[Name] varchar(200)
,[SQL] nvarchar(max)
,[Subject] varchar(500)
,[Frequency] varchar(20)
,[ScheduleTime] float
,[LastRunDate] datetime
,[Emails] varchar(500)
,[isActive] bit
,[FailedMsgs] varchar(max)
,[ErrorDesc] nvarchar(max)
)
#3 Insert data into metadata table
insert into [dbo].[AlertSystem]([Name],[SQL],[Subject],[Frequency],[ScheduleTime],[Emails],[IsActive])
select 'NewCustomer1','select top 10 [customerid],[firstname],[lastname],[address],[city],[state],[zipcode] from []..[]','Test Email From SQL Server','daily',13.00,'test@gmail.com;test1@gmail.com',1 union all
select 'CCExpiry','select top 10 [uniqueid],[p1first],[p1last],[company],[address1],[city],[state],[zipcode] from [].[].[]','','weekly',6,'test@gmail.com;test1@gmail.com',1 union all
select 'NewCustomer','select top 10 [customerid],[firstname],[lastname],[address],[city],[state],[zipcode] from [].[].[]','Test Emal from sql server','monthly',1,'test@gmail.com;test1@gmail.com',1
#4 Create SQL Server Procedure to send mail
create procedure [dbo].[spu_alertsystems_sendemial]
as
begin
begin try
declare @weekday int, @currentday int,@lastrunday int
declare @lastrundate datetime
declare @mailsubject varchar(200),@ErrDesc nvarchar(max)
declare @ID int,@name varchar(200),@sql nvarchar(max),@frequency varchar(50),@ScheduleTime varchar(20),@emails varchar(500),@subject varchar(500)
SELECT @weekday= DATEPART(WEEKDAY, getdate());
SELECT @currentday= DATEPART(day, getdate());
DECLARE Cursor_AlertSystem CURSOR LOCAL FAST_FORWARD FOR
select [ID],isnull([name],'') as [name],isnull([sql],'') as [sql],isnull([subject],'') as [subject],isnull([frequency],'') as [frequency],isnull([ScheduleTime],'') as [ScheduleTime],isnull([emails],'') as [emails],isnull([lastrundate],'') as [lastrundate] from [dbo].[AlertSystem] where isactive=1
OPEN Cursor_AlertSystem
FETCH NEXT FROM Cursor_AlertSystem into @ID, @name,@sql,@mailsubject,@frequency,@ScheduleTime,@emails,@lastrundate
While (@@FETCH_STATUS = 0)
begin
set @ErrDesc =''
if(@ID = '')
begin
set @ErrDesc ='ID should not be empty.'
goto label
end
if(@name = '')
begin
set @ErrDesc ='Name should not be empty.'
goto label
end
if(@sql = '')
begin
set @ErrDesc ='Query should not be empty.'
goto label
end
if(@sql = '')
begin
set @ErrDesc ='Query should not be empty.'
goto label
end
if(@frequency = '')
begin
set @ErrDesc ='Frequency should not be empty.'
goto label
end
if(lower(@frequency) not in('monthly','daily','weekly'))
begin
set @ErrDesc ='Invalid frequency. Frequency should be monthly,weekly or daily.'
goto label
end
if(lower(@frequency) = 'monthly')
begin
if(@ScheduleTime <= 0 or @ScheduleTime >31)
begin
set @ErrDesc ='Schedule between 1 to 31.'
goto label
end
end
if(lower(@frequency) = 'weekly')
begin
if(@ScheduleTime <= 0 or @ScheduleTime >7)
begin
set @ErrDesc ='Schedule between 1 to 7.'
goto label
end
end
if(lower(@frequency) = 'daily')
begin
if(@ScheduleTime <= 0 or @ScheduleTime >24)
begin
set @ErrDesc ='Schedule between 1 to 24.'
goto label
end
end
if(@emails = '')
begin
set @ErrDesc ='Email should not be empty.'
goto label
end
SELECT @lastrunday= DATEPART(day, @lastrundate);
print @lastrunday
declare @datediff int
--declare @lastrundate datetime ='2022/12/1'
SELECT @datediff = DATEDIFF(day, getdate(), @lastrundate);
print @datediff
if @mailsubject =''
begin
set @mailsubject ='Alert System - ' + @name + ''
end
if(lower(@frequency) = 'monthly')
begin
if((@currentday = @ScheduleTime and @datediff <> 0) or ( @currentday = @ScheduleTime and @lastrundate=''))
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLEmail',
@recipients = @emails,
@query = @sql ,
@subject = @mailsubject,
@body_format = 'HTML';
if(@@ERROR =0)
begin
update [dbo].[AlertSystem] set [lastrundate] = getdate() where [ID] = @ID
end
end
end
else if(lower(@frequency) ='weekly')
begin
if((@weekday = @ScheduleTime and @datediff <> 0) or ( @weekday = @ScheduleTime and @lastrundate=''))
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLEmail',
@recipients = @emails,
@query = @sql ,
@subject = @mailsubject,
@body_format = 'HTML';
if(@@ERROR =0)
begin
update [dbo].[AlertSystem] set [lastrundate] = getdate() where [ID] = @ID
end
end
end
else if(lower(@frequency) ='daily')
begin
if((@lastrunday <> @currentday) or (@lastrundate=''))
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLEmail',
@recipients = @emails,
@query = @sql ,
@subject = @mailsubject,
@body_format = 'HTML';
if(@@ERROR =0)
begin
update [dbo].[AlertSystem] set [lastrundate] = getdate() where [ID] = @ID
end
end
end
label:
update [dbo].[AlertSystem] set [ErrorDesc] = @ErrDesc where [ID] = @ID
FETCH NEXT FROM Cursor_AlertSystem into @ID,@name,@sql,@mailsubject,@frequency,@ScheduleTime,@emails,@lastrundate
end
CLOSE Cursor_AlertSystem
DEALLOCATE Cursor_AlertSystem
end try
begin catch
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE();
--ROLLBACK TRANSACTION;
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
update [dbo].[AlertSystem] set [ErrorDesc] = @ErrorMessage where [ID] = @ID
return
end catch
end
#5 Test procedure
Exec [dbo].[spu_alertsystems_sendemial]
#6 Create and schedule a job.
Create a SQL Job
Step 1
Expand the SQL Server Agent and right click on Jobs and click on New Job…
Step 2
In General tab, Enter job name, owner, category and description.
Step 3
In Steps tab, click New and enter step name, select Type as Transact-SQL script (T-SQL) and select database and put EXEC procedure name in command area.
Step 4
From schedules tab, click new button and put schedule name, frequency, daily frequency and duration.
In my job, I have scheduled it for every 1 minute.
Step 5
Now we are done here with job part, let’s start the job. Right click on job and hit Start Job at Step…
