SAS-异常报表邮件自动预警
原创
小编最近收到一个任务，要给报表平台做监控。
目的是便于监测每日常用的报表，是否及时更新成功，以提醒到相应的报表维护者。
当前在用报表平台是帆软旗下的BI和Report，目前版本还不支持邮件提醒异常报表。
小编左思右想，屡败屡战，终于试水成功。
激动到第一时间想把这些写下来分享出来^_^……
文章中部分代码参考了技术大牛的文章https://cloud.tencent.com/developer/article/1524113。
如有侵权，可随时联系。
目录
一、将需要监测的报表整理到一张表
二、报表每日跑批信息插入日志表
三、预警逻辑
四、SAS发送邮件
五、BAT文件及定时任务配置
一、将需要监测的报表整理到一张表
bi_mis_table表结构如下：
CREATE TABLE `bi_mis_table` (
`auto_id` bigint(20) NOT NULL AUTO_INCREMENT,
`bi_mis_nm` varchar(200) DEFAULT NULL COMMENT 'bi_mis报表名',
`owner` varchar(100) DEFAULT NULL COMMENT '维护人',
`table_nm` varchar(100) DEFAULT NULL COMMENT '数据库表',
`should_tm` time DEFAULT NULL COMMENT '理论插入时间',
PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报表理论插入时间';长这个样子（第一行是注释，第二行是表头）：
自动序号 | BI/MIS的报表名 | 维护人 | 对应的数据库表名 | 理论跑批完成时间，用于判断是否进行预警提示 |
|---|---|---|---|---|
auto_id | bi_mis_nm | owner | table_nm | should_tm |
1 | aa | cus1 | table1 | 6:00:00 |
2 | bb | cus2 | table2 | 6:00:00 |
3 | cc | cus3 | table3 | 8:00:00 |
4 | dd | cus4 | table4 | 8:00:00 |
二、报表每日跑批信息插入日志表
bi_mis_monitor表结构如下：
CREATE TABLE `bi_mis_monitor` (
`auto_id` bigint(20) NOT NULL AUTO_INCREMENT,
`bi_mis_nm` varchar(200) DEFAULT NULL COMMENT 'bi_mis报表名',
`owner` varchar(100) DEFAULT NULL COMMENT '维护人',
`table_nm` varchar(100) DEFAULT NULL COMMENT '数据库表',
`etl_dt` date DEFAULT NULL COMMENT '报表日期',
`cnt` double DEFAULT NULL COMMENT '插入条数',
PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报表监控';用SAS写的插入代码供参考：
/*以客户画像表为例，根据自己的报表适当调整*/
%LET etl_dt=%SYSFUNC(INTNX(DAY,%SYSFUNC(TODAY()),-1,E));
/*etl日期，默认为前一天*/
PROC SQL;
SELECT COUNT(*) INTO : cnt FROM work.table1;
/*条数>0即表示etl_dt当天代码执行有数*/
INSERT INTO user1.bi_mis_monitor(bi_mis_nm,owner,table_nm,etl_dt,cnt)
/*bi_mis_nm为关联字段，必须与其他表一致*/
/*bi_mis_monitor为日志表，每次只需要插入即可，不需要删除历史数据*/
VALUES('aa','cus1','table1',&etl_dt,&cnt);
QUIT;长这个样子（第一行是注释，第二行是表头）：
自动序号 | BI/MIS的报表名 | 维护人 | 对应的数据库表名 | 数据日期 | 数据集条数 |
|---|---|---|---|---|---|
auto_id | bi_mis_nm | owner | table_nm | etl_dt | cnt |
1 | aa | cus1 | table1 | 2020/4/10 | 193087 |
2 | bb | cus2 | table2 | 2020/4/10 | 27796 |
3 | cc | cus3 | table3 | 2020/4/10 | 25887 |
4 | dd | cus4 | table4 | 2020/4/10 | 118688 |
三、预警逻辑
当前时间>bi_mis_table中的理论跑批时间 & 在bi_mis_monitor没有相应的跑批结束日志
vw_bi_mis_alarm视图结构如下：
CREATE OR REPLACE VIEW user1.vw_bi_mis_alarm AS
SELECT
bi_mis_nm AS bi_mis报表名
,owner AS 维护人
,table_nm AS 数据库表
,should_tm AS 理论插入时间
,CURTIME() AS 当前时间
FROM user1.bi_mis_table
WHERE CURTIME()>should_tm
AND table_nm NOT IN
(SELECT table_nm FROM user1.bi_mis_monitor
WHERE etl_dt=DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND cnt>0)
ORDER BY 4;长这个样子（只在有报表未按时跑批完成的情况下，该表才会有记录）：
bi_mis报表名 | 维护人 | 数据库表 | 理论插入时间 | 当前时间 |
|---|---|---|---|---|
aa | cus1 | table1 | 6:00:00 | 17:06:57 |
bb | cus2 | table2 | 6:00:00 | 17:06:57 |
四、SAS发送邮件
这一步是最最关键的一步，用了好多时间进行调整。
考虑到要在满足预警表中条数>0的情况下，才会触发邮件发送，故采用macro方式进行。
SAS宏代码如下：
%macro sas_send;
/*1：option选项的配置*/
options emailsys=SMTP;
options emailauthprotocol=LOGIN;
options emailhost="设置邮箱";
options emailid="账号";
options emailpw="密码";
options emailport=25;/*端口*/
/*2：filename设置邮件主题、收件人*/
filename mymail EMAIL
to=("aa@163.com", "bb@163.com") /*收件人之间用逗号隔开*/
cc=("cc@qq.com" )
subject="【测试】风险报表监测告警" encoding=gb2312 type="text/html" ;
/*3：制作邮件中的数据集*/
data temp1;
set user1.vw_bi_mis_alarm;
by '理论插入时间'n;
run;
/*4：ods text与proc report书写正文*/
ods html body=mymail style =seaside/*在工具-样式管理器里可设置*/;
title ;
ods html text = "Hi,您好！";
ods html text = "以下为今日异常报表, 请相关人员留意！";
proc report data=temp1 nowd headline headskip
style(report)={just=left asis=on }
style(header)={just=left asis=on }
style (column) = {background = white CELLHEIGHT = 4.5% font_face = "Times New Roman" font_size = 12pt just=left};
columns (bi_mis报表名 维护人 数据库表 理论插入时间 当前时间);
define bi_mis报表名/display 'BI_MIS报表名';
run;
ods html text = "邮件自动发送，请勿回复！";
ods _all_ close;
%mend sas_send;
/*5.考虑到要在满足预警表中条数>0的情况下，才会触发邮件发送，故采用macro方式进行*/
PROC SQL;
SELECT COUNT(*) INTO : cnt_alarm FROM user1.vw_bi_mis_alarm;
QUIT;
%macro sas_send_macro;
%if &cnt_alarm>0 %then %do;
%sas_send;
%end;
%mend sas_send_macro;
%sas_send_macro;最后一步啦~
五、BAT文件及定时任务配置
以下为bat文件内容：
"D:\Program Files\SASHome2\SASFoundation\9.4\sas.exe"
-sysin "……\自动邮件.sas"
-log "……\自动邮件.log"
-CONFIG "D:\Program Files\SASHome2\SASFoundation\9.4\nls\zh\sasv9.cfg"去任务计划程序中设置下：
小编设置的是每隔半个小时提醒一次。
此处敲黑板！！！
这里一定要选择【使用最高权限运行】，要不然会提示【ERROR: 授权不足，无法访问 C:\Windows\system32\自动邮件.lst】
bingo~
第一次写技术贴，觉得有用希望点个赞~
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。