將所有偵測LOG後的結果存入DB，
目前設定:
一、執行它時，[SAS_JOB_log]會先清空，會抓
"F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\"下的LOG檔名稱包含"AGP.%" "C2C.%" "L2C.%" "LCK.%" '%VA_Report%' '%WATCH%'的LOG，會先抓取今天日期的LOG，如果沒抓到就會抓前一天的LOG，最多到前天。
"F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\"下的LOG檔名稱包含"AGP.%" "C2C.%" "L2C.%" "LCK.%" '%VA_Report%' '%WATCH%'的LOG，會先抓取今天日期的LOG，如果沒抓到就會抓前一天的LOG，最多到前天。
二、那些LOG要寫入DB
會先判斷ERROR_n1,
ERROR_n2, ERROR_n2。
n1:該行出現 "ERROR:" 且不出現"MPRINT" 與 '%put ERROR:'。
n2: 該行出現了"ERROR: SAS ended due to errors" 或是 "ERROR: Errors printed on page"
n3:該行出現了NOTE:
The SAS System used
三、寫到資料庫[SAS_JOB_log]的欄位[log] ,[job_nm] ,[status] ,[date]
[log]:為LOG檔該行的LOG訊息。
[job_nm]:LOG檔的完整名稱。
[date]:執行當下的日期。
[status]:如最下面三張圖
如果該JOB除現ERROR: status就會記錄ABORT， 如果LOG訊息有多行在DB就會記錄多行如圖: (從n1-8到n2行寫入DB，如果沒有n2就寫入n1上下8行)
結果:
 |
AGP.01.battest_07MAR19134632.2190001.log
|
 |
