#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]#本文出自 “折腾岁月。” 博客，谢绝转载！
AC 与 GLPI的MAC地址自动同步脚本 expect mail 【原创】
原文：http://990487026.blog.51cto.com/10133282/1719628