Checking AWS RDS databases details
Tags: devops, aws, database, monitoring
Published: 2022-12-06 05:17:42

This blog presents a script to get AWS RDS databases detailed information using AWS CLI.

AWS CLI provides a unified command line interface to Amazon Web Services. The script is using 2 methods of the AWS CLI:

- aws ec2 describe-regions
- aws rds describe-db-instances

The script scans all the AWS regions and prints out detailed information about AWS RDS DB instances.

The output includes 

- DB engine 
- DB version
- DB identifier
- DB machine class
- MultiAZ (enabled/disabled)
- Backups Retention Period
- Allocated Storage
- Maximum Allocated Storage

The script has internal parameter nStorageAllocationWatermarkPctg to mark the DB instances that have distance between Maximum Allocated Storage and Allocated Storage less than nStorageAllocationWatermarkPctg percents. In such cases the DB instance is highlighted as “Bad”. It’s an indication that Maximum Allocated Storage should be increased.

In case a DB instance is defined without storage autoscale property it will be marked as “Bad” too. I recommend any Production-grade DB instance in the AWS RDS to define with Storage Autoscaling property.

Also in case DB instance defined with MultiAZ disabled it marked as “Bad”.

## Script

```
# Check_AWS_RDS_DB_Instances_Details

now=$(date)

echo ".................................................................. "
echo "Check AWS RDS DB Instances Details"
echo "Date: $now "

echo ".................................................................. "

tot_val=0

nStorageAllocationWatermarkPctg="25"

for region in `aws ec2 describe-regions --query 'Regions[].RegionName' --output text`
do
    printf "Region: %15s\n" $region

    db_counter_in_reg_val=0

    for db_ident in `aws rds describe-db-instances --region ${region} --query 'DBInstances[*].[DBInstanceIdentifier]' --output text`
    do

     a=`aws rds describe-db-instances --region ${region} --db-instance-identifier ${db_ident} --query 'DBInstances[*].[Engine,DBInstanceIdentifier,EngineVersion,MultiAZ,AllocatedStorage,MaxAllocatedStorage,DBInstanceClass,Status,BackupRetentionPeriod]' --output text`

     strEngine=$(echo $a | cut -d' ' -f1)
     strDBIdent=$(echo $a | cut -d' ' -f2)
     strEngineVersion=$(echo $a | cut -d' ' -f3)
     strMultiAZ=$(echo $a | cut -d' ' -f4)
     strAllocatedStorage=$(echo $a | cut -d' ' -f5)
     strMaxAllocatedStorage=$(echo $a | cut -d' ' -f6)
     strDBInstanceClass=$(echo $a | cut -d' ' -f7)
     strBackupRetentionPeriod=$(echo $a | cut -d' ' -f9)

     strAttention1="Ok"
     strAttention2="Ok"

     storage_left_pct="N/A"

     if [ "$strMaxAllocatedStorage" = "None" ]; then
        strAttention1="Bad"
     else
        storage_left="$(($strMaxAllocatedStorage-$strAllocatedStorage))"
        storage_left_pct="$(( 100*($strMaxAllocatedStorage-$strAllocatedStorage)/$strMaxAllocatedStorage ))"
 
        if [ "$storage_left_pct" -lt "$nStorageAllocationWatermarkPctg" ]; then
           strAttention1="Bad"
        fi
     fi

     if [ "$strMultiAZ" = "False" ]; then
        strAttention2="Bad"
     fi

     printf "#%-3s | eng: %-8s | ver: %-8s | ident: %-50s | class: %-15s | mAZ: %-6s %-3s | backupRetPeriod: %-3d | allocStorage: %-8s (left %-3s pct) | maxAllocStorage: %-8s %-3s \n" $((db_counter_in_reg_val + 1)) ${strEngine} ${strEngineVersion} ${strDBIdent} ${strDBInstanceClass} ${strMultiAZ} ${strAttention2} ${strBackupRetentionPeriod} ${strAllocatedStorage} ${storage_left_pct} ${strMaxAllocatedStorage} ${strAttention1}

     db_counter_in_reg_val=$((db_counter_in_reg_val + 1))

     tot_val=$((tot_val + 1))

    done
done

echo ".................................................................. "

printf "TOTAL:                                                  %10s\n" $tot_val

echo ".................................................................. "
```

## Example of output

```
…
.................................................................. 
Check AWS RDS DB Instances Details
Date: Tue Dec  6 06:48:09 IST 2022 
.................................................................. 

...
Region:       us-east-1
#1   | eng: postgres | ver: 13.3     | ident: my-db-1         | class: db.r5.xlarge    | mAZ: True   Ok  | backupRetPeriod: 35  | allocStorage: 2000     (left 20  pct) | maxAllocStorage: 2500     Bad
#2   | eng: postgres | ver: 13.3     | ident: my-db-2         | class: db.r5.xlarge    | mAZ: True   Ok  | backupRetPeriod: 35  | allocStorage: 1500     (left 20  pct) | maxAllocStorage: 1875     Bad
#3   | eng: mysql    | ver: 8.0.28    | ident: my-db-3        | class: db.m6g.large    | mAZ: False  Bad | backupRetPeriod: 7   | allocStorage: 49       (left 83  pct) | maxAllocStorage: 297      Ok  
#4   | eng: postgres | ver: 13.3     | ident: my-db-4         | class: db.t3.small     | mAZ: True   Ok  | backupRetPeriod: 7   | allocStorage: 10       (left 66  pct) | maxAllocStorage: 30       Ok  
#5   | eng: mysql    | ver: 5.7.48   | ident: my-db-5         | class: db.m5.2xlarge   | mAZ: True   Ok  | backupRetPeriod: 14  | allocStorage: 100      (left 50  pct) | maxAllocStorage: 200      Ok 
...

.................................................................. 
TOTAL:                                                         123
..................................................................
…
```

## Conclusion

In this blog I presented a script that iterates all the AWS regions and prints the detailed information about AWS RDS DB instances. The script can be used to monitor your AWS RDS database instances on development, staging and production environments.