目标
- 公司有1000左右的摄像头需要每天检查在线情况,以及在线率。
- 把在线情况以邮件的方式发送给“项目经理等干系人”
- 项目经理通过邮件情况再行处理(这个是管理范畴)
解决方法
因为条件简陋,为了省钱的原则,只能用python+ping的方法监测在线及在线率情况。需要的条件如下:
1.申请一个免费邮箱(QQ,126,163等),或者公司邮箱。
2. python + mysql数据库
效果
架构及源码
1. 表结构
共3个表
- cfg_camera 摄像头信息表(注册)
- log_mon_vedio(监控日志表)
- log_mon_vedio_tj(监控日志统计表)
CREATE TABLE `cfg_camera` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`ip` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'IP地址',
`port` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '端口号',
`devname` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '设备名称',
`devgrp` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '所属分组',
`jkgrp` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '监控用到的分组名称',
`yylb` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '应用类别',
`sbyhm` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '设备用户名',
`sbmm` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '设备密码',
`djxy` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '对接协议',
`sbgbbh` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '设备国标编号',
`sspau` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '所属PAU',
`tdh` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '通道号',
`tdmc` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '通道名称',
`tdgbbh` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '通道国标编号',
`tdjpbh` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '通道键盘编号',
`sfkk` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '是否可控',
`gbcsfs` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '国标流传输方式',
`tdqxd` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '通道清晰度',
`credate` datetime DEFAULT NULL COMMENT '创建日期',
`isvalid` decimal(1,0) DEFAULT NULL COMMENT '有效',
`remark` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=727 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='监控-摄像机';
--
CREATE TABLE `log_mon_vedio` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`rq` datetime DEFAULT NULL COMMENT '日期',
`jkpc` varchar(50) DEFAULT NULL COMMENT '监控批次',
`ip` varchar(50) DEFAULT NULL COMMENT 'IP地址',
`port` varchar(10) DEFAULT NULL COMMENT '端口号',
`devname` varchar(150) DEFAULT NULL COMMENT '设备名称',
`devgrp` varchar(100) DEFAULT NULL COMMENT '所属分组',
`jkgrp` varchar(50) DEFAULT NULL COMMENT '监控分组名称',
`jkjg` varchar(50) DEFAULT NULL COMMENT '监控结果',
`sendrq` datetime DEFAULT NULL COMMENT '邮件发送时间或采集时间',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5052868 DEFAULT CHARSET=utf8 COMMENT='监控-摄像机-监控日志';
--
CREATE TABLE `log_mon_vedio_tj` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`rq` datetime DEFAULT NULL COMMENT '日期',
`jkpc` varchar(50) DEFAULT NULL COMMENT '监控批次',
`lx` varchar(50) DEFAULT NULL COMMENT '监控类型',
`zxl` decimal(6,2) DEFAULT NULL COMMENT '在线率',
`sendrq` datetime DEFAULT NULL COMMENT '邮件发送时间或采集时间',
`hbdd` int(11) DEFAULT NULL COMMENT '环保单独使用标志',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5155 DEFAULT CHARSET=utf8 COMMENT='监控-摄像机-监控日志-统计信息-用于abi';
2. 源码
源码: 就4个重要的
1 . Run.bat --定时执行
2. sendmail.py --发送邮件
3. test_ping.py – scoket ping方法
4. vedioCheck.py --主程序
test_ping.py
# test_ping.py
#!/usr/bin/python3.7
# !coding:utf-8
__author__ = '6509051@qq.com'
__date__ = 'Thu Feb 27 EST 2021'
import time
import struct
import socket
import select
import sys
def chesksum(data):
"""
校验
"""
n = len(data)
m = n % 2
sum = 0
for i in range(0, n - m, 2):
sum += (data[i]) + ((data[i + 1]) << 8) # 传入data以每两个字节(十六进制)通过ord转十进制,第一字节在低位,第二个字节在高位
if m:
sum += (data[-1])
# 将高于16位与低16位相加
sum = (sum >> 16) + (sum & 0xffff)
sum += (sum >> 16) # 如果还有高于16位,将继续与低16位相加
answer = ~sum & 0xffff
# 主机字节序转网络字节序列(参考小端序转大端序)
answer = answer >> 8 | (answer << 8 & 0xff00)
return answer
'''
连接套接字,并将数据发送到套接字
'''
def raw_socket(dst_addr, imcp_packet):
rawsocket = socket.socket(socket.AF_INET, socket.SOCK_RAW, socket.getprotobyname("icmp"))
send_request_ping_time = time.time()
# send data to the socket
rawsocket.sendto(imcp_packet, (dst_addr, 80))
return send_request_ping_time, rawsocket, dst_addr
'''
request ping
'''
def request_ping(data_type, data_code, data_checksum, data_ID, data_Sequence, payload_body):
# 把字节打包成二进制数据
imcp_packet = struct.pack('>BBHHH32s', data_type, data_code, data_checksum, data_ID, data_Sequence, payload_body)
icmp_chesksum = chesksum(imcp_packet) # 获取校验和
imcp_packet = struct.pack('>BBHHH32s', data_type, data_code, icmp_chesksum, data_ID, data_Sequence, payload_body)
return imcp_packet
'''
reply ping
'''
def reply_ping(send_request_ping_time, rawsocket, data_Sequence, timeout=2):
while True:
started_select = time.time()
what_ready = select.select([rawsocket], [], [], timeout)
wait_for_time = (time.time() - started_select)
if what_ready[0] == []: # Timeout
return -1
time_received = time.time()
received_packet, addr = rawsocket.recvfrom(1024)
icmpHeader = received_packet[20:28]
type, code, checksum, packet_id, sequence = struct.unpack(
">BBHHH", icmpHeader
)
if type == 0 and sequence == data_Sequence:
return time_received - send_request_ping_time
timeout = timeout - wait_for_time
if timeout <= 0:
return -1
'''
实现 ping 主机/ip
'''
def ping(host):
msg =""
data_type = 8 # ICMP Echo Request
data_code = 0 # must be zero
data_checksum = 0 # "...with value 0 substituted for this field..."
data_ID = 0 # Identifier
data_Sequence = 1 # Sequence number
payload_body = b'abcdefghijklmnopqrstuvwabcdefghi' # data
dst_addr = socket.gethostbyname(host) # 将主机名转ipv4地址格式,返回以ipv4地址格式的字符串,如果主机名称是ipv4地址,则它将保持不变
print("正在 Ping {0} [{1}] 具有 32 字节的数据:".format(host, dst_addr))
js = 0
for i in range(0, 4):
icmp_packet = request_ping(data_type, data_code, data_checksum, data_ID, data_Sequence + i, payload_body)
send_request_ping_time, rawsocket, addr = raw_socket(dst_addr, icmp_packet)
times = reply_ping(send_request_ping_time, rawsocket, data_Sequence + i)
if times > 0:
msg = "来自 {0} 的回复: 字节=32 时间={1}ms".format(addr, int(times * 1000))
print(msg)
js = js + 1
# time.sleep(0.7)
# break
else:
msg = "请求超时"
# js = 0
# break
print(js)
if js>0 :
msg = "online"
else:
msg = "offline"
# print("msg:" + msg)
return msg
if __name__ == "__main__":
# if len(sys.argv) < 2:
# sys.exit('Usage: ping.py <host>')
ip = '10.33.21.2' # ping 不通
# ip = '10.16.67.89' # 通:10.16.67.
ping(ip) # sys.argv[1]
sendmail.py
from email import encoders
from email.mime.text import MIMEText
from email.utils import formatdate
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.header import Header
import smtplib
import time
import os
def send_email(msg_content):
'''发送测试报告'''
try:
From = "mtest111@126.com"
# To = "aaa@111.cn,"
# cc_mail = ''
To = "主送邮箱"
cc_mail = '抄送邮箱,多个用,隔开'
# source = '..//Report//' + file_name
server = smtplib.SMTP("smtp.126.com")
server.login("monitormi@126.com","FFHAXLVCJIDMIELZ") #仅smtp服务器需要验证时
send_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
subject = '运维监控情况-摄像头-' + send_time
# 构造MIMEMultipart对象做为根容器
main_msg = MIMEMultipart()
# print(msg_content)
# 构造MIMEText对象做为邮件显示内容并附加到根容器
# text_msg = MIMEText("xx自动化测试报告!")
text_msg = MIMEText(msg_content, 'plain', 'utf-8')
main_msg.attach(text_msg)
# 构造MIMEBase对象做为文件附件内容并附加到根容器
contype = 'application/octet-stream'
maintype, subtype = contype.split('/', 1)
txtFile_hb = mail_fj('.\\环保-离线设备信息.txt', '环保-离线设备信息.txt')
txtFile_cg = mail_fj('.\\城管-离线设备信息.txt', '城管-离线设备信息.txt')
txtFile_zz = mail_fj('.\\综治-离线设备信息.txt', '综治-离线设备信息.txt')
txtFile_zj = mail_fj('.\\宗教-离线设备信息.txt', '宗教-离线设备信息.txt')
# 设置根容器属性
main_msg['From'] = From
main_msg['To'] = To
main_msg['Cc'] = cc_mail
main_msg['Subject'] = Header(subject,"utf-8")
main_msg['Date'] = formatdate( )
main_msg.attach(txtFile_hb)
main_msg.attach(txtFile_cg)
main_msg.attach(txtFile_zz)
main_msg.attach(txtFile_zj)
# 得到格式化后的完整文本
fullText = main_msg.as_string( )
# 用smtp发送邮件
try:
server.sendmail(From, To.split(',') + cc_mail.split(','), fullText)
print('sucess!')
finally:
server.quit()
except Exception as e:
# logger.exception('发送邮件出错!')
print('failure!')
print(e)
def mail_fj(filename, fileTitle ):
att = MIMEText(open(filename, "rb").read(), "base64", "utf-8")
att["Content-Type"] = "application/octet-stream"
att.add_header("Content-Disposition", "attachment", filename=("utf-8", "", fileTitle))
return att
if __name__ == "__main__":
send_email('测试')
vedioCheck.py
import os
import datetime
import pymysql
import subprocess
import sendmail
import test_ping as MyPingTools
sendMailRq = datetime.datetime.now()
def get_conn():
"""
获取数据连接
:return:
"""
db = pymysql.Connect(
host='localhost',
port=3306,
user='root',
passwd='root',
db='webmon',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor # 指定类型
)
return db
def ping():
db = get_conn()
cursor = db.cursor()
# sql_str = "select ip, devname, devgrp, jkgrp from cfg_camera a where jkgrp='综治' order by devname"
sql_str = "select ip, devname, devgrp, jkgrp from cfg_camera a where a.isvalid=1 order by devname"
cursor.execute(sql_str)
info = cursor.fetchall()
i = 0
msg_str = ""
jkpc = datetime.datetime.strftime(datetime.datetime.now(),'%Y%m%d%H%M') # 监控批次
for item in info:
rq = datetime.datetime.now()
ip = item['ip']
devname = item['devname']
devgrp = item['devgrp']
jkgrp = item['jkgrp']
jkjg = ping_single(ip)
print('{0} -- {1}'.format(ip, jkjg))
write_db(cursor, rq, jkpc, ip, devname, devgrp, jkgrp, jkjg)
# sql_str = "INSERT INTO log_mon_vedio(rq, jkpc, ip, devname, devgrp, jkgrp, jkjg) \
# VALUES ('{0}', '{1}','{2}','{3}','{4}','{5}','{6}') \
# ".format(rq, jkpc, ip, devname, devgrp, jkgrp, jkjg)
# print(sql_str)
return jkpc # 返回 监控批次
def ping_single(ip):
rtu_msg = ''
msg = MyPingTools.ping(ip)
print(msg)
return msg
def write_db(cursor, rq, jkpc, ip, devname, devgrp, jkgrp, jkjg):
'''
写数据库
'''
# db = get_conn()
# cursor = db.cursor()
# rq = datetime.datetime.now()
# jkpc = datetime.datetime.strftime(time1,'%Y%m%d%H%M')
sql_str = "INSERT INTO log_mon_vedio(rq, jkpc, ip, devname, devgrp, jkgrp, jkjg) \
VALUES ('{0}', '{1}','{2}','{3}','{4}','{5}', '{6}') \
".format(rq, jkpc, ip, devname, devgrp, jkgrp, jkjg)
print(sql_str)
cursor.execute(sql_str)
# def data_tj(cursor, jkpc):
def data_tj(jkpc):
# jkpc = '202009161642'
rq = str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M"))
# rq = '2020.09.20'
msg_hb = data_tj_single(jkpc, '环保')
msg_cg = data_tj_single(jkpc, '城管')
msg_zz = data_tj_single(jkpc, '综治')
msg_zj = data_tj_single(jkpc, '宗教')
offline_msg_hb = data_offline_to_file(jkpc, '环保')
offline_msg_cg = data_offline_to_file(jkpc, '城管')
offline_msg_zz = data_offline_to_file(jkpc, '综治')
offline_msg_zj = data_offline_to_file(jkpc, '宗教')
msg_str = '各位好: \n\n 摄像头监控情况({0}):\n 1. {1};\n 2. {2};\n 3. {3};\n 4. {4}; \n\n 以上信息请知悉或处理. 离线设备信息-详见附件。\n\n'.format(rq, msg_hb, msg_cg, msg_zz, msg_zj)
# msg_str = msg_str + offline_msg_hb + offline_msg_cg + offline_msg_zz
print(msg_str)
return msg_str
def data_tj_single(jkpc, grpname):
'''
统计数据
'''
db = get_conn()
cursor = db.cursor()
# grpname = '综治'
# jkpc = '202009161642'
all_num = 0
online_num = 0
# 查询所有数据
sql_str_all_num = "select count(*) as icount from log_mon_vedio where jkpc='{0}' and jkgrp='{1}'".format(jkpc, grpname)
cursor.execute(sql_str_all_num)
all_data = cursor.fetchone()
all_num = all_data['icount']
# print(all_num)
# 查询所有online数据
sql_str_online_num = "select count(*) as icount from log_mon_vedio where jkpc='{0}' and jkgrp='{1}' and jkjg='online' ".format(jkpc, grpname)
cursor.execute(sql_str_online_num)
online_data = cursor.fetchone()
online_num = online_data['icount']
# print(online_num)
# 在线率
zxl = round(online_num*100/all_num,2)
# 将在线率和类别存储数据库
data_tj_2db(jkpc, grpname, zxl)
# print(zxl)
msg = '{0}-在线率:{1}% ({2}/{3})'.format(grpname, str(zxl), online_num, all_num)
# print(msg)
return msg
def data_tj_2db(jkpc, jkgrp, zxl):
"""
将在线率和类别存储数据库
"""
db = get_conn()
cursor = db.cursor()
# sql_str_tj= "INSERT INTO log_mon_vedio_tj(jkpc, lx, zxl) VALUES ('{0}', '{1}', {2}); ".format(jkpc, jkgrp, zxl)
sql_str_tj= "INSERT INTO log_mon_vedio_tj(rq, jkpc, lx, zxl, sendrq) VALUES ('{0}', '{1}', '{2}', {3}, '{4}'); ".format(sendMailRq, jkpc, jkgrp, zxl, sendMailRq)
cursor.execute(sql_str_tj)
def data_offline_to_file(jkpc, grpname):
'''
离线设备 转 txt文件
'''
# grpname = '综治'
# jkpc = '202009161642'
offline_detail_content = data_offline_to_html(jkpc, grpname)
f = "{0}-离线设备信息.txt".format(grpname)
a =8
with open(f,"w") as file: #”w"代表着每次运行都覆盖内容
file.write(offline_detail_content)
def data_offline_to_html(jkpc, grpname):
'''
离线设备 转 html
'''
db = get_conn()
cursor = db.cursor()
# grpname = '综治'
# jkpc = '202009161642'
# 查询所有online数据
sql_str_offline= "select ip, devname, devgrp from log_mon_vedio where jkpc='{0}' and jkgrp='{1}' and jkjg='offline' ".format(jkpc, grpname)
cursor.execute(sql_str_offline)
info = cursor.fetchall()
i = 0
offline_dev_str = []
str_msg = ''
for item in info:
rq = datetime.datetime.now()
ip = item['ip']
devname = item['devname']
devgrp = item['devgrp']
i = i + 1
str_msg = str_msg + '{0} . {1} -- {2} -- {3}\n'.format(i, devname, ip, devgrp)
str_text = '\n== {0} == 离线设备信息[{1}]:(序号--设备名--IP--设备分组)\n\n'.format(grpname, str(i))
print(str_text + str_msg)
return str_text + str_msg
def main():
# jkpc = '202010200839'
# grpname = '环保'
jkpc = ping() # ping并返回监控批号
msg_str = data_tj(jkpc) # 获取汇总信息
# data_offline_to_html(jkpc, grpname)
sendmail.send_email(msg_str)
if __name__ == "__main__":
# ip = '10.33.105.164'
# ip = '10.33.21.2' # 不通
# ip = '10.16.67.89' # 通:10.16.67.
# ip = '10.16.67.185'
# get_PING(ip)
# times = 1000
# ping_single(ip)
# get_PING(ip)
# ping()
# jkpc = '202009161642'
# grpname = '环保'
# grpname = '城管'
# grpname = '综治'
# data_tj(grpname)
main() # 主程序
# jkpc = '202010200839'
# grpname = '环保'
# grpname = '城管'
# grpname = '综治'
# data_tj_single(jkpc, grpname)
Run.bat
@echo off
D:/Python/Python37/python.exe D:/pythonWs/webmon/vedioCheck.py
exit
其余就是做个windows的定时任务ok!