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 觸發器

過程如下：

-- 下面開始配置 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"，共同學習，共同成長！！！