turbot/cloudflare
steampipe plugin install cloudflare

Table: cloudflare_r2_object_data - Query Cloudflare R2 Objects using SQL

Cloudflare R2 is a storage service that offers a simple, scalable, and cost-effective way to store and retrieve any amount of data at any time. It is designed to deliver 99.999999999% durability, and scale past trillions of objects worldwide. Customers use R2 for backups, restores, and to serve user-generated content.

Table Usage Guide

The cloudflare_r2_object_data table provides insights into the objects stored in Cloudflare R2 storage. As a data analyst or a DevOps engineer, you can explore object-specific details through this table, including object metadata, storage class, and associated data. Utilize it to uncover information about objects, such as their size, last modified date, and the storage class they belong to.

Important Notes

  • You must specify both the key and bucket in the where clause to query this table.
  • Using this table adds to cost to your monthly bill from Cloudflare. Optimizations have been put in place to minimize the impact as much as possible. Please refer to Cloudflare R2 Pricing to understand the cost implications.

Examples

Basic info

Explore which types of content are stored in a specific Cloudflare account and bucket. This can be useful for understanding the structure and organization of your data, particularly for large-scale log management.

select
key,
bucket,
content_type
from
cloudflare_r2_object_data
where
account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';
select
key,
bucket,
content_type
from
cloudflare_r2_object_data
where
account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';

Parse object data into jsonb

Analyze the settings to understand the specific object data within a given Cloudflare account and bucket. This is particularly useful for exploring and understanding application log data.

select
key,
bucket,
data :: jsonb
from
cloudflare_r2_object_data
where
account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';
select
key,
bucket,
json(data)
from
cloudflare_r2_object_data
where
account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';

Process jsonb data in objects

Determine the areas in which errors occur in your application by analyzing event data from your Cloudflare logs. This allows you to pinpoint specific instances of error levels, enhancing your ability to troubleshoot and improve your application's performance.

select
event ->> 'level' as level,
event ->> 'severity' as severity,
event ->> 'message' as event_message,
event ->> 'data' as event_data,
event ->> 'timestamp' as timestamp
from
cloudflare_r2_object_data,
jsonb_array_elements((data :: jsonb) -> 'events') as event
where
account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/auth_logs.json.gz'
and event ->> 'level' = 'error';
select
json_extract(event.value, '$.level') as level,
json_extract(event.value, '$.severity') as severity,
json_extract(event.value, '$.message') as event_message,
json_extract(event.value, '$.data') as event_data,
json_extract(event.value, '$.timestamp') as timestamp
from
cloudflare_r2_object_data,
json_each(json_extract(data, '$.events')) as event
where
account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/auth_logs.json.gz'
and json_extract(event.value, '$.level') = 'error';

Get the raw binary data by converting back from base64

Discover the segments that enable the extraction of raw binary data from a specific user's uploaded files in a Cloudflare account. This might be used to analyze or manipulate the file data directly, bypassing the need for base64 encoding.

select
decode(data, 'base64')
from
cloudflare_r2_object_data
where
account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'user_uploads'
and key = 'avatar_9ac3097c-1e56-4108-b92e-226a3f4caeb8';
select
decode(data, 'base64')
from
cloudflare_r2_object_data
where
account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'user_uploads'
and key = 'avatar_9ac3097c-1e56-4108-b92e-226a3f4caeb8';

List the object data of those objects that are encrypted with SSE KMS key

Explore which objects within a specific account and bucket are encrypted using a KMS key. This is particularly useful for identifying and managing sensitive data that requires enhanced security measures.

select
key,
bucket,
content_type
from
cloudflare_r2_object_data
where
sse_kms_key_id is not null
and account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';
select
key,
bucket,
content_type
from
cloudflare_r2_object_data
where
sse_kms_key_id is not null
and account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';

List the object data of those objects that are expiring in the next 7 days

Determine the details of certain objects set to expire within the next week. This could be particularly useful for managing and prioritizing updates or renewals for those objects, especially within a large dataset.

select
key,
bucket,
content_type
from
cloudflare_r2_object_data
where
expires >= now() + interval '7' day
and account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';
select
key,
bucket,
content_type
from
cloudflare_r2_object_data
where
expires >= datetime('now', '+7 days')
and account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';

List the object data of those objects that are 'Delete Marker'

Determine the areas in which certain objects are marked for deletion within a specific account and bucket. This is particularly useful in identifying and managing potential data removals.

