笔记：dbck_v1

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"

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或以上版本
#  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


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:
                    tbsne.append(tbs_info[0])
                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 + ','
                tbs_stat = tbs_stat + "空闲空间不足10%"
                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 user_info:
                        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) == 0:
                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('df -h')
            disk_info = stdout.read().decode('utf-8')
            stdin, stdout, stderr = os_conn.exec_command('sar 2 5 | grep Average')
            cpu_info = stdout.read().decode('utf-8')
            stdin, stdout, stderr = os_conn.exec_command('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信息
            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使用率"] != "None":
                    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):
        self.ip = ip
        self.url = url
        self.stat_dict = {"IP": ip, "类型": "URL", "URL状态": ""}

    def urlch_statv(self):
        try:
            req = requests.get(self.url)
            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("网络连接异常")
        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')
    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]
        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 = Url(urls_ip, urls_url)
            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
                ck_ws.cell(row=o_s_r, column=data_v_i, value=data_dirt[ck_dk_v])
            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
                ck_ws.cell(row=m_s_r, column=data_v_i, value=data_dirt[ck_dk_v])
            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
                ck_ws.cell(row=s_s_r, column=data_v_i, value=data_dirt[ck_dk_v])
            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
                ck_ws.cell(row=u_s_r, column=data_v_i, value=data_dirt[ck_dk_v])
            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()), "：完成")