SAS-异常报表邮件自动预警

2020-04-13 10:57:27 浏览数 (1)

小编最近收到一个任务，要给报表平台做监控。

目的是便于监测每日常用的报表，是否及时更新成功，以提醒到相应的报表维护者。

当前在用报表平台是帆软旗下的BI和Report，目前版本还不支持邮件提醒异常报表。

小编左思右想，屡败屡战，终于试水成功。

激动到第一时间想把这些写下来分享出来^_^……

文章中部分代码参考了技术大牛的文章https://cloud.tencent.com/developer/article/1524113。

如有侵权，可随时联系。

目录

一、将需要监测的报表整理到一张表

二、报表每日跑批信息插入日志表

三、预警逻辑

四、SAS发送邮件

五、BAT文件及定时任务配置

一、将需要监测的报表整理到一张表

bi_mis_table表结构如下：

代码语言：javascript复制
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表结构如下：

代码语言：javascript复制
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写的插入代码供参考：

代码语言：javascript复制
/*以客户画像表为例，根据自己的报表适当调整*/
%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视图结构如下：

代码语言：javascript复制
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宏代码如下：

代码语言：javascript复制
%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文件内容：

代码语言：javascript复制
"D:Program FilesSASHome2SASFoundation9.4sas.exe"  
-sysin "……自动邮件.sas"   
-log "……自动邮件.log" 
-CONFIG "D:Program FilesSASHome2SASFoundation9.4nlszhsasv9.cfg"

去任务计划程序中设置下：

图片图片

小编设置的是每隔半个小时提醒一次。

此处敲黑板！！！

这里一定要选择【使用最高权限运行】，要不然会提示【ERROR: 授权不足，无法访问 C:Windowssystem32自动邮件.lst】

bingo~

第一次写技术贴，觉得有用希望点个赞~

sas 数据库 sql linux

0 人点赞