第三方库下载
https://pypi.org/
connectInfo
Oracle,"192.168.1.1","1521","phy","username","password"
Mysql,"192.168.1.1","3306","mysql","username","password"
Linux,"192.168.1.1","22","username","1"
SQLServer,"192.168.1.1","master","1433","username","password"
Windows,"192.168.1.1","username","password"
URL,"192.168.1.1","http://192.168.1.1","URL名称"
daycheck.py
import math
import os
import paramiko
import time
import wmi
import cx_Oracle
import mysql.connector
import openpyxl.styles
import pymssql
import requests
from mysql import connector
from mysql.connector import errorcode
import openpyxl
from openpyxl.styles import *
# 安装cx_oracle前，下载oracle client 复制oci，oraocci11，oraociei11的3个DLL粘贴到你的Python目录的Lib/site-packages文件夹下面
# 并安装 Microsoft Visual C++ 14.0或以上版本
# https://visualstudio.microsoft.com/visual-cpp-build-tools/
# Windows需开通的端口 135，49152-65535
# E:\python\daycheck\daycheck\.venv\Scripts\python.exe -m pip install --upgrade pip
# E:\python\daycheck\daycheck\.venv\Scripts\pip install cx_Oracle
# E:\python\daycheck\daycheck\.venv\Scripts\pip install openpyxl
# E:\python\daycheck\daycheck\.venv\Scripts\pip install paramiko
# E:\python\daycheck\daycheck\.venv\Scripts\pip install mysql-connector-python
# E:\python\daycheck\daycheck\.venv\Scripts\pip install pymssql
# E:\python\daycheck\daycheck\.venv\Scripts\pip install wmi
# E:\python\daycheck\daycheck\.venv\Scripts\pip install requests
# 离线安装whl文件 D:\daycheck\pythonProject\.venv\Scripts\python.exe -m pip install D:\software\python\pip-24.2-py3-none-any.whl
# 离线安装tar.gz文件
# 1，cd /d D:\software\python\WMI-1.5.1\dist\WMI-1.5.1
# 2，E:\python\daycheck\daycheck\.venv\Scripts\pip install .
class OracleDb:
def __init__(self, ip, port, sid, u, p):
self.c_user = u
self.__c_pass = p
self.ip = ip
self.port = port
self.sid = sid
self.stat_dict = {"IP": ip, "类型": "Oracle", "SID": sid, "数据库可用性": "/", "表空间使用率": "/", "用户状态": "/",
"表空间状态": "/", "数据库备份": "/", "连接占用率": "/", "DG同步状态": "/"}
# 获取检查结果
def dbck_statv(self):
c_url = self.ip + ':' + self.port + '/' + self.sid
try:
tbs_stat_sql = """SELECT d.tablespace_name name,
d.contents type,
d.status status,
NVL(f.bytes / a.bytes * 100, 0) pct_free
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name name,
d.contents type,
d.status status,
NVL((a.bytes - NVL(t.bytes, 0)) / a.bytes * 100, 0) pct_free
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
"""
usr_stat_sql = """select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where username not in
('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DIP','EXFSYS',
'FLOWS_FILES','MDDATA','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS',
'OUTLN','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR','WMSYS','XDB','XS$NULL','DBSNMP','MONITOR','SYSMAN','BASELINE',
'MGMT_VIEW','AUDSYS','DBSFWUSER','DVF','DVSYS','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER',
'GSMUSER','LBACSYS','OJVMSYS','REMOTE_SCHEDULER_AGENT','SYS$UMF','SYSBACKUP','SYSDG',
'SYSKM','SYSRAC','HR','IX','OE','PM','SH','PRIVUSER','APEX_040200')
"""
rmbk_stat_sql = """SELECT session_key,
TO_CHAR (START_TIME, 'yyyy-mm-dd hh24:mi:ss') start_time,
TO_CHAR (end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
TIME_TAKEN_DISPLAY,
INPUT_BYTES_DISPLAY "INPUT(SUM)",
OUTPUT_BYTES_DISPLAY "OUTPUT(SUM)",
OUTPUT_DEVICE_TYPE,
STATUS,
INPUT_TYPE,
INPUT_BYTES_PER_SEC_DISPLAY "INPUT(s)",
OUTPUT_BYTES_PER_SEC_DISPLAY "OUTPUT(s)"
FROM v$rman_backup_job_details
where TO_CHAR (START_TIME, 'yyyy-mm-dd') > to_char(sysdate-2,'yyyy-mm-dd')
order by session_key
"""
conn_stat_sql = """select COUNT(*) AS current_sessions from v$session
union
select TO_NUMBER(VALUE) AS max_sessions from V$PARAMETER WHERE NAME = 'sessions'
"""
dg_stat_sql = """select dest_id ,sum(seq) from (select dest_id,thread#,max(sequence#) seq from
v$archived_log where dest_id=1 group by thread#,dest_id
union
select dest_id,thread#,max(sequence#) seq from v$archived_log
where dest_id=2 and applied='YES' group by thread#,dest_id)
group by dest_id
"""
# 获取数据
conn = cx_Oracle.connect(user=self.c_user, password=self.__c_pass, dsn=c_url)
curs = conn.cursor()
curs.execute(tbs_stat_sql)
tbs_stat_v = curs.fetchall()
curs.execute(usr_stat_sql)
usr_stat_v = curs.fetchall()
curs.execute(rmbk_stat_sql)
rmbk_stat_v = curs.fetchall()
curs.execute(conn_stat_sql)
conn_stat_v = curs.fetchall()
curs.execute(dg_stat_sql)
dg_stat_v = curs.fetchall()
curs.close()
conn.close()
self.stat_dict["数据库可用性"] = "正常"
# 判断表空间状态
tbsne = [] # 表空间空闲低于10%
tbsso = [] # 表空间状态非ONLINE
for tbs_info in tbs_stat_v:
if tbs_info[3] < 10:
tmp_str = tbs_info[0] + '空闲率' + str(math.ceil(tbs_info[3])) + '%'
tbsne.append(tmp_str)
if tbs_info[2] != 'ONLINE':
tbsso.append(tbs_info[0])
if len(tbsso) == 0:
self.stat_dict["表空间状态"] = "正常"
if len(tbsne) == 0:
self.stat_dict["表空间使用率"] = "正常"
else:
tbs_stat = "表空间空闲空间不足"
for tmp in tbsne:
tbs_stat = tbs_stat + ',' + tmp
self.stat_dict["表空间使用率"] = tbs_stat
# 判断用户状态
usrs = [] # 用户状态异常
for user_info in usr_stat_v:
if user_info[1] != "OPEN":
usrs.append(user_info)
if len(usrs) == 0:
self.stat_dict["用户状态"] = "正常"
else:
usr_stat = "用户"
for tmp in usrs:
usr_stat = usr_stat + tmp[0] + ','
usr_stat = usr_stat + "状态异常"
self.stat_dict["用户状态"] = usr_stat
# 判断数据库备份状态
if len(rmbk_stat_v) == 0:
self.stat_dict["数据库备份"] = "一天内无rman备份"
else:
t = 0 # 备份总数
c = 0 # 成功次数
f = 0 # 失败次数
o = 0 # 其他
for tmp in rmbk_stat_v:
t += 1
if tmp[7] == "COMPLETED":
c += 1
if tmp[7] == "FAILED":
f += 1
else:
o += 1
rmbk_stat = "一天内备份" + str(t) + "次，成功" + str(c) + "次，失败" + str(f) + "次"
self.stat_dict["数据库备份"] = rmbk_stat
# 链接占用率
if conn_stat_v[0][0] / conn_stat_v[1][0] > 0.9:
self.stat_dict["连接占用率"] = "连接数超90%"
else:
self.stat_dict["连接占用率"] = "正常"
# DG同步状态
if len(dg_stat_v) < 2:
self.stat_dict["DG同步状态"] = "/"
else:
max_v = dg_stat_v[0][1]
for tmp in dg_stat_v:
if max_v < tmp[1]:
max_v = tmp[1]
dg_g = 0
for tmp in dg_stat_v:
if max_v - tmp[1] > 3:
dg_g += 1
if dg_g == 0:
self.stat_dict["DG同步状态"] = "正常"
else:
v_tmp = str(dg_g) + "个备库同步异常"
self.stat_dict["DG同步状态"] = v_tmp
return self.stat_dict
except cx_Oracle.DatabaseError as err:
self.stat_dict["数据库可用性"] = "连接失败！错误信息为：" + format(err)
return self.stat_dict
class LinuxOs:
def __init__(self, ip, port, u, p):
self.c_user = u
self.__c_pass = p
self.ip = ip
self.port = port
self.stat_dict = {"IP": ip, "类型": "Linux", "系统状态": "/", "系统空间": "/", "系统CPU使用率": "/",
"系统内存使用率": "/"}
# 获取检查结果
def osck_statv(self):
try:
os_conn = paramiko.SSHClient()
os_conn.set_missing_host_key_policy(paramiko.AutoAddPolicy())
os_conn.connect(self.ip, port=self.port, username=self.c_user, password=self.__c_pass)
stdin, stdout, stderr = os_conn.exec_command('export LANG=en_US.UTF-8 && df -h | grep %')
disk_info = stdout.read().decode('utf-8')
stdin, stdout, stderr = os_conn.exec_command('export LANG=en_US.UTF-8 && sar 2 5 | grep Average')
cpu_info = stdout.read().decode('utf-8')
stdin, stdout, stderr = os_conn.exec_command('export LANG=en_US.UTF-8 && free -g |grep Mem')
mem_info = stdout.read().decode('utf-8')
os_conn.close()
# 磁盘空间信息
d_u_p_n = "磁盘空间不足10%的目录有"
d_col_num = disk_info.count('\n')
pn_num = 0
for i in range(d_col_num - 1):
col_v = disk_info.split('\n')[i + 1]
use_p = col_v.split('%')[0][-3:].split()
use_p_int = int(use_p[0])
if use_p_int >= 90:
pn_num += 1
disk_name = col_v.split('%')[1].split()[0]
d_u_p_n = d_u_p_n + ',' + disk_name
if pn_num == 0:
self.stat_dict["系统空间"] = "正常"
else:
self.stat_dict["系统空间"] = d_u_p_n
# CPU信息
if len(cpu_info) > 6:
cpu_free_p = cpu_info[-6:].split()[0]
cpu_used_p = 100 - float(cpu_free_p)
cpu_used_p = math.ceil(cpu_used_p)
self.stat_dict["系统CPU使用率"] = str(cpu_used_p) + "%"
# 系统内存使用率
mem_tmp = mem_info.split()
mem_totol = int(mem_tmp[1])
mem_used = int(mem_tmp[2])
mem_cache = int(mem_tmp[5])
mem_used_p = mem_used / mem_totol * 100
if mem_used_p < 90:
mem_stat = str(math.ceil(mem_used_p)) + r'%'
else:
mem_stat = str(math.ceil(mem_used_p)) + r'% buff/cache ' + str(mem_cache)
self.stat_dict["系统内存使用率"] = mem_stat
except paramiko.SSHException as e:
print(f"Connection error: {e}")
self.stat_dict["系统状态"] = "异常"
else:
self.stat_dict["系统状态"] = "正常"
return self.stat_dict
class MysqlDb:
def __init__(self, ip, port, db, u, p):
self.c_user = u
self.__c_pass = p
self.ip = ip
self.port = port
self.db = db
self.stat_dict = {"IP": ip, "类型": "Mysql", "数据库可用性": "/", "主从复制状态": "/", "用户状态": "/",
"连接占用率": "/"}
def dbck_statv(self):
try:
conn = mysql.connector.connect(host=self.ip, port=self.port, user=self.c_user, password=self.__c_pass,
database=self.db,)
cursor = conn.cursor()
self.stat_dict["数据库可用性"] = "正常"
version_sql = r"""select @@version"""
usr_stat_sql = r"""select * from mysql.user where User not in ('mysql.session','mysql.sys')
and account_locked='Y';
"""
conn_now_sql = r"""show status like 'Threads_connected%'"""
conn_max_sql = r"""show variables like '%max_connection%'"""
slave_stat_sql = r"""show slave status"""
cursor.execute(version_sql)
version = cursor.fetchall()
m_v = version[0][0][:3]
usr_stat = []
if m_v < "5.7":
self.stat_dict["用户状态"] = "正常"
else:
cursor.execute(usr_stat_sql)
usr_stat = cursor.fetchall()
if len(usr_stat) == 0:
self.stat_dict["用户状态"] = "正常"
else:
usr_stat_v = str(len(usr_stat)) + "个用户被锁定"
self.stat_dict["用户状态"] = usr_stat_v
cursor.execute(conn_max_sql)
conn_max = cursor.fetchall()
cursor.execute(conn_now_sql)
conn_now = cursor.fetchall()
cursor.execute(slave_stat_sql)
slave_stat = cursor.fetchall()
# 连接占用率
conn_pct = math.ceil(int(conn_now[0][1]) / int(conn_max[0][1]) * 100)
conn_stat = str(conn_pct) + r'%'
self.stat_dict["连接占用率"] = conn_stat
# 主从复制状态
if len(slave_stat) == 0:
self.stat_dict["主从复制状态"] = r'/'
else:
ss_io_stat = slave_stat[0][10]
ss_sql_stat = slave_stat[0][10]
ss_second = slave_stat[0][32]
if ss_io_stat != "Yes" or ss_sql_stat != "Yes":
self.stat_dict["主从复制状态"] = r'复制故障'
else:
if ss_second < 600:
self.stat_dict["主从复制状态"] = r'正常'
else:
ss_stat = "复制延迟" + str(math.ceil(ss_second / 60)) + "分钟"
self.stat_dict["主从复制状态"] = ss_stat
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print('账号或密码错误！')
self.stat_dict["数据库可用性"] = '账号或密码错误'
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print('数据库不存在！')
self.stat_dict["数据库可用性"] = '数据库不存在'
else:
print(err)
self.stat_dict["数据库可用性"] = '数据库连接失败'
else:
# 关闭连接
conn.close()
return self.stat_dict
class WindowsOs:
def __init__(self, ip, u, p):
self.c_user = u
self.__c_pass = p
self.ip = ip
self.stat_dict = {"IP": ip, "类型": "Windows", "系统状态": "/", "系统空间": "/", "系统CPU使用率": "/",
"系统内存使用率": "/"}
def osch_statv(self):
try:
win_conn = wmi.WMI(self.ip, user=self.c_user, password=self.__c_pass)
# 内存信息
total_mem_info = win_conn.Win32_ComputerSystem()[0].TotalPhysicalMemory
free_mem_info = win_conn.Win32_OperatingSystem()[0].FreePhysicalMemory
total_mem_gb = int(total_mem_info) / 1024 / 1024 / 1024
free_mem_gb = int(free_mem_info) / 1024 / 1024
used_pct = (total_mem_gb - free_mem_gb) / total_mem_gb * 100
self.stat_dict["系统内存使用率"] = str(math.ceil(used_pct)) + r"%"
# CPU信息
cpu_data = []
for cpu in win_conn.Win32_Processor():
# print('Utilization: %s: %d %%' % (cpu.DeviceID, cpu.LoadPercentage))
tmp_dict = {"CPU": cpu.DeviceID, "CPU状态": cpu.CpuStatus, "CPU最大速度（MHz）": cpu.MaxClockSpeed,
"CPU当前速度（MHz）": cpu.CurrentClockSpeed, "CPU使用率": cpu.LoadPercentage}
cpu_data.append(tmp_dict)
cpu_p = 0
for cpu_d_t in cpu_data:
if cpu_d_t["CPU使用率"] is not None:
cpu_p = cpu_p + cpu_d_t["CPU使用率"]
cpu_num = len(cpu_data)
cpu_used_p = cpu_p / cpu_num
self.stat_dict["系统CPU使用率"] = str(math.ceil(cpu_used_p)) + r'%'
# 磁盘空间
GB = 1024 ** 3
Disk_data = []
Disk_f_list = ""
for Physical_Disk in win_conn.Win32_DiskDrive():
for Partition in Physical_Disk.associators("Win32_DiskDriveToDiskPartition"):
for Logical_Disk in Partition.associators("Win32_LogicalDiskToPartition"):
TmpDict = {"盘符": Logical_Disk.Caption, "总量": format(int(Logical_Disk.Size) / GB, '.2f'),
"使用量": format((int(Logical_Disk.Size) - int(Logical_Disk.FreeSpace)) / GB, '.2f'),
"空闲量": format(int(Logical_Disk.FreeSpace) / GB, '.2f'), "使用率": format(
int(100.0 * (int(Logical_Disk.Size) - int(Logical_Disk.FreeSpace)) / int(
Logical_Disk.Size)), '.2f') + "％"}
Disk_data.append(TmpDict)
tmp_u_p = int(100.0 * (int(Logical_Disk.Size) - int(Logical_Disk.FreeSpace)) /
int(Logical_Disk.Size))
if tmp_u_p > 90:
Disk_f_list = Disk_f_list + Logical_Disk.Caption + ','
if len(Disk_f_list) == 0:
self.stat_dict["系统空间"] = "正常"
else:
self.stat_dict["系统空间"] = Disk_f_list + "盘空间不足10%"
except wmi.x_wmi_timed_out as err:
print(f"WMI Exception: {err}")
self.stat_dict["系统状态"] = "异常"
# 捕获所有其他异常
except Exception as err:
print(f"General Exception: {err}")
self.stat_dict["系统状态"] = "异常"
# 如果没有异常发生，打印成功信息
else:
self.stat_dict["系统状态"] = "正常"
return self.stat_dict
class SQLserverDb:
def __init__(self, ip, port, db, u, p):
self.c_user = u
self.__c_pass = p
self.ip = ip
self.port = port
self.db = db
self.stat_dict = {"IP": ip, "类型": "SQL server", "数据库可用性": "/", "文件状态": "/", "数据空间": "/",
"日志空间": "/", "用户状态": "/", "连接占用率": "/"}
def dbch_statv(self):
try:
file_space_sql = """select database_id,file_id,type_desc,name,physical_name,state_desc,size,max_size,growth
from sys.master_files
"""
usr_stat_sql = """SELECT name AS LoginName, type_desc AS LoginType, is_disabled AS IsDisabled
FROM sys.server_principals WHERE type IN ('S') and name not like '%##%'
"""
conn_max_sql = """select @@max_connections;
"""
conn_current_sql = """select count(distinct(login_time)) from sys.sysprocesses
"""
bak_stat_sql = """
SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,
CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],
CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio],
DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
bs.backup_finish_date AS [Backup Finish Date]
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
WHERE DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
AND bs.backup_size > 0
AND bs.[type] = 'D' -- Change to L if you want Log backups
AND database_name = DB_NAME(DB_ID())
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);
"""
conn = pymssql.connect(server=self.ip, database=self.db, user=self.c_user, password=self.__c_pass,
port=self.port)
cursor = conn.cursor()
cursor.execute(conn_max_sql)
conn_max_v = cursor.fetchall()
cursor.execute(conn_current_sql)
conn_current_v = cursor.fetchall()
cursor.execute(usr_stat_sql)
usr_stat_v = cursor.fetchall()
cursor.execute(file_space_sql)
file_space_v = cursor.fetchall()
cursor.close()
conn.close()
self.stat_dict["数据库可用性"] = "正常"
# 连接占用率
conn_pct_v = math.ceil(conn_current_v[0][0] / conn_max_v[0][0])
self.stat_dict["连接占用率"] = str(conn_pct_v) + '%'
# 用户状态
usr_dis_list = "异常用户"
for usr_stat_tmp in usr_stat_v:
if not usr_stat_tmp[2]:
pass
else:
usr_dis_list = usr_dis_list + "," + usr_stat_tmp[0]
if usr_dis_list == "异常用户":
self.stat_dict["用户状态"] = "正常"
else:
self.stat_dict["用户状态"] = usr_dis_list
# 数据/日志空间/状态信息
file_stat_list = []
data_stat_list = []
log_stat_list = []
data_file_autoe = []
log_file_autoe = []
for file_space_tmp in file_space_v:
if file_space_tmp[7] == -1:
if file_space_tmp[2] == 'ROWS':
data_file_autoe.append(file_space_tmp[3])
elif file_space_tmp[2] == 'LOG':
log_file_autoe.append(file_space_tmp[3])
else:
pass
else:
file_pct = file_space_tmp[6] / file_space_tmp[7] * 100
file_pct = math.ceil(file_pct)
if file_pct > 90:
if file_space_tmp[2] == 'ROWS':
data_stat_list.append(file_space_tmp[3])
elif file_space_tmp[2] == 'LOG':
log_stat_list.append(file_space_tmp[3])
else:
pass
if file_space_tmp[5] != 'ONLINE':
file_stat_list.append(file_space_tmp[3])
else:
pass
# 判断文件状态
file_stat = "正常"
if len(file_stat_list) != 0:
file_stat = "数据库文件异常"
for file_stat_tmp in file_stat_list:
file_stat = file_stat + ',' + file_stat_tmp
else:
pass
self.stat_dict["文件状态"] = file_stat
# 判断日志空间
log_stat = "正常"
if len(log_stat_list) != 0:
log_stat = "数据库"
for log_stat_tmp in log_stat_list:
log_stat = log_stat + ',' + log_stat_tmp
log_stat = log_stat + "数据空间使用率大于90%"
else:
if len(log_file_autoe) != 0:
log_stat = "正常，部分文件自动扩展，请注意磁盘空间使用情况"
self.stat_dict["日志空间"] = log_stat
# 判断数据空间
data_stat = "正常"
if len(data_stat_list) != 0:
data_stat = "数据库"
for data_stat_tmp in data_stat_list:
data_stat = data_stat + ',' + data_stat_tmp
data_stat = data_stat + "数据空间使用率大于90%"
else:
if len(data_file_autoe) != 0:
data_stat = "正常，部分文件自动扩展，请注意磁盘空间使用情况"
self.stat_dict["数据空间"] = data_stat
except pymssql.OperationalError as e:
# 处理连接错误
self.stat_dict["数据库可用性"] = "异常"
print("Connection Error:", e)
return self.stat_dict
class Url:
def __init__(self, ip, url, name):
self.ip = ip
self.url = url
self.stat_dict = {"IP": ip, "类型": "URL", "名称": name, "URL状态": "/"}
def urlch_statv(self):
try:
req = requests.get(self.url, timeout=20)
status_code = req.status_code
if status_code == 200:
self.stat_dict["URL状态"] = "正常"
else:
self.stat_dict["URL状态"] = "异常"
except requests.exceptions.ConnectionError:
self.stat_dict["URL状态"] = "异常"
print("网络连接异常")
except requests.exceptions.Timeout:
self.stat_dict["URL状态"] = "异常"
print("连接超时")
# 如果IP为空，则复制 '/'
if self.stat_dict["IP"] is None:
self.stat_dict["IP"] = '/'
return self.stat_dict
if __name__ == '__main__':
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "：开始巡检")
ck_dt = time.strftime("%Y-%m-%d-%H%M%S", time.localtime())
ck_path = os.getcwd()
ck_rep_name = "日常检查报告-" + ck_dt + ".xlsx"
mysql_num = 0 # mysql数据库数量
oracle_num = 0 # oracle数据库数量
sqlserver_num = 0 # sqlserver数据库数量
url_num = 0 # url数量
os_res = [] # 系统检查信息
db_u_res = [] # 数据库/URL检查信息
o_s_r = 0 # oracle 数据库记录开始位置
m_s_r = 0 # mysql 数据库记录开始位置
s_s_r = 0 # sqlserver 数据库记录开始位置
u_s_r = 0 # url 表头位置
o_k_r = 0 # oracle 表头位置
m_k_r = 0 # mysql 表头位置
s_k_r = 0 # sqlserver 表头位置
u_k_r = 0 # url 表头位置
oracle_db_key = [] # oracle 表头内容
mysql_db_key = [] # mysql 表头内容
sqlserver_db_key = [] # sqlserver 表头内容
url_key = [] # url 表头内容
# excel 类型格式 bold为加粗
type_key_font = Font(name='宋体', bold=True, size=14, color='ffffff')
type_key_fill = PatternFill(start_color='6A5ACD', end_color='6A5ACD', fill_type='solid')
# excel 表头格式
key_f_font = Font(name='宋体', bold=True, size=12)
key_f_fill = PatternFill(start_color='00BFFF', end_color='00BFFF', fill_type='solid')
key_f_align = Alignment(horizontal='center', vertical='center')
# excel 数据格式
data_f_font = Font(name='宋体', size=9)
data_f_font_c = Font(name='宋体', bold=True, size=10)
data_f_fill = PatternFill(start_color='00FA9A', end_color='00FA9A', fill_type='solid')
data_f_align_c = Alignment(horizontal='center', vertical='center')
# excel 边框
border = Border(left=Side(style='medium', color='000000'),
right=Side(style='medium', color='000000'),
top=Side(style='medium', color='000000'),
bottom=Side(style='medium', color='000000'))
# 获取连接信息
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "：获取连接信息")
cfile = open('connectinfo', 'r', encoding='utf-8')
conn_list = cfile.readlines()
# 获取数据
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "：开始获取数据")
for res in conn_list:
res = res.strip()
c_type = res.split(',')[0]
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "：获取数据，类型", c_type, ",IP:",
res.split(',')[1].replace('"', ''))
if c_type == "Oracle":
o_db_ip = res.split(',')[1].replace('"', '')
o_db_port = res.split(',')[2].replace('"', '')
o_db_sid = res.split(',')[3].replace('"', '')
o_db_u = res.split(',')[4].replace('"', '')
o_db_p = res.split(',')[5].replace('"', '')
odb = OracleDb(o_db_ip, o_db_port, o_db_sid, o_db_u, o_db_p)
odb_res = odb.dbck_statv()
db_u_res.append(odb_res)
oracle_num += 1
del odb
elif c_type == "Mysql":
m_db_ip = res.split(',')[1].replace('"', '')
m_db_port = res.split(',')[2].replace('"', '')
m_db_db = res.split(',')[3].replace('"', '')
m_db_u = res.split(',')[4].replace('"', '')
m_db_p = res.split(',')[5].replace('"', '')
mdb = MysqlDb(m_db_ip, m_db_port, m_db_db, m_db_u, m_db_p)
mdb_res = mdb.dbck_statv()
db_u_res.append(mdb_res)
mysql_num += 1
del mdb
elif c_type == "SQLServer":
s_ip = res.split(',')[1].replace('"', '')
s_db = res.split(',')[2].replace('"', '')
s_port = res.split(',')[3].replace('"', '')
s_u = res.split(',')[4].replace('"', '')
s_p = res.split(',')[5].replace('"', '')
dbs = SQLserverDb(s_ip, s_port, s_db, s_u, s_p)
dbss_res = dbs.dbch_statv()
db_u_res.append(dbss_res)
sqlserver_num += 1
del dbs
elif c_type == "Linux":
l_ip = res.split(',')[1].replace('"', '')
l_port = res.split(',')[2].replace('"', '')
l_u = res.split(',')[3].replace('"', '')
l_p = res.split(',')[4].replace('"', '')
osl = LinuxOs(l_ip, l_port, l_u, l_p)
osl_res = osl.osck_statv()
os_res.append(osl_res)
del osl
elif c_type == "Windows":
w_ip = res.split(',')[1].replace('"', '')
w_u = res.split(',')[2].replace('"', '')
w_p = res.split(',')[3].replace('"', '')
osw = WindowsOs(w_ip, w_u, w_p)
osw_res = osw.osch_statv()
os_res.append(osw_res)
del osw
elif c_type == "URL":
urls_ip = res.split(',')[1].replace('"', '')
urls_url = res.split(',')[2].replace('"', '')
urls_name = res.split(',')[3].replace('"', '')
urls = Url(urls_ip, urls_url, urls_name)
urls_res = urls.urlch_statv()
db_u_res.append(urls_res)
url_num += 1
del urls
else:
pass
cfile.close()
# 融合数据
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "：融合数据")
for db_num in range(0, len(db_u_res)):
db_ip_up = db_u_res[db_num]["IP"]
for os_num in range(0, len(os_res)):
os_ip_up = os_res[os_num]["IP"]
if os_ip_up == db_ip_up:
db_type = db_u_res[db_num]["类型"]
db_u_res[db_num].update(os_res[os_num])
db_u_res[db_num]["类型"] = db_type
break
# 获取表头
for key_tmp in db_u_res:
if key_tmp["类型"] == "Oracle":
oracle_db_key = list(key_tmp.keys())
continue
if key_tmp["类型"] == "Mysql":
mysql_db_key = list(key_tmp.keys())
continue
if key_tmp["类型"] == "SQL server":
sqlserver_db_key = list(key_tmp.keys())
continue
if key_tmp["类型"] == "URL":
url_key = list(key_tmp.keys())
continue
# 生成巡检文件
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "：生成巡检文件")
ck_wb = openpyxl.Workbook()
ck_ws = ck_wb.active
# oracle 表头
k_r = 1
if oracle_num != 0:
ck_o_k_c = ck_ws.cell(k_r, 1)
ck_o_k_s = ck_o_k_c.coordinate
ck_o_k_c = ck_ws.cell(k_r, len(oracle_db_key))
ck_o_k_e = ck_o_k_c.coordinate
ck_o_k_l = ck_o_k_s + ':' + ck_o_k_e
ck_ws.merge_cells(ck_o_k_l)
ck_ws[ck_o_k_s].font = type_key_font
ck_ws[ck_o_k_s].fill = type_key_fill
ck_ws.cell(k_r, 1, "ORACLE")
k_r += 1
o_k_r = k_r
o_s_r = k_r + 1
for k_v_i in range(0, len(oracle_db_key)):
ck_ws.cell(k_r, k_v_i + 1, oracle_db_key[k_v_i])
ck_key_l = ck_ws.cell(k_r, k_v_i + 1).coordinate
ck_ws[ck_key_l].font = key_f_font
ck_ws[ck_key_l].fill = key_f_fill
ck_ws[ck_key_l].alignment = key_f_align
k_r = k_r + oracle_num + 1
# mysql表头
if mysql_num != 0:
ck_m_k_c = ck_ws.cell(k_r, 1)
ck_m_k_s = ck_m_k_c.coordinate
ck_m_k_c = ck_ws.cell(k_r, len(mysql_db_key))
ck_m_k_e = ck_m_k_c.coordinate
ck_m_k_l = ck_m_k_s + ':' + ck_m_k_e
ck_ws.merge_cells(ck_m_k_l)
ck_ws[ck_m_k_s].font = type_key_font
ck_ws[ck_m_k_s].fill = type_key_fill
ck_ws.cell(k_r, 1, "MYSQL")
k_r += 1
m_k_r = k_r
m_s_r = k_r + 1
for k_v_i in range(0, len(mysql_db_key)):
ck_ws.cell(k_r, k_v_i + 1, mysql_db_key[k_v_i])
ck_key_l = ck_ws.cell(k_r, k_v_i + 1).coordinate
ck_ws[ck_key_l].font = key_f_font
ck_ws[ck_key_l].fill = key_f_fill
ck_ws[ck_key_l].alignment = key_f_align
k_r = k_r + mysql_num + 1
# SQL server表头
if sqlserver_num != 0:
ck_s_k_c = ck_ws.cell(k_r, 1)
ck_s_k_s = ck_s_k_c.coordinate
ck_s_k_c = ck_ws.cell(k_r, len(sqlserver_db_key))
ck_s_k_e = ck_s_k_c.coordinate
ck_s_k_l = ck_s_k_s + ':' + ck_s_k_e
ck_ws.merge_cells(ck_s_k_l)
ck_ws[ck_s_k_s].font = type_key_font
ck_ws[ck_s_k_s].fill = type_key_fill
ck_ws.cell(k_r, 1, "SQL SERVER")
k_r += 1
s_k_r = k_r
s_s_r = k_r + 1
for k_v_i in range(0, len(sqlserver_db_key)):
ck_ws.cell(k_r, k_v_i + 1, sqlserver_db_key[k_v_i])
ck_key_l = ck_ws.cell(k_r, k_v_i + 1).coordinate
ck_ws[ck_key_l].font = key_f_font
ck_ws[ck_key_l].fill = key_f_fill
ck_ws[ck_key_l].alignment = key_f_align
k_r = k_r + sqlserver_num + 1
# URL表头
if url_num != 0:
ck_u_k_c = ck_ws.cell(k_r, 1)
ck_u_k_s = ck_u_k_c.coordinate
ck_u_k_c = ck_ws.cell(k_r, len(url_key))
ck_u_k_e = ck_u_k_c.coordinate
ck_u_k_l = ck_u_k_s + ':' + ck_u_k_e
ck_ws.merge_cells(ck_u_k_l)
ck_ws[ck_u_k_s].font = type_key_font
ck_ws[ck_u_k_s].fill = type_key_fill
ck_ws.cell(k_r, 1, "URL")
k_r += 1
u_k_r = k_r
u_s_r = k_r + 1
for k_v_i in range(0, len(url_key)):
ck_ws.cell(k_r, k_v_i + 1, url_key[k_v_i])
ck_key_l = ck_ws.cell(k_r, k_v_i + 1).coordinate
ck_ws[ck_key_l].font = key_f_font
ck_ws[ck_key_l].fill = key_f_fill
ck_ws[ck_key_l].alignment = key_f_align
# 数据填充
for data_dirt in db_u_res:
if data_dirt["类型"] == "Oracle":
ck_dk_l = ck_ws.cell(row=o_s_r, column=1).coordinate
ck_ws[ck_dk_l].font = data_f_font_c
ck_ws[ck_dk_l].fill = data_f_fill
ck_ws[ck_dk_l].alignment = data_f_align_c
for data_v_i in range(1, len(data_dirt) + 1):
ck_dk_cell = ck_ws.cell(row=o_k_r, column=data_v_i)
ck_dk_v = ck_dk_cell.value
if data_v_i != 1:
ck_dk_l = ck_ws.cell(row=o_s_r, column=data_v_i).coordinate
ck_ws[ck_dk_l].font = data_f_font
ck_ws[ck_dk_l].alignment = data_f_align_c
try:
ck_ws.cell(row=o_s_r, column=data_v_i, value=data_dirt[ck_dk_v])
except KeyError:
ck_ws.cell(row=o_s_r, column=data_v_i, value='')
o_s_r += 1
if data_dirt["类型"] == "Mysql":
ck_dk_l = ck_ws.cell(row=m_s_r, column=1).coordinate
ck_ws[ck_dk_l].font = data_f_font_c
ck_ws[ck_dk_l].fill = data_f_fill
ck_ws[ck_dk_l].alignment = data_f_align_c
for data_v_i in range(1, len(data_dirt) + 1):
ck_dk_cell = ck_ws.cell(row=m_k_r, column=data_v_i)
ck_dk_v = ck_dk_cell.value
if data_v_i != 1:
ck_dk_l = ck_ws.cell(row=m_s_r, column=data_v_i).coordinate
ck_ws[ck_dk_l].font = data_f_font
ck_ws[ck_dk_l].alignment = data_f_align_c
try:
ck_ws.cell(row=m_s_r, column=data_v_i, value=data_dirt[ck_dk_v])
except KeyError:
ck_ws.cell(row=m_s_r, column=data_v_i, value='')
m_s_r += 1
if data_dirt["类型"] == "SQL server":
ck_dk_l = ck_ws.cell(row=s_s_r, column=1).coordinate
ck_ws[ck_dk_l].font = data_f_font_c
ck_ws[ck_dk_l].fill = data_f_fill
ck_ws[ck_dk_l].alignment = data_f_align_c
for data_v_i in range(1, len(data_dirt) + 1):
ck_dk_cell = ck_ws.cell(row=s_k_r, column=data_v_i)
ck_dk_v = ck_dk_cell.value
if data_v_i != 1:
ck_dk_l = ck_ws.cell(row=s_s_r, column=data_v_i).coordinate
ck_ws[ck_dk_l].font = data_f_font
ck_ws[ck_dk_l].alignment = data_f_align_c
try:
ck_ws.cell(row=s_s_r, column=data_v_i, value=data_dirt[ck_dk_v])
except KeyError:
ck_ws.cell(row=s_s_r, column=data_v_i, value='')
s_s_r += 1
if data_dirt["类型"] == "URL":
ck_dk_l = ck_ws.cell(row=u_s_r, column=1).coordinate
ck_ws[ck_dk_l].font = data_f_font_c
ck_ws[ck_dk_l].fill = data_f_fill
ck_ws[ck_dk_l].alignment = data_f_align_c
for data_v_i in range(1, len(data_dirt) + 1):
ck_dk_cell = ck_ws.cell(row=u_k_r, column=data_v_i)
ck_dk_v = ck_dk_cell.value
if data_v_i != 1:
ck_dk_l = ck_ws.cell(row=u_s_r, column=data_v_i).coordinate
ck_ws[ck_dk_l].font = data_f_font
ck_ws[ck_dk_l].alignment = data_f_align_c
try:
ck_ws.cell(row=u_s_r, column=data_v_i, value=data_dirt[ck_dk_v])
except KeyError:
ck_ws.cell(row=u_s_r, column=data_v_i, value='')
u_s_r += 1
# 设置列宽
ck_ws.column_dimensions['A'].width = 20
for col_num in range(1, 20):
ck_ws.column_dimensions[chr(col_num + 65)].width = 18
# 设置边框
# 总行数,总列数
ws_row_num = 1
ws_col_num = 1
for tmp_num in (o_s_r-1, m_s_r-1, s_s_r-1, u_s_r-1):
if tmp_num > ws_row_num:
ws_row_num = tmp_num
for tmp_num in (len(oracle_db_key), len(mysql_db_key), len(sqlserver_db_key), len(url_key)):
if tmp_num > ws_col_num:
ws_col_num = tmp_num
for ck_row in range(1, ws_row_num+1):
for ck_col in range(1, ws_col_num+1):
ck_ck_l = ck_ws.cell(row=ck_row, column=ck_col).coordinate
ck_ws[ck_ck_l].border = border
ck_wb.save(ck_rep_name)
print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), "：完成")
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
