Ejemplo n.º 1
0
def main():

    #get mysql servers list
    servers = func.mysql_query('select id,host,port,username,password,tags from db_servers_mysql where is_delete=0 and monitor=1;')

    logger.info("check mysql controller started.")

    if servers:
         plist = []
         for row in servers:
             server_id=row[0]
             host=row[1]
             port=row[2]
             username=row[3]
             password=row[4]
             tags=row[5]
             #thread.start_new_thread(check_mysql, (host,port,user,passwd,server_id,application_id))
             #time.sleep(1)
             p = Process(target = check_all_indexes, args = (host,port,username,password))
             plist.append(p)
         for p in plist:
             p.start()
         time.sleep(10)
         for p in plist:
             p.terminate()
         for p in plist:
             p.join()

    else:
         logger.warning("check mysql: not found any servers")

    logger.info("check mysql controller finished.")
def main():
    #get mysql servers list
    servers = func.mysql_query('select m.id,m.host,m.port,m.tags,m.bigtable_size from db_servers_mysql as m , mysql_status as s where m.is_delete=0 and m.monitor=1 and m.bigtable_monitor=1 and m.host=s.host and m.port=s.port and s.role="master" order by host;')

    #++ guoqi
    exeTimeout = 20
    cnfKey = "monitor_mysql"
    username = func.get_config(cnfKey,'user')
    password = func.get_config(cnfKey,'passwd')

    if servers:
        print("%s: check mysql bigtable controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
        plist = []
	no = 1
        for row in servers:
            (server_id, host, port, tags, bigtable_size) = row
	    check_mysql_bigtable(no,host,port,username,password,server_id,tags,bigtable_size)
	    no += 1
	'''
            p = Process(target = check_mysql_bigtable, args = (no,host,port,username,password,server_id,tags,bigtable_size))
            plist.append(p)
	    no += 1
        for p in plist:
            p.start()
        time.sleep(exeTimeout)
        for p in plist:
            p.terminate()
        for p in plist:
            p.join()
	'''
        print("%s: check mysql bigtable controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
Ejemplo n.º 3
0
def main():

    func.mysql_exec("insert into oracle_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_status;",'')
    func.mysql_exec('delete from oracle_status;','')

    func.mysql_exec("insert into oracle_tablespace_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_tablespace;",'')
    func.mysql_exec('delete from oracle_tablespace;','')

    #get oracle servers list
    servers=func.mysql_query("select id,host,port,dsn,username,password,tags from db_servers_oracle where is_delete=0 and monitor=1;")

    logger.info("check oracle controller start.")
    if servers:
        plist = []
        for row in servers:
            server_id=row[0]
            host=row[1]
            port=row[2]
            dsn=row[3]
            username=row[4]
            password=row[5]
            tags=row[6]
            p = Process(target = check_oracle, args = (host,port,dsn,username,password,server_id,tags))
            plist.append(p)
            p.start()
        #time.sleep(10)
        #for p in plist:
        #    p.terminate()
        for p in plist:
            p.join()

    else:
        logger.warning("check oracle: not found any servers")

    logger.info("check oracle controller finished.")
Ejemplo n.º 4
0
def mysql_result(indexDict):
    ## 获取数据库信息
    mysqlDB   = portTEU.db      or mysql_conn.db
    mysqlTB   = portTEU.tb      or mysql_conn.tb
    mysqlHost = portTEU.host    or mysql_conn.host
    mysqlUser = portTEU.user    or mysql_conn.user
    mysqlPass = portTEU.passwd  or mysql_conn.passwd
    mysqlPort = portTEU.port    or mysql_conn.port
    mysqlChrt = portTEU.charset or mysql_conn.charset

    filterKeys = indexDict.get('wds')

    sql = "SELECT year,value FROM {db}.{tb} WHERE wds='{fk}' \
         AND year=(SELECT MAX(year) FROM {db}.{tb} WHERE wds='{fk}' AND value!=0)".format(
         db = mysqlDB, tb = mysqlTB, fk = filterKeys)

    result = mysql_query(
        cmd      = sql,
        host     = mysqlHost,
        user     = mysqlUser,
        passwd   = mysqlPass,
        port     = mysqlPort,
        charset  = mysqlChrt,
        fetchone = 1
    )

    return result
Ejemplo n.º 5
0
def main():
    #get os servers list
    cpus = 12
    servers = func.mysql_query(
        "select host,tags from db_servers_os where is_delete=0 and monitor=1;")
    logger.info("check os controller started.")
    if servers:
        plist = []
        proHostsNum = len(servers) / cpus + 1
        logger.info("check os sum:%s, cpus:%s, percpu:%s" %
                    (len(servers), cpus, proHostsNum))
        for i in range(0, len(servers), proHostsNum):
            proSrvs = servers[i:i + proHostsNum]
            p = Process(target=check_hosts, args=(i, proSrvs))
            plist.append(p)
        intervals = exeTimeout / len(plist)
        if intervals <= 0:
            intervals = 1

        for p in plist:
            p.start()
            #time.sleep(intervals)
            time.sleep(1)

        for p in plist:
            p.join(timeout=10)

    else:
        logger.warning("check os: not found any servers")

    logger.info("check os controller finished.")
Ejemplo n.º 6
0
def main():

    func.mysql_exec("insert into redis_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from redis_status;",'')
    func.mysql_exec('delete from redis_status  where server_id not in (select id from db_servers_redis where monitor=1);','')
    func.mysql_exec("insert into redis_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from redis_replication;",'')
    func.mysql_exec('delete from redis_replication where server_id not in (select id from db_servers_redis where monitor=1);','')

    servers = func.mysql_query('select id,host,port,password,tags from db_servers_redis where is_delete=0 and monitor=1;')

    logger.info("check redis controller started.")

    if servers:
         plist = []

         for row in servers:
             server_id=row[0]
             host=row[1]
             port=row[2]
             passwd=row[3]
             tags=row[4]
             p = Process(target = check_redis, args = (host,port,passwd,server_id,tags))
             plist.append(p)
             p.start()

         for p in plist:
             p.join()

    else:
         logger.warning("check redis: not found any servers")

    logger.info("check redis controller finished.")
Ejemplo n.º 7
0
def main():

    servers = func.mysql_query(
        'select id,host,port,username,password,tags from db_cfg_mongodb where is_delete=0 and monitor=1;'
    )

    logger.info("check mongodb controller started.")

    if servers:
        plist = []
        for row in servers:
            server_id = row[0]
            host = row[1]
            port = row[2]
            username = row[3]
            password = row[4]
            tags = row[5]
            p = Process(target=check_mongodb,
                        args=(host, port, username, password, server_id, tags))
            plist.append(p)
            p.start()

        for p in plist:
            p.join()

    else:
        logger.warning("check mongodb: not found any servers")

    logger.info("check mongodb controller finished.")
Ejemplo n.º 8
0
def main():

    func.mysql_exec("insert into redis_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from redis_status;",'')
    func.mysql_exec('delete from redis_status;','')
    func.mysql_exec("insert into redis_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from redis_replication;",'')
    func.mysql_exec('delete from redis_replication;','')

    servers = func.mysql_query('select id,host,port,password,tags from db_servers_redis where is_delete=0 and monitor=1;')

    logger.info("check redis controller started.")

    if servers:
         plist = []

         for row in servers:
             server_id=row[0]
             host=row[1]
             port=row[2]
             passwd=row[3]
             tags=row[4]
             p = Process(target = check_redis, args = (host,port,passwd,server_id,tags))
             plist.append(p)
             p.start()

         for p in plist:
             p.join()

    else:
         logger.warning("check redis: not found any servers")

    logger.info("check redis controller finished.")
def main():

    user = func.get_config('mysql_db', 'username')
    passwd = func.get_config('mysql_db', 'password')
    servers = func.mysql_query(
        "select host,port,binlog_store_days from db_cfg_mysql where is_delete=0 and monitor=1 and binlog_auto_purge=1;"
    )
    if servers:
        print("%s: admin mysql purge binlog controller started." %
              (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
        plist = []
        for row in servers:
            host = row[0]
            port = row[1]
            binlog_store_days = row[2]
            p = Process(target=admin_mysql_purge_binlog,
                        args=(host, port, user, passwd, binlog_store_days))
            plist.append(p)
        for p in plist:
            p.start()
        time.sleep(60)
        for p in plist:
            p.terminate()
        for p in plist:
            p.join()
        print("%s: admin mysql purge binlog controller finished." %
              (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
Ejemplo n.º 10
0
def get_result(_dict):
    filterKeys = _dict.get('wds')

    ## 获取数据库信息
    mysqlDB   = railwaytraveller.db      or mysql_conn.db
    mysqlTB   = railwaytraveller.tb      or mysql_conn.tb
    mysqlHost = railwaytraveller.host    or mysql_conn.host
    mysqlUser = railwaytraveller.user    or mysql_conn.user
    mysqlPass = railwaytraveller.passwd  or mysql_conn.passwd
    mysqlPort = railwaytraveller.port    or mysql_conn.port
    mysqlChrt = railwaytraveller.charset or mysql_conn.charset

    ## 查询语句
    sql = "SELECT year,value FROM {db}.{tb} WHERE wds='{fk}' \
         AND year=(SELECT MAX(year) FROM {db}.{tb} WHERE wds='{fk}' AND value!=0)".format(
         db = mysqlDB, tb = mysqlTB, fk = filterKeys)

    ## 查询结果
    resultDict = mysql_query(
        cmd      = sql,
        host     = mysqlHost,
        user     = mysqlUser,
        passwd   = mysqlPass,
        port     = mysqlPort,
        charset  = mysqlChrt,
        fetchone = 1
    )

    return resultDict
Ejemplo n.º 11
0
def main():
    func.mysql_exec(
        "insert into mysql_bigtable_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),8) from mysql_bigtable",
        '')
    func.mysql_exec('delete from mysql_bigtable;', '')
    #get mysql servers list
    servers = func.mysql_query(
        'select id,host,port,username,password,tags,bigtable_size from db_servers_mysql where is_delete=0 and monitor=1 and bigtable_monitor=1;'
    )
    if servers:
        print("%s: check mysql bigtable controller started." %
              (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
        plist = []
        for row in servers:
            server_id = row[0]
            host = row[1]
            port = row[2]
            username = row[3]
            password = row[4]
            tags = row[5]
            bigtable_size = row[6]
            p = Process(target=check_mysql_bigtable,
                        args=(host, port, username, password, server_id, tags,
                              bigtable_size))
            plist.append(p)
        for p in plist:
            p.start()
        time.sleep(15)
        for p in plist:
            p.terminate()
        for p in plist:
            p.join()
        print("%s: check mysql bigtable controller finished." %
              (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
Ejemplo n.º 12
0
def main():

    func.mysql_exec(
        "insert into mongodb_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mongodb_status;",
        '')
    func.mysql_exec('delete from mongodb_status;', '')

    #get mongodb servers list
    servers = func.mysql_query(
        'select id,host,port,username,password,tags from db_servers_mongodb where is_delete=0 and monitor=1;'
    )

    logger.info("check mongodb controller started.")

    if servers:
        plist = []
        for row in servers:
            server_id = row[0]
            host = row[1]
            port = row[2]
            username = row[3]
            password = row[4]
            tags = row[5]
            p = Process(target=check_mongodb,
                        args=(host, port, username, password, server_id, tags))
            plist.append(p)
            p.start()

        for p in plist:
            p.join()

    else:
        logger.warning("check mongodb: not found any servers")

    logger.info("check mongodb controller finished.")
Ejemplo n.º 13
0
def main():
    func.mysql_exec(
        "replace into memcache_status_history  SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from memcache_status;",
        '')
    func.mysql_exec(
        'delete from memcache_status where server_id not in (select id from db_servers_memcache where monitor=1);',
        '')
    #func.mysql_exec('delete from memcache_status;','')
    #get memcache servers list
    servers = func.mysql_query(
        'select id,host,port,tags from db_servers_memcache where is_delete=0 and monitor=1;'
    )
    logger.info("check memcache controller started.")
    if servers:
        plist = []
        for row in servers:
            server_id = row[0]
            host = row[1]
            port = row[2]
            tags = row[3]
            p = Process(target=check_memcache,
                        args=(host, port, server_id, tags))
            plist.append(p)
            p.start()

        for p in plist:
            p.join()

    else:
        logger.warning("check memcache: not found any servers")
    logger.info("check memcache controller finished.")
Ejemplo n.º 14
0
def main():

    #get os servers list
    servers=func.mysql_query("select host,community,filter_os_disk,tags from db_servers_os where is_delete=0 and monitor=1;")
    
    logger.info("check os controller started.")
    if servers:
         plist = []
         for row in servers:
             host=row[0]
             community=row[1]
             filter_os_disk=row[2]
             tags=row[3]
             if host <> '' :
                 thread.start_new_thread(check_os, (host,community,filter_os_disk,tags))
                 time.sleep(1)
		 #p = Process(target = check_os, args=(host,filter_os_disk))
                 #plist.append(p)
                 #p.start()

         #for p in plist:
         #    p.join()

    else: 
         logger.warning("check os: not found any servers")

    logger.info("check os controller finished.")
Ejemplo n.º 15
0
def main():

    func.mysql_exec(
        "insert into sqlserver_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from sqlserver_status;",
        "",
    )
    func.mysql_exec("delete from sqlserver_status;", "")

    servers = func.mysql_query(
        "select id,host,port,username,password,tags from db_servers_sqlserver where is_delete=0 and monitor=1;"
    )

    logger.info("check sqlserver controller started.")

    if servers:
        plist = []

        for row in servers:
            server_id = row[0]
            host = row[1]
            port = row[2]
            username = row[3]
            passwd = row[4]
            tags = row[5]
            p = Process(target=check_sqlserver, args=(host, port, username, passwd, server_id, tags))
            plist.append(p)
            p.start()

        for p in plist:
            p.join()

    else:
        logger.warning("check sqlserver: not found any servers")

    logger.info("check sqlserver controller finished.")
Ejemplo n.º 16
0
def main():

    #get os servers list
    servers = func.mysql_query(
        "select host,community,filter_os_disk,tags from db_servers_os where is_delete=0 and monitor=1;"
    )

    logger.info("check os controller started.")
    if servers:
        plist = []
        for row in servers:
            host = row[0]
            community = row[1]
            filter_os_disk = row[2]
            tags = row[3]
            if host <> '':
                thread.start_new_thread(
                    check_os, (host, community, filter_os_disk, tags))
                time.sleep(1)

#p = Process(target = check_os, args=(host,filter_os_disk))
#plist.append(p)
#p.start()

#for p in plist:
#    p.join()

    else:
        logger.warning("check os: not found any servers")

    logger.info("check os controller finished.")
Ejemplo n.º 17
0
def main():
    func.mysql_exec("insert into mysql_bigtable_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),8) from mysql_bigtable",'')
    func.mysql_exec('delete from mysql_bigtable;','')
    #get mysql servers list
    servers = func.mysql_query('select id,host,port,username,password,tags,bigtable_size from db_servers_mysql where is_delete=0 and monitor=1 and bigtable_monitor=1;')
    if servers:
        print("%s: check mysql bigtable controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
        plist = []
        for row in servers:
            server_id=row[0]
            host=row[1]
            port=row[2]
            username=row[3]
            password=row[4]
            tags=row[5]
            bigtable_size=row[6]
            p = Process(target = check_mysql_bigtable, args = (host,port,username,password,server_id,tags,bigtable_size))
            plist.append(p)
        for p in plist:
            p.start()
        time.sleep(15)
        for p in plist:
            p.terminate()
        for p in plist:
            p.join()
        print("%s: check mysql bigtable controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
Ejemplo n.º 18
0
def main():

    servers = func.mysql_query('select id,host,port,username,password,tags from db_servers_mongodb where is_delete=0 and monitor=1;')

    logger.info("check mongodb controller started.")

    if servers:
         plist = []
         for row in servers:
             server_id=row[0]
             host=row[1]
             port=row[2]
             username=row[3]
             password=row[4]
             tags=row[5]
             p = Process(target = check_mongodb, args = (host,port,username,password,server_id,tags))
             plist.append(p)
             p.start()

         for p in plist:
             p.join()

    else:
         logger.warning("check mongodb: not found any servers")

    logger.info("check mongodb controller finished.")
Ejemplo n.º 19
0
def main():
    #get os servers list
    zbItems=func.mysql_query("SELECT item_type, stat_item_name, zabbix_item_name, zabbix_item_value_unit, zabbix_server,last_stat_time FROM zabbix_item where item_type='os';")
    zbSrvItems = {}
    for zbItem in zbItems:
	(item_type, stat_item_name, zabbix_item_name, zabbix_item_value_unit, zabbix_server,last_stat_time) = zbItem
        if not zbSrvItems.has_key(zabbix_server):
            zbSrvItems[zabbix_server] = []
        zbSrvItems.get(zabbix_server).append(zbItem)
    #print zbSrvItems 
    zbSectors = ["zabbix_dc", "zabbix_dba"]
    zbApis = {}
    for zbSector in zbSectors:
        zbKey = func.get_config(zbSector,'key')
        zbHost = func.get_config(zbSector,'host')
        zbUser = func.get_config(zbSector,'user')
        zbPasswd = func.get_config(zbSector, 'passwd')
        zbApis[zbKey] = zbCli(zbHost, zbUser, zbPasswd)

    logger.info("check os controller started.")
    servers=func.mysql_query("select id, host,tags, create_time from db_servers_os where is_delete=0 and monitor=1;")
    if servers:
        plist = []
	proHostsNum = len(servers)/cpus+1
	logger.info("check os sum:%s, cpus:%s, percpu:%s" % (len(servers), cpus, proHostsNum))
	for i in range(0,len(servers), proHostsNum):
	    proSrvs = servers[i:i+proHostsNum]
	    p = Process(target = check_hosts, args=(i, proSrvs, zbSrvItems, zbApis))
	    plist.append(p)
	intervals = exeTimeout/len(plist)
	if intervals <= 0:
	    intervals = 1

	for p in plist:
	    p.start()
	    #time.sleep(intervals)
	    time.sleep(1)
        
	for p in plist:
	    p.join(timeout=10)

    else: 
         logger.warning("check os: not found any servers")

    func.mysql_exec('update os_status set zabbix=0,create_time=now() where create_time<date_sub(now(), interval %s second)' % (min_interval))
    logger.info("check os controller finished.")
Ejemplo n.º 20
0
def main():

    #get os servers list
    servers=func.mysql_query("select host,community,filter_os_disk,tags from db_servers_os where is_delete=0 and monitor=1;")
    for row in servers:      
        host=row[0]          
        community=row[1]     
        filter_os_disk=row[2]
        tags=row[3]          
        check_os(host,community,filter_os_disk,tags)    
Ejemplo n.º 21
0
def disk_mod():
    label = config.disk.label
    dataDict = mysql_query(cmd=mysql_conn.sltSQL.format(label),
                           host=mysql_conn.host,
                           user=mysql_conn.user,
                           passwd=mysql_conn.passwd,
                           port=mysql_conn.port,
                           fetchone=1,
                           charset='utf8')

    return dataDict.get('data')
Ejemplo n.º 22
0
def data_query(_sql):
    _resultList = mysql_query(cmd=_sql,
                              host=zbxdb_conn.host,
                              user=zbxdb_conn.user,
                              passwd=zbxdb_conn.passwd,
                              port=zbxdb_conn.port,
                              fetchone=1,
                              charset='utf8')

    ## 返回结果
    _result = _resultList.get('result')
    return _result
Ejemplo n.º 23
0
def main():
    #func.mysql_exec("insert into mysql_processlist_history SELECT * from mysql_processlist",'')
    #func.mysql_exec("delete from mysql_processlist",'')
    func.mysql_exec(
        "replace into mysql_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status",
        '')
    func.mysql_exec(
        'delete from mysql_status where server_id not in (select id from db_servers_mysql where monitor=1);',
        '')

    func.mysql_exec(
        "replace into mysql_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication",
        '')
    func.mysql_exec(
        'delete from mysql_replication where server_id not in (select id from db_servers_mysql where monitor=1);',
        '')
    #get mysql servers list
    servers = func.mysql_query(
        "select id,host,port,username,password,tags,repl_channel from db_servers_mysql where is_delete=0 and monitor=1 and monitor_proxy='0';"
    )

    logger.info("check mysql controller started.")

    if servers:
        plist = []
        for row in servers:
            server_id = row[0]
            host = row[1]
            port = row[2]
            username = row[3]
            password = row[4]
            tags = row[5]
            replchannel = row[6]
            #thread.start_new_thread(check_mysql, (host,port,user,passwd,server_id,application_id))
            #time.sleep(1)
            p = Process(target=check_mysql,
                        args=(host, port, username, password, server_id, tags,
                              replchannel))
            plist.append(p)
        for p in plist:
            p.start()
        time.sleep(10)
        for p in plist:
            p.terminate()
        for p in plist:
            p.join()

    else:
        logger.warning("check mysql: not found any servers")

    logger.info("check mysql controller finished.")
Ejemplo n.º 24
0
def get_connect(server_id):
    url = ""
    host = ""
    port = ""
    username = ""
    password = ""
    tags = ""

    server = func.mysql_query(
        "select host,port,dsn,username,password,tags from db_cfg_oracle where id=%s;"
        % (server_id))
    if server:
        for row in server:
            host = row[0]
            port = row[1]
            username = row[3]
            password = row[4]
            tags = row[5]
            url = row[0] + ':' + row[1] + '/' + row[2]

    if host == "":
        logger.warning("get host failed, exit!")
        sys.exit(1)

    try:
        conn = cx_Oracle.connect(username,
                                 password,
                                 url,
                                 mode=cx_Oracle.SYSDBA)  #获取connection对象
        return conn
    except Exception, e:
        logger_msg = "check oracle %s : %s" % (url, str(e).strip('\n'))
        logger.warning(logger_msg)

        try:
            connect = 0

            func.mysql_exec("begin;", '')

            sql = "delete from oracle_status where server_id = %s; " % (
                server_id)
            func.mysql_exec(sql, '')

            sql = "insert into oracle_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)"
            param = (server_id, host, port, tags, connect)
            func.mysql_exec(sql, param)

            func.mysql_exec("commit;", '')
        except Exception, e:
            func.mysql_exec("rollback;", '')
            logger.error(str(e).strip('\n'))
Ejemplo n.º 25
0
def data_query(_sql):
    _resultList = mysql_query(cmd=_sql,
                              host=zbxdb_conn.host,
                              user=zbxdb_conn.user,
                              passwd=zbxdb_conn.passwd,
                              port=zbxdb_conn.port,
                              charset='utf8')

    ## 数据处理, 主要是将名称简化并并规范
    _dataList = [{
        'name': '_'.join(x.get('name').split()[1:-2]),
        'value': x.get('result')
    } for x in _resultList]

    return _dataList
Ejemplo n.º 26
0
def main():

    func.mysql_exec("insert into oracle_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_status;",'')
    func.mysql_exec('delete from oracle_status;','')

    func.mysql_exec("insert into oracle_tablespace_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_tablespace;",'')
    func.mysql_exec('delete from oracle_tablespace;','')

    func.mysql_exec("insert into oracle_dg_p_status_his SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_dg_p_status;",'')
    func.mysql_exec('delete from oracle_dg_p_status_tmp;','')
    func.mysql_exec('insert into oracle_dg_p_status_tmp select * from oracle_dg_p_status;','')

    func.mysql_exec("insert into oracle_dg_s_status_his SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from oracle_dg_s_status;",'')
    func.mysql_exec('delete from oracle_dg_s_status_tmp;','')
    func.mysql_exec('insert into oracle_dg_s_status_tmp select * from oracle_dg_s_status;','')

    #get oracle servers list
    servers=func.mysql_query("select id,host,port,dsn,username,password,tags from db_servers_oracle where is_delete=0 and monitor=1;")

    logger.info("check oracle controller start.")
    if servers:
        plist = []
        for row in servers:
            server_id=row[0]
            host=row[1]
            port=row[2]
            dsn=row[3]
            username=row[4]
            password=row[5]
            tags=row[6]
            p = Process(target = check_oracle, args = (host,port,dsn,username,password,server_id,tags))
            plist.append(p)
            p.start()
        #time.sleep(10)
        #for p in plist:
        #    p.terminate()
        for p in plist:
            p.join()

    else:
        logger.warning("check oracle: not found any servers")


    func.mysql_exec('DELETE FROM oracle_dg_p_status WHERE id IN (select id from oracle_dg_p_status_tmp);','')
    func.mysql_exec('DELETE FROM oracle_dg_s_status WHERE id IN (select id from oracle_dg_s_status_tmp);','')


    logger.info("check oracle controller finished.")
Ejemplo n.º 27
0
def sub_mysql(args):
    qSql = "select host, port from db_servers_mysql"
    qSql = "select host, port from mysql_status where connect=1 and role='master'"
    qSql = '''SELECT s.host, s.port,t.db_ip, t.db_port FROM mysql_status AS s 
	left join (SELECT db_ip,db_port FROM mysql_databases WHERE upd_time>DATE_SUB(CURDATE(),INTERVAL 3 DAY) GROUP BY db_ip,db_port) AS t on CONCAT_WS(',',s.host,s.`port`) = CONCAT_WS(',',t.db_ip,t.db_port) WHERE s.connect=1 AND s.role='master' having t.db_ip is null'''
    lines = func.mysql_query(qSql)

    for line in lines:
        (ip, port, sip, sport) = line
        if re.search("^172.30.", ip):
            continue
        print datetime.now(), ip, port
        myCli = mysClient.mysqlClient(ip, port)
        if myCli == None:
            print "Error: %s, %s" % (ip, port)
        else:
            dbs = collect_tables(myCli)
            saveTables(ip, port, dbs)
Ejemplo n.º 28
0
def series_mod(**kwargs):
    ## 通过参数获取分支名称
    _branchName=kwargs.get('branchName')

    ## 获取对应分支数据在yaml数据中
    _textTagList   = yamlDict.get(_branchName).get('textTagList')
    _divUnit       = yamlDict.get(_branchName).get('divUnit') or 1
    _filterKeyList = [ w.get('wds') for w in _textTagList ]

    ## 根据元组长度判断sql查询语句
    ### python单元素元组写法是(e,), 直接用IN查询会失败
    if len(_filterKeyList) == 1:
        _sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds='{}' ORDER BY year".format(
             mysqlDB, mysqlTB, _filterKeyList[0])
    elif len(_filterKeyList) > 1:
        _sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds IN {} ORDER BY year".format(
             mysqlDB, mysqlTB, tuple(_filterKeyList))

    ## 调用数据库查询函数查询结果
    _resultList = mysql_query(
        cmd     = _sql,
        host    = mysqlHost,
        user    = mysqlUser,
        passwd  = mysqlPass,
        port    = mysqlPort,
        charset = mysqlChrt
    )

    ## 获取X轴刻度列表
    _yearList = [ y.get('year') for y in _resultList ]
    ## 刻度列表修正(去重)
    _yearList = list_uniq(_yearList)
    ## 刻度列表重新排序
    list.sort(_yearList)

    ## 获取series列表
    _seriesList = [ series(_resultList, _x, _divUnit, picType, _yearList) for _x in _textTagList ]

    ## 返回年份列表和series列表
    return {
        'year':_yearList, 
        'series':_seriesList
    }
Ejemplo n.º 29
0
def main():

    user = func.get_config('mysql_db','username')
    passwd = func.get_config('mysql_db','password')
    servers=func.mysql_query("select host,port,binlog_store_days from db_servers_mysql where is_delete=0 and monitor=1 and binlog_auto_purge=1;")
    if servers:
         print("%s: admin mysql purge binlog controller started." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),));
         plist = []
         for row in servers:
             host=row[0]
             port=row[1]
             binlog_store_days=row[2]
             p = Process(target = admin_mysql_purge_binlog, args = (host,port,user,passwd,binlog_store_days))
             plist.append(p)
         for p in plist:
             p.start()
         time.sleep(60)
         for p in plist:
             p.terminate()
         for p in plist:
             p.join()
         print("%s: admin mysql purge binlog controller finished." % (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()),))
Ejemplo n.º 30
0
def main():

    func.mysql_exec("insert into mysql_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status",'')
    func.mysql_exec('delete from mysql_status;','')

    func.mysql_exec("insert into mysql_replication_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_replication",'')
    func.mysql_exec('delete from mysql_replication;','')
    #get mysql servers list
    servers = func.mysql_query('select id,host,port,username,password,tags from db_servers_mysql where is_delete=0 and monitor=1;')

    logger.info("check mysql controller started.")

    if servers:
         plist = []
         for row in servers:
             server_id=row[0]
             host=row[1]
             port=row[2]
             username=row[3]
             password=row[4]
             tags=row[5]
             #thread.start_new_thread(check_mysql, (host,port,user,passwd,server_id,application_id))
             #time.sleep(1)
             p = Process(target = check_mysql, args = (host,port,username,password,server_id,tags))
             plist.append(p)
         for p in plist:
             p.start()
         time.sleep(10)
         for p in plist:
             p.terminate()	
         for p in plist:
             p.join()
         
    else:
         logger.warning("check mysql: not found any servers")

    logger.info("check mysql controller finished.")
Ejemplo n.º 31
0
def stat_mysql_tablespace():
    print("%s: check mysql bigtable controller started." %
          (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
    # get mysql servers list
    servers = func.mysql_query(
        'select m.id,m.host,m.port,m.tags,m.bigtable_size from db_servers_mysql as m , mysql_status as s where m.is_delete=0 and m.monitor=1 and m.bigtable_monitor=1 and m.host=s.host and m.port=s.port and s.role="master" order by host;'
    )

    cnfKey = "monitor_mysql"
    username = func.get_config(cnfKey, 'user')
    password = func.get_config(cnfKey, 'passwd')

    if servers:
        no = 1
        for row in servers:
            (server_id, host, port, tags, bigtable_size) = row
            if re.search("^172.30", host):
                continue
            print "%s/%s %s, %s" % (no, len(servers), host, port)
            check_mysql_tablespace(no, host, port, username, password,
                                   server_id, tags, bigtable_size)
            no += 1
    print("%s: check mysql bigtable controller finished." %
          (time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()), ))
Ejemplo n.º 32
0
def main():
    servers = func.mysql_query(
        'select id,host,port,username,password,tags from db_cfg_sqlserver where is_delete=0 and monitor=1;'
    )

    logger.info("check sqlserver controller started.")

    if servers:
        func.update_check_time('sqlserver')

        plist = []

        for row in servers:
            server_id = row[0]
            host = row[1]
            port = row[2]
            username = row[3]
            passwd = row[4]
            tags = row[5]
            p = Process(target=check_sqlserver,
                        args=(host, port, username, passwd, server_id, tags))
            plist.append(p)
            p.start()

        for p in plist:
            p.join()

    else:
        logger.warning("check sqlserver: not found any servers")

    logger.info("check sqlserver controller finished.")

    #check for mirror group
    mirror_list = func.mysql_query(
        "select id, mirror_name, primary_db_id, standby_db_id, db_name, is_switch from db_cfg_sqlserver_mirror where is_delete=0 and on_process = 0;"
    )

    logger.info("check sqlserver mirror start.")
    if mirror_list:
        plist_2 = []
        for row in mirror_list:
            mirror_id = row[0]
            mirror_name = row[1]
            pri_id = row[2]
            sta_id = row[3]
            db_name = row[4]
            is_switch = row[5]
            p2 = Process(target=check_mirror,
                         args=(mirror_id, pri_id, sta_id, db_name, is_switch))
            plist_2.append(p2)
            p2.start()

        for p2 in plist_2:
            p2.join()

    else:
        logger.warning("check sqlserver mirror: not found any mirror group")

    logger.info("check sqlserver mirror finished.")

    # Clean invalid data
    logger.info("Clean invalid sqlserver status start.")
    clean_invalid_db_status()
    logger.info("Clean invalid sqlserver status finished.")
Ejemplo n.º 33
0
def get_connect(server_id):
    host = ""
    port = ""
    username = ""
    password = ""
    tags = ""

    server = func.mysql_query(
        "select host,port,username,password,tags from db_cfg_sqlserver where id=%s;"
        % (server_id))
    if server:
        for row in server:
            host = row[0]
            port = row[1]
            username = row[2]
            passwd = row[3]
            tags = row[4]

    if host == "":
        logger.warning("get host failed, exit!")
        sys.exit(1)

    try:
        conn = pymssql.connect(host=host,
                               port=int(port),
                               user=username,
                               password=passwd,
                               charset="utf8")
        return conn
    except Exception, e:
        func.mysql_exec("rollback;", '')
        logger_msg = "check sqlserver %s:%s : %s" % (host, port, e)
        logger.warning(logger_msg)

        try:
            connect = 0

            func.mysql_exec("begin;", '')

            sql = "delete from sqlserver_status where server_id = %s; " % (
                server_id)
            func.mysql_exec(sql, '')

            # delete for the mirror record
            sql = "delete from sqlserver_mirror_p where server_id = %s; " % (
                server_id)
            func.mysql_exec(sql, '')

            sql = "delete from sqlserver_mirror_s where server_id = %s; " % (
                server_id)
            func.mysql_exec(sql, '')

            sql = "insert into sqlserver_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)"
            param = (server_id, host, port, tags, connect)
            func.mysql_exec(sql, param)

            logger.info(
                "Generate sqlserver instance alert for server: %s begin:" %
                (server_id))
            alert.gen_alert_sqlserver_status(
                server_id)  # generate oracle instance alert
            logger.info(
                "Generate sqlserver instance alert for server: %s end." %
                (server_id))

            func.mysql_exec("commit;", '')

        except Exception, e:
            logger.error(e)
            sys.exit(1)
Ejemplo n.º 34
0
def main():
    ## ## 从yaml文件数组中获取数据
    textTagList = yamlDict.get('textTagList')
    legendList = [x.get('name') for x in textTagList]
    colorList = yamlDict.get('colorList')
    titleName = yamlDict.get('titleName')
    yUnit = yamlDict.get('yUnit')
    divUnit = yamlDict.get('divUnit') or 1
    picType = yamlDict.get('picType')

    ## 获取数据库信息
    mysqlDB = express.db or mysql_conn.db
    mysqlTB = express.tb or mysql_conn.tb
    mysqlHost = express.host or mysql_conn.host
    mysqlUser = express.user or mysql_conn.user
    mysqlPass = express.passwd or mysql_conn.passwd
    mysqlPort = express.port or mysql_conn.port
    mysqlChrt = express.charset or mysql_conn.charset

    ## 获取数据索引列表
    keyList = [e.get('wds') for e in textTagList]
    ## 将索引列表转元组(方便数据库IN逻辑查询)
    keyTuple = tuple(keyList)

    ## 根据元组长度判断sql查询语句
    ### python单元素元组写法是(e,), 直接用IN查询会失败
    if len(keyTuple) == 1:
        sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds='{}'".format(
            mysqlDB, mysqlTB, keyTuple[0])
    elif len(keyTuple) > 1:
        sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds IN {}".format(
            mysqlDB, mysqlTB, keyTuple)

    ## 调用数据库查询函数查询结果
    resultList = mysql_query(cmd=sql,
                             host=mysqlHost,
                             user=mysqlUser,
                             passwd=mysqlPass,
                             port=mysqlPort,
                             charset=mysqlChrt)

    ## 获取X轴刻度列表
    yearList = [y.get('year') for y in resultList]
    ## 刻度列表修正(去重)
    yearList = list_uniq(yearList)
    ## 刻度列表重新排序
    list.sort(yearList)

    ## 获取series列表
    seriesList = [
        series(resultList, x, divUnit, picType, yearList) for x in textTagList
    ]

    ## 生成输出字典
    outputDict = {
        'title': titleName,
        'legend': legendList,
        'color': colorList,
        'xlabel': yearList,
        'yname': yUnit,
        'series': seriesList
    }
    ## 转json字符串,不转码
    outputStr = json.dumps(outputDict, ensure_ascii=False)

    ## 返回数据
    return outputStr
Ejemplo n.º 35
0
def gen_alert_sqlserver_mirror(server_id, mirror_role):
    if g_alert != "1":
        return -1

    sql = """SELECT a.server_id,
									a.connect,
									a.create_time,
									a.host,
									a.port,
									b.send_mail,
									b.send_mail_to_list,
									b.send_sms,
									b.send_sms_to_list,
									b.send_wx,
									b.tags,
									'sqlserver' AS db_type
								FROM sqlserver_status a, db_cfg_sqlserver b
								WHERE a.server_id = b.id 
									and a.server_id = %s """ % (server_id)
    result = func.mysql_query(sql)
    if result <> 0:
        for line in result:
            server_id = line[0]
            connect = line[1]
            create_time = line[2]
            host = line[3]
            port = line[4]
            send_mail = line[5]
            send_mail_to_list = line[6]
            send_sms = line[7]
            send_sms_to_list = line[8]
            send_wx = line[9]
            tags = line[10]
            db_type = line[11]

            if send_mail_to_list is None or send_mail_to_list.strip() == '':
                send_mail_to_list = mail_to_list_common
            if send_sms_to_list is None or send_sms_to_list.strip() == '':
                send_sms_to_list = sms_to_list_common

            if mirror_role == 1:
                send_mail = func.update_send_mail_status(
                    server_id, db_type, 'mirroring_role', send_mail,
                    send_mail_max_count)
                send_sms = func.update_send_sms_status(server_id, db_type,
                                                       'mirroring_role',
                                                       send_sms,
                                                       send_sms_max_count)
                send_wx = func.update_send_wx_status(server_id, db_type,
                                                     'mirroring_role', send_wx)
                func.add_alert(server_id, tags, host, port, create_time,
                               db_type, 'mirroring_role', mirror_role,
                               'critical', 'Database role is NOT match!',
                               send_mail, send_mail_to_list, send_sms,
                               send_sms_to_list, send_wx)
                func.update_db_status('repl', 3, server_id, host, db_type,
                                      create_time, 'mirroring_role',
                                      mirror_role, 'critical')
            else:
                func.check_if_ok(server_id, tags, host, port, create_time,
                                 db_type, 'mirroring_role', mirror_role,
                                 'Database role is OK!', send_mail,
                                 send_mail_to_list, send_sms, send_sms_to_list,
                                 send_wx)
                func.update_db_status('repl', 1, server_id, host, db_type,
                                      create_time, 'mirroring_role',
                                      mirror_role, 'ok')
    else:
        pass
Ejemplo n.º 36
0
def check_redis(host, port, passwd, server_id, tags):
    try:
        r = redis.StrictRedis(host=host,
                              port=int(port),
                              password=passwd,
                              db=0,
                              socket_timeout=3,
                              encoding='utf-8')
        info = r.info()
        time.sleep(1)
        info_2 = r.info()
        # Server
        redis_version = info['redis_version']
        redis_git_sha1 = info['redis_git_sha1']
        redis_git_dirty = info['redis_git_dirty']
        arch_bits = info['arch_bits']
        multiplexing_api = info['multiplexing_api']
        gcc_version = info['gcc_version']
        process_id = info['process_id']
        uptime_in_seconds = info['uptime_in_seconds']
        uptime_in_days = info['uptime_in_days']
        lru_clock = info['lru_clock']
        os = check_value(info, 'os')
        redis_mode = check_value(info, 'redis_mode')
        hz = check_value(info, 'hz')
        run_id = check_value(info, 'run_id')
        tcp_port = check_value(info, 'tcp_port')

        # Clients
        connected_clients = info['connected_clients']
        client_longest_output_list = info['client_longest_output_list']
        client_biggest_input_buf = info['client_biggest_input_buf']
        blocked_clients = info['blocked_clients']
        # Memory
        used_memory = info['used_memory']
        used_memory_human = info['used_memory_human']
        used_memory_rss = info['used_memory_rss']
        used_memory_peak = info['used_memory_peak']
        used_memory_peak_human = info['used_memory_peak_human']
        used_memory_lua = check_value(info, 'used_memory_lua')
        mem_fragmentation_ratio = info['mem_fragmentation_ratio']
        mem_allocator = info['mem_allocator']
        # Persistence
        loading = info['loading']
        rdb_changes_since_last_save = check_value(
            info, 'rdb_changes_since_last_save')
        rdb_bgsave_in_progress = check_value(info, 'rdb_bgsave_in_progress')
        rdb_last_save_time = check_value(info, 'rdb_last_save_time')
        rdb_last_bgsave_status = check_value(info, 'rdb_last_bgsave_status')
        rdb_last_bgsave_time_sec = check_value(info,
                                               'rdb_last_bgsave_time_sec')
        rdb_current_bgsave_time_sec = check_value(
            info, 'rdb_current_bgsave_time_sec')
        aof_enabled = check_value(info, 'aof_enabled')
        aof_rewrite_in_progress = check_value(info, 'aof_rewrite_in_progress')
        aof_rewrite_scheduled = check_value(info, 'aof_rewrite_scheduled')
        aof_last_rewrite_time_sec = check_value(info,
                                                'aof_last_rewrite_time_sec')
        aof_current_rewrite_time_sec = check_value(
            info, 'aof_current_rewrite_time_sec')
        aof_last_bgrewrite_status = check_value(info,
                                                'aof_last_bgrewrite_status')
        # Stats
        total_connections_received = check_value(info,
                                                 'total_connections_received')
        total_commands_processed = check_value(info,
                                               'total_commands_processed')
        current_commands_processed = int(info_2['total_commands_processed'] -
                                         info['total_commands_processed'])
        instantaneous_ops_per_sec = check_value(info,
                                                'instantaneous_ops_per_sec')
        rejected_connections = check_value(info, 'rejected_connections')

        expired_keys = info['expired_keys']
        evicted_keys = info['evicted_keys']
        keyspace_hits = info['keyspace_hits']
        keyspace_misses = info['keyspace_misses']
        pubsub_channels = info['pubsub_channels']
        pubsub_patterns = info['pubsub_patterns']
        latest_fork_usec = info['latest_fork_usec']
        # Replication
        role = info['role']
        connected_slaves = info['connected_slaves']

        # CPU
        used_cpu_sys = info['used_cpu_sys']
        used_cpu_user = info['used_cpu_user']
        used_cpu_sys_children = info['used_cpu_sys_children']
        used_cpu_user_children = info['used_cpu_user_children']

        # replication
        if role == 'slave':
            #print info
            master_host = info['master_host']
            master_port = info['master_port']
            master_link_status = info['master_link_status']
            master_last_io_seconds_ago = info['master_last_io_seconds_ago']
            master_sync_in_progress = info['master_sync_in_progress']
            #slave_repl_offset = info['slave_repl_offset']
            slave_priority = check_value(info, 'slave_priority')
            slave_read_only = check_value(info, 'slave_read_only')
            master_server_id = func.mysql_query(
                "SELECT id FROM db_servers_redis WHERE host='%s' AND port='%s' limit 1;"
                % (master_host, master_port))
            master_server_id = master_server_id[0][0]
            role_new = 's'
        else:
            master_host = '-1'
            master_port = '-1'
            master_link_status = '-1'
            master_last_io_seconds_ago = '-1'
            master_sync_in_progress = '-1'
            #slave_repl_offset = '---'
            slave_priority = '-1'
            slave_read_only = '-1'
            master_server_id = '-1'
            role_new = 'm'

        #add redis_status
        connect = 1
        sql = "insert into redis_status(server_id,host,port,tags,redis_role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        param = (server_id, host, port, tags, role, connect, redis_version,
                 redis_git_sha1, redis_git_dirty, redis_mode, os, arch_bits,
                 multiplexing_api, gcc_version, process_id, run_id, tcp_port,
                 uptime_in_seconds, uptime_in_days, hz, lru_clock,
                 connected_clients, client_longest_output_list,
                 client_biggest_input_buf, blocked_clients, used_memory,
                 used_memory_human, used_memory_rss, used_memory_peak,
                 used_memory_peak_human, used_memory_lua,
                 mem_fragmentation_ratio, mem_allocator, loading,
                 rdb_changes_since_last_save, rdb_bgsave_in_progress,
                 rdb_last_save_time, rdb_last_bgsave_status,
                 rdb_last_bgsave_time_sec, rdb_current_bgsave_time_sec,
                 aof_enabled, aof_rewrite_in_progress, aof_rewrite_scheduled,
                 aof_last_rewrite_time_sec, aof_current_rewrite_time_sec,
                 aof_last_bgrewrite_status, total_connections_received,
                 total_commands_processed, current_commands_processed,
                 instantaneous_ops_per_sec, rejected_connections, expired_keys,
                 evicted_keys, keyspace_hits, keyspace_misses, pubsub_channels,
                 pubsub_patterns, latest_fork_usec, used_cpu_sys,
                 used_cpu_user, used_cpu_sys_children, used_cpu_user_children)
        func.mysql_exec(sql, param)

        #add redis_replication
        sql_1 = "insert into redis_replication(server_id,tags,host,port,role,master_server_id,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_sync_in_progress,slave_priority,slave_read_only,connected_slaves) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        param_1 = (server_id, tags, host, port, role, master_server_id,
                   master_host, master_port, master_link_status,
                   master_last_io_seconds_ago, master_sync_in_progress,
                   slave_priority, slave_read_only, connected_slaves)
        func.mysql_exec(sql_1, param_1)
        func.update_db_status_init(role_new, redis_version, host, port, tags)

    except Exception, e:
        logger_msg = "check redis %s:%s : %s" % (host, port, e)
        logger.warning(logger_msg)

        try:
            connect = 0
            sql = "insert into redis_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)"
            param = (server_id, host, port, tags, connect)
            func.mysql_exec(sql, param)

        except Exception, e:
            logger.error(e)
            sys.exit(1)
Ejemplo n.º 37
0
def gen_alert_sqlserver_status(server_id):
    if g_alert != "1":
        return -1

    sql = """SELECT a.server_id,
									a.connect,
									a.processes,
									a.processes_running,
									a.processes_waits,
									a.create_time,
									a.host,
									a.port,
									b.alarm_processes,
									b.alarm_processes_running,
									alarm_processes_waits,
									b.threshold_warning_processes,
									b.threshold_warning_processes_running,
									b.threshold_warning_processes_waits,
									b.threshold_critical_processes,
									threshold_critical_processes_running,
									threshold_critical_processes_waits,
									b.send_mail,
									b.send_mail_to_list,
									b.send_sms,
									b.send_sms_to_list,
									b.send_wx,
									b.tags,
									'sqlserver' AS db_type
								FROM sqlserver_status a, db_cfg_sqlserver b
								WHERE a.server_id = b.id 
									and a.server_id = %s """ % (server_id)
    result = func.mysql_query(sql)
    if result <> 0:
        for line in result:
            server_id = line[0]
            connect = line[1]
            processes = line[2]
            processes_running = line[3]
            processes_waits = line[4]
            create_time = line[5]
            host = line[6]
            port = line[7]
            alarm_processes = line[8]
            alarm_processes_running = line[9]
            alarm_processes_waits = line[10]
            threshold_warning_processes = line[11]
            threshold_warning_processes_running = line[12]
            threshold_warning_processes_waits = line[13]
            threshold_critical_processes = line[14]
            threshold_critical_processes_running = line[15]
            threshold_critical_processes_waits = line[16]
            send_mail = line[17]
            send_mail_to_list = line[18]
            send_sms = line[19]
            send_sms_to_list = line[20]
            send_wx = line[21]
            tags = line[22]
            db_type = line[23]

            if send_mail_to_list is None or send_mail_to_list.strip() == '':
                send_mail_to_list = mail_to_list_common
            if send_sms_to_list is None or send_sms_to_list.strip() == '':
                send_sms_to_list = sms_to_list_common
            if connect <> 1:
                send_mail = func.update_send_mail_status(
                    server_id, db_type, 'connect', send_mail,
                    send_mail_max_count)
                send_sms = func.update_send_sms_status(server_id, db_type,
                                                       'connect', send_sms,
                                                       send_sms_max_count)
                send_wx = func.update_send_wx_status(server_id, db_type,
                                                     'connect', send_wx)
                func.add_alert(server_id, tags, host, port, create_time,
                               db_type, 'connect', 'down', 'critical',
                               'sqlserver server down', send_mail,
                               send_mail_to_list, send_sms, send_sms_to_list,
                               send_wx)
                func.update_db_status('connect', '3', server_id, host, db_type,
                                      create_time, 'connect', 'down',
                                      'critical')
                func.update_db_status('sessions', '-1', server_id, host,
                                      db_type, '', '', '', '')
                func.update_db_status('actives', '-1', server_id, host,
                                      db_type, '', '', '', '')
                func.update_db_status('waits', '-1', server_id, host, db_type,
                                      '', '', '', '')
                func.update_db_status('repl', '-1', server_id, host, db_type,
                                      '', '', '', '')
                func.update_db_status('repl_delay', '-1', server_id, host,
                                      db_type, '', '', '', '')
            else:

                func.check_if_ok(server_id, tags, host, port, create_time,
                                 db_type, 'connect', 'up',
                                 'sqlserver server up', send_mail,
                                 send_mail_to_list, send_sms, send_sms_to_list,
                                 send_wx)
                func.update_db_status('connect', '1', server_id, host, db_type,
                                      create_time, 'connect', 'up', 'ok')
                if int(alarm_processes) == 1:
                    if int(processes) >= int(threshold_critical_processes):
                        #send_mail = func.update_send_mail_status(server_id,db_type,'processes',send_mail,send_mail_max_count)
                        #send_sms = func.update_send_sms_status(server_id,db_type,'processes',send_sms,send_sms_max_count)
                        #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes',processes,'critical','too many processes',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx)
                        func.update_db_status('sessions', 3, server_id, host,
                                              db_type, create_time,
                                              'processes', processes,
                                              'critical')
                    elif int(processes) >= int(threshold_warning_processes):
                        #send_mail = func.update_send_mail_status(server_id,db_type,'processes',send_mail,send_mail_max_count)
                        #send_sms = func.update_send_sms_status(server_id,db_type,'processes',send_sms,send_sms_max_count)
                        #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes',processes,'warning','too many processes',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx)
                        func.update_db_status('sessions', 2, server_id, host,
                                              db_type, create_time,
                                              'processes', processes,
                                              'warning')
                    else:
                        func.update_db_status('sessions', 1, server_id, host,
                                              db_type, create_time,
                                              'processes', processes, 'ok')
                        func.check_if_ok(server_id, tags, host, port,
                                         create_time, db_type, 'processes',
                                         processes, 'processes ok', send_mail,
                                         send_mail_to_list, send_sms,
                                         send_sms_to_list, send_wx)

                if int(alarm_processes_running) == 1:
                    if int(processes_running) >= int(
                            threshold_critical_processes_running):
                        #send_mail = func.update_send_mail_status(server_id,db_type,'processes_running',send_mail,send_mail_max_count)
                        #send_sms = func.update_send_sms_status(server_id,db_type,'processes_running',send_sms,send_sms_max_count)
                        #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes_running',processes_running,'critical','too many processes running',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx)
                        func.update_db_status('actives', 3, server_id, host,
                                              db_type, create_time,
                                              'processes_running',
                                              processes_running, 'critical')
                    elif int(processes_running) >= int(
                            threshold_warning_processes_running):
                        #send_mail = func.update_send_mail_status(server_id,db_type,'processes_running',send_mail,send_mail_max_count)
                        #send_sms = func.update_send_sms_status(server_id,db_type,'processes_running',send_sms,send_sms_max_count)
                        #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes_running',processes_running,'critical','too many processes running',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx)
                        func.update_db_status('actives', 2, server_id, host,
                                              db_type, create_time,
                                              'processes_running',
                                              processes_running, 'warning')
                    else:
                        func.update_db_status('actives', 1, server_id, host,
                                              db_type, create_time,
                                              'processes_running',
                                              processes_running, 'ok')
                        func.check_if_ok(server_id, tags, host, port,
                                         create_time, db_type,
                                         'processes_running',
                                         processes_running,
                                         'processes running ok', send_mail,
                                         send_mail_to_list, send_sms,
                                         send_sms_to_list, send_wx)

                if int(alarm_processes_waits) == 1:
                    if int(processes_waits) >= int(
                            threshold_critical_processes_waits):
                        #send_mail = func.update_send_mail_status(server_id,db_type,'processes_waits',send_mail,send_mail_max_count)
                        #send_sms = func.update_send_sms_status(server_id,db_type,'processes_waits',send_sms,send_sms_max_count)
                        #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes_waits',processes_waits,'critical','too many processes waits',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx)
                        func.update_db_status('waits', 3, server_id, host,
                                              db_type, create_time,
                                              'processes_waits',
                                              processes_waits, 'critical')
                    elif int(processes_waits) >= int(
                            threshold_warning_processes_waits):
                        #send_mail = func.update_send_mail_status(server_id,db_type,'processes_waits',send_mail,send_mail_max_count)
                        #send_sms = func.update_send_sms_status(server_id,db_type,'processes_waits',send_sms,send_sms_max_count)
                        #func.add_alert(server_id,tags,host,port,create_time,db_type,'processes_waits',processes_waits,'warning','too many processes waits',send_mail,send_mail_to_list,send_sms,send_sms_to_list,send_wx)
                        func.update_db_status('waits', 2, server_id, host,
                                              db_type, create_time,
                                              'processes_waits',
                                              processes_waits, 'warning')
                    else:
                        func.update_db_status('waits', 1, server_id, host,
                                              db_type, create_time,
                                              'processes_waits',
                                              processes_waits, 'ok')
                        func.check_if_ok(server_id, tags, host, port,
                                         create_time, db_type,
                                         'processes_waits', processes_waits,
                                         'processes waits ok', send_mail,
                                         send_mail_to_list, send_sms,
                                         send_sms_to_list, send_wx)

    else:
        pass
Ejemplo n.º 38
0
def check_redis(host,port,passwd,server_id,tags):
    try:
        r=redis.StrictRedis(host=host,port=port,password=passwd,db=0,socket_timeout=3,charset='utf-8') 
        info=r.info()
        time.sleep(1)
        info_2=r.info()
        # Server
        redis_version = info['redis_version']
        redis_git_sha1 = info['redis_git_sha1']
        redis_git_dirty = info['redis_git_dirty']
        arch_bits = info['arch_bits']
        multiplexing_api = info['multiplexing_api']
        gcc_version = info['gcc_version']
        process_id = info['process_id']
        uptime_in_seconds = info['uptime_in_seconds']
        uptime_in_days = info['uptime_in_days']
        lru_clock = info['lru_clock']
        os = check_value(info,'os')
        redis_mode = check_value(info,'redis_mode')
        hz = check_value(info,'hz')
        run_id = check_value(info,'run_id')
        tcp_port = check_value(info,'tcp_port')

        # Clients 
        connected_clients = info['connected_clients']
        client_longest_output_list = info['client_longest_output_list']
        client_biggest_input_buf = info['client_biggest_input_buf']
        blocked_clients = info['blocked_clients']
        # Memory
        used_memory = info['used_memory']
        used_memory_human = info['used_memory_human']
        used_memory_rss = info['used_memory_rss']
        used_memory_peak = info['used_memory_peak']
        used_memory_peak_human = info['used_memory_peak_human']
        used_memory_lua = check_value(info,'used_memory_lua')
        mem_fragmentation_ratio = info['mem_fragmentation_ratio']
        mem_allocator = info['mem_allocator']
        # Persistence
        loading = info['loading']
        rdb_changes_since_last_save = check_value(info,'rdb_changes_since_last_save')
        rdb_bgsave_in_progress = check_value(info,'rdb_bgsave_in_progress')
        rdb_last_save_time = check_value(info,'rdb_last_save_time')
        rdb_last_bgsave_status = check_value(info,'rdb_last_bgsave_status')
        rdb_last_bgsave_time_sec = check_value(info,'rdb_last_bgsave_time_sec')
        rdb_current_bgsave_time_sec = check_value(info,'rdb_current_bgsave_time_sec')
        aof_enabled = check_value(info,'aof_enabled')
        aof_rewrite_in_progress = check_value(info,'aof_rewrite_in_progress')
        aof_rewrite_scheduled = check_value(info,'aof_rewrite_scheduled')
        aof_last_rewrite_time_sec = check_value(info,'aof_last_rewrite_time_sec')
        aof_current_rewrite_time_sec = check_value(info,'aof_current_rewrite_time_sec')
        aof_last_bgrewrite_status = check_value(info,'aof_last_bgrewrite_status')
        # Stats
        total_connections_received = check_value(info,'total_connections_received')
        total_commands_processed = check_value(info,'total_commands_processed')
        current_commands_processed = int(info_2['total_commands_processed'] - info['total_commands_processed'])
        instantaneous_ops_per_sec = check_value(info,'instantaneous_ops_per_sec')
        rejected_connections = check_value(info,'rejected_connections')

        expired_keys = info['expired_keys']
        evicted_keys = info['evicted_keys']
        keyspace_hits = info['keyspace_hits']
        keyspace_misses = info['keyspace_misses']
        pubsub_channels = info['pubsub_channels']
        pubsub_patterns = info['pubsub_patterns']
        latest_fork_usec = info['latest_fork_usec']
        # Replication
        role = info['role']
        connected_slaves = info['connected_slaves']
        
        # CPU
        used_cpu_sys = info['used_cpu_sys']
        used_cpu_user = info['used_cpu_user']
        used_cpu_sys_children = info['used_cpu_sys_children']
        used_cpu_user_children = info['used_cpu_user_children']

        # replication
        if role == 'slave':
           #print info
           master_host = info['master_host']
           master_port = info['master_port']
           master_link_status = info['master_link_status']
           master_last_io_seconds_ago = info['master_last_io_seconds_ago']
           master_sync_in_progress = info['master_sync_in_progress']
           #slave_repl_offset = info['slave_repl_offset']
           slave_priority = check_value(info,'slave_priority')
           slave_read_only = check_value(info,'slave_read_only')
           master_server_id = func.mysql_query("SELECT id FROM db_servers_redis WHERE host='%s' AND port='%s' limit 1;" %(master_host,master_port))
           master_server_id = master_server_id[0][0]
           role_new='s'
        else:
           master_host = '-1'
           master_port = '-1'
           master_link_status= '-1'
           master_last_io_seconds_ago = '-1'
           master_sync_in_progress = '-1'
           #slave_repl_offset = '---'
           slave_priority = '-1'
           slave_read_only = '-1'
           master_server_id = '-1'
           role_new='m'

        #add redis_status
        connect=1
        sql = "insert into redis_status(server_id,host,port,tags,redis_role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        param = (server_id,host,port,tags,role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children)
        func.mysql_exec(sql,param)

        #add redis_replication
        sql_1 = "insert into redis_replication(server_id,tags,host,port,role,master_server_id,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_sync_in_progress,slave_priority,slave_read_only,connected_slaves) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        param_1 = (server_id,tags,host,port,role,master_server_id,master_host,master_port,master_link_status,master_last_io_seconds_ago,master_sync_in_progress,slave_priority,slave_read_only,connected_slaves)
        func.mysql_exec(sql_1,param_1)
        func.update_db_status_init(role_new,redis_version,host,port,tags)

    except Exception, e:
        logger_msg="check redis %s:%s : %s" %(host,port,e)
        logger.warning(logger_msg)
   
        try:
            connect=0
            sql="insert into redis_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)"
            param=(server_id,host,port,tags,connect)
            func.mysql_exec(sql,param)

        except Exception, e:
            logger.error(e)
            sys.exit(1)
Ejemplo n.º 39
0
        ##################### insert data to mysql server#############################
        sql = "insert into oracle_status(server_id,host,port,tags,connect,db_name, instance_name,instance_role,instance_status,database_role,open_mode,protection_mode,host_name,database_status,startup_time,uptime,version,archiver,session_total,session_actives,session_waits,dg_stats,dg_delay,processes,session_logical_reads_persecond,physical_reads_persecond,physical_writes_persecond,physical_read_io_requests_persecond,physical_write_io_requests_persecond,db_block_changes_persecond,os_cpu_wait_time,logons_persecond,logons_current,opened_cursors_persecond,opened_cursors_current,user_commits_persecond,user_rollbacks_persecond,user_calls_persecond,db_block_gets_persecond) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        param = (server_id,host,port,tags,connect,db_name,instance_name,instance_role,instance_status,database_role,open_mode,protection_mode,host_name,database_status,startup_time,uptime,version,archiver,session_total,session_actives,session_waits,dg_stats,dg_delay,processes,session_logical_reads_persecond,physical_reads_persecond,physical_writes_persecond,physical_read_io_requests_persecond,physical_write_io_requests_persecond,db_block_changes_persecond,os_cpu_wait_time,logons_persecond,logons_current,opened_cursors_persecond,opened_cursors_current,user_commits_persecond,user_rollbacks_persecond,user_calls_persecond,db_block_gets_persecond)
        func.mysql_exec(sql,param) 
        func.update_db_status_init(database_role_new,version,host,port,tags)

        #check tablespace
        tablespace = oracle.get_tablespace(conn)
        if tablespace:
           for line in tablespace:
              sql="insert into oracle_tablespace(server_id,host,port,tags,tablespace_name,total_size,used_size,avail_size,used_rate) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
              param=(server_id,host,port,tags,line[0],line[1],line[2],line[3],line[4])
              func.mysql_exec(sql,param)
              
        #check dataguard status
        result = func.mysql_query("select count(1) from db_servers_oracle_dg where primary_db_id = '%s' or standby_db_id = '%s'" %(server_id, server_id))
        if result:
            is_dg = result[0][0]

        if is_dg > 0:
            if database_role == 'PRIMARY':  
                dg_p_info = oracle.get_dg_p_info(conn, 1)
                
                dest_id = -1
                thread = -1
                sequence = -1
                archived = -1
                applied = -1
                current_scn = -1
                curr_db_time = ""
                if dg_p_info:
def main():
    #host = "192.168.1.175"
    doHosts = ["172.21.100.40", "172.21.100.121", "172.21.100.122"]
    doHosts = ["172.21.100.88"]

    qSql = '''
	SELECT d.id,d.host,d.port,d.dsn,d.tags,s.id FROM db_servers_oracle d
	LEFT JOIN oracle_status s ON d.`host`=s.`host`AND d.`port`=s.`port` AND s.`database_role`='PRIMARY'
	WHERE is_delete=0 AND monitor=1 AND awrreport=1
	HAVING s.id IS NOT NULL
	'''
    servers = func.mysql_query(qSql)
    logger.info("check oracle awsreport started.")

    cnfKey = "monitor_oracle"
    username = func.get_config(cnfKey, 'user')
    password = func.get_config(cnfKey, 'passwd')
    errSrvs = []
    if servers:
        for row in servers:
            (server_id, host, port, dsn, tags, stid) = row
            if doHosts.count(host) <= 0:
                #continue
                pass
            logger.info("AwrReport:%s,%s,%s" % (host, port, dsn))
            (isOk, reports) = createReport(host, port, dsn, username, password)
            if not isOk:
                logger.error("Err createReport:%s,%s,%s" % (host, port, dsn))
                errSrvs.append([server_id, host, port, tags])
                continue

            for report in reports:
                (host, port, instance_num, statTime, textReportFile,
                 htmlReportFile) = report
                sectors = parseAwrReport(textReportFile)

                ## LoadProfile
                lpVals = parseLoadProfile(sectors[SECTOR_LoadProfile])
                logger.info("parseLoadProfile OK")
                # save db
                saveLoadProfile(statTime, server_id, host, port, tags,
                                instance_num, lpVals)

                ## TOP SQL
                topSqls = parseTopsql(sectors)
                logger.info("parseTopsql OK")
                # save db
                saveTopsql(statTime, server_id, host, port, tags, instance_num,
                           topSqls)

    #
    doTopsqlSummary()

    # check err
    db_type = "oracle"
    create_time = datetime.datetime.now()
    alarm_item = "oracle AwrReport"
    alarm_value = "Fail"
    level = "warning"
    message = ""
    for errSrv in errSrvs:
        (server_id, db_host, db_port, tags) = errSrv
        func.add_alarm(server_id, tags, db_host, db_port, create_time, db_type,
                       alarm_item, alarm_value, level, message)