Creating Ansible Custom Module for AWR report generation

[code language="python"] #!/usr/bin/python import os try: import cx_Oracle cx_oracle_found = True except ImportError: cx_oracle_found = False def main(): module = AnsibleModule( argument_spec = dict( tns_name = dict(required=True), begin_interval = dict(required=True), end_interval = dict(required=True), instance = dict(default = 1), out_directory = dict(default='/tmp'), date_format = dict(default='yyyy-mm-dd hh24:mi'), report_type = dict(default='text', choices=["text", "html"]) ) ) # Define arguments passed from ansible playbook. tns_name = module.params["tns_name"] begin_interval = module.params["begin_interval"] end_interval = module.params["end_interval"] instance = module.params["instance"] out_directory = module.params["out_directory"] report_type = module.params["report_type"] date_format = module.params["date_format"] if not cx_oracle_found: module.fail_json(msg="Can't import cx_Oracle module") # Connect to database try: con = cx_Oracle.connect('/@%s' % tns_name) except cx_Oracle.DatabaseError, exception: error, = exception.args module.fail_json(msg='Database connection error: %s, tns_name: %s' % (error.message, tns_name), changed=False) cursor = con.cursor() # Get dbid try: cursor.execute ("select dbid from v$database") dbid = cursor.fetchone ()[0] except cx_Oracle.DatabaseError, exception: error, = exception.args module.fail_json(msg= 'Error selecting v$database for dbid: %s' % (error.message), changed=False) # Get the 'Begin Snapshot' ID try: cursor.execute ("select max(snap_id) from dba_hist_snapshot where END_INTERVAL_TIME < to_date(:1,:2)",[begin_interval, date_format]) begin_snap = cursor.fetchone ()[0] if begin_snap is None: module.fail_json(msg = 'Fist snapshot is not found. Begin_interval: %s' % (begin_interval), changed=False) except cx_Oracle.DatabaseError, exception: error, = exception.args module.fail_json(msg='Error selecting dba_hist_snapshot for interval begin: %s' % (error.message), changed=False) # Get the 'End Snapshot' ID try: cursor.execute ("select min(snap_id) from dba_hist_snapshot where END_INTERVAL_TIME > to_date(:1,:2)",[end_interval, date_format]) end_snap = cursor.fetchone ()[0] if end_snap is None: module.fail_json(msg = 'Last snapshot is not found. End_interval: %s' % (end_interval), changed=False) except cx_Oracle.DatabaseError, exception: error, = exception.args module.fail_json(msg = 'Error selecting dba_hist_snapshot for interval end: %s' % (error.message), changed=False) if report_type=='text': sql = 'SELECT output FROM TABLE(dbms_workload_repository.awr_report_text (:1,:2,:3,:4))' file_ext=".txt" else: sql = 'SELECT output FROM TABLE(dbms_workload_repository.awr_report_html (:1,:2,:3,:4))' file_ext=".html" file_name=out_directory + str(dbid) + '_' + str(instance) + '_' + str(begin_snap) + '_' + str(end_snap) + file_ext # Generate the AWR report and save it into the file try: cursor.execute(sql,[dbid, instance, begin_snap, end_snap]) try: f = open(file_name,'w') for row in cursor.fetchall(): if row[0] is not None: f.write('\n' + row[0]) else: f.write('\n') f.close except IOError as e: module.fail_json( 'Couldn\'t open file: %s' % (file_name), changed=False) except cx_Oracle.DatabaseError, exc: error, = exc.args module.fail_json( msg='Error executing dbms_workload_repository: %s, begin_snap %s, end_snap %s' % (error.message,begin_snap, end_snap), changed=False) module.exit_json(fname=file_name, changed=True) from ansible.module_utils.basic import * if __name__ == '__main__': main() [/code]