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})
