def mysql_slowquery(request): messageinfo_list = models_frame.TabAlarmInfo.objects.all() tagsinfo = models_mysql.MysqlDb.objects.filter(mon_status='connected') tagsdefault = request.GET.get('tagsdefault') if not tagsdefault: tagsdefault = models_mysql.MysqlDb.objects.filter(mon_status='connected').order_by('tags')[0].tags sql = "select host,port,user,password,user_os,password_os from tab_mysql_servers where tags= '%s' " % tagsdefault mysql_conf = tools.mysql_query(sql) host = mysql_conf[0][0] port = mysql_conf[0][1] user = mysql_conf[0][2] password = mysql_conf[0][3] password = base64.decodestring(password) user_os = mysql_conf[0][4] password_os = mysql_conf[0][5] password_os = base64.decodestring(password_os) conn = MySQLdb.connect(host=host, user=user, passwd=password, port=int(port), connect_timeout=5, charset='utf8') # 获取慢查询日志文件 slow_log_file = check_msql.get_mysql_para(conn,'slow_query_log_file') # 清空历史解析结果 sql = "delete from mysql_slowquery where tags='%s' " %tagsdefault tools.mysql_exec(sql,'') # 解析慢查询日志 logparser.mysql_slow_query(tagsdefault,host,port,user_os,password_os,slow_log_file) # 查询解析结果 sql = """ select id, host,port,start_time,client_host,db_name,substr(sql_text,1,30) sql_text,sql_text sql_text_full,query_time,lock_time,rows_examined,rows_sent from mysql_slowquery where tags='%s' order by start_time desc """ %tagsdefault slow_query_list = tools.mysql_django_query(sql) if request.method == 'POST': if request.POST.has_key('select_tags'): tagsdefault = request.POST.get('select_tags', None).encode("utf-8") return HttpResponseRedirect('/mysql_slowquery?tagsdefault=%s' % ( tagsdefault)) else: logout(request) return HttpResponseRedirect('/login/') if messageinfo_list: msg_num = len(messageinfo_list) msg_last = models_frame.TabAlarmInfo.objects.latest('id') msg_last_content = msg_last.alarm_content tim_last = (datetime.datetime.now() - msg_last.alarm_time).seconds / 60 else: msg_num = 0 msg_last_content = '' tim_last = '' return render(request, 'mysql_mon/mysql_slowquery.html', {'tagsdefault': tagsdefault, 'tagsinfo':tagsinfo, 'msg_num':msg_num, 'msg_last_content':msg_last_content, 'tim_last':tim_last, 'slow_query_list':slow_query_list})
def oracle_grant(request): messageinfo_list = models_frame.TabAlarmInfo.objects.all() tags = request.GET.get('tags') print tags username = request.GET.get('username') sql = "select host,port,service_name,user,password,user_os,password_os from tab_oracle_servers where tags= '%s' " %tags oracle = tools.mysql_query(sql) host = oracle[0][0] port = oracle[0][1] service_name = oracle[0][2] user = oracle[0][3] password = oracle[0][4] password = base64.decodestring(password) url = host + ':' + port + '/' + service_name # 角色权限 sql = """ select grantee, granted_role, admin_option, default_role, to_char(sysdate, 'yyyy-mm-dd') get_date from dba_role_privs where grantee = '%s' """ %username user_roles = tools.oracle_django_query(user,password,url,sql) # 系统权限 sql = """ select grantee,privilege,admin_option,to_char(sysdate,'yyyy-mm-dd') get_date from dba_sys_privs where grantee = '%s' """ %username sys_privs = tools.oracle_django_query(user,password,url,sql) # 对象权限 sql = """ select owner,grantee,grantor,table_name,privilege ,grantable,hierarchy,to_char(sysdate,'yyyy-mm-dd') get_date from dba_tab_privs where grantee <> 'PUBLIC' and privilege <> 'EXECUTE' and grantee = '%s' """ %username tab_privs = tools.oracle_django_query(user,password,url,sql) now = tools.now() if request.method == 'POST': logout(request) return HttpResponseRedirect('/login/') if messageinfo_list: msg_num = len(messageinfo_list) msg_last = models_frame.TabAlarmInfo.objects.latest('id') msg_last_content = msg_last.alarm_content tim_last = (datetime.datetime.now() - msg_last.alarm_time).seconds / 60 else: msg_num = 0 msg_last_content = '' tim_last = '' return render_to_response('oracle_grant.html', {'messageinfo_list': messageinfo_list, 'msg_num':msg_num,'user_roles': user_roles,'sys_privs':sys_privs, 'tab_privs':tab_privs,'username':username,'tags': tags,'now': now, 'msg_last_content': msg_last_content, 'tim_last': tim_last})
def oracle_profile(request): messageinfo_list = models_frame.TabAlarmInfo.objects.all() tags = request.GET.get('tags') profile_name = request.GET.get('profile_name') sql = "select host,port,service_name,user,password,user_os,password_os from tab_oracle_servers where tags= '%s' " % tags oracle = tools.mysql_query(sql) host = oracle[0][0] port = oracle[0][1] service_name = oracle[0][2] user = oracle[0][3] password = oracle[0][4] password = base64.decodestring(password) url = host + ':' + port + '/' + service_name sql = """ select profile,resource_name,resource_type,limit,to_char(sysdate,'yyyy-mm-dd') get_date from dba_profiles where profile = '%s' """ % profile_name oracle_profiles = tools.oracle_django_query(user, password, url, sql) now = tools.now() if request.method == 'POST': logout(request) return HttpResponseRedirect('/login/') if messageinfo_list: msg_num = len(messageinfo_list) msg_last = models_frame.TabAlarmInfo.objects.latest('id') msg_last_content = msg_last.alarm_content tim_last = (datetime.datetime.now() - msg_last.alarm_time).seconds / 60 else: msg_num = 0 msg_last_content = '' tim_last = '' return render_to_response( 'oracle_profile.html', { 'messageinfo_list': messageinfo_list, 'msg_num': msg_num, 'oracle_profiles': oracle_profiles, 'profile_name': profile_name, 'tags': tags, 'now': now, 'msg_last_content': msg_last_content, 'tim_last': tim_last })
def get_mysql_errorlog(request): tags = request.GET.get('tags') sql = '''select host,port,user,password,user_os,password_os,ssh_port_os,logfile from tab_mysql_servers where tags='%s' ''' % tags mysqlinfo = tools.mysql_query(sql) host,port,user,password,user_os,password_os,ssh_port_os,logfile = mysqlinfo[0] password = base64.decodestring(password) password_os = base64.decodestring(password_os) if not logfile: # 后台日志参数 conn = MySQLdb.connect(host=host, user=user, passwd=password, port=int(port), connect_timeout=5, charset='utf8') logfile = tools.get_mysql_para(conn, 'log_error') ssh_client = paramiko.SSHClient() ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) ssh_client.connect(host, ssh_port_os, user_os, password_os) command = 'tail -300 %s' %logfile std_in, std_out, std_err = ssh_client.exec_command(command) errorlog = std_out.read() return render_to_response('frame/show_log.html', {'tags':tags,'log':errorlog})
def show_oracle_resource(request): messageinfo_list = models_frame.TabAlarmInfo.objects.all() tagsinfo = models_oracle.OracleDb.objects.filter(mon_status='connected') tagsdefault = request.GET.get('tagsdefault') if not tagsdefault: tagsdefault = models_oracle.OracleDb.objects.filter( mon_status='connected').order_by('tags')[0].tags typedefault = request.GET.get('typedefault') redo_range_default = request.GET.get('redo_range_default') if not redo_range_default: redo_range_default = 7 tbsinfo_list = models_oracle.OracleTbs.objects.filter( tags=tagsdefault).order_by('-pct_used') # 分页 paginator_tbs = Paginator(tbsinfo_list, 5) undotbsinfo_list = models_oracle.OracleUndoTbs.objects.filter( tags=tagsdefault).order_by('-pct_used') paginator_undo = Paginator(undotbsinfo_list, 5) tmptbsinfo_list = models_oracle.OracleTmpTbs.objects.filter( tags=tagsdefault).order_by('-pct_used') paginator_tmp = Paginator(tmptbsinfo_list, 5) page_tbs = request.GET.get('page_tbs') try: tbsinfos = paginator_tbs.page(page_tbs) except PageNotAnInteger: # If page is not an integer, deliver first page. tbsinfos = paginator_tbs.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. tbsinfos = paginator_tbs.page(paginator_tbs.num_pages) page_undo = request.GET.get('page_undo') try: undotbsinfos = paginator_undo.page(page_undo) except PageNotAnInteger: # If page is not an integer, deliver first page. undotbsinfos = paginator_undo.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. undotbsinfos = paginator_undo.page(paginator_undo.num_pages) page_tmp = request.GET.get('page_tmp') try: tmptbsinfos = paginator_undo.page(page_tmp) except PageNotAnInteger: # If page is not an integer, deliver first page. tmptbsinfos = paginator_tmp.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. tmptbsinfos = paginator_tmp.page(paginator_tmp.num_pages) # 获取控制文件信息 sql = "select host,port,service_name,user,password,user_os,password_os from tab_oracle_servers where tags= '%s' " % tagsdefault oracle = tools.mysql_query(sql) host = oracle[0][0] port = oracle[0][1] service_name = oracle[0][2] user = oracle[0][3] password = oracle[0][4] password = base64.decodestring(password) url = host + ':' + port + '/' + service_name sql = """ select name,round(block_size*file_size_blks/1024/1024,2) size_M,'controlfile' tpye from v$controlfile """ oracle_controlfiles = tools.oracle_django_query(user, password, url, sql) # 获取在线日志 sql = """ select a.GROUP# group_no,b.THREAD# thread_no,a.TYPE,b.SEQUENCE# sequence_no,b.BYTES/1024/1024 SIZE_M,b.ARCHIVED,b.STATUS,a.MEMBER from v$logfile a,v$log b where a.GROUP#=b.GROUP#(+) """ oracle_redo_files = tools.oracle_django_query(user, password, url, sql) # 在线日志统计 if redo_range_default == '1': sql = """ select 'hh'||to_char(first_time, 'hh24') stat_date, count(1) log_count, (select bytes / 1024 / 1024 sizem from v$log where rownum < 2) log_size from v$log_history where to_char(first_time, 'yyyymmdd') < to_char(sysdate, 'yyyymmdd') and to_char(first_time, 'yyyymmdd') >= to_char(sysdate - 1, 'yyyymmdd') group by to_char(first_time, 'hh24'),to_char(first_time, 'dy') order by to_char(first_time, 'hh24') """ oracle_redo_cnts = tools.oracle_django_query(user, password, url, sql) else: sql = """ select to_char(first_time, 'yyyy-mm-dd') stat_date, count(1) log_count, (select bytes / 1024 / 1024 sizem from v$log where rownum < 2) log_size from v$log_history where to_char(first_time, 'yyyymmdd') < to_char(sysdate, 'yyyymmdd') and to_char(first_time, 'yyyymmdd') >= to_char(sysdate-%s, 'yyyymmdd') group by to_char(first_time, 'yyyy-mm-dd'), to_char(first_time, 'dy') order by to_char(first_time, 'yyyy-mm-dd')""" % redo_range_default oracle_redo_cnts = tools.oracle_django_query(user, password, url, sql) # 表变化记录 sql = """ select table_owner,table_name,inss,upds,dels, to_char(inss + upds + dels) dmls,to_char(sysdate , 'yyyy-mm-dd') get_date,truncated,num_rows, to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') last_analyzed from (select m.table_owner, m.table_name, inserts as inss, updates as upds, deletes as dels, truncated, t.num_rows,t.last_analyzed from sys.dba_tab_modifications m, dba_tables t where m.table_name = t.table_name and t.owner not in ('SYS','SYSTEM','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS', 'CTXSYS','ANONYMOUS','XDB','XS$NULL','ORDDATA','SI_INFORMTN_SCHEMA','ORDPLUGINS','ORDSYS','MDSYS','OLAPSYS', 'MDDATA','SPATIAL_WFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','APEX_030200','FLOWS_FILES', 'APEX_PUBLIC_USER','OWBSYS','OWBSYS_AUDIT','SCOTT') and m.table_owner = t.owner and m.partition_name is null) """ oracle_table_changes = tools.oracle_django_query(user, password, url, sql) # 序列 sql = """ select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag, cache_size,last_number, round((max_value - last_number) / (max_value - min_value), 2) * 100 pct_used, (case when (round((max_value - last_number) / (max_value - min_value), 2) * 100) > 30 then 'green' when (round((max_value - last_number) / (max_value - min_value), 2) * 100) <= 30 and (round((max_value - last_number) / (max_value - min_value), 2) * 100) > 10 then 'yellow' when (round((max_value - last_number) / (max_value - min_value), 2) * 100) <= 10 then 'red' else '' end) seq_color, to_char(sysdate, 'yyyy-mm-dd') last_analyzed from dba_sequences s where s.sequence_owner not in ('SYS','SYSTEM','OUTLN','DIP','ORACLE_OCM','DBSNMP','APPQOSSYS','WMSYS','EXFSYS', 'CTXSYS','ANONYMOUS','XDB','XS$NULL','ORDDATA','SI_INFORMTN_SCHEMA','ORDPLUGINS','ORDSYS','MDSYS','OLAPSYS', 'MDDATA','SPATIAL_WFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','SYSMAN','MGMT_VIEW','APEX_030200','FLOWS_FILES', 'APEX_PUBLIC_USER','OWBSYS','OWBSYS_AUDIT','SCOTT') """ oracle_sequences = tools.oracle_django_query(user, password, url, sql) # 账号 sql = """ select username,profile,to_char(created,'yyyy-mm-dd hh24:mi:ss') created, account_status, (case when account_status <> 'OPEN' then 'red' else 'green'end ) account_color, to_char(lock_date,'yyyy-mm-dd hh24:mi:ss') lock_date, to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') expiry_date, (case when expiry_date - sysdate > 30 then 'green' when expiry_date - sysdate <= 30 and expiry_date - sysdate > 7 then 'yellow' when expiry_date - sysdate <= 7 then 'red' else '' end) expiry_color,default_tablespace,temporary_tablespace from dba_users """ oracle_users = tools.oracle_django_query(user, password, url, sql) # alert日志 oracle_alert_logs = models_oracle.OracleAlertLog.objects.all().order_by( '-log_time') if request.method == 'POST': if request.POST.has_key('select_tags'): tagsdefault = request.POST.get('select_tags', None).encode("utf-8") return HttpResponseRedirect( '/show_oracle_resource?tagsdefault=%s&redo_range_default=%s' % (tagsdefault, redo_range_default)) else: logout(request) return HttpResponseRedirect('/login/') if messageinfo_list: msg_num = len(messageinfo_list) msg_last = models_frame.TabAlarmInfo.objects.latest('id') msg_last_content = msg_last.alarm_content tim_last = (datetime.datetime.now() - msg_last.alarm_time).seconds / 60 else: msg_num = 0 msg_last_content = '' tim_last = '' return render_to_response( 'show_oracle_res.html', { 'tagsdefault': tagsdefault, 'typedefault': typedefault, 'tagsinfo': tagsinfo, 'msg_num': msg_num, 'msg_last_content': msg_last_content, 'tim_last': tim_last, 'tbsinfos': tbsinfos, 'undotbsinfos': undotbsinfos, 'tmptbsinfos': tmptbsinfos, 'oracle_controlfiles': oracle_controlfiles, 'oracle_redo_files': oracle_redo_files, 'oracle_redo_cnts': oracle_redo_cnts, 'oracle_table_changes': oracle_table_changes, 'oracle_sequences': oracle_sequences, 'oracle_users': oracle_users, 'oracle_alert_logs': oracle_alert_logs })
tmpgrow = models_oracle.OracleTmpTbsHis.objects.filter( tags=tagsdefault, pct_used__isnull=False).filter( chk_time__gt=tmp_begin_time, chk_time__lt=end_time).order_by('-chk_time') tmpgrow_list = list(tmpgrow) tmpgrow_list.reverse() psgrow = models_oracle.OracleDbHis.objects.filter( tags=tagsdefault, qps__isnull=False).filter(chk_time__gt=ps_begin_time, chk_time__lt=end_time).order_by('-chk_time') psgrow_list = list(psgrow) psgrow_list.reverse() # 连接信息 sql = "select host,port,service_name,user,password,user_os,password_os from tab_oracle_servers where tags= '%s' " % tagsdefault oracle = tools.mysql_query(sql) host = oracle[0][0] port = oracle[0][1] service_name = oracle[0][2] user = oracle[0][3] password = oracle[0][4] password = base64.decodestring(password) url = host + ':' + port + '/' + service_name # 基础信息 is_rac = ora_do.get_oracle_para(url, user, password, 'cluster_database') sql = "select flashback_on from v$database" dbinfo = tools.oracle_query(url, user, password, sql) flashback_on = dbinfo[0][0] if request.method == 'POST': if request.POST.has_key('select_tags') or request.POST.has_key(
def mysql_ctl(request): # 告警 messageinfo_list = models_frame.TabAlarmInfo.objects.all() oper_type = request.GET.get('oper_type') host = request.GET.get('host') if oper_type: log_type = 'Mysql启停' sql = '''select user,password from tab_linux_servers where host='%s' ''' % host mysql = tools.mysql_query(sql) user = mysql[0][0] password = mysql[0][1] password = base64.decodestring(password) if oper_type == 'startup': # ora_do.oracle_startup(host, user, password) return HttpResponseRedirect('/mysql_ctl/') elif oper_type == 'shutdown': # ora_do.oracle_shutdown(host, user, password) return HttpResponseRedirect('/mysql_ctl/') else: # ora_do.oracle_shutdown(host, user, password) # ora_do.oracle_startup(host, user, password) return HttpResponseRedirect('/mysql_ctl/') else: # 数据库操作面板 mysql_ctl_sql = '''select t1.tags, t1.host, t1.port, (case t2.mon_status when 'connected' then 'running' else 'suspend' end) run_status, (case t2.mon_status when 'connected' then 'success' else 'danger' end) is_run, (case t2.mon_status when 'connected' then 'red' else 'green' end) run_color, (case t2.mon_status when 'connected' then 'shutdown' else 'startup' end) oper_type from tab_mysql_servers t1 left join mysql_db t2 on t1.tags = t2.tags''' mysql_ctl_list = tools.mysql_django_query(mysql_ctl_sql) paginator_mysql_ctl = Paginator(mysql_ctl_list, 5) page_mysql_ctl = request.GET.get('page_mysql_ctl') try: mysql_ctls = paginator_mysql_ctl.page(page_mysql_ctl) except PageNotAnInteger: # If page is not an integer, deliver first page. mysql_ctls = paginator_mysql_ctl.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. mysql_ctls = paginator_mysql_ctl.page(page_mysql_ctl.num_pages) now = tools.now() if request.method == 'POST': logout(request) return HttpResponseRedirect('/login/') if messageinfo_list: msg_num = len(messageinfo_list) msg_last = models_frame.TabAlarmInfo.objects.latest('id') msg_last_content = msg_last.alarm_content tim_last = (datetime.datetime.now() - msg_last.alarm_time).seconds / 60 return render_to_response('mysql_mon/mysql_ctl.html', {'messageinfo_list': messageinfo_list, 'mysql_ctls': mysql_ctls, 'msg_num': msg_num, 'now': now, 'msg_last_content': msg_last_content, 'tim_last': tim_last}) else: msg_num = 0 msg_last_content = '' tim_last = '' return render_to_response('mysql_mon/mysql_ctl.html', {'messageinfo_list': messageinfo_list, 'mysql_ctls': mysql_ctls, 'now': now, 'msg_last_content': msg_last_content, 'tim_last': tim_last})