select
key,
bucket,
content_type
from
cloudflare_r2_object_data
where
delete_marker
and account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';
select
key,
bucket,
content_type
from
cloudflare_r2_object_data
where
delete_marker
and account_id = 'fb1696f453testaccount39e734f5f96e9'
and bucket = 'logs'
and key = 'logs/application_logs/2020/11/04/14/40/dashboard/db_logs.json.gz';

Schema for cloudflare_r2_object_data

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
accept_rangestextIndicates that a range of bytes was specified.
account_idtext=ID of the account.
buckettext=The name of the container bucket of the object.
bucket_key_enabledbooleanIndicates whether the object uses an S3 Bucket Key for server-side encryption with Amazon Web Services KMS (SSE-KMS).
cache_controltextSpecifies caching behavior along the request/reply chain.
checksum_crc32textThe base64-encoded, 32-bit CRC32 checksum of the object. This will only be present if it was uploaded with the object. With multipart uploads, this may not be a checksum value of the object.
checksum_crc32ctextThe base64-encoded, 32-bit CRC32C checksum of the object. This will only be present if it was uploaded with the object. With multipart uploads, this may not be a checksum value of the object.
checksum_sha1textThe base64-encoded, 160-bit SHA-1 digest of the object. This will only be present if it was uploaded with the object. With multipart uploads, this may not be a checksum value of the object.
checksum_sha256textThe base64-encoded, 256-bit SHA-256 digest of the object. This will only be present if it was uploaded with the object. With multipart uploads, this may not be a checksum value of the object.
content_dispositiontextSpecifies presentational information for the object.
content_encodingtextSpecifies what content encodings have been applied to the object.
content_languagetextThe language the content is in.
content_lengthbigintSize of the body in bytes.
content_rangetextThe portion of the object returned in the response.
content_typetextA standard MIME type describing the format of the object data.
datatextThe raw bytes of the object as a string. An UTF8 encoded string is sent, if the bytes entirely consists of valid UTF8 runes, an UTF8 is sent otherwise the bas64 encoding of the bytes is sent.
delete_markerbooleanSpecifies whether the object retrieved was (true) or was not (false) a Delete Marker. If false, this response header does not appear in the response.
etagtextA a hash of the object.
expirationtextIf the object expiration is configured (see PUT Bucket lifecycle), the response includes this header. It includes the expiry-date and rule-id key-value pairs providing object expiration information. The value of the rule-id is URL-encoded.
expirestimestamp with time zoneThe date and time at which the object is no longer cacheable.
keytext=The name assigned to an object.
last_modifiedtimestamp with time zoneSpecifies the time when the object is last modified.
metadatajsonbA map of metadata to store with the object in S3.
missing_metabigintA map of metadata to store with the object in S3.
object_lock_legal_hold_statusjsonbIndicates whether this object has an active legal hold. This field is only returned if you have permission to view an object's legal hold status.
object_lock_modejsonbThe Object Lock mode currently in place for this object.
object_lock_retain_until_datejsonbThe date and time when this object's Object Lock will expire.
parts_countbigintThe count of parts this object has. This value is only returned if you specify partNumber in your request and the object was uploaded as a multipart upload.
replication_statusjsonbAmazon S3 can return this if your request involves a bucket that is either a source or destination in a replication rule.
request_chargedjsonbIf present, indicates that the requester was successfully charged for the request.
restoretextProvides information about object restoration action and expiration time of the restored object copy.
result_metadatajsonbMetadata pertaining to the operation's result.
server_side_encryptionjsonbThe server-side encryption algorithm used when storing this object in Amazon S3 (for example, AES256, aws:kms).
sse_customer_algorithmtextIf server-side encryption with a customer-provided encryption key was requested, the response will include this header confirming the encryption algorithm used.
sse_customer_key_md5textIf server-side encryption with a customer-provided encryption key was requested, the response will include this header to provide round-trip message integrity verification of the customer-provided encryption key.
sse_kms_key_idtextIf present, specifies the ID of the Amazon Web Services Key Management Service (Amazon Web Services KMS) symmetric customer managed key that was used for the object.
storage_classjsonbProvides storage class information of the object. Amazon S3 returns this header for all objects except for S3 Standard storage class objects.
tag_countbigintThe number of tags, if any, on the object.
titletextTitle of the resource.
version_idtextVersion of the object.
website_redirection_loactiontextIf the bucket is configured as a website, redirects requests for this object to another object in the same bucket or to an external URL. Amazon S3 stores the value of this header in the object metadata.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- cloudflare

You can pass the configuration to the command with the --config argument:

steampipe_export_cloudflare --config '<your_config>' cloudflare_r2_object_data