ip='{{ inventory_hostname }}';
dt=`date -d '1 days ago' +"%Y-%m-%d"`;
for i in `ls /sas/SAS94/SASConfig/Lev2/SASApp/WorkspaceServer/Logs/SASApp_WorkspaceServer_${dt}*`;do
user=`grep "CLIENTUSERID" $i`;
if [ $? -eq 0 ];then
exe_user=`echo "$user" |head -1| awk '{print $NF}' | sed "s/'//g;s/;//g"`;
user_group=`echo "$user" | head -1| awk '{print substr($4,2)}'`;
active_time=`grep -E "^[0-9]{4}-[0-9]{2}-[0-9]{2}.*$" $i |sed -n '3p;$p' | awk '{ORS=" "}{print $1}' |awk '{cmd="echo $(($(date +%s -d "$2") - $(date +%s -d "$1")))";system(cmd)}'`;
process_id=`echo $i | awk -F '[.|_]' '{print $(NF-1)}'`;
exec_time=`grep -E " - 实际时间 [0-9]+.*" -C 1 $i |sed '/SAS 系统/,$d' | grep "实际时间" |awk '{print $7}' | awk '{split($1,a,":");for(i in a){sum+=(a[i]*(60**(length(a)-i)))}};END{print sum}'`;
cpu_time=`grep -E " - CPU 时间 [0-9]+.*" -C 1 $i |sed '/SAS 系统/,$d' | grep "CPU 时间" |awk '{print $8}' | awk '{split($1,a,":");for(i in a){sum+=(a[i]*(60**(length(a)-i)))}};END{print sum}'`;
echo ${ip}" "${process_id}" "$exe_user" "$user_group" "$active_time" "$exec_time" "$cpu_time;
fi;
done
for file in `ls $dir/SASApp_WorkspaceServer_${dt}*`;do userlog=`grep "CLIENTUSERID" $file`;if [ $? -eq 0 ];then user=`echo "$userlog" | head -1 | awk '{print $NF}' | sed "s/'//g;s/;//g"`;group=`echo "$userlog" | head -1 | awk '{print substr($4,2)}'`;process_id=`echo $file | awk -F '[.|_]' '{print $(NF-1)}'`;sqls=`cat $file | grep -E "^.* INFO (.*) :$group - [0-9]+ +(.*)$" | grep -v -E "INFO \[000000(06|07|08|09|20|14)\]"| grep -E -v "SAS 系统"|sed -r "s/^.* INFO \[(.*)\] :$group - [0-9]+ +(.*)$/\1 \2/g"`;sql_id=`echo "$sqls" | awk '{print $1}' | sort | uniq`;for i in $sql_id;do exe_time=`grep -E " INFO \[$i\] :.* +实际时间 +" $file | awk '{print $7}' | awk '{split($1,a,":");for(i in a){sum+=(a[i]*(60**(length(a)-i)))}};END{print sum}'`;cpu_time=`grep -E " INFO \[$i\] :.* +CPU 时间 +" $file | awk '{print $8}' | awk '{split($1,a,":");for(i in a){sum+=(a[i]*(60**(length(a)-i)))}};END{print sum}'`;sql=`echo "$sqls" | grep "^$i" | awk '{print substr($0,10)}'`;sql_text=`echo $sql`;echo "$ip@$process_id@$group@$user@$exe_time@$cpu_time@"$sql;done;fi;done
---
- hosts: SAS
gather_facts: no
tasks:
- name: rm history data
local_action: shell rm -f {{ inventory_dir }}/log/*
run_once: true
- name: get eg log
shell: ip='{{ inventory_hostname }}';dir='{{ eg_log }}';dt=`date -d "1 days ago" +%Y-%m-%d`;field=$'\001';for file in `ls ${dir}_WorkspaceServer_${dt}*`;do chinese=`grep " 实际时间 " $file`;if [ $? -eq 0 ];then realtime='实际时间';cputime='CPU 时间';libcon='逻辑库引用名';else realtime='real time';cputime='cpu time';libcon='was successfully assigned';fi;userlog=`grep "CLIENTUSERID" $file`;if [ $? -eq 0 ];then user=`echo "$userlog" | head -1 | awk '{print $NF}' | sed "s/'//g;s/;//g"`;group=`echo "$userlog" | head -1 | awk '{print substr($4,2)}'`;process_id=`echo $file | awk -F '[.|_]' '{print $(NF-1)}'`;sqls=`cat $file | grep -E "^.* INFO (.*) :$group - [0-9]+ +(.*)$" | grep -v -E "INFO \[000000(06|07|08|09|20|14)\]"| grep -E -v "SAS 系统"|sed -r "s/^.* INFO \[(.*)\] :$group - [0-9]+ +(.*)$/\1 \2/g"`;sql_id=`echo "$sqls" | awk '{print $1}' | sort | uniq`;for i in $sql_id;do exe_time=`grep -E " INFO \[$i\] :.* +${realtime} +" $file | awk '{print $7}' | awk '{split($1,a,":");for(i in a){sum+=(a[i]*(60**(length(a)-i)))}};END{print sum}'`;cpu_time=`grep -E " INFO \[$i\] :.* +$cputime +" $file | awk '{print $8}' | awk '{split($1,a,":");for(i in a){sum+=(a[i]*(60**(length(a)-i)))}};END{print sum}'`;sql=`echo "$sqls" | grep "^$i" | awk '{print substr($0,10)}' | tr '\n' ' '`;echo "EG${field}$ip${field}$process_id${field}$group${field}$user${field}$exe_time${field}$cpu_time${field}$sql";done;libname=`sed -n "/ libname /,/$libcon/Ip" $file | awk '{a=0;print "0 "$0;while(getline) {if(tolower($0)~/ libname /) {a+=1};print a" "$0;}}'`;for i in `echo "$libname" | awk '{print $1}' | sort | uniq`;do ss=`echo "$libname" | grep "^$i " |tail -1 |grep "$libcon"`;if [ $? -eq 0 ];then sql=`echo "$libname" | grep "^$i "|grep -E -v "SAS 系统"|sed -r -n "/^.* INFO \[(.*)\] :$group - [0-9]+ +(.*)$/p" | sed -r "s/^.* INFO \[(.*)\] :$group - [0-9]+ +(.*)$/\2/g" | tr '\n' ' '`;time=`echo "$libname" | grep "^$i " |sed -n '1p;$p' | awk '{ORS=" "}{print $2}' |awk '{cmd="echo $(($(date +%s -d "$2") - $(date +%s -d "$1")))";system(cmd)}'`;echo "EG${field}$ip${field}$process_id${field}$group${field}$user${field}$time${field}$time${field}$sql";fi;done;fi;done
register: saseg
- name: output eg data
local_action: copy content="{{ saseg.stdout }}" dest={{ inventory_dir }}/log/eg_{{ inventory_hostname }}
when: saseg.stdout
- name: get base log
shell: ip='{{ inventory_hostname }}';dir='{{ base_log }}';dt=`date -d "1 days ago" +%Y-%m-%d`;field=$'\001';for file in `ls ${dir}/Base_${dt}*`;do chinese=`grep " 实际时间 " $file`;if [ $? -eq 0 ];then realtime='实际时间';cputime='CPU 时间';libcon='逻辑库引用名';else realtime='real time';cputime='cpu time';libcon='was successfully assigned';fi;userlog=`grep "\- PAB-HQ-SAS" $file`;if [ $? -eq 0 ];then user=`echo "$userlog" | head -1 | awk '{print $9}'`;group=`echo "$userlog" | head -1 | awk '{print substr($4,2)}'`;process_id=`echo $file | awk -F '[.|_]' '{print $(NF-1)}'`;sqls=`cat $file | grep -E "^.* INFO (.*) :$group - [0-9]+ +(.*)$" | grep -v -E "INFO \[000000(06|07|08|09|20|14)\]"| grep -E -v "SAS 系统"|sed -r "s/^.* INFO \[(.*)\] :$group - [0-9]+ +(.*)$/\1 \2/g"`;sql_id=`echo "$sqls" | awk '{print $1}' | sort | uniq`;for i in $sql_id;do exe_time=`grep -E " INFO \[$i\] :.* +$realtime +" $file | awk '{print $7}' | awk '{split($1,a,":");for(i in a){sum+=(a[i]*(60**(length(a)-i)))}};END{print sum}'`;cpu_time=`grep -E " INFO \[$i\] :.* +$cputime +" $file | awk '{print $8}' | awk '{split($1,a,":");for(i in a){sum+=(a[i]*(60**(length(a)-i)))}};END{print sum}'`;sql=`echo "$sqls" | grep "^$i" | awk '{print substr($0,10)}' | tr '\n' ' '`;echo "BASE${field}$ip${field}$process_id${field}$group${field}$user${field}$exe_time${field}$cpu_time${field}$sql";done;libname=`sed -n "/ libname /,/$libcon/Ip" $file | awk '{a=0;print "0 "$0;while(getline) {if(tolower($0)~/ libname /) {a+=1};print a" "$0;}}'`;for i in `echo "$libname" | awk '{print $1}' | sort -n | uniq |sed '1d'`;do ss=`echo "$libname" | grep "^$i " |tail -1 |grep "$libcon"`;if [ $? -eq 0 ];then sql=`echo "$libname" | grep "^$i " | grep -E -v "SAS 系统" |sed -r -n "/^.* INFO \[(.*)\] :$group - [0-9]+ +(.*)$/p" | sed -r "s/^.* INFO \[(.*)\] :$group - [0-9]+ +(.*)$/\2/g" | tr '\n' ' '`;time=`echo "$libname" | grep "^$i " |sed -n '1p;$p' | awk '{ORS=" "}{print $2}' |awk '{cmd="echo $(($(date +%s -d "$2") - $(date +%s -d "$1")))";system(cmd)}'`;echo "BASE${field}$ip${field}$process_id${field}$group${field}$user${field}$time${field}$time${field}$sql";fi;done;fi;done
register: sasbase
when: base_log is defined and base_log
- name: output base data
local_action: copy content="{{ sasbase.stdout }}" dest={{ inventory_dir }}/log/base_{{ inventory_hostname }}
when: base_log is defined and base_log and sasbase.stdout
- name: merge data
local_action: shell cat {{ inventory_dir }}/log/* > /tmp/sas_execute_log.txt
run_once: true
- name: add hive partition
local_action: shell dt=`date -d '1 days ago' +"%Y%m%d"`;hive -e "load data local inpath '/tmp/sas_execute_log.txt' overwrite into table external.sas_execute_log partition(dt=${dt});"
run_once: true
- name: put data to grafana
local_action: shell {{ python3 }} {{ inventory_dir }}/sas_put.py
run_once: true
