#function:将AC与GLPI数据库的MAC同步
#exception.mac单独存放于GLPI数据库，为以后GLPI二次开发做准备,mac,user,model,approval
#date: 2015.12.01邮件显示MAC属性信息，简化代码
#date: 2015.12.02修改部分NULL问题，练习left join on 多表联合查询,增加判断是否有重复的MAC
#date: 2015.12.03调试脚本在crontab运行发邮件的测试
#date: 2015.12.04增加对mysql查询和AC的连接做retry动作。
#提醒 如果添加到计划任务，注意 mac.add.del.exp脚本文件 添加删除文件路径
# 如果在手动执行脚本时，请确保计划任务没有正在执行本脚本，要不然有冲突！
#我已经遇到在调试过程中，因为计划任务刚巧在执行这个脚本，我也在手动执行这个脚本，导致AC 的MAC会被全部删除！
#date 2015.12.04 15:38 增加脚本是否已经被执行的判断语句，如果脚本已经被系统执行，那么本次就不执行！
# 使用方法
# 安装expect mysql-client mail
# yum install expect mysql
# mail使用CentOS自带的mailx，测试：echo "`date`" | mail -v -s "测试" user@163.com
# [root@pc0003 sync.for.mail]# pwd
# /sync.for.mail
# [root@pc0003 sync.for.mail]# ll
# -rwxr-xr-x. 1 root root 1.3K 12月 3 16:22 mac.add.del.exp
# -rwxr-xr-x. 1 root root 9.7K 12月 3 17:38 sync.for.mail.sh
# [root@pc0003 sync.for.mail]# vim /etc/crontab
# */5 * * * * root /bin/bash /sync.for.mail/sync.for.mail.sh > /dev/null 2>&1
mysql 8表 联合查询
8表联合查询的sql语句，输出 Mac Type User/Group Computer/Phone/Device AssetTag Status
select upper(glpi_items_devicenetworkcards.mac),
glpi_devicenetworkcards.designation,
concat_ws('',glpi_users.realname,glpi_users.firstname,glpi_groups.name),
concat_ws('',glpi_computers.name,glpi_peripherals.name,glpi_phones.name),
concat_ws('',glpi_computers.otherserial,glpi_peripherals.otherserial,glpi_phones.otherserial),
glpi_states.name
from glpi_items_devicenetworkcards
left join glpi_computers on glpi_computers.id=glpi_items_devicenetworkcards.items_id
left join glpi_peripherals on glpi_items_devicenetworkcards.items_id=glpi_peripherals.id
left join glpi_phones on glpi_items_devicenetworkcards.items_id=glpi_phones.id
left join glpi_states on (glpi_computers.states_id=glpi_states.id) or (glpi_peripherals.states_id=glpi_states.id) or (glpi_phones.states_id=glpi_states.id)
left join glpi_devicenetworkcards on glpi_items_devicenetworkcards.devicenetworkcards_id = glpi_devicenetworkcards.id
left join glpi_users on (glpi_computers.users_id=glpi_users.id) or (glpi_peripherals.users_id=glpi_users.id) or (glpi_phones.users_id=glpi_users.id)
left join glpi_groups on (glpi_computers.groups_id=glpi_groups.id) or (glpi_peripherals.groups_id=glpi_groups.id) or (glpi_phones.groups_id=glpi_groups.id)
where glpi_devicenetworkcards.designation="Wireless"
order by glpi_users.name;
邮件内容输出格式：
与AC 交互，有 mac地址查询，添加，删除，3种功能
[root@hghast001 sync.for.mail]# cat mac.add.del.exp
#!/usr/bin/expect -f
set var [lindex $argv 0]
set ip 192.168.AC.IP
set user AC.USER
set password AC.PASSWORD 没有引号
set timeout 1
spawn ssh $user@$ip
expect {
"*yes/no" { send "yes\r"; exp_continue}
"*password:" { send "$password\r" }
}
expect "<ac001>"
send "sys\r"
expect "\[ac001\]"
send "wlan\r"
expect "ac001-wlan-view"
if { $var == "add" } {
send "sta-whitelist-profile id 1\r"
expect "wlan-whitelist-prof"
set fd [open /sync.for.mail/MoreThanAC r]
while {[gets $fd line] != -1} {
expect "wlan-whitelist-prof"
send "sta-mac $line \r"
sleep 1
}
close $fd
}
if { $var == "del" } {
send "sta-whitelist-profile id 1\r"
expect "wlan-whitelist-prof"
set fd [open /sync.for.mail/MoreThanGlpi r]
while {[gets $fd line] != -1} {
expect "wlan-whitelist-prof"
send "undo sta-mac $line \r"
sleep 0.2
}
close $fd
}
if { $var == "search" } {
set i 0
expect "wlan-view"
send "sta-whitelist-profile id 1\r"
expect "wlan-whitelist-prof"
send "d th\r"
while {$i < 7} {
expect "More"
send "\t"
incr i
}
}
send "q \r"
expect "ac001-wlan-view"
send "q \r"
expect "ac001"
send "q \r"
expect "<ac001>"
#send "save all \r"
#expect "Are you sure to continue"
#
#send "y\r"
#expect "<ac001>"
send "q \r"
expect "closed."
#expect eof
interact
[root@hghast001 sync.for.mail]#
主程序，会调用上面的与AC交互的程序，用来从mysql查询，Retry，比对，，重复提醒，发送邮件
[root@hghast001 sync.for.mail]# cat sync.for.mail.sh
#!/bin/bash
#date:2015.11.30
#function:将AC与GLPI数据库的MAC同步
#exception.mac单独存放于GLPI数据库，为以后GLPI二次开发做准备,mac,user,model,approval
#date: 2015.12.01邮件显示MAC属性信息，简化代码
#date: 2015.12.02修改部分NULL问题，练习left join on 多表联合查询,增加判断是否有重复的MAC
#date: 2015.12.03调试脚本在crontab运行发邮件的测试
#date: 2015.12.04增加对mysql查询和AC的连接做retry动作。
#提醒 如果添加到计划任务，注意 mac.add.del.exp脚本文件 添加删除文件路径
# 如果在手动执行脚本时，请确保计划任务没有正在执行本脚本，要不然有冲突！
#我已经遇到在调试过程中，因为计划任务刚巧在执行这个脚本，我也在手动执行这个脚本，导致AC 的MAC会被全部删除！
#date 2015.12.04 15:38 增加脚本是否已经被执行的判断语句，如果脚本已经被系统执行，那么本次就不执行！
# 使用方法
# 安装expect mysql-client mail
# yum install expect mysql
# mail使用CentOS自带的mailx，测试：echo "`date`" | mail -v -s "测试" user@163.com
# [root@pc0003 sync.for.mail]# pwd
# /sync.for.mail
# [root@pc0003 sync.for.mail]# ll
# -rwxr-xr-x. 1 root root 1.3K 12月 3 16:22 mac.add.del.exp
# -rwxr-xr-x. 1 root root 9.7K 12月 3 17:38 sync.for.mail.sh
# [root@pc0003 sync.for.mail]# vim /etc/crontab
# */5 * * * * root /bin/bash /sync.for.mail/sync.for.mail.sh > /dev/null 2>&1
#判断脚本是否已经正在被执行
ps aux > thread
grep "bin/bash ./sync.for.mail" thread > /dev/null
let num=`grep "bin/bash ./sync.for.mail" thread | wc -l`
if [ $num -gt 1 ];then
grep "bin/bash ./sync.for.mail" thread
echo "脚本已经被系统执行！，请3分钟后重试！"
rm -rf thread
exit 5
fi
rm -rf thread
#环境
cd /sync.for.mail
export PATH=$PATH:/usr/local/mysql/bin:/usr/lib64/qt-3.3/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/usr/java/jdk1.7.0_79//bin:/usr/java/jdk1.7.0_79/jre/bin:/root/bin
# export TERM=xterm
# flag1全局变量用来判断是否需要向AC添加mac信息
# flag2全局变量用来判断是否需要从AC删除mac信息
# flag3全局变量用来判断是否有变更动作，并发送邮件
# flag4全局变量用来判断是否有重复的mac
clear
flag1=1
flag2=1
flag3=1
flag4=1
#清空临时文件,避免带来影响
cat /dev/null > MoreThanAC
cat /dev/null > MoreThanAC.info
cat /dev/null > MoreThanGlpi
cat /dev/null > MoreThanGlpi.info
cat /dev/null > WirelessView
cat /dev/null > exception.info
cat /dev/null > mail.info
cat /dev/null > GlpiAndExceptionMac
cat /dev/null > WlanMac
cat /dev/null > repeat
touch sync.log
# 日志文件的清空
let SyncLog=`cat sync.log|wc -l`
if [ $SyncLog -gt 9999 ];then
echo "`date` 清空日志" > sync.log
fi
####################################################################
#获取GlpiAndExceptionMac WlanMac 列表，做正反对比
mysql_user="user"
mysql_passwd="PASSWORD"
mysql_host="MYSQL_SERVER_IP"
while [ 1 ]
do
echo "从MySQL获取glpi Wireless Mac 和Exception Mac 列表"
mysql -h$mysql_host -p$mysql_passwd -u$mysql_user -Ne "use glpi; select glpi_devicenetworkcards.designation, upper(glpi_items_devicenetworkcards.mac) from glpi_items_devicenetworkcards,glpi_devicenetworkcards where glpi_items_devicenetworkcards.devicenetworkcards_id = glpi_devicenetworkcards.id and glpi_devicenetworkcards.designation=\"Wireless\";select mac from exception;" | egrep -o "([0-9a-fA-F]{2})(([/\s:][0-9a-fA-F]{2}){5})" | sed 's/://g'|sed -r 's/^(.{4})(.{4})(.{4})$/\1-\2-\3/g' > MacList
let GlpiMacNum=`cat MacList|wc -l`
# 判断与mysql网络连接
if [ $GlpiMacNum -lt 50 ]
then
echo -e "`date` 正在重试与MySQL联络" >> sync.log
sleep 5
else
#把重复的mac 合并
echo "GLPI+exception有`sort MacList |uniq -c | awk '{print $2}'|wc -l`个无线MAC"
cat MacList >>GlpiAndExceptionMac
break
fi
done
while [ 1 ]
do
echo "获取当前wlan.mac 列表"
expect /sync.for.mail/mac.add.del.exp search |grep "sta-mac" | egrep -o "([0-9a-fA-F]{4})(([/\s-][0-9a-fA-F]{4}){2})"| tr '[a-z]' '[A-Z]' > WlanMac
let ACMacNum=`cat WlanMac | wc -l`
if [ $ACMacNum -gt 50 ] ;then
echo "wlan 有`echo $ACMacNum`个无线MAC"
break
else
echo -e "`date` 正在重试与AC 连接" >> sync.log
sleep 5
fi
done
while [ 1 ]
do
echo "获取exception 一览表 "
mysql -h$mysql_host -p$mysql_passwd -u$mysql_user -Ne "use glpi;select * from exception;" > exception.info
let GlpiMacNum=`cat exception.info |wc -l`
# 判断与mysql网络连接
if [ $GlpiMacNum -lt 50 ]
then
echo -e "`date` 正在重试与MySQL联络" >> sync.log
sleep 5
else
break
fi
done
while [ 1 ]
do
echo "获取GLPI MAC地址属性一览表"
# 8表联合查询的sql语句，输出 Mac Type User/Group Computer/Phone/Device AssetTag Status
# select upper(glpi_items_devicenetworkcards.mac),
# glpi_devicenetworkcards.designation,
# concat_ws('',glpi_users.realname,glpi_users.firstname,glpi_groups.name),
# concat_ws('',glpi_computers.name,glpi_peripherals.name,glpi_phones.name),
# concat_ws('',glpi_computers.otherserial,glpi_peripherals.otherserial,glpi_phones.otherserial),
# glpi_states.name
# from glpi_items_devicenetworkcards
# left join glpi_computers on glpi_computers.id=glpi_items_devicenetworkcards.items_id
# left join glpi_peripherals on glpi_items_devicenetworkcards.items_id=glpi_peripherals.id
# left join glpi_phones on glpi_items_devicenetworkcards.items_id=glpi_phones.id
# left join glpi_states on (glpi_computers.states_id=glpi_states.id) or (glpi_peripherals.states_id=glpi_states.id) or (glpi_phones.states_id=glpi_states.id)
# left join glpi_devicenetworkcards on glpi_items_devicenetworkcards.devicenetworkcards_id = glpi_devicenetworkcards.id
# left join glpi_users on (glpi_computers.users_id=glpi_users.id) or (glpi_peripherals.users_id=glpi_users.id) or (glpi_phones.users_id=glpi_users.id)
# left join glpi_groups on (glpi_computers.groups_id=glpi_groups.id) or (glpi_peripherals.groups_id=glpi_groups.id) or (glpi_phones.groups_id=glpi_groups.id)
# where glpi_devicenetworkcards.designation="Wireless"
# order by glpi_users.name;
mysql -h$mysql_host -p$mysql_passwd -u$mysql_user -Ne "use glpi; select upper(glpi_items_devicenetworkcards.mac), glpi_devicenetworkcards.designation, concat_ws('',glpi_users.realname,glpi_users.firstname,glpi_groups.name), concat_ws('',glpi_computers.name,glpi_peripherals.name,glpi_phones.name), concat_ws('',glpi_computers.otherserial,glpi_peripherals.otherserial,glpi_phones.otherserial), glpi_states.name from glpi_items_devicenetworkcards left join glpi_computers on glpi_computers.id=glpi_items_devicenetworkcards.items_id left join glpi_peripherals on glpi_items_devicenetworkcards.items_id=glpi_peripherals.id left join glpi_phones on glpi_items_devicenetworkcards.items_id=glpi_phones.id left join glpi_states on (glpi_computers.states_id=glpi_states.id) or (glpi_peripherals.states_id=glpi_states.id) or (glpi_phones.states_id=glpi_states.id) left join glpi_devicenetworkcards on glpi_items_devicenetworkcards.devicenetworkcards_id = glpi_devicenetworkcards.id left join glpi_users on (glpi_computers.users_id=glpi_users.id) or (glpi_peripherals.users_id=glpi_users.id) or (glpi_phones.users_id=glpi_users.id) left join glpi_groups on (glpi_computers.groups_id=glpi_groups.id) or (glpi_peripherals.groups_id=glpi_groups.id) or (glpi_phones.groups_id=glpi_groups.id) where glpi_devicenetworkcards.designation=\"Wireless\" order by glpi_states.name;" > WirelessView
let GlpiMacNum=`cat WirelessView |wc -l`
# 判断与mysql网络连接
if [ $GlpiMacNum -lt 50 ]
then
echo -e "`date` 正在重试与MySQL联络" >> sync.log
sleep 5
else
break
fi
done
##################################################################
#开始比较，把比AC多的MAC 筛选出来,待添加到AC;如果没有需要添加的mac,邮件中就不会显示这一项
#编程思想：在for循环申明一个stat局部变量初始值为0，如果在每次内循环结束，stat值没有发生变化，说明本次外循环的值没有在内循环找到,这就是要被添加的MAC
for i in `cat GlpiAndExceptionMac`; do
stat=0
for j in `cat WlanMac`; do
if [ $i == $j ] ;then
stat=1
fi
done
if [ $stat -eq 0 ] ;then
#待添加的MAC地址保存到文件 MoreThanAC
echo $i >> MoreThanAC
#转换格式，并把mac地址保存，待发邮件
grep `echo $i | sed 's/\-//g'|sed -r 's/^(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})$/\1:\2:\3:\4:\5:\6/g'` WirelessView >> MoreThanAC.info
flag1=0
fi
done
if [ $flag1 -eq 0 ] ;then
echo "开始向AC添加以下MAC";cat MoreThanAC
#执行添加mac地址的脚本
./mac.add.del.exp add
sed -i "1i往WLAN中新增MAC如下,有 `wc -l MoreThanAC |awk '{print $1}'`个" MoreThanAC.info
#输出添加的mac地址信息到 邮件内容
cat MoreThanAC.info >>mail.info
echo -e "`date` \n`cat MoreThanAC.info`\n" >> sync.log
# 为邮件内容段落换行
echo "">>mail.info
flag3=0
fi
########################################################
#开始比较，把比GLPI多的MAC筛选出来,待从AC中删除;如果没有需要删除的mac,邮件中就不会显示这一项
for i in `cat WlanMac`; do
stat=0
for j in `cat GlpiAndExceptionMac`; do
if [ $i == $j ] ;then
stat=1
fi
done
if [ $stat -eq 0 ] ;then
echo $i >> MoreThanGlpi
#注意：待从AC中删除的MAC，也就是GLPI 中没有的MAC
echo $i | sed 's/\-//g'|sed -r 's/^(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})$/\1:\2:\3:\4:\5:\6/g' >> MoreThanGlpi.info
flag2=0
fi
done
if [ $flag2 -eq 0 ] ;then
echo -e "\n开始从AC删除以下MAC"; cat MoreThanGlpi
./mac.add.del.exp del
sed -i "1i 从WLAN中删除MAC如下,有 `wc -l MoreThanGlpi |awk '{print $1}'`个" MoreThanGlpi.info
cat MoreThanGlpi.info >>mail.info
echo -e "`date` \n`cat MoreThanGlpi.info`\n" >> sync.log
echo "">> mail.info
flag3=0
fi
############################################################
#判断重复的mac，如果存在重复的mac，就发邮件;如果没有重复的mac,邮件中就不会显示这一项
for j in `sort WirelessView |awk '{print $1}'|uniq -c|awk '{if ($1>1) print $2}'` ; do
grep $j WirelessView >> repeat
if [ $? -eq 0 ] ;then
flag4=0
fi
done
if [ $flag4 -eq 0 ] ;then
flag3=0
sed -i "1iGLPI 数据库重复的Wireless MAC信息如下" repeat
cat repeat >> mail.info
echo "">>mail.info #段与段之间隔开
fi
############################################################
#如果有任何wireless mac 变动，发邮件出来
if [ $flag3 -eq 0 ] ;then
echo -e "GLPI Wireless Mac 一览表\n Mac Type User/Group Computer/Phone/Device AssetTag Status\n">>mail.info
cat -n WirelessView >> mail.info
echo -e "\n\nWLAN Exception Mac 一览表\n Mac User Model Approver\n ">>mail.info
cat -n exception.info >> mail.info
while [ 1 ]
do
echo "正在发送邮件"
cat mail.info | /bin/mail -v -s "Wireless Mac变更通知" Team_IT@公司logo.cn |grep "公司logo.cn.*Sent"
if [ $? -eq 0 ];then
echo "邮件发送成功"
break
else
echo "重新 发送邮件"
sleep 10
fi
done
else
echo -e "`date` 没有任何变动，就不发邮件了。" >> sync.log
fi
#清除临时文件
rm -rf MoreThanAC
rm -rf MoreThanAC.info
rm -rf MoreThanGlpi
rm -rf MoreThanGlpi.info
rm -rf WirelessView
rm -rf exception.info
rm -rf mail.info
rm -rf GlpiAndExceptionMac
rm -rf WlanMac
rm -rf MacList
rm -rf repeat
[root@hghast001 sync.for.mail]#
Ver4.0 版本发布
相对Ver3.0版本，做如下改动
1,ping 网关，如果ping网关不正常，就退出脚本【已测试】
2,ping DNS，如果ping DNS不正常，就退出脚本【已测试】
3,ping 公网某个网站，如果ping不正常，邮件就发不出来，退出脚本【已测试】
4，对获取glpi和exception的mac地址判断，如果获取不到，就会重试，重试次数达到5次还不正常的话，就发警告邮件，并退出脚本【已测试】
5，对从AC获取的mac地址判断，如果获取不到，就会重试，重试次数达到5次还不正常的话，就发警告邮件，并退出脚本【已测试】
6，对MySQL服务器中的例外mac地址一览表做判断，如果获取不到，就会重试，重试次数达到5次还不正常的话，就发警告邮件，并退出脚本【已测试】
7，对MySQL服务器中的glpi无线mac地址一览表做判断，如果获取不到，就会重试，重试次数达到5次还不正常的话，就发警告邮件，并退出脚本【已测试】
8，判断脚本是否已经正在被执行。如果这个脚本正在被执行并且没有结束，又被系统或者用户再次执行，脚本检测到有相同的进程就会自动退出。防止一个程序被多次执行【已测试】
sync.for.mail.sh 脚本，主要负责：
判断程序的进程数
从MySQL 数据库获取MAC地址信息，把MySQL中与AC中的MAC地址进行比对
调用expect脚本，用来查询，添加 删除，
判断网络状态
判断重复的MAC地址
发邮件
mac.add.del.exp与AC 交互，有查询 添加 删除 MAC地址 三种功能，此脚本主要用于被主程序调用
[root@hghast001 sync.for.mail]# cat mac.add.del.exp
#!/usr/bin/expect -f
set var [lindex $argv 0]
set ip 192.168.***.***
set user ad**************
set password G0************
set timeout 20
spawn ssh $user@$ip
expect {
"*yes/no" { send "yes\r"; exp_continue}
"*password:" { send "$password\r" }
}
expect "<ac001>"
send "sys\r"
expect "\[ac001\]"
send "wlan\r"
expect "ac001-wlan-view"
if { $var == "add" } {
send "sta-whitelist-profile id 1\r"
expect "wlan-whitelist-prof"
set fd [open /sync.for.mail/MoreThanAC r]
while {[gets $fd line] != -1} {
expect "wlan-whitelist-prof"
send "sta-mac $line \r"
sleep 1
}
close $fd
}
if { $var == "del" } {
send "sta-whitelist-profile id 1\r"
expect "wlan-whitelist-prof"
set fd [open /sync.for.mail/MoreThanGlpi r]
while {[gets $fd line] != -1} {
expect "wlan-whitelist-prof"
send "undo sta-mac $line \r"
sleep 0.2
}
close $fd
}
if { $var == "search" } {
set i 0
expect "wlan-view"
send "sta-whitelist-profile id 1\r"
expect "wlan-whitelist-prof"
send "d th\r"
while {$i < 7} {
expect "More"
send "\t"
incr i
}
}
send "q \r"
expect "ac001-wlan-view"
send "q \r"
expect "ac001"
send "q \r"
expect "<ac001>"
#send "save all \r"
#expect "Are you sure to continue"
#
#send "y\r"
#expect "<ac001>"
send "q \r"
expect "closed."
#expect eof
interact
[root@hghast001 sync.for.mail]#
[root@hghast001 sync.for.mail]# cat sync.for.mail.sh
#!/bin/bash
#date:2015.11.30
#function:将AC与GLPI数据库的MAC同步
#exception.mac单独存放于GLPI数据库，为以后GLPI二次开发做准备,mac,user,model,approval
#date: 2015.12.01邮件显示MAC属性信息，简化代码
#date: 2015.12.02修改部分NULL问题，练习left join on 多表联合查询,增加判断是否有重复的MAC
#date: 2015.12.03调试脚本在crontab运行发邮件的测试
#date: 2015.12.04增加对mysql查询和AC的连接做retry动作。
#date: 2015.12.23
#提醒 如果添加到计划任务，注意 mac.add.del.exp脚本文件 添加删除文件路径
# 如果在手动执行脚本时，请确保计划任务没有正在执行本脚本，要不然有冲突！
#我已经遇到在调试过程中，因为计划任务刚巧在执行这个脚本，我也在手动执行这个脚本，导致AC 的MAC会被全部删除！
#date 2015.12.04 15:38 增加脚本是否已经被执行的判断语句，如果脚本已经被系统执行，那么本次就不执行！
# 使用方法
# 安装expect mysql-client mail
# yum install expect mysql
# mail使用CentOS自带的mailx，测试：echo "`date`" | mail -v -s "测试" user@163.com
# [root@pc0003 sync.for.mail]# pwd
# /sync.for.mail
# [root@pc0003 sync.for.mail]# ll
# -rwxr-xr-x. 1 root root 1.3K 12月 3 16:22 mac.add.del.exp
# -rwxr-xr-x. 1 root root 9.7K 12月 3 17:38 sync.for.mail.sh
# [root@pc0003 sync.for.mail]# vim /etc/crontab
# */5 * * * * root /bin/bash /sync.for.mail/sync.for.mail.sh > /dev/null 2>&1
############### 函数 #####################################
check_thread(){
#判断脚本是否已经正在被执行
ps aux > thread
grep "bin/bash ./sync.for.mail" thread > /dev/null
let num=`grep "bin/bash ./sync.for.mail" thread | wc -l`
if [ $num -gt 1 ];then
grep "bin/bash ./sync.for.mail" thread
echo "`date` 脚本已经被系统执行！，请3分钟后重试！" >>$log_file
rm -rf thread
exit 1
fi
rm -rf thread
return 0
}
check_log_file(){
# 判断日志文件记录数目，是否对日志文件清空
touch $log_file
let SyncLog=`wc -l $log_file |awk '{print $1}'`
if [ $SyncLog -gt 99999 ];then
echo "`date` 清空日志" > $log_file
fi
return 0
}
check_network(){
# 网络检测,检测网关
ping -c 4 $my_Gateway
if [ `echo $?` -ne 0 ];then
echo "`date` 网关异常,脚本退出运行" >>$log_file
exit 2
fi
# 网络检测,检测DNS
ping -c 4 $my_DNS_server
if [ `echo $?` -ne 0 ];then
echo "`date` DNS异常，脚本退出运行" >>$log_file
exit 2
fi
# 网络检测,ping 百度
ping -c 4 $my_target
if [ `echo $?` -ne 0 ];then
echo "`date` 访问公网异常，脚本退出运行" >>$log_file
exit 2
fi
return 0
}
get_glpi_and_exception_mac_num(){
#获取GlpiAndExceptionMac WlanMac 列表，做正反对比
let retry_count=1 # retry 计数器
while [ 1 ]
do
echo "从MySQL获取glpi Wireless Mac 和Exception Mac 列表"
mysql -h$mysql_host -p$mysql_passwd -u$mysql_user -Ne "use glpi; select glpi_devicenetworkcards.designation, upper(glpi_items_devicenetworkcards.mac) from glpi_items_devicenetworkcards,glpi_devicenetworkcards where glpi_items_devicenetworkcards.devicenetworkcards_id = glpi_devicenetworkcards.id and glpi_devicenetworkcards.designation=\"Wireless\";select mac from exception;" | egrep -o "([0-9a-fA-F]{2})(([/\s:][0-9a-fA-F]{2}){5})" | sed 's/://g'|sed -r 's/^(.{4})(.{4})(.{4})$/\1-\2-\3/g' > MacList
let GlpiMacNum=`cat MacList|wc -l`
# 判断与mysql网络连接
if [ $GlpiMacNum -lt 50 ]
then
echo -e "`date` 正在重试与MySQL联络" >> $log_file
sleep 10
else
#把重复的mac 合并
echo "GLPI+exception有`sort MacList |uniq -c | awk '{print $2}'|wc -l`个无线MAC"
cat MacList >>GlpiAndExceptionMac
break
fi
# retry 5 次
let retry_count+=1
if [ $retry_count -gt 5 ]
then
echo "`date` 与MySQL联络异常" | /bin/mail -v -s "警告：MySQL连接异常" $send_for_mail |grep "这里是公司的logo.cn.*Sent"
exit 2
fi
done
return 0
}
get_wlan_mac(){
let retry_count=1 # retry 计数器
while [ 1 ]
do
echo "获取当前wlan.mac 列表"
expect /sync.for.mail/mac.add.del.exp search |grep "sta-mac" | egrep -o "([0-9a-fA-F]{4})(([/\s-][0-9a-fA-F]{4}){2})"| tr '[a-z]' '[A-Z]' > WlanMac
let ACMacNum=`cat WlanMac | wc -l`
if [ $ACMacNum -gt 50 ] ;then
echo "wlan 有`echo $ACMacNum`个无线MAC"
break
else
echo -e "`date` 正在重试与AC 连接" >> $log_file
sleep 5
fi
# retry 5 次
let retry_count+=1
if [ $retry_count -gt 5 ]
then
echo "`date` 与AC联络异常" | /bin/mail -v -s "警告：AC连接异常" $send_for_mail |grep "这里是公司的logo.cn.*Sent"
exit 2
fi
done
return 0
}
get_exception_mac(){
let retry_count=1 # retry 计数器
while [ 1 ]
do
echo "获取exception 一览表 "
mysql -h$mysql_host -p$mysql_passwd -u$mysql_user -Ne "use glpi;select * from exception order by user desc;" > exception.info
let GlpiMacNum=`cat exception.info |wc -l`
# 判断与mysql网络连接
if [ $GlpiMacNum -lt 50 ]
then
echo -e "`date` 正在重试与MySQL联络" >> $log_file
sleep 5
else
break
fi
let retry_count+=1
if [ $retry_count -gt 5 ]
then
echo "`date` 获取Mysql glpi 库exception 一览表异常" | /bin/mail -v -s "警告：获取exception 一览表异常" $send_for_mail |grep "这里是公司的logo.cn.*Sent"
exit 2
fi
done
return 0
}
get_wireless_view(){
let retry_count=1 # retry 计数器
while [ 1 ]
do
echo "获取GLPI MAC地址属性一览表"
# 8表联合查询的sql语句，输出 Mac Type User/Group Computer/Phone/Device AssetTag Status
# select upper(glpi_items_devicenetworkcards.mac),
# glpi_devicenetworkcards.designation,
# concat_ws('',glpi_users.realname,glpi_users.firstname,glpi_groups.name),
# concat_ws('',glpi_computers.name,glpi_peripherals.name,glpi_phones.name),
# concat_ws('',glpi_computers.otherserial,glpi_peripherals.otherserial,glpi_phones.otherserial),
# glpi_states.name
# from glpi_items_devicenetworkcards
# left join glpi_computers on glpi_computers.id=glpi_items_devicenetworkcards.items_id
# left join glpi_peripherals on glpi_items_devicenetworkcards.items_id=glpi_peripherals.id
# left join glpi_phones on glpi_items_devicenetworkcards.items_id=glpi_phones.id
# left join glpi_states on (glpi_computers.states_id=glpi_states.id) or (glpi_peripherals.states_id=glpi_states.id) or (glpi_phones.states_id=glpi_states.id)
# left join glpi_devicenetworkcards on glpi_items_devicenetworkcards.devicenetworkcards_id = glpi_devicenetworkcards.id
# left join glpi_users on (glpi_computers.users_id=glpi_users.id) or (glpi_peripherals.users_id=glpi_users.id) or (glpi_phones.users_id=glpi_users.id)
# left join glpi_groups on (glpi_computers.groups_id=glpi_groups.id) or (glpi_peripherals.groups_id=glpi_groups.id) or (glpi_phones.groups_id=glpi_groups.id)
# where glpi_devicenetworkcards.designation="Wireless"
# order by glpi_users.name;
mysql -h$mysql_host -p$mysql_passwd -u$mysql_user -Ne "use glpi; select upper(glpi_items_devicenetworkcards.mac), glpi_devicenetworkcards.designation, concat_ws('',glpi_users.realname,glpi_users.firstname,glpi_groups.name), concat_ws('',glpi_computers.name,glpi_peripherals.name,glpi_phones.name), concat_ws('',glpi_computers.otherserial,glpi_peripherals.otherserial,glpi_phones.otherserial), glpi_states.name from glpi_items_devicenetworkcards left join glpi_computers on glpi_computers.id=glpi_items_devicenetworkcards.items_id left join glpi_peripherals on glpi_items_devicenetworkcards.items_id=glpi_peripherals.id left join glpi_phones on glpi_items_devicenetworkcards.items_id=glpi_phones.id left join glpi_states on (glpi_computers.states_id=glpi_states.id) or (glpi_peripherals.states_id=glpi_states.id) or (glpi_phones.states_id=glpi_states.id) left join glpi_devicenetworkcards on glpi_items_devicenetworkcards.devicenetworkcards_id = glpi_devicenetworkcards.id left join glpi_users on (glpi_computers.users_id=glpi_users.id) or (glpi_peripherals.users_id=glpi_users.id) or (glpi_phones.users_id=glpi_users.id) left join glpi_groups on (glpi_computers.groups_id=glpi_groups.id) or (glpi_peripherals.groups_id=glpi_groups.id) or (glpi_phones.groups_id=glpi_groups.id) where glpi_devicenetworkcards.designation=\"Wireless\" order by glpi_states.name;" > WirelessView
let GlpiMacNum=`cat WirelessView |wc -l`
# 判断与mysql网络连接
if [ $GlpiMacNum -lt 50 ]
then
echo -e "`date` 正在重试与MySQL联络" >> $log_file
sleep 5
else
break
fi
let retry_count+=1
if [ $retry_count -gt 5 ]
then
echo "`date` 获取GLPI MAC地址属性一览表异常" | /bin/mail -v -s "警告：获取GLPI MAC地址属性一览表异常" $send_for_mail |grep "这里是公司的logo.cn.*Sent"
exit 2
fi
done
return 0
}
check_for_add(){
##################################################################
#开始比较，把比AC多的MAC 筛选出来,待添加到AC;如果没有需要添加的mac,邮件中就不会显示这一项
#编程思想：在for循环申明一个stat局部变量初始值为0，如果在每次内循环结束，stat值没有发生变化，说明本次外循环的值没有在内循环找到,这就是要被添加的MAC
for i in `cat GlpiAndExceptionMac`; do
stat=0
for j in `cat WlanMac`; do
if [ $i == $j ] ;then
stat=1
fi
done
if [ $stat -eq 0 ] ;then
#待添加的MAC地址保存到文件 MoreThanAC
echo $i >> MoreThanAC
#转换格式，并把mac地址保存，待发邮件
grep `echo $i | sed 's/\-//g'|sed -r 's/^(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})$/\1:\2:\3:\4:\5:\6/g'` WirelessView >> MoreThanAC.info
grep `echo $i | sed 's/\-//g'|sed -r 's/^(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})$/\1:\2:\3:\4:\5:\6/g'` exception.info >> MoreThanAC.info
flag1=0
fi
done
if [ $flag1 -eq 0 ] ;then
echo "开始向AC添加以下MAC";cat MoreThanAC
#执行添加mac地址的脚本
./mac.add.del.exp add
sed -i "1i往WLAN中新增MAC如下,有 `wc -l MoreThanAC |awk '{print $1}'`个" MoreThanAC.info
#输出添加的mac地址信息到 邮件内容
cat MoreThanAC.info >>mail.info
echo -e "`date` \n`cat MoreThanAC.info`\n" >> $log_file
# 为邮件内容段落换行
echo "">>mail.info
flag3=0
fi
return 0
}
check_for_del(){
#开始比较，把比GLPI多的MAC筛选出来,待从AC中删除;如果没有需要删除的mac,邮件中就不会显示这一项
for i in `cat WlanMac`; do
stat=0
for j in `cat GlpiAndExceptionMac`; do
if [ $i == $j ] ;then
stat=1
fi
done
if [ $stat -eq 0 ] ;then
echo $i >> MoreThanGlpi
#注意：待从AC中删除的MAC，也就是GLPI 中没有的MAC
echo $i | sed 's/\-//g'|sed -r 's/^(.{2})(.{2})(.{2})(.{2})(.{2})(.{2})$/\1:\2:\3:\4:\5:\6/g' >> MoreThanGlpi.info
flag2=0
fi
done
if [ $flag2 -eq 0 ] ;then
echo -e "\n开始从AC删除以下MAC"; cat MoreThanGlpi
./mac.add.del.exp del
sed -i "1i 从WLAN中删除MAC如下,有 `wc -l MoreThanGlpi |awk '{print $1}'`个" MoreThanGlpi.info
cat MoreThanGlpi.info >>mail.info
echo -e "`date` \n`cat MoreThanGlpi.info`\n" >> $log_file
echo "">> mail.info
flag3=0
fi
return 0
}
check_repeat_mac(){
#判断重复的mac，如果存在重复的mac，就发邮件;如果没有重复的mac,邮件中就不会显示这一项
for j in `sort WirelessView |awk '{print $1}'|uniq -c|awk '{if ($1>1) print $2}'` ; do
grep $j WirelessView >> repeat
if [ $? -eq 0 ] ;then
flag4=0
fi
done
if [ $flag4 -eq 0 ] ;then
flag3=0
sed -i "1iGLPI 数据库重复的Wireless MAC信息如下" repeat
cat repeat >> mail.info
echo "">>mail.info #段与段之间隔开
fi
return 0
}
result_for_mail(){
#如果有任何wireless mac 变动，发邮件出来
if [ $flag3 -eq 0 ] ;then
echo -e "GLPI Wireless Mac 一览表\n Mac Type User/Group Computer/Phone/Device AssetTag Status\n">>mail.info
cat -n WirelessView >> mail.info
echo -e "\n\nWLAN Exception Mac 一览表\n Mac User Model Approver\n ">>mail.info
cat -n exception.info >> mail.info
while [ 1 ]
do
echo "正在发送邮件"
cat mail.info | /bin/mail -v -s "Wireless Mac变更通知" $send_for_mail |grep "这里是公司的logo.cn.*Sent"
if [ $? -eq 0 ];then
echo "邮件发送成功"
break
else
echo "重新 发送邮件"
sleep 60
fi
done
else
echo -e "`date` 没有任何变动，就不发邮件了。" >> $log_file
fi
return 0
}
clear_tmp_files(){
rm -rf MoreThanAC
rm -rf MoreThanAC.info
rm -rf MoreThanGlpi
rm -rf MoreThanGlpi.info
rm -rf WirelessView
rm -rf exception.info
rm -rf mail.info
rm -rf GlpiAndExceptionMac
rm -rf WlanMac
rm -rf MacList
rm -rf repeat
}
##########################################################
##########################################################
#主程序
cd /sync.for.mail
export PATH=$PATH:/usr/local/mysql/bin:/usr/lib64/qt-3.3/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/usr/java/jdk1.7.0_79//bin:/usr/java/jdk1.7.0_79/jre/bin:/root/bin
clear
flag1=1 # flag1全局变量用来判断是否需要向AC添加mac信息
flag2=1 # flag2全局变量用来判断是否需要从AC删除mac信息
flag3=1 # flag3全局变量用来判断是否有变更动作，并发送邮件
flag4=1 # flag4全局变量用来判断是否有重复的mac
mysql_user="************"
mysql_passwd="************"
mysql_host="asset.这里是公司的logo.cn"
my_Gateway=192.168.***.***
my_DNS_server=192.168.***.***
my_target="www.baidu.com"
log_file=journal
send_for_mail=".***.***@这里是公司的logo.cn"
#send_for_mail=".***.***@这里是公司的logo.cn"
clear_tmp_files
check_thread
check_log_file
check_network
get_glpi_and_exception_mac_num
get_wlan_mac
get_exception_mac
get_wireless_view
check_for_add
check_for_del
check_repeat_mac
result_for_mail
clear_tmp_files
[root@hghast001 sync.for.mail]#
转载于:https://blog.51cto.com/990487026/1719628
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
