Sqlserver监控+企业微信提醒

1、创建http请求函数

CREATE FUNCTION dbo.HTTP_POST_FUNC(
    @URL VARCHAR(256),
    @DATA VARCHAR(2000),
    @REQ_H_ACCEPT VARCHAR(256),
    @REQ_H_CONTENT_TYPE VARCHAR(256)
)
RETURNS VARCHAR(5000)
AS
BEGIN
    DECLARE
        @object int,
        @returnStatus int,
        @returnText varchar(5000),
        @errMsg varchar(2000),
        @httpStatus varchar(20);
    /* 初始化 */
    EXEC @returnStatus = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0',@object OUT;
 
    IF @returnStatus <> 0
        BEGIN
            EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
            RETURN ('初始化对象失败，' + @errMsg + ISNULL(@returnText,''));
        END
 
    /* 创建链接 */
    EXEC @returnStatus= SP_OAMethod @object,'open',NULL,'post',@URL,'false';
 
    IF @returnStatus <> 0
        BEGIN
            EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
            RETURN ('创建连接失败，' + @errMsg + ISNULL(@returnText, ''));
        END
 
    EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Accept',@REQ_H_ACCEPT;
    EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Type',@REQ_H_CONTENT_TYPE;
    EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Length','1000000';
 
    /* 发起请求 */
    EXEC @returnStatus= SP_OAMethod @object,'send',NULL,@DATA;
    IF @returnStatus <> 0
        BEGIN
            EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
            RETURN ('发起请求失败，' + @errMsg + ISNULL(@returnText, ''));
        END
    /* 获取HTTP状态代码 */
    EXEC @returnStatus = SP_OAGetProperty @Object, 'Status', @httpStatus OUT;
 
    IF @returnStatus <> 0
        BEGIN
            EXEC sp_OAGetErrorInfo @Object, @errMsg OUT, @returnText OUT;
            RETURN ('获取HTTP状态代码失败，' + @errMsg + ISNULL(@returnText, ''));
        END
    IF @httpStatus <> 200
        BEGIN
            RETURN ('访问错误，HTTP状态代码:' + @httpStatus);
        END
    /* 获取返回信息 */
    EXEC @returnStatus= SP_OAGetProperty @object,'responseText',@returnText OUT;
    IF @returnStatus <> 0
        BEGIN
            EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
            RETURN ('获取返回信息失败，' + @errMsg + ISNULL(@returnText, ''));
        END
    RETURN @returnText;
END;

2、更新配置

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO

3、增加磁盘监控存储过程

USE [Acadsoc.COM.CN]
GO
/****** Object:  StoredProcedure [dbo].[SystemMonitor]    Script Date: 2022/6/1 18:01:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SystemMonitor]
AS
BEGIN
     /*磁盘使用报警监控*/
    CREATE TABLE temp_disk_space(Drive_Name nvarchar(50),Total_Space_GB NUMERIC(18,2),Free_Space_GB NUMERIC(18,2))
    INSERT INTO temp_disk_space  
    SELECT DISTINCT 
        vs.volume_mount_point,   
        CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)),
        CAST(vs.available_bytes / 1024.0 / 1024 / 1024  AS NUMERIC(18,2)) 
    FROM sys.master_files AS f  
    CROSS APPLY sys.dm_os_volume_stats(f.database_id,f.file_id) AS vs  

    --磁盘小于10G发送提示
    IF exists(SELECT 1 FROM temp_disk_space WHERE Free_Space_GB < 10)  
    BEGIN

		select dbo.HTTP_POST_FUNC('https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=youkey………………………………………………………………………………',
        '{
			"msgtype": "text",
			"text": {
				"content": "数据库有磁盘空间严重不足，请及时处理",
				"mentioned_list":["@all"],
			}
		}',
        'application/json',
        'application/json');
    END   
    
    DROP table temp_disk_space 
                
END

4、增加数据库作业，定时执行存储过程

……

5、手动执行一下看看效果

更多信息请关注