AWS Config advanced query for EC2 instance backups Report


Dear All,

Can you assist me in obtaining the backup report for an Amazon EC2 instance, I am trying to get it but it is not working properly. The following are the queries I am using.

SELECT configuration, configuration.Status, configuration.CreationDate, configuration.CompletionDate, configuration.BackupVaultName, configuration.RecoveryPointTags, configuration.BackupSizeInBytes, configuration.ResourceName, configuration.Lifecycle, accountId, resourceId, awsRegion, resourceName, resourceType WHERE resourceType IN ('AWS::Backup::RecoveryPoint','AWS::EC2::Instance') AND configuration.CreationDate = date(-24h)

1 Answer


It looks like you're attempting to retrieve the backup report for an Amazon EC2 instance using a query. However, there are a few things that could be causing issues with your query. Here are a few suggestions:

Make sure you have the necessary permissions: To retrieve backup reports for an EC2 instance, you'll need to have the appropriate permissions in your AWS account. Specifically, you'll need the "AWSBackupServiceRolePolicy" and "AWSBackupServiceLinkedRolePolicy" policies attached to your IAM role. If you don't have these permissions, you won't be able to retrieve the backup report.

Check your query syntax: There are a few issues with your query syntax that may be causing problems. First, it looks like you're using "date(-24h)" to retrieve backup reports for the past 24 hours. However, the correct syntax for this would be "date_sub(now(), INTERVAL 24 HOUR)". Additionally, you'll need to specify which columns you want to retrieve in your SELECT statement. Here's an updated query that should work:

SELECT backupsizeinbytes, backupvaultname, completiondate, configuration, configurationstatus, creationdate, lifecycle, recoverypointtags, resourcename, resourcetype
FROM "AWSBackup"."<backup_table>"
WHERE resourcetype = 'AWS::EC2::Instance' AND configuration.CreationDate >= date_sub(now(), INTERVAL 24 HOUR)

Note that you'll need to replace "<backup_table>" with the actual name of the backup table in your AWS account.

Check your backup settings: Finally, make sure that backups are actually being performed for your EC2 instance. You can check this by going to the AWS Backup console and verifying that your EC2 instance is included in the backup plan. If backups are not being performed, you won't be able to retrieve a backup report. I hope these suggestions help you retrieve the backup report for your Amazon EC2 instance! Please let me know if i answered your question

answered a year ago
  • Thanks, Zokir, it's not working, there an error on syntax error at line 16, column 62 ( AND configuration.CreationDate >= date_sub(now(), INTERVAL 24 HOUR)) and Error FROM clauses are currently not supported in aggregator

  • It seems that AWS Backup does not support running SQL queries directly. To obtain the backup report, you can use the AWS Management Console, AWS CLI, or SDKs. Lets try through the AWS CLI approach.

    First, make sure you have the AWS CLI installed and configured with the proper access keys and region. If you haven't installed it yet, follow the instructions here:

    Once you have the AWS CLI set up, you can use the following command to list all the recovery points for a specific backup vault:

    aws backup list-recovery-points-by-backup-vault --backup-vault-name YOUR_BACKUP_VAULT_NAME --query "RecoveryPoints[?CreationDate>=`date -24h`]"

    Replace YOUR_BACKUP_VAULT_NAME with the name of the backup vault you're using.

    The command above will return a list of recovery points created within the last 24 hours. You can use additional filters in the --query parameter to further refine the results.

    Next, to obtain the details of an EC2 instance, you can use the following command:

    aws ec2 describe-instances --instance-ids YOUR_INSTANCE_ID

    Replace YOUR_INSTANCE_ID with the ID of the EC2 instance you want to get the details for.

    You can then parse and manipulate the JSON outputs from the above commands to create a backup report in your desired format. Alternatively, you can use SDKs (e.g., Boto3 for Python) to programmatically access this information and generate the report.

    Try this and let me know

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions