Python Pull RDS Audit log

Source: Internet
Author: User
Tags timedelta


This script can pull the RDS audit log and insert it into the local data.

#!/usr/bin/env  python2.6#coding=utf-8import osfrom aliyunsdkcore import  clientfrom aliyunsdkrds.request.v20140815 import describesqllogrecordsrequestimport  jsonimport urllibimport datetime,timeimport subprocessfrom   subprocess  Import callimport warningsimport mysqldbfrom math import ceilfrom retrying  import retrydbserver= "192.168.0.94" dbuser= "root" dbpwd= "[email protected]" dbport=3306dbname= " Audit "warnings.filterwarnings (" Ignore ") os.environ[" PATH "]="/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/ Bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin "dblist = [' rm-1111 ', ' rm-2222 ', ' rr-333 ', ' rm-444 ', ' rm-555 ' ]list = {"rm-1111": "Beizhu1", "rm-2222": "Beizhu2",  "rr-333": "Beizhu3", "rm-444": "Beizhu4"}#  mysqldb  Operation class Class db_operate (object):         def __init_ _ (Self,_hostname,_user,_pwd,_port,_db):                 Self.conn=mysqldb.connect (host=_hostname,user=_user,passwd=_pwd,port=_port,db=_db)                  self.conn.set_character_set (' UTF8 ')                 self.cur= Self.conn.cursor ()         def execsql_fetchall (self,sqlcmd):                  Self.cur.execute (sqlcmd)                  result=self.cur.fetchall ()                  return result        def execsql_ Fetchone (Self,sqlcmd):                self.cur.execute (sqlcmd)                  result=self.cur.fetchall ()                  return result         DEF EXECSQL_DML (Self,sqlcmd):                 self.cur.execute (sqlcmd)          def is_connection_usable (self):                 try:                          Self.conn.ping ()                  except exception:                         return False                 else:                         return True                                                   #  time range required for backup def getdate ():         global start_date         global end_date        current_time =  Datetime.datetime.now ()         END_DATE = LOCALTRFUTC (current_time)              # Current Time         start_date =  LOCALTRFUTC (Current_time - datetime.timedelta (minutes=5))       #拉取指定时间范围内的日志         print start_date,end_date         return 0                  #本地   Time UTCDEF LOCALTRFUTC (local_time):         utc_time = local_time - datetime.timedelta (hours=8)          return utc_time.strftime ("%y-%m-%dt%h:%m:%sz")                   @retry (wait_fixed=5000) Def geterrorlog (db_ Instanceid,pagenum,flag):         print db_instanceid,pagenum,flag         clt = client. Acsclient (' 1234 ', ' 123456 ', ' Cn-hangzhou ')         request =  Describesqllogrecordsrequest.describesqllogrecordsrequest ()          Request.set_accept_format (' json ')         request.set_action_name (' Describesqllogrecords ')         request.set_dbinstanceid (Db_instanceid)         request.set_starttime (start_date)          request.set_endtime (end_date)          Request.set_pagenumber (int (pagenum))         result = clt.do_ Action (Request)         s=json.loads (result)         if flag == 0:                 pagerecordcount = float (s["PageRecordCount")                  totalrecordcount  = float (s["Totalrecordcount")                  if TotalRecordCount == 0:                          return 0                 Result = int (Ceil (totalrecordcount/pagerecordcount))          Else:                result  = s[' Items ' [' Sqlrecord ']        return result                                              def installlog (dbid,auditlog):         for log in  auditlog:                 totalexecutiontimes = log["Totalexecutiontimes"]                 executetime = datetime.datetime.strptime (log[" Executetime "], '%y-%m-%dt%h:%m:%sz ')  + datetime.timedelta (hours=8)                  accountname = log["AccountName"]                 hostaddress = log["HostAddress"]                 threadid =  log["ThreadID"]                 dbname = log["DBName"]                 returnrowcounts = log["Returnrowcounts"]                 sqltext =  '/*   ' +log[' SQLText "].replace (" ' ", '" ') + '   */'                  serverid = dbid                 servermark = unicode (List.get (dbid), "Utf-8")        &nbsP;         sqlcmd= '  insert into yw_business ( Totalexecutiontimes,executetime,accountname,hostaddress,threadid,dbname,returnrowcounts,sqltext,serverid, Servermark)  values ('%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ')   '  %  ( Totalexecutiontimes,executetime,accountname,hostaddress,threadid,dbname,returnrowcounts,sqltext,serverid, Servermark) #               sqlcmd= ' " insert into yw_business (Totalexecutiontimes,executetime,accountname,hostaddress,threadid, Dbname,returnrowcounts,sqltext)  values ('%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ', '%s ')   '  %  ( Totalexecutiontimes,executetime,accountname,hostaddress,threadid,dbname,returnrowcounts,sqltext)                                try:                         FO.EXECSQL_DML (sqlcmd)                  except Exception,e:                          print sqlcmd                         print e         fo.conn.commit ()                  #  inserting data from other pages Def insertdata (dbid,totalpagenum):         for pagenum in range (1,totalpagenum+1):            &nbSp;    result = geterrorlog (dbid,pagenum,1)                   if result ==  "0":                          continue                 installlog (Dbid,result)                                   try:        if __name__ ==  "__main__" :                 getdate ()                 global fo    &nbsP;            fo=db_operate (Dbserver,dbuser,dbpwd, Int (dbport), dbname)                  for i in dblist:                         print i                          totalpages=geterrorlog (i,1,0)                          print totalpages                          insertdata (i,totalpages)               &nbsP;  fo.conn.close () except exception,e:        cmd  =  '  echo  -e   ' audit log insert exception '  |  mail -s   '  %s    Audit log Exceptions   '   [email protected]  '  %  (List.get ("%s"  % i) )          call (cmd,shell=true)          print e


Python Pull RDS Audit log

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.