#!/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],"\t",$5,"\t",$6}' $logdir/cpu$month.txt $logdir/mem$month.txt >$logdir/result$month.txt

sed -i '1i Month\tGroup name\tHost\tCpu_avg_Unitil\tCpu_Max_Unitil\tMem_Avg_usage\tMem_Max_Usage' $logdir/result$month.txt

rm -rf $logdir/cpu$month.txt

rm -rf $logdir/mem$month.txt