| [SAS_JOB_log] |
設定LOG路徑
與需要納入的LOG檔案名稱
建立一個暫存的資料夾存放LOG，因為可能有正在執行中的SAS程式，
其LOG會鎖住，不能讀取。
所以將全部LOG複製新的一份到暫存進行偵測。
%macro sas_job_log();
%MACRO FIND_SAS(DIRNAME,TYPE);/*参数有两个：路径，文件类型后缀*/
LIBNAME SASJOB "&SASJOB_PATH";
%PUT %STR(----------->DIRNAME=&DIRNAME);
%PUT %STR(----------->TYPE=&TYPE);
DATA SASJOB.DIRFILES;
RC=FILENAME("DIR","&DIRNAME");/*把&DIRNAME值传给文件引用符“DIR"*/
OPENFILE=DOPEN("DIR");/*得到路径标示符OPENFILE，DOPEN是打开directory的sas内置函数*/
IF OPENFILE>0 THEN DO;/*如果OPENFILE>0表示正确打开路径*/
NUMMEM=DNUM(OPENFILE);/*得到路径标示符OPENFILE中member的个数nummem*/
DO II=1 TO NUMMEM;
NAME=DREAD(OPENFILE,II);/*用DREAD依次读取每个文件的名字到NAME*/
FILEPATH="&DIRNAME"||NAME;
fnum=COMPRESS(NAME,".xml","");
fnum=COMPRESS(fnum,"user_case","");
OUTPUT;/*依次输出*/
END;
END;
KEEP NAME filepath fnum;/*只保留filepath、fnum列*/
RUN;
PROC SORT DATA=SASJOB.DIRFILES;/*按照NAME排序*/
BY DESCENDING NAME;
%IF &TYPE^=ALL %THEN %DO;/*是否过滤特定的文件类型&TYPE*/
WHERE INDEX(UPCASE(NAME),UPCASE(".&TYPE"));/*Y,则通过检索NAME是否包含&TYPE*/
%END;
RUN;
/*PROC PRINT DATA=SASJOB.DIRFILES;*/
/*RUN;*/
%MEND FIND_SAS;
%MACRO DIR(SASJOB_PATH,TYPE,DATASET_NUM);
LIBNAME SASJOB "&SASJOB_PATH";
%LET SASJOB_PATH=&SASJOB_PATH.;
%FIND_SAS(&SASJOB_PATH,&TYPE);
DATA TEMP(KEEP=NAME_SAS NAME_LOG);
SET SASJOB.DIRFILES;
NAME_SAS=COMPRESS(NAME);
NAME_LOG=COMPRESS(TRANWRD(NAME,'.sas','.log'),);
IF NAME_SAS ^='dirfiles.sas7bdat' AND NAME_SAS ^='dirfiles.sas7bdat.lck';
RUN;
PROC SORT DATA=TEMP OUT=SASJOB.DIRFILES;
BY NAME_SAS;
RUN;
data _null_;
td_p = today();
td_1_p=today()-1;
td = put(td_p, date7.);
td_1 = put(td_1_p, date7.);
call symputx("td",td);
call symputx("td_1",td_1);
run;
data SASJOB.DIRFILES;
set SASJOB.DIRFILES;
x_obs=1;
if find(NAME_LOG , "&td.") >0 then do;
date = input("&td.", date9.);
Real_job_nm= SUBSTR(NAME_SAS,1, 14);
output;
end;
/* 前1天*/
if find(NAME_LOG , "&td_1.") >0 then do;
date = input("&td_1.", date9.);
Real_job_nm= SUBSTR(NAME_SAS,1, 14);
output;
end;
run;
data tmp_X_obs;
x_obs = 1;
run;
proc append base = SASJOB.DIRFILES data =tmp_X_obs;
run;
%let open_d = %sysfunc (open(SASJOB.DIRFILES));
%let DIRFILES_n = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));
%if &DIRFILES_n. =1 %then %do;
data SASJOB.DIRFILES;
set TEMP;
where NAME_LOG ? "&td_1."
and (NAME_LOG like "AGP.%" or NAME_LOG like "C2C.%"
or NAME_LOG like "L2C.%" or NAME_LOG like "LCK.%"
or NAME_LOG like '%VA_Report%' or NAME_LOG like '%WATCH%');
run;
%end;
%MEND DIR;
%DIR(D:\SAS\Config\Lev1\SASApp\BatchServer\Logs,log,0);
%x_fcf_twn_ini(log=T,ea=Y);
%fcf_get_runasofdate;
proc sql noprint;
select distinct job_nm
into : lag_job_nm separated by "|"
from sasjob.tmp_lag_log
where status in("NO CONNECTION", "ABORT");
create table tmp_lag_job as
select distinct job_nm
from sasjob.tmp_lag_log
where status in("NO CONNECTION", "ABORT");
quit;
/*%put &lag_job_nm.; 出ERROR的只要一次*/
%let open_d = %sysfunc (open(tmp_lag_job));
%let LAG_JOB_OBS = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));
%do L= 1 %to &LAG_JOB_OBS.;
%let lag_job_nm&L. = %qscan(%bquote(&lag_job_nm.),&L.,%str(|));
data sasjob.DIRFILES;
set sasjob.DIRFILES;
where NAME_LOG ^= "&&lag_job_nm&L..";
run;
%end;
data tmp_log;
if 0 then set seg_kc.SAS_JOB_log;
run;
proc datasets library=seg_kc noprint;
delete SAS_JOB_log ;
run;
data seg_kc.SAS_JOB_log ;
if 0 then set tmp_log;
if job_nm ^= "";
run;
proc datasets library=work noprint;
delete tmp_log;
run;
proc sql noprint;
create table tmp_job_n as
select NAME_LOG as my_logtime
from sasjob.DIRFILES
where (NAME_LOG like "AGP.%" or NAME_LOG like "C2C.%"
or NAME_LOG like "L2C.%" or NAME_LOG like "LCK.%"
or NAME_LOG like '%VA_Report%' or NAME_LOG like '%WATCH%');
select my_logtime
into : my_logtime separated by "\"
from tmp_job_n;
quit;
%let open_d = %sysfunc (open(work.tmp_job_n));
%let job_n = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));
/*%put &my_logtime.;*/
/*%let etls_jobName=LCK.02.SOURCE_CHECK;*/
/*%let t=1;*/
/**/
/*%let etls_jobName = AGP.05.CDD_MAIN_PROCESS;*/
/*%let t=14JAN19143028.3110001;*/
/**/
/**/
/*%let etls_jobName=RERUNAGP;*/
/*%let t=1;*/
/**/
/**/
/*%put &etls_jobName._&t..log;*/
/* */
x 'md "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\" ';
x 'xcopy "D:\SAS\Config\Lev1\SASApp\BatchServer\Logs\*.*" "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG "/Y' ;
%do jon_num = 1 %to &job_n.;
%let jon_num&jon_num. = %scan(&my_logtime., &jon_num.,%str(\));
data SAS_JOB_log_tmp1 ;
infile "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\&&jon_num&jon_num.." dsd missover dlm='09'x;
length log1 log2 log3 $3000.;
input log1 $ log2 $;
retain error_n error_n2 error_n3;
if find(log1, "The SAS System 20") =0 ;
if find(log1, "Create Time=20") =0 ;
if missing(log1)=0;
if find(log1, "ERROR:") >0 and find(log1, "MPRINT")=0 and find(log1, '%put ERROR:')=0 then do ;
log3 = log1;
error_n=_n_;
output;
end;
if find(log1, "ERROR: SAS ended due to errors")>0 or find(log1, "ERROR: Errors printed on page") >0 then do ;
log3 = log1;
error_n2=_n_;
output;
end;
if find(log1, "NOTE: The SAS System used:")>0 then do;
error_n3=_n_;
output;
end;
run;
%let open_d = %sysfunc (open(work.SAS_JOB_log_tmp1));
%let SASLOG_obs = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));
%if &SASLOG_obs. > 0 %then %do;
data _null_;
set SAS_JOB_log_tmp1;
if _n_ = 1 then do ;
call symputx("error_n",error_n) ;
end;
call symputx("error_n2",error_n2);
call symputx("error_n3",error_n3);
/* %put &error_n. &error_n2. &error_n3.;*/
run;
data SAS_JOB_log_TMP2 ( drop=log1);
infile "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\&&jon_num&jon_num.." dsd missover dlm='09'x;
length log1 log $3000. job_nm $80.;
input log1 $ log $ job_nm$;
job_nm= "&&jon_num&jon_num..";
date = input(put(&runasofdate.,8.),yymmdd8.) ;
if find(log1, "The SAS System 20") =0;
if find(log1, "Create Time=20") =0 ;
if missing(log1)=0;
format date yymmdd8.;
/* if find(log1, "NOTE: The SAS System used:") >0 and find(log1, "ERROR:") >0 then do;*/
/* if find(log1, 'MPRINT(SVCREG): put "ERROR:') =0 or find(log1, "MPRINT(SVCREG): put 'ERROR:") =0 then do;*/
/* log3="RUNOK"; */
/* output;*/
/* end;*/
/* end;*/
if &error_n2.^=. then do;
if &error_n2. >= _n_ >= &error_n. - 8 then do;
log = log1;
if (find(log,"關閉一個現存的連線") >0 or find(log,"無法開啟 SQL Server 連線") >0 ) and
(find(job_nm, "AGP.01.I")>0 or find(job_nm, "AGP.03.I")>0 or find(job_nm, "LCK")>0 or find(job_nm, "L2C")>0)
then do;
status = "NO CONNECTION";
output;
end; else do;
status = "ABORT";
output;
end;
end;
end; else do;
if &error_n.+8 >= _n_ >= &error_n. - 8 then do;
log = log1;
if find(log,"關閉一個現存的連線") >0 or find(log,"無法開啟 SQL Server 連線") >0 and
(find(job_nm, "AGP.01.I")>0 or find(job_nm, "AGP.03.I")>0 or find(job_nm, "LCK")>0 or find(job_nm, "L2C")>0)
then do;
status = "NO CONNECTION";
output;
end; else do;
status = "ABORT";
output;
end;
end;
end;
if _n_ = &error_n3.;
log = "RUNOK";
status = "RUNOK";
output;
run;
%end; %else %do;
data SAS_JOB_log_TMP2 ;
length job_nm $80. ;
log = 'RUNNING';
status = 'RUNNING';
job_nm= "&&jon_num&jon_num..";
date = input(put(&runasofdate.,8.),yymmdd8.) ;
format date yymmdd8.;
run;
%end;
%let open_d = %sysfunc (open(work.SAS_JOB_log_TMP2));
%let noc = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));
data SAS_JOB_log_TMP2 ;
set SAS_JOB_log_TMP2;
XN = _N_;
do _n_ = 1 to &noc.;
if lag(status) = "NO CONNECTION" then status = "NO CONNECTION";
if lag(status) = "ABORT" then status = "ABORT";
end;
if log = "RUNOK" and (status= "ABORT" or status = "NO CONNECTION") then delete;
run;
proc sort data=SAS_JOB_log_TMP2;by descending status ;run;
data SAS_JOB_log_TMP2 ;
set SAS_JOB_log_TMP2;
do _n_ = 1 to &noc.;
if lag(status) = "NO CONNECTION" then status = "NO CONNECTION";
if lag(status) = "ABORT" then status = "ABORT";
end;
run;
proc sort data=SAS_JOB_log_TMP2;by XN ;run;
proc append base=sasjob.tmp_lag_log data=SAS_JOB_log_TMP2 FORCE;
run;
proc append base=seg_kc.SAS_JOB_log data=SAS_JOB_log_TMP2 FORCE;
run;
%end;
proc sort data=sasjob.tmp_lag_log nodupkey;
by job_nm;
run;
x ' rd "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\" /s /q';
%mend;
%sas_job_log()
0 意見:
張貼留言