""" RDS demo(s) by mttmss@ """ from __future__ import print_function import boto3 # Initialize client with access key of an IAM User with no permissions ssm_ak = boto3.client('ssm') ssm_ak_response = ssm_ak.get_parameter( Name="/rds/configuration/nopermissions/ak", WithDecryption=True ) ssm_sak = boto3.client('ssm') ssm_sak_response = ssm_sak.get_parameter( Name="/rds/configuration/nopermissions/sak", WithDecryption=True ) ssm_accnum = boto3.client('ssm') ssm_accnum_response = ssm_accnum.get_parameter( Name="/rds/configuration/nopermissions/accnum", WithDecryption=True ) stscall = boto3.client( 'sts', aws_access_key_id=ssm_ak_response['Parameter']['Value'], aws_secret_access_key=ssm_sak_response['Parameter']['Value'] ) def build_speechlet_response(title, output, reprompt_text, should_end_session): return { 'outputSpeech': { 'type': 'PlainText', 'text': output }, 'card': { 'type': 'Simple', 'title': "SessionSpeechlet - " + title, 'content': "SessionSpeechlet - " + output }, 'reprompt': { 'outputSpeech': { 'type': 'PlainText', 'text': reprompt_text } }, 'shouldEndSession': should_end_session } def build_response(session_attributes, speechlet_response): return { 'version': '1.0', 'sessionAttributes': session_attributes, 'response': speechlet_response } # --------------- Functions that control the skill's behavior ------------------ def get_welcome_response(): import boto3 """ If we wanted to initialize the session to have some attributes we could add those here """ #Reset the value of the environment variable Notifications mylambda = boto3.client('lambda') response = mylambda.update_function_configuration( FunctionName='alexardsdemo', Environment={ 'Variables': { 'Notifications': 'off' } } ) session_attributes = {} card_title = "Welcome" speech_output = "Welcome to the Virginia Alexa Skill RDS demo. " \ "Please tell me which actions I have to run on RDS" # If the user either does not reply to the welcome message or says something # that is not understood, they will be prompted again with this text. reprompt_text = "Please tell me which actions I have to run on RDS" should_end_session = False return build_response(session_attributes, build_speechlet_response( card_title, speech_output, reprompt_text, should_end_session)) def handle_session_end_request(): card_title = "Session Ended" speech_output = "Thank you for trying the Alexa Skill RDS demo. " \ "Have a nice day! " # Setting this to true ends the session and exits the skill should_end_session = True return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def WrongIntent(): card_title = "Error" speech_output = "Incorrect command. Please retry." should_end_session = False return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def GetRegionCode(region): import boto3 region_code = "us-east-1" if region == "oregon": region_code = "us-west-2" elif region == "virginia": region_code = "us-east-1" elif region == "california": region_code = "us-east-2" elif region == "mumbai": region_code = "ap-south-1" elif region == "osaka": region_code = "ap-northeast-3" elif region == "seoul": region_code = "ap-northeast-2" elif region == "singapore": region_code = "ap-southeast-1" elif region == "sidney": region_code = "ap-southeast-2" elif region == "tokyo": region_code = "ap-northeast-1" elif region == "canada": region_code = "ca-central-1" elif region == "beijing": region_code = "cn-north-1" elif region == "ningxia": region_code = "cn-northwest-1" elif region == "frankfurt": region_code = "eu-central-1" elif region == "ireland": region_code = "eu-west-1" elif region == "london": region_code = "eu-west-2" elif region == "paris": region_code = "eu-west-3" elif region == "sao paulo": region_code = "sa-east-1" return region_code def SendNotification(final_str): import boto3 ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/configuration/snsTargetArn" ) ArnSNStopic = response['Parameter']['Value'] notification = boto3.client('sns') response = notification.publish( TargetArn=ArnSNStopic, Message=final_str ) def IsMFAValid(token): # The GetSessionToken function will throw an error when the token is invalid try: stscall.get_session_token( SerialNumber="arn:aws:iam::" + ssm_accnum_response['Parameter']['Value'] + ":mfa/nopermissions", TokenCode=token, ) return True except: return False def NumberOfInstances(intent, session): import boto3 # Utterance: "Number of instances in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() count = len(instances['DBInstances']) card_title = "Number of instances" should_end_session = False if str(count) == 1: speech_output = "There is " + str(count) + " instance in " + region else: speech_output = "There are " + str(count) + " instances in " + region return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def ListInstances(intent, session): import boto3 import os # Utterance: "list instances in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() count = len(instances['DBInstances']) if count > 0: #Number of instances limited to 20 if count > 20: final_str = "The number of RDS instances in " + region + " is higher than 20. Please activate notifications in order to receive the complete list via e-mail" final_str_to_send = "List of all the RDS instances in " + region + ": \r" for instance in instances['DBInstances']: final_str_to_send += instance['Engine'] + " instance " + instance['DBInstanceIdentifier'] + ". \r" else: final_str = "List of all the RDS instances in " + region + ": \r" for instance in instances['DBInstances']: final_str += instance['Engine'] + " instance " + instance['DBInstanceIdentifier'] + ". \r" final_str_to_send = final_str else: final_str = "There aren't any instances in " + region card_title = "List all the instances in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str_to_send) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def ListApplications(intent, session): import boto3 import os # Utterance: "list applications in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() count = len(instances['DBInstances']) tags_unique = set() if count > 0: for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'application': tags_unique.add(tag['Value']) #Number of applications limited to 20 if count > 20: final_str = "The number of applications in " + region + " is higher than 20. Please activate notifications in order to receive the complete list via e-mail" final_str_to_send = "List of all the applications in " + region + ": \r" for tag in sorted(tags_unique): final_str_to_send += tag + ". \r" else: final_str = "List of all the applications in " + region + ": \r" for tag in sorted(tags_unique): final_str += tag + ". \r" final_str_to_send = final_str else: final_str = "There aren't any applications in " + region card_title = "List all the applications in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str_to_send) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def ListInstancesForApplication(intent, session): import boto3 import os # Utterance: "list instances of application {application} in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) app = intent['slots']['application']['value'].lower() rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() instance_list = set() for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'application': if tag['Value'] == app: instance_list.add(instance['DBInstanceIdentifier']) count = len(instance_list) if count > 0: #The number of RDS instances is limited to 20 if count > 20: final_str = "The number of the RDS instances for the application " + app + " in " + region + " is higher than 20. Please activate notifications in order to receive the complete list via e-mail" final_str_to_send = "List of the RDS instances for the application " + app + " in " + region + ": \r" for instance in instance_list: final_str_to_send += instance + ". \r" else: final_str = "List of the RDS instances for the application " + app + " in " + region + ": \r" for instance in instance_list: final_str += instance + ". \r" final_str_to_send = final_str else: final_str = "There aren't any RDS instances for the application " + app + " in " + region card_title = "List instances of an application in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str_to_send) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def CheckInstanceMetric(intent, session): import boto3 import os from datetime import datetime, timedelta # Utterance: "{metric} for {instance} instance in {awsregion} {window}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() cwa = boto3.client('cloudwatch', region_name=region_code) metric_name = intent['slots']['metric']['value'].lower() instance_name = intent['slots']['instance']['value'].lower() window = intent['slots']['window']['value'].lower() window_modified = 1 identifier = "" if window == 'now': window_modified = 1 elif window == '5 minutes ago': window_modified = 5 elif window == '15 minutes ago': window_modified = 15 elif window == '30 minutes ago': window_modified = 30 elif window == '1 hour ago': window_modified = 60 else: window_modified = 1 for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] if identifier == "": final_str = "There isn't a " + instance_name + " instance in " + region else: if metric_name == "free storage space": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='FreeStorageSpace', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=86400, Statistics=[ 'Average' ], Unit='Bytes' ) gb = str(round(((metric['Datapoints'][0]['Average'])/1024/1024/1024),2)) final_str = gb + " gigabytes of free storage for the " + instance_name + " instance in " + region elif metric_name == "database connections": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='DatabaseConnections', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Count' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " database connections for the " + instance_name + " instance in " + region elif metric_name == "cpu utilization": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='CPUUtilization', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Percent' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " percent CPU utilization for the " + instance_name + " instance in " + region elif metric_name == "free memory": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='FreeableMemory', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Bytes' ) gb = str(round(((metric['Datapoints'][0]['Average'])/1024/1024/1024),2)) final_str = gb + " gigabytes of free memory for the " + instance_name + " instance in " + region elif metric_name == "swap usage": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='SwapUsage', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Bytes' ) gb = str(round(((metric['Datapoints'][0]['Average'])/1024/1024/1024),2)) final_str = gb + " gigabytes of swap usage for the " + instance_name + " instance in " + region elif metric_name == "read iops": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='ReadIOPS', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Count/Second' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " read iops per second for the " + instance_name + " instance in " + region elif metric_name == "write iops": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='WriteIOPS', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Count/Second' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " write iops per second for the " + instance_name + " instance in " + region elif metric_name == "read leatency": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='ReadLatency', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Seconds' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " seconds of read latency for the " + instance_name + " instance in " + region elif metric_name == "write latency": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='WriteLatency', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Seconds' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " seconds of write latency for the " + instance_name + " instance in " + region elif metric_name == "read throughput": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='ReadThroughput', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Bytes/Second' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " bytes per second of read throughput for the " + instance_name + " instance in " + region elif metric_name == "write throughput": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='WriteThroughput', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Bytes/Second' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " bytes per second of write throughput for the " + instance_name + " instance in " + region elif metric_name == "network receive throughput": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='NetworkReceiveThroughput', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Bytes/Second' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " bytes per second of network receive throughput for the " + instance_name + " instance in " + region elif metric_name == "network transmit throughput": metric = cwa.get_metric_statistics( Namespace='AWS/RDS', MetricName='NetworkTransmitThroughput', Dimensions=[ { 'Name': 'DBInstanceIdentifier', 'Value': identifier }, ], StartTime=datetime.now() - timedelta(minutes=window_modified), EndTime=datetime.now(), Period=60, Statistics=[ 'Average' ], Unit='Bytes/Second' ) count = str(int(metric['Datapoints'][0]['Average'])) final_str = count + " bytes per second of network transmit throughput for the " + instance_name + " instance in " + region else: final_str = "Metric not recognized" card_title = "Metric for a specific instance in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def RebootInstance(intent, session): import boto3 import os # Utterance: "reboot {instance} instance in {awsregion} mfa {mfa}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() try: mfatoken = intent['slots']['mfa']['value'] except: mfatoken = "" if mfatoken == "": final_str = "Sorry, the operation requested requires multi-factor authentication" else: identifier = "" rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] if identifier == "": final_str = "There isn't a " + instance_name + " instance in " + region else: if IsMFAValid(mfatoken): rds.reboot_db_instance( DBInstanceIdentifier=identifier, ForceFailover=False) final_str = "Reboot in progress for the " + instance_name + " instance in " + region else: final_str = "Reboot not authorized for the " + instance_name + " instance in " + region card_title = "Reboot a specific instance in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def FailoverInstance(intent, session): import boto3 import os # Utterance: "failover {instance} instance in {awsregion} mfa {mfa}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() try: mfatoken = intent['slots']['mfa']['value'] except: mfatoken = "" if mfatoken == "": final_str = "Sorry, the operation requested requires multi-factor authentication" else: identifier = "" engine = "" clusterid = "" rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] multiaz = instance['MultiAZ'] engine = instance['Engine'] if (engine == "aurora") or (engine == "aurora-mysql") or (engine == "aurora-postgresql"): clusterid = instance['DBClusterIdentifier'] if identifier == "": final_str = "There isn't a " + instance_name + " instance in " + region else: if (engine == "aurora") or (engine == "aurora-mysql") or (engine == "aurora-postgresql"): if IsMFAValid(mfatoken): rds.failover_db_cluster(DBClusterIdentifier=clusterid) final_str = "Failover in progress for the " + instance_name + " instance in " + region else: final_str = "Failover not authorized for the " + instance_name + " instance in " + region else: if multiaz == True: if IsMFAValid(mfatoken): rds.reboot_db_instance( DBInstanceIdentifier=identifier, ForceFailover=True) final_str = "Failover in progress for the " + instance_name + " instance in " + region else: final_str = "Failover not authorized for the " + instance_name + " instance in " + region else: final_str = "Failover not possible. Multi-A-Z feature disabled for the " + instance_name + " instance in " + region card_title = "Failover a specific instance" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def ConnectInstance(intent, session): import boto3 import os # Utterance: "connect to {instance} instance in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() identifier = "" engine = "" rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] engine = instance['Engine'] dbname = instance['DBName'] masterusr = instance['MasterUsername'] dbendopoint = instance['Endpoint']['Address'] dbport = instance['Endpoint']['Port'] if identifier == "": final_str = "There isn't a " + instance_name + " instance in the " + region + " region" else: if (engine == "sqlserver-ee") or (engine == "sqlserver-se") or (engine == "sqlserver-web") or (engine == "sqlserver-ex"): final_str = "Database engine not supported" else: if (engine == "mysql") or (engine == "aurora") or (engine == "aurora-mysql") or (engine == "aurora-postgresql") or (engine == "postgres"): ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/" + instance_name + "/dbpassword" ) dbpassword = response['Parameter']['Value'] command1 = "echo" command2 = "/usr/bin/mysql -h " + dbendopoint + " -P " + str(dbport) + " -u " + masterusr + " -p" + dbpassword + " -DB " + dbname + " < /home/ec2-user/mysql_statusdb.sql" #health_check = "Current database:\tmysqldb" health_check = "Current database:" elif engine == "oracle-ee": ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/" + instance_name + "/dbpassword" ) dbpassword = response['Parameter']['Value'] command1 = "export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib" command2 = "/usr/lib/oracle/11.2/client64/bin/sqlplus '" + masterusr + "/" + dbpassword + "@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + dbendopoint + ")(PORT=" + str(dbport) + "))(CONNECT_DATA=(SID=" + dbname + ")))' @/home/ec2-user/oracle_statusdb.sql" health_check = "CONNECTED" #The EC2 instance used as a bastion host must have the following tag: ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/configuration/bastion/" + region ) ec2_tag = response['Parameter']['Value'] ec2 = boto3.client('ec2', region_name=region_code) ec2_instance = ec2.describe_instances( Filters=[ { 'Name': 'tag:Name', 'Values': [ ec2_tag ] }, ] ) ec2InstanceId = ec2_instance['Reservations'][0]['Instances'][0]['InstanceId'] ssm = boto3.client('ssm') response = ssm.send_command( InstanceIds=[ec2InstanceId], DocumentName='AWS-RunShellScript', TimeoutSeconds=60, Parameters={ "commands": [ command1, command2, ] } ) cmdId = response['Command']['CommandId'] ssm = boto3.client('ssm') final_status = "" while final_status not in ('Success','Delivery Timed Out','Execution Timed Out','Failed','Canceled','Undeliverable','Terminated'): temp_status = ssm.get_command_invocation( CommandId=cmdId, InstanceId=ec2InstanceId ) final_status = temp_status['Status'] final_output = temp_status['StandardOutputContent'] if final_output.find(health_check) == -1: final_str = "Database unreachable" else: final_str = "Database reachable" card_title = "Verify instance connection in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def CheckInstanceProperty(intent, session): import boto3 import os from datetime import datetime, timedelta # Utterance: "{property} for {instance} instance in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) property_name = intent['slots']['property']['value'].lower() instance_name = intent['slots']['instance']['value'].lower() cwa = boto3.client('cloudwatch', region_name=region_code) rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() temp_str = "" for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: if property_name == "latest restorable time": property_value = instance['LatestRestorableTime'] temp_str = "The latest restorable time of the instance " + instance_name + " in " + region + " is: " + '{:%B %d-%Y - %H:%M:%S}'.format(property_value) elif property_name == "parameter group": property_value = instance['DBParameterGroups'][0]['DBParameterGroupName'] temp_str = "The parameter group name of the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "status": property_value = instance['DBInstanceStatus'] temp_str = "The status of the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "public accessible": property_value = instance['PubliclyAccessible'] temp_str = "The value for the public accessible parameter of the instance " + instance_name + " in " + region + " is: " + str(property_value) elif property_name == "master username": property_value = instance['MasterUsername'] temp_str = "The master username of the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "license model": property_value = instance['LicenseModel'] temp_str = "The license model for the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "engine": property_value = instance['Engine'] temp_str = "The engine of the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "multi-az": property_value = instance['MultiAZ'] temp_str = "The value for the multi-az property of the instance " + instance_name + " in " + region + " is: " + str(property_value) elif property_name == "auto minor upgrades": property_value = instance['AutoMinorVersionUpgrade'] temp_str = "The value for the auto minor upgrades property of the instance " + instance_name + " in " + region + " is: " + str(property_value) elif property_name == "allocated storage": property_value = instance['AllocatedStorage'] temp_str = "The allocated storage for the instance " + instance_name + " in " + region + " is: " + str(property_value) + " gigabytes" elif property_name == "backup retention period": property_value = instance['BackupRetentionPeriod'] temp_str = "The backup retention of the instance " + instance_name + " in " + region + " is: " + str(property_value) + " days" elif property_name == "db name": property_value = instance['DBName'] temp_str = "The db name of the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "engine version": property_value = instance['EngineVersion'] temp_str = "The engine version of the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "availability zone": property_value = instance['AvailabilityZone'] temp_str = "The availability zone of the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "storage type": property_value = instance['StorageType'] temp_str = "The storage type of the instance " + instance_name + " in " + region + " is: " + property_value elif property_name == "db instance class": property_value = instance['DBInstanceClass'] temp_str = "The db instance class of the instance " + instance_name + " in " + region + " is: " + property_value else: property_value = "n/a" temp_str = "Property not correct or not supported by the instance specified" if property_value == "": final_str = "There isn't a " + instance_name + " instance in " + region else: final_str = temp_str card_title = "Property of a specific instance in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def TablespaceUsage(intent, session): import boto3 import os # IMPORTANT: This function is valid only for oracle-ee databases # NOTE: The .sql script that is run limits the result set to maximum 20 rows # Utterance: "tablespace usage for {instance} instance in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() identifier = "" engine = "" rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] engine = instance['Engine'] dbname = instance['DBName'] masterusr = instance['MasterUsername'] dbendopoint = instance['Endpoint']['Address'] dbport = instance['Endpoint']['Port'] if identifier == "": final_output = "There isn't a " + instance_name + " instance in the " + region + " region" else: if engine != "oracle-ee": final_output = "The engine doesn't support the action requested" else: ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/" + instance_name + "/dbpassword" ) dbpassword = response['Parameter']['Value'] command1 = "export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib" command2 = "/usr/lib/oracle/11.2/client64/bin/sqlplus -s '" + masterusr + "/" + dbpassword + "@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + dbendopoint + ")(PORT=" + str(dbport) + "))(CONNECT_DATA=(SID=" + dbname + ")))' @/home/ec2-user/oracle_tablespaceusage.sql" #The EC2 instance used as a bastion host must have the following tag: ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/configuration/bastion/" + region ) ec2_tag = response['Parameter']['Value'] ec2 = boto3.client('ec2', region_name=region_code) ec2_instance = ec2.describe_instances( Filters=[ { 'Name': 'tag:Name', 'Values': [ ec2_tag ] }, ] ) ec2InstanceId = ec2_instance['Reservations'][0]['Instances'][0]['InstanceId'] ssm = boto3.client('ssm') response = ssm.send_command( InstanceIds=[ec2InstanceId], DocumentName='AWS-RunShellScript', TimeoutSeconds=60, Parameters={ "commands": [ command1, command2, ] } ) cmdId = response['Command']['CommandId'] ssm = boto3.client('ssm') final_status = "" while final_status not in ('Success','Delivery Timed Out','Execution Timed Out','Failed','Canceled','Undeliverable','Terminated'): temp_status = ssm.get_command_invocation( CommandId=cmdId, InstanceId=ec2InstanceId ) final_status = temp_status['Status'] #StandardOutputContent: The first 24,000 characters written by the plugin to stdout final_output = temp_status['StandardOutputContent'] final_str = final_output if os.environ['Notifications'] == "on": SendNotification(final_str) card_title = "Check tablespace usage" should_end_session = False speech_output = final_str return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def ListEvents(intent, session): import boto3 import os from datetime import datetime, timedelta # Utterance: "list events for {instance} instance in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() identifier = "" rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() source_type = "db-instance" #Last 24 hours window_modified = 1440 #MaxRecords: must be between 20 and 100 max_num_events = 20 for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] if identifier == "": final_str = "There isn't a " + instance_name + " instance in " + region else: list_events = rds.describe_events( SourceIdentifier=identifier, SourceType='db-instance', StartTime=datetime.now() - timedelta(minutes=1440), EndTime=datetime.now(), MaxRecords=max_num_events ) count = len(list_events['Events']) if count > 0: #The list of events is limited to 20 if count > 20: final_str = "The number of events for the last 24 hours in " + region + " is higher than 20. Please activate notifications in order to receive the complete list via e-mail" final_str_to_send = "List of the events for the last 24 hours: " for event in list_events['Events']: message = event['Message'] final_str += message + ". \r" else: final_str = "List of the events for the last 24 hours: " for event in list_events['Events']: message = event['Message'] final_str += message + ". \r" final_str_to_send = final_str else: final_str = "No events for the last 24 hours" card_title = "Property of a specific instance in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str_to_send) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def CheckInstanceParameter(intent, session): import boto3 import os # Utterance: "{parameter} for {instance} instance in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() parameter_name = intent['slots']['parameter']['value'] identifier = "" engine = "" rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] engine = instance['Engine'] dbname = instance['DBName'] masterusr = instance['MasterUsername'] dbendopoint = instance['Endpoint']['Address'] dbport = instance['Endpoint']['Port'] if identifier == "": final_output = "There isn't a " + instance_name + " instance in the " + region + " region" else: if (engine == "sqlserver-ee") or (engine == "sqlserver-se") or (engine == "sqlserver-web") or (engine == "sqlserver-ex"): final_output = "The engine doesn't support the action requested" elif (engine == "mysql") or (engine == "aurora") or (engine == "postgres"): final_output = "Sorry, functionality to develop" else: ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/" + instance_name + "/dbpassword" ) dbpassword = response['Parameter']['Value'] parameter_final = FindInstanceParameter(parameter_name) command1 = "export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib" command2 = "/usr/lib/oracle/11.2/client64/bin/sqlplus -s '" + masterusr + "/" + dbpassword + "@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + dbendopoint + ")(PORT=" + str(dbport) + "))(CONNECT_DATA=(SID=" + dbname + ")))' @/home/ec2-user/oracle_checkparameter.sql " + parameter_final #The EC2 instance used as a bastion host must have the following tag: ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/configuration/bastion/" + region ) ec2_tag = response['Parameter']['Value'] ec2 = boto3.client('ec2', region_name=region_code) ec2_instance = ec2.describe_instances( Filters=[ { 'Name': 'tag:Name', 'Values': [ ec2_tag ] }, ] ) ec2InstanceId = ec2_instance['Reservations'][0]['Instances'][0]['InstanceId'] ssm = boto3.client('ssm') response = ssm.send_command( InstanceIds=[ec2InstanceId], DocumentName='AWS-RunShellScript', TimeoutSeconds=60, Parameters={ "commands": [ command1, command2, ] } ) cmdId = response['Command']['CommandId'] ssm = boto3.client('ssm') final_status = "" while final_status not in ('Success','Delivery Timed Out','Execution Timed Out','Failed','Canceled','Undeliverable','Terminated'): temp_status = ssm.get_command_invocation( CommandId=cmdId, InstanceId=ec2InstanceId ) final_status = temp_status['Status'] final_output = temp_status['StandardOutputContent'] final_str = final_output card_title = "Check instance parameter in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def SetNotifications(intent, session): import boto3 # Utterance: "set notifications to {notificationstatus}" notificationstatus = intent['slots']['notificationstatus']['value'].lower() mylambda = boto3.client('lambda') response = mylambda.update_function_configuration( FunctionName='alexardsdemo', Environment={ 'Variables': { 'Notifications': notificationstatus } } ) final_str = "Notifications set to " + notificationstatus card_title = "Enable or disable e-mail notifications via SNS" should_end_session = False speech_output = final_str return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def CheckLog(intent, session): import boto3 # Utterance: "check {logtype} of {instance} instance in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() log_type = intent['slots']['logtype']['value'].lower() identifier = "" rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] engine = instance['Engine'] if identifier == "": final_str = "There isn't a " + instance_name + " instance in the " + region + " region" else: if engine == "oracle-ee": if log_type == "alert log": errors_check = "ORA-" rds = boto3.client('rds', region_name=region_code) response = rds.download_db_log_file_portion( DBInstanceIdentifier=identifier, LogFileName="trace/alert_ORCLDB.log" ) final_log = response['LogFileData'] if final_log.find(errors_check) == -1: final_str = "No errors found in the alert log. Check your mailbox to see its content" else: final_str = "WARNING: errors found in the log. Check your mailbox to see its content" SendNotification(final_log) else: final_str = "Log type not correct or not available for the engine specified" elif (engine == "mysql") or (engine == "aurora-mysql"): if log_type == "error log": rds = boto3.client('rds', region_name=region_code) response = rds.download_db_log_file_portion( DBInstanceIdentifier=identifier, LogFileName="error/mysql-error.log" ) if response['LogFileData'] == "": final_log = "The log is empty" else: final_log = response['LogFileData'] final_str = "Database log sent. Check your mailbox" SendNotification(final_log) else: final_str = "Log type not correct or not available for the engine specified" elif (engine == "aurora"): if log_type == "error log": rds = boto3.client('rds', region_name=region_code) response = rds.download_db_log_file_portion( DBInstanceIdentifier=identifier, LogFileName="error/mysql-error.log" ) if response['LogFileData'] == "": final_log = "The log is empty" else: final_log = response['LogFileData'] final_str = "Database log sent. Check your mailbox" SendNotification(final_log) else: final_str = "Log type not correct or not available for the engine specified" card_title = "Check for errors into logs" should_end_session = False speech_output = final_str return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def ListAlarms(intent, session): import boto3 import os # Utterance: "list of alarms fired for {instance} instance in {awsregion}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() rds = boto3.client('rds', region_name=region_code) cwa = boto3.client('cloudwatch', region_name=region_code) alarmprefix = "cloudwatch-alarm-" + instance_name alarmslist = cwa.describe_alarms( AlarmNamePrefix=alarmprefix, StateValue='ALARM' ) count = len(alarmslist) if count > 0: final_str = "The following alarms have the ALARM state for the " + instance_name + " instance in " + region + ": \r" for alarm in alarmslist['MetricAlarms']: final_str += alarm['AlarmName'] + ". \r" final_str_to_send = final_str else: final_str = "There are not alarms fired for the " + instance_name + " instance in " + region final_str_to_send = final_str card_title = "List fired alarms for a database in a region" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str_to_send) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def ShowTopSession(intent, session): import boto3 import os # IMPORTANT: This function is valid only for oracle-ee databases # Utterance: "show top session by {resource} for {instance} instance in {region}" region = intent['slots']['awsregion']['value'].lower() region_code = GetRegionCode(region) instance_name = intent['slots']['instance']['value'].lower() byresource = intent['slots']['resource']['value'].lower() identifier = "" engine = "" rds = boto3.client('rds', region_name=region_code) instances = rds.describe_db_instances() for instance in instances['DBInstances']: arn = instance['DBInstanceArn'] tags = rds.list_tags_for_resource(ResourceName=arn) for tag in tags['TagList']: if tag['Key'] == 'instance': if tag['Value'] == instance_name: identifier = instance['DBInstanceIdentifier'] engine = instance['Engine'] dbname = instance['DBName'] masterusr = instance['MasterUsername'] dbendopoint = instance['Endpoint']['Address'] dbport = instance['Endpoint']['Port'] if identifier == "": final_str = "There isn't a " + instance_name + " instance in the " + region + " region" else: if engine != "oracle-ee": final_str = "The engine doesn't support the action requested" else: ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/" + instance_name + "/dbpassword" ) if byresource == "cpu": scriptname = "oracle_show_top_session_by_cpu.sql" else: scriptname = "not_supported" if scriptname != "not_supported": dbpassword = response['Parameter']['Value'] command1 = "export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib" command2 = "/usr/lib/oracle/11.2/client64/bin/sqlplus -s '" + masterusr + "/" + dbpassword + "@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + dbendopoint + ")(PORT=" + str(dbport) + "))(CONNECT_DATA=(SID=" + dbname + ")))' @/home/ec2-user/" + scriptname #The EC2 instance used as a bastion host must have the following tag: ssm = boto3.client('ssm') response = ssm.get_parameter( Name="/rds/configuration/bastion/" + region ) ec2_tag = response['Parameter']['Value'] ec2 = boto3.client('ec2', region_name=region_code) ec2_instance = ec2.describe_instances( Filters=[ { 'Name': 'tag:Name', 'Values': [ ec2_tag ] }, ] ) ec2InstanceId = ec2_instance['Reservations'][0]['Instances'][0]['InstanceId'] ssm = boto3.client('ssm') response = ssm.send_command( InstanceIds=[ec2InstanceId], DocumentName='AWS-RunShellScript', TimeoutSeconds=60, Parameters={ "commands": [ command1, command2, ] } ) cmdId = response['Command']['CommandId'] ssm = boto3.client('ssm') final_status = "" while final_status not in ('Success','Delivery Timed Out','Execution Timed Out','Failed','Canceled','Undeliverable','Terminated'): temp_status = ssm.get_command_invocation( CommandId=cmdId, InstanceId=ec2InstanceId ) final_status = temp_status['Status'] #StandardOutputContent: The first 24,000 characters written by the plugin to stdout final_output = temp_status['StandardOutputContent'] final_str = "The top session by " + byresource + " is the following: " + final_output else: final_str = "Metric specified not existing or not supported" card_title = "Show top session" should_end_session = False speech_output = final_str if os.environ['Notifications'] == "on": SendNotification(final_str) return build_response({}, build_speechlet_response( card_title, speech_output, None, should_end_session)) def FindInstanceParameter(parameter_name): import boto3 if parameter_name == "open cursors": return "open_cursors" # --------------- Events ------------------ def on_session_started(session_started_request, session): """ Called when the session starts """ print("on_session_started requestId=" + session_started_request['requestId'] + ", sessionId=" + session['sessionId']) def on_launch(launch_request, session): """ Called when the user launches the skill without specifying what they want """ print("on_launch requestId=" + launch_request['requestId'] + ", sessionId=" + session['sessionId']) # Dispatch to your skill's launch return get_welcome_response() def on_intent(intent_request, session): """ Called when the user specifies an intent for this skill """ print("on_intent requestId=" + intent_request['requestId'] + ", sessionId=" + session['sessionId']) intent = intent_request['intent'] intent_name = intent_request['intent']['name'] # Dispatch to your skill's intent handlers if intent_name == "NumberOfInstances": return NumberOfInstances(intent, session) elif intent_name == "ListInstances": return ListInstances(intent, session) elif intent_name == "ListApplications": return ListApplications(intent, session) elif intent_name == "ListInstancesForApplication": return ListInstancesForApplication(intent, session) elif intent_name == "MetricRDSInstance": return MetricRDSInstance(intent, session) elif intent_name == "RebootInstance": return RebootInstance(intent, session) elif intent_name == "FailoverInstance": return FailoverInstance(intent, session) elif intent_name == "CheckInstanceMetric": return CheckInstanceMetric(intent, session) elif intent_name == "ConnectInstance": return ConnectInstance(intent, session) elif intent_name == "CheckInstanceProperty": return CheckInstanceProperty(intent, session) elif intent_name == "TablespaceUsage": return TablespaceUsage(intent, session) elif intent_name == "ListEvents": return ListEvents(intent, session) elif intent_name == "CheckInstanceParameter": return CheckInstanceParameter(intent, session) elif intent_name == "SetNotifications": return SetNotifications(intent, session) elif intent_name == "CheckLog": return CheckLog(intent, session) elif intent_name == "ListAlarms": return ListAlarms(intent, session) elif intent_name == "ShowTopSession": return ShowTopSession(intent, session) elif intent_name == "AMAZON.HelpIntent": return get_welcome_response() elif intent_name == "AMAZON.CancelIntent" or intent_name == "AMAZON.StopIntent": return handle_session_end_request() else: #raise ValueError("Invalid intent") return WrongIntent() def on_session_ended(session_ended_request, session): """ Called when the user ends the session. Is not called when the skill returns should_end_session=true """ print("on_session_ended requestId=" + session_ended_request['requestId'] + ", sessionId=" + session['sessionId']) # add cleanup logic here # --------------- Main handler ------------------ def lambda_handler(event, context): """ Route the incoming request based on type (LaunchRequest, IntentRequest, etc.) The JSON body of the request is provided in the event parameter. """ print("event.session.application.applicationId=" + event['session']['application']['applicationId']) """ Uncomment this if statement and populate with your skill's application ID to prevent someone else from configuring a skill that sends requests to this function. """ # if (event['session']['application']['applicationId'] != # "amzn1.echo-sdk-ams.app.[unique-value-here]"): # raise ValueError("Invalid Application ID") #dialog_state = event['request']['dialogState'] if event['session']['new']: on_session_started({'requestId': event['request']['requestId']}, event['session']) if event['request']['type'] == "LaunchRequest": return on_launch(event['request'], event['session']) elif event['request']['type'] == "IntentRequest": return on_intent(event['request'], event['session']) elif event['request']['type'] == "SessionEndedRequest": return on_session_ended(event['request'], event['session'])