Zabbix 自定义report

#!/bin/bash
. /etc/profile
logdir='/home/admin/zabbix/zabbix_log'
mysql_host='localhost'
mysql_user='root'
mysql_passwd='*****'
mysql_database='zabbix'
year=`date +%Y`
month=`date +%m`
next_month=`echo $month+1|bc`

#if today is the last day of month
#then run this script
##################################################
today=`date +%d`
last_day=`cal | xargs | awk '{print $NF}'`
if [ "$today" != "$last_day" ]; then 
exit 1
fi
#################################################




if [ ! -d $logdir ];then
    mkdir $logdir
fi

##zabbix host month report
#select cpu avg unitil
mysql -h $mysql_host -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/cpu$month.txt<<EOF
set names utf8;
select from_unixtime(hi.clock,'%m') as Date,g.name as Group_Name,h.host as Host, round(avg(hi.value_avg),1) as Cpu_Avg_Unitil,round(max(hi.value_max),1) as Cpu_Max_Unitil  from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends hi on  i.itemid = hi.itemid  where  i.name='CPU unitil' and  hi.clock >= UNIX_TIMESTAMP('${year}-${month}-01 00:00:00') and  hi.clock < UNIX_TIMESTAMP('${year}-0${next_month}-01 00:00:00') group by h.host;
EOF
#select Mem usage
mysql -h $mysql_host  -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/mem$month.txt<<EOF
set names utf8;
select from_unixtime(hi.clock,'%m') as Date,g.name as Group_Name,h.host as Host, round(avg(hi.value_avg),1) as Mem_Avg_usage,round(max(hi.value_max),1) as Mem_Max_Usage   from hosts_groups hg join groups g on g.groupid = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends hi on  i.itemid = hi.itemid  where  i.name='Mem usage' and  hi.clock >= UNIX_TIMESTAMP('${year}-${month}-01 00:00:00') and  hi.clock < UNIX_TIMESTAMP('${year}-0${next_month}-01 00:00:00') group by h.host;
EOF

sed -i '1d' $logdir/cpu$month.txt
sed -i '1d' $logdir/mem$month.txt
awk 'NR==FNR{a[$1,$2,$3,$4]=$0;next}{print a[$1,$2,$3,$4],"	",$5,"	",$6}' $logdir/cpu$month.txt $logdir/mem$month.txt >$logdir/result$month.txt
sed -i '1i Month	Group name	Host	Cpu_avg_Unitil	Cpu_Max_Unitil	Mem_Avg_usage	Mem_Max_Usage' $logdir/result$month.txt

rm -rf $logdir/cpu$month.txt
rm -rf $logdir/mem$month.txt