0679-6.2.0-通过Nginx获取CDSW的登录信息-续-2

#!/bin/bash
PASSWORD="123456"
DBNAME="cdsw_login_info"
TABLENAME="login_info"
log_dir=/usr/local/nginx/logs/
log_name=$(date -d "yesterday" +"%Y%m%d")

#将nginx日志中的十六进制引号转为正常显示的引号并定向到前一天日期命令的新日志文件
sed 's#\\x22#"#g' ${log_dir}access.log > ${log_dir}${log_name}.log
#将nginx日志文件清空，确保每次处理的是前一天的日志
cat /dev/null > ${log_dir}access.log
#按行读取新的日志文件进行处理
cat ${log_dir}${log_name}.log | while read line
do
if [[ $line =~ "authenticate" ]] && [[ $line =~ "POST" ]] && [[ $line =~ "login" ]]; then
    OIFS=$IFS; IFS="|"; set -- $line; aa=$1;bb=$3;cc=$4;source_ip=$5;referer=$6;user_agent=$7 IFS=$OIFS
    OIFS=$IFS; IFS='""'; set -- $cc; username=$7 IFS=$OIFS
    occur_time=${aa:0:10}" "${aa:11:8}
    if [[ $bb = "200"  ]]; then
        login_state='1'
    elif [[ $bb = "401"  ]]; then
        login_state='0'
    else
        login_state=''
    fi
    insert_sql="insert into ${DBNAME}.${TABLENAME}(source_ip,name,referer,user_agent,login_state,occur_time) values('$source_ip','$username','$referer','$user_agent',$login_state,'$occur_time')"
    mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${insert_sql}"
fi
if [[ $line =~ "sharing" ]]; then
    OIFS=$IFS; IFS="|"; set -- $line; aa=$1;bb=$3;cc=$4;source_ip=$5;referer=$6;user_agent=$7 IFS=$OIFS
    OIFS=$IFS; IFS='""'; set -- $cc; share_flag=$9 IFS=$OIFS
    occur_time=${aa:0:10}" "${aa:11:8}
    if [[ $share_flag = ""  ]]; then
        share_flag="clickShare"
    fi
    insert_sql="insert into ${DBNAME}.${TABLENAME}(source_ip,name,referer,user_agent,login_state,occur_time,share_flag) values('$source_ip','$username','$referer','$user_agent','1','$occur_time','$share_flag')"
    mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${insert_sql}"
fi
if [[ $line =~ "kerberos-credentials" ]]; then
    OIFS=$IFS; IFS="|"; set -- $line; aa=$1;request=$2;bb=$3;cc=$4;source_ip=$5;referer=$6;user_agent=$7 IFS=$OIFS
    OIFS=$IFS; IFS='""'; set -- $cc; kerberos_principal=$5 IFS=$OIFS
    occur_time=${aa:0:10}" "${aa:11:8}
    if [[ $bb = "204"  ]] && [[ $request =~ "POST" ]]; then
        kerberos_bind_state='1'
    elif [[ $bb = "422"  ]]; then
        kerberos_bind_state='0'
    else
        kerberos_bind_state=''
    fi
    insert_sql="insert into ${DBNAME}.${TABLENAME}(source_ip,name,referer,user_agent,login_state,occur_time,request,kerberos_principal,kerberos_bind_state) values('$source_ip','$username','$referer','$user_agent','1','$occur_time','$request','$kerberos_principal','$kerberos_bind_state')"
    mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${insert_sql}"
fi
done

CREATE TABLE `login_info` (
  `id` int(5) primary key NOT NULL AUTO_INCREMENT,
  `source_ip` varchar(32) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL,
  `referer` varchar(64) DEFAULT NULL,
  `user_agent` varchar(256) DEFAULT NULL,
  `login_state` char(1) DEFAULT NULL,
  `occur_time` timestamp ,
  `request` varchar(256) DEFAULT NULL,
  `share_flag` varchar(16) DEFAULT NULL,
`kerberos_principal` varchar(64) DEFAULT NULL,
`kerberos_bind_state` char(1) DEFAULT NULL) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

select kerberos_principal,kerberos_bind_state from login_info where kerberos_bind_state='1';

select kerberos_principal,kerberos_bind_state from login_info where kerberos_bind_state='0';