SQLServer郵件預警

SQLServer 郵件預警

問題：

OA 系統向異構系統推送待辦時偶爾會出現各種原因導致推送失敗，為了避免問題升級，需要管理員第一時間知道推送失敗的情況，并手動進行干預。

問題分析：

待辦推送狀態會記錄在OA 數據庫wx_scanlog 表中，當resultstatus 狀態為-1 時表示待辦推送失敗。

解決方案：

一：SQLServer 觸發器

為了及時知道待辦推送失敗信息，可以使用SQLServer 觸發器，當wx_scanlog 表中新增數據時，校驗resultstatus 狀態值，當值為-1 時，自動發送郵件到指定郵箱。

二：Grafana 查看待辦狀態

通過Grafana 連接OA 數據庫，指定時間間隔查看待辦狀態，例如如下SQL ：

select top 10 scantime , content , resultcontent from wx_scanlog where resultstatus =-1  order by scantime desc ;

一：SQLServer 觸發器

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms189635(v=sql.90)

過程如下：

-- 下面開始配置 sql 發送電子郵件：

-- 啟用 sql server 郵件的功能

exec sp_configure 'show advanced options' ,1

go

reconfigure ;

go

-- 配置選項 'show advanced options' 已從 0 更改為 1 。請運行 RECONFIGURE 語句進行安裝。

exec sp_configure 'Database Mail XPs' ,1

go

reconfigure ;

go

-- 配置選項 'Database Mail XPs' 已從 0 更改為 1 。請運行 RECONFIGURE 語句進行安裝。

-- 使用下面的語句查看數據庫郵件功能是否開啟成功和數據庫配置信息：

-- 查詢數據庫的配置信息

select * from sys . configurations

-- 查看數據庫郵件功能是否開啟， value 值為 1 表示已開啟， 0 為未開啟

select name , value , description ,

       is_dynamic , is_advanced

from sys . configurations

where name like '%mail%'

---name value   description is_dynamic  is_advanced

---Database Mail XPs    1   Enable or disable Database Mail XPs 1   1

-- 步驟二：

if exists( SELECT * FROM msdb .. sysmail_account WHERE NAME = 'chenmail' )

begin

    EXEC msdb .. sysmail_delete_account_sp @account_name = 'chenmail'

end

exec msdb .. sysmail_add_account_sp     -- 創建郵件賬戶

        @account_name = 'cjcamail'   -- 郵件帳戶名稱

       , @email_address = 'chenjuchao163@163.com'    -- 發件人郵件地址  

       , @display_name = 'chenjuchao'      -- 發件人姓名  

       , @replyto_address = null        -- 回復地址

       , @description = null          -- 郵件賬戶描述

       , @mailserver_name = 'smtp.163.com'    -- 郵件服務器地址

       , @mailserver_type = 'SMTP'        -- 郵件協議

       , @port = 25                  -- 郵件服務器端口  

       , @username = 'chenjuchao163'        -- 用戶名  

       , @password = '**********'      -- 密碼  

       , @use_default_credentials = 0    -- 是否使用默認憑證， 0 為否， 1 為是

       , @enable_ssl = 1        -- 是否啟用 ssl 加密， 0 為否， 1 為是

       , @account_id = null  -- 輸出參數，返回創建的郵件賬戶的 ID

-- 步驟三：

if exists( SELECT * FROM msdb .. sysmail_profile where NAME = N'SendEmailProfile0323' ) -- 判斷名為 SendEmailProfile0323 的郵件配置文件是否存在

begin  

    exec msdb .. sysmail_delete_profile_sp @profile_name = 'SendEmailProfile0323'  -- 刪除名為 SendEmailProfile0323 的郵件配置文件

end

exec msdb .. sysmail_add_profile_sp     -- 添加郵件配置文件

     @profile_name = 'SendEmailProfile0323' ,   -- 配置文件名稱    

     @description = ' 數據庫發送郵件配置文件 ' ,     -- 配置文件描述      

     @profile_id = NULL        -- 輸出參數，返回創建的郵件配置文件的 ID

-- 步驟四：

-- 郵件賬戶和郵件配置文件相關聯  

exec msdb .. sysmail_add_profileaccount_sp   

     @profile_name = 'SendEmailProfile0323' ,   -- 郵件配置文件名稱     

     @account_name = 'chenmail' ,    -- 郵件賬戶名稱       

     @sequence_number = 1    -- account 在 profile 中的順序，一個配置文件可以有多個不同的郵件賬戶

-- 到這里 sql 發送郵件的配置就基本結束了。下面創建一個觸發器實現用戶注冊成功后，發送郵件給用戶。

-- 然后創建一個 insert 類型的 after 觸發器：

create trigger undo_fail_cjc_tr

--alter trigger undo_fail_cjc_tr

on wx_scanlog

after insert

as

    declare @errormsg nvarchar (1000 )

    declare @resultcontent  nvarchar (500 )

    declare @content  nvarchar (500 )

    declare @title    nvarchar (100 )

    declare @xxx      nvarchar (1000 )

    declare @count    int

    declare @id       int

    select @count = COUNT (1 ) from inserted

    select @id = id from inserted

    select @resultcontent =( select CAST ( resultcontent as nvarchar (1000 )) from wx_scanlog where id = @id )

    select @content =( select CAST ( content as nvarchar (1000 )) from wx_scanlog where id = @id )

    set @xxx = 'content 標題如下： ' + @content + CHAR ( 13 )+ 'resultcontent 內容如下： ' + @resultcontent

    --select @msgcode=msgcode,@errormsg=errormsg  from inserted

    -- if(@count>0)

    if (( @count >0 ) and ( select resultstatus from inserted )= '-1' )

    begin

        set @title = 'OA( 正式系統 ) 待辦推送 Eanar 失敗，請及時處理！ '

        exec msdb . dbo . sp_send_dbmail @profile_name = 'SendEmailProfile0323' ,  -- 郵件配置文件名稱

                   @recipients = 'cjc@xxx.com' ,        -- 郵件發送地址

                        @subject = 'OA( 正式系統 ) 待辦推送 Eanar 失敗，請及時處理！ ' ,   -- 郵件標題

                   @body = @xxx ,    -- 郵件內容

                   @body_format = 'text'  -- 郵件內容的類型， text 為文本，還可以設置為 html

    end

go

-- 執行上面的語句之后，大概兩三秒鐘，就會收到郵件了（如果沒有出現錯誤的話）。如果沒有收到郵件可以使用下面的語句查看郵件發送情況。

use msdb

go

select * from sysmail_allitems    -- 郵件發送情況，可以用來查看郵件是否發送成功

select * from sysmail_mailitems   -- 發送郵件的記錄

select * from sysmail_event_log   -- 數據庫郵件日志，可以用來查詢是否報錯

郵件：

二：Grafana 查看待辦狀態

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！