def export_slowlog_toexcel(id, name, region, daterange):
accid = 'sssssssssss'
acckey = 'xxxxxxxxxxxxxxxxxx'
clt = client.AcsClient(accid, acckey, region)
request = DescribeSlowLogsRequest.DescribeSlowLogsRequest()
request.set_accept_format('json')
# 设置参数
request.add_query_param('StartTime', daterange)
request.add_query_param('EndTime', daterange)
request.add_query_param('SortKey', 'TotalQueryTimes')
request.add_query_param('DBInstanceId', id)
request.add_query_param('PageSize', 100)
try:
response = clt.do_action_with_exception(request)
json_data = json.loads(response)
#print json_data
rds_sqlslowlog = json_data['Items']['SQLSlowLog']
#print rds_sqlslowlog
rds_engine = json_data['Engine']
#print rds_engine
#print len(rds_sqlslowlog)
if rds_sqlslowlog:
write_slowlog_toexcel(rds_sqlslowlog, rds_engine, name,
import_excel)
else:
return False
except Exception, e:
print e
return False
def GetSlowlog(DBInstanceId, StartTime, EndTime):
Slowlog = DescribeSlowLogsRequest.DescribeSlowLogsRequest()
Slowlog.set_accept_format('json')
Slowlog.set_DBInstanceId(DBInstanceId)
Slowlog.set_StartTime(StartTime)
Slowlog.set_PageSize(100)
# Slowlog.set_SortKey(Sortkey)
# Slowlog.set_StartTime("2017-10-13T15:00Z")
Slowlog.set_EndTime(EndTime)
SlowlogInfo = clt.do_action_with_exception(Slowlog)
Info = (json.loads(SlowlogInfo))
# print Info
PageRecordCount = Info['PageRecordCount']
# print PageRecordCount
if PageRecordCount == 0:
print 0
else:
i = 0
while i < PageRecordCount:
SQL = Info['Items']['SQLSlowLog'][i]['SQLText']
DBNAME = Info['Items']['SQLSlowLog'][i]['DBName']
ExecutionCounts = Info['Items']['SQLSlowLog'][i][
'MySQLTotalExecutionCounts']
MaxExecutionTime = Info['Items']['SQLSlowLog'][i][
'MaxExecutionTime']
MySQLTotalExecutionTimes = Info['Items']['SQLSlowLog'][i][
'MySQLTotalExecutionTimes']
i = i + 1
print "No: %s ,DBNAME: %s ,ExecutionCounts: %s ,MaxExecutionTime: %s ,MySQLTotalExecutionTimes: %s ,SLOWSQL: %s" % (
i, DBNAME, MaxExecutionTime, ExecutionCounts,
MySQLTotalExecutionTimes, SQL)
def DescribeSlowLogs(self, StartTime, EndTime, **kwargs):
"""获取实例慢日志列表DBName,SortKey、PageSize、PageNumber"""
request = DescribeSlowLogsRequest.DescribeSlowLogsRequest()
values = {"action_name": "DescribeSlowLogs", "DBInstanceId": self.DBInstanceId,
"StartTime": StartTime, "EndTime": EndTime, "SortKey": "TotalExecutionCounts"}
values = dict(values, **kwargs)
result = self.request_api(request, values)
return result
def get_DescribeSlowLogRecordsRequest(self, **kwargs):
'用户可以查询某日期范围内、某个用户实例下、某个数据库的慢查询明细，目前支持MySQL、PostgreSQL和PPAS类型的实例。'
request = DescribeSlowLogsRequest.DescribeSlowLogsRequest()
request.set_accept_format('json')
request.set_action_name('DescribeSlowLogRecords')
request.set_DBInstanceId(kwargs['DBInstanceId'])
request.set_DBName(kwargs['DBName'])
request.set_StartTime(kwargs['StartTime'])
request.set_EndTime(kwargs['EndTime'])
# request.set_PageSize(100)
# request.set_PageNumber(1)
return self.clt.do_action_with_exception(request)
def get_DescribeSlowLogsRequest(self, **kwargs):
'获取RDS慢查询'
request = DescribeSlowLogsRequest.DescribeSlowLogsRequest()
request.set_accept_format('json')
request.set_action_name('DescribeSlowLogs')
request.set_DBInstanceId(kwargs['DBInstanceId'])
request.set_DBName(kwargs['DBName'])
request.set_StartTime(kwargs['StartTime'])
request.set_EndTime(kwargs['EndTime'])
"""
排序依据，取值如下：
TotalExecutionCounts：总执行次数最多；
TotalQueryTimes：总执行时间最多
TotalLogicalReads：总逻辑读最多；
TotalPhysicalReads：总物理读最多。此参数对SQL Server实例有效，SQL Server类型必传此参数。
"""
request.set_SortKey(kwargs['SortKey'])
request.set_PageSize(100)
request.set_PageNumber(1)
return self.clt.do_action_with_exception(request)
# -*- coding: utf-8 -*-
from aliyunsdkcore.client import AcsClient
from aliyunsdkrds.request.v20140815 import DescribeSlowLogsRequest
from datetime import date, timedelta
import json
import csv
import os
from txfunctions import smail
ISOTIMEFORMAT = '%Y-%m-%dZ'
Log_Date = (date.today() - timedelta(days=1)).strftime(ISOTIMEFORMAT)
client = AcsClient('XXXXXXX', 'XXXXXXXXXX', 'cn-beijing')
request = DescribeSlowLogsRequest.DescribeSlowLogsRequest()
request.set_DBInstanceId('XXXXX')
request.set_accept_format('json')
request.set_PageSize(100)
request.set_StartTime(Log_Date)
request.set_EndTime(Log_Date)
response = client.do_action_with_exception(request)
messages = json.loads(response).get('Items').get('SQLSlowLog')
SlowFlag = 2
csv_file_name = '/tmp/Cse_SlowLOg_' + Log_Date + '.csv'
mail_addrs = ["XXXXX", "XXXX"]
mail_body = u'阿里云RDS慢查询日志,详情见附件。'
mail_subject = u'阿里云RDS慢查询日志-' + Log_Date
mail_attachments = [csv_file_name]
if messages:
SlowFlag = 1
headers = messages[0].keys()
with open(csv_file_name, 'wb') as csvfile:
csv_writer = csv.DictWriter(csvfile, headers)