|
1
2
3
4
5
6
7
8
|
[root@ip-10-10-13-8 zabbix_log]# pwd
/tmp/zabbix_log
[root@ip-10-10-13-8 zabbix_log]# ll
总用量 100
-rw-r--r-- 1 root root 5484 5月 14 11:19 zabbix_group_network_traffic.txt
-rw-r--r-- 1 root root 78282 5月 14 11:19 zabbix_host_search.txt
-rw-r--r-- 1 root root 5477 5月 14 11:19 zabbix_network_percent.txt
-rw-r--r-- 1 root root 296 5月 14 11:19 zabbix_room_network.txt
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
#!/bin/bash
. /etc/profile
logdir='/tmp/zabbix_log'
mysql_host='10.10.11.12'
mysql_user='zabbix'
mysql_passwd='zabbix'
mysql_database='zabbix'
year=`date +%Y`
month=`date +%m`
next_month=`echo $month+1|bc`
if [ ! -d $logdir ];then
mkdir $logdir
fi
|
|
1
2
3
4
5
6
7
|
#select cpu avg idle
mysql -h $mysql_host -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/info_mysql_cpu_avg_idle.txt<
set names utf8;
select from_unixtime(hi.clock,'%Y-%m') as Date,g.name as Group_Name,h.host as Host, round(avg(hi.value_avg),1) as Cpu_Avg_Idle from hosts_groups hg join groups g on g.groupid = hg.groupid jo
in 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.key_='system.cpu.util[,idle]' 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
|
|
1
2
3
4
5
6
7
|
#select cpu avg idle
mysql -h $mysql_host -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/info_mysql_cpu_avg_idle.txt<
set names utf8;
select from_unixtime(hi.clock,'%Y-%m') as Date,g.name as Group_Name,h.host as Host, round(avg(hi.value_avg),1) as Cpu_Avg_Idle from hosts_groups hg join groups g on g.groupid = hg.groupid jo
in 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.key_='system.cpu.util[,idle]' 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
|
|
1
2
3
4
5
6
7
|
#select cpu max load 5 minute
mysql -h $mysql_host -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/info_mysql_cpu_max_load5.txt<
set names utf8;
select from_unixtime(hi.clock,'%Y-%m') as Date,g.name as Group_Name,h.host as Host, round(max(hi.value_max),0) as Cpu_Max_Iowait 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.key_='system.cpu.load[all,avg5]' and hi.clock >= UNIX_TIMESTAMP('${ye
ar}-${month}-01 00:00:00') and hi.clock < UNIX_TIMESTAMP('${year}-0${next_month}-01 00:00:00') group by h.host;
EOF
|
|
1
2
3
4
5
6
7
|
#select memory avg avaiable
mysql -h $mysql_host -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/info_mysql_memory_avg_avaiable.txt<
set names utf8;
select from_unixtime(hi.clock,'%Y-%m') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg)/1024/1024/1024,1) as Memory_Avaiable from hosts_groups hg join groups g on g.groupi
d = hg.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on i.itemid = hi.itemid where i.key_='vm.memory.size[available]' 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
|
|
1
2
3
4
5
6
7
|
#select memory total
mysql -h $mysql_host -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/info_mysql_memory_total.txt<
set names utf8;
select from_unixtime(hi.clock,'%Y-%m') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg)/1024/1024/1024,0) as Memory_Total 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_uint hi on i.itemid = hi.itemid where i.key_='vm.memory.size[total]' and hi.clock >= UNIX_TI
MESTAMP('${year}-${month}-01 00:00:00') and hi.clock < UNIX_TIMESTAMP('${year}-0${next_month}-01 00:00:00') group by h.host;
EOF
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
#select network em2 avg in
mysql -h $mysql_host -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/info_mysql_network_em2_avg_in.txt<
set names utf8;
select from_unixtime(hi.clock,'%Y-%m') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg)/1000,0) as Network_Em2_Avg_In 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_uint hi on i.itemid = hi.itemid where i.key_='net.if.in[em2]' 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 '/Date*/d' $logdir/info_mysql_network_em2_avg_in.txt
#select network em2 avg out
mysql -h $mysql_host -u $mysql_user -p$mysql_passwd $mysql_database >$logdir/info_mysql_network_em2_avg_out.txt<
set names utf8;
select from_unixtime(hi.clock,'%Y-%m') as Date,g.name as Group_Name,h.host as Host,round(avg(hi.value_avg)/1000,0) as Network_Em2_Avg_Out from hosts_groups hg join groups g on g.groupid = h
g.groupid join items i on hg.hostid = i.hostid join hosts h on h.hostid=i.hostid join trends_uint hi on i.itemid = hi.itemid where i.key_='net.if.out[em2]' 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 '/Date*/d' $logdir/info_mysql_network_em2_avg_in.txt
paste $logdir/info_mysql_network_em2_avg_in.txt $logdir/info_mysql_network_em2_avg_out.txt |awk '{print $1"\t"$2"\t"$3"\t"$4"\t"$NF}' >$logdir/info_mysql_network_em2_avg.txt
|
zabbix报表功能实现脚本.zip