RDS - Delete Unused Databases With No Connections

The following example policy workflow uses the mark-for-op and marked-for-op filters and actions to chain together a set of policies to accomplish a task. In this example it will find any RDS that is older than 14 days that has had no connections to it in the last 14 days and tag it with a delete op and date 14 days out. The policy workflow will also email the RDS resource owner to inform them of the upcoming stopping and deletion if the RDS remains unused. If a customer connects to the RDS before the 14 day window it will get unmarked so it doesn’t get deleted.

Note the use of the notify action requires the Cloud Custodian mailer to be installed and configured.

vars:
  metrics-filters: &metrics-filter
        type: metrics
        name: DatabaseConnections
        days: 14
        value: 0
        op: equal

policies:

- name: rds-unused-databases-notify-step1
  resource: rds
  description: |
    Take the average number of connections over 14 days for databases that are greater than 14
    days old and notify the resources owner on any unused RDS and mark for delete action in 14 days.
  filters:
    - "tag:c7n_rds_unused": absent
    - type: value
      value_type: age
      key: InstanceCreateTime
      value: 14
      op: greater-than
    - <<: *metrics-filter
    - or:
        - "tag:Resource Contact": present
        - "tag:CreatorName": present
  actions:
    - type: mark-for-op
      tag: c7n_rds_unused
      op: delete
      days: 14
    - type: notify
      template: default.html
      priority_header: 1
      subject: "RDS - Unused Database - [custodian {{ account }} - {{ region }}]"
      violation_desc: "RDS Instance has had no connections in the last 2 weeks and is unused:"
      action_desc: |
          "Actions Taken:  Database deletion has been scheduled for 14 days from now.
          At this point we are just notifying you of the upcoming deletion if not used."
      to:
        - CloudCustodian@Company.com
        - resource-owner
      transport:
          type: sqs
          queue: https://sqs.us-east-1.amazonaws.com/12345678900/cloud-custodian-mailer
          region: us-east-1

- name: rds-unused-databases-notify-step2
  resource: rds
  description: |
    Take the average number of connections over 21
    days and notify on any unused RDS that have already been marked for delete
  filters:
    - "tag:c7n_rds_unused": present
    - type: marked-for-op
      tag: c7n_rds_unused
      op: delete
      skew: 7
    - type: value
      value_type: age
      key: InstanceCreateTime
      value: 21
      op: gte
    - <<: *metrics-filter
    - or:
        - "tag:Resource Contact": present
        - "tag:CreatorName": present
  actions:
    - type: notify
      template: default.html
      priority_header: 1
      subject: "RDS - URGENT - Unused Database - [custodian {{ account }} - {{ region }}]"
      violation_desc: |
          "RDS Instance has had no connections in the last 3 weeks and is unused and will be stopped
          hourly in 5 days (if supported by DB type) and then deleted 2 days after its stopped:"
      action_desc: |
          "Actions Taken:  Hourly database stopping and email will occur in 5 days and deleted will occur in 7 days.
          At this point we are just notifying you of the upcoming stoppage and deleted if not used"
      to:
        - resource-owner
      transport:
          type: sqs
          queue: https://sqs.us-east-1.amazonaws.com/12345678900/cloud-custodian-mailer
          region: us-east-1

- name: rds-unused-databases-stop-and-nag-hourly-step3
  resource: rds
  mode:
      type: periodic
      schedule: "rate(1 hour)"
      timeout: 300
  description: |
    This policy deploys a Lambda function with an hourly CloudWatch Event Schedule trigger.
    The policy takes the average number of connections over 26 days and stops the RDS and
    notifies the resource owner hourly on any of their unused databases that have already
    been marked for deletion.
  filters:
    - "tag:c7n_rds_unused": present
    - type: marked-for-op
      tag: c7n_rds_unused
      op: delete
      skew: 1
    - type: value
      value_type: age
      key: InstanceCreateTime
      value: 26
      op: gte
    - <<: *metrics-filter
    - or:
        - "tag:Resource Contact": present
        - "tag:CreatorName": present
  actions:
    - type: notify
      template: default.html
      priority_header: 1
      subject: "RDS - URGENT!!! - Unused Database! - [custodian {{ account }} - {{ region }}]"
      violation_desc: |
          "RDS Instance has had no connections in the last 26 days and is unused
          and will be deleted in less than 48 hours"
      action_desc: |
          "Actions Taken: Hourly Stopping of RDS and notify.  Deletion will occur in less than
          48 hours. Please connect to the RDS or snapshot it if you don't need it at this time."
      to:
        - resource-owner
      transport:
          type: sqs
          queue: https://sqs.us-east-1.amazonaws.com/12345678900/cloud-custodian-mailer
          region: us-east-1

- name: rds-unused-databases-delete-step4
  resource: rds
  description: |
    Take the average number of connections over 28 days and delete
    any unused databases that have already been marked for delete
  filters:
    - "tag:c7n_rds_unused": present
    - type: marked-for-op
      tag: c7n_rds_unused
      op: delete
    - type: value
      value_type: age
      key: InstanceCreateTime
      value: 28
      op: gte
    - <<: *metrics-filter
    - or:
        - "tag:Resource Contact": present
        - "tag:CreatorName": present
  actions:
    - type: delete
      skip-snapshot: true
    - type: notify
      template: default.html
      priority_header: 1
      subject: "RDS - URGENT!!! - Unused Database Deleted! - [custodian {{ account }} - {{ region }}]"
      violation_desc: "RDS Instance has had no connections in the last 28 days and has been deleted."
      action_desc: "Actions Taken: RDS Instance(s) have been deleted."
      to:
        - CloudCustodian@Company.com
        - resource-owner
      transport:
          type: sqs
          queue: https://sqs.us-east-1.amazonaws.com/12345678900/cloud-custodian-mailer
          region: us-east-1

- name: rds-unused-databases-unmark
  resource: rds
  description: |
    The policy takes the average number of connections over 14 days and if there are connections
    then unmark the RDS instance and notify the resource owner.
  filters:
    - "tag:c7n_rds_unused": present
    - type: value
      value_type: age
      key: InstanceCreateTime
      value: 14
      op: gte
    - type: metrics
      name: DatabaseConnections
      days: 14
      value: 0
      op: gt
    - or:
        - "tag:Resource Contact": present
        - "tag:CreatorName": present
  actions:
    - type: unmark
      tags: ["c7n_rds_unused"]
    - type: notify
      template: default.html
      priority_header: 1
      subject: "RDS - Previously Unused DB Unmarked! - [custodian {{ account }} - {{ region }}]"
      violation_desc: |
          "RDS Instance that previously had no connections for over 2 weeks is now showing
          connections and it has been unmarked for deletion."
      action_desc: "Actions Taken: RDS Instance(s) have been unmarked. No further action needed"
      to:
        - CloudCustodian@Company.com
        - resource-owner
      transport:
          type: sqs
          queue: https://sqs.us-east-1.amazonaws.com/12345678900/cloud-custodian-mailer
          region: us-east-1