steampipe plugin install gcp

Table: gcp_storage_object - Query Google Cloud Storage Objects using SQL

Google Cloud Storage is a scalable, fully-managed, highly reliable, and cost-efficient object/blob store. It is designed to handle data from any size, type, and ingestion speed with a simple and consistent API. In addition to archiving, Google Cloud Storage offers high durability for backup, restore, and disaster recovery use cases.

Table Usage Guide

The gcp_storage_object table provides insights into objects stored within Google Cloud Storage. As a data analyst or storage administrator, explore object-specific details through this table, including metadata, storage class, and associated bucket information. Utilize it to uncover information about objects, such as their size, content type, creation time, and the bucket they are stored in.

Examples

Basic info

Explore which objects within a specific Google Cloud Storage bucket are taking up the most space. This allows for efficient space management and can help identify potential areas for data optimization.

select
id,
name,
bucket,
size,
storage_class,
time_created
from
gcp_storage_object
where
bucket = 'steampipe-test';
select
id,
name,
bucket,
size,
storage_class,
time_created
from
gcp_storage_object
where
bucket = 'steampipe-test';

Get a specific object in a bucket

Discover the segments that contain a specific object within a certain bucket in Google Cloud Storage. This can be useful to assess the elements within that object such as its size, storage class, and the time it was created.

select
id,
name,
bucket,
size,
storage_class,
time_created
from
gcp_storage_object
where
bucket = 'steampipe-test'
and name = 'test/logs/2021/03/01/12/abc.txt';
select
id,
name,
bucket,
size,
storage_class,
time_created
from
gcp_storage_object
where
bucket = 'steampipe-test'
and name = 'test/logs/2021/03/01/12/abc.txt';

List storage objects with a prefix in a bucket

Explore storage objects within a specific bucket that share a common prefix, which can be useful for organizing and identifying related files or data sets. This is especially beneficial when dealing with large amounts of data, as it allows you to quickly locate and analyze related objects.

select
id,
name,
bucket,
size,
storage_class,
time_created
from
gcp_storage_object
where
bucket = 'steampipe-test'
and prefix = 'test/logs/2021/03/01/12';
select
id,
name,
bucket,
size,
storage_class,
time_created
from
gcp_storage_object
where
bucket = 'steampipe-test'
and prefix = 'test/logs/2021/03/01/12';

List storage objects encrypted with customer managed keys

Explore which storage objects within a specific bucket are encrypted using customer-managed keys. This helps in assessing the level of control you have over your data encryption and security.

select
id,
name,
bucket,
kms_key_name
from
gcp_storage_object
where
bucket = 'steampipe-test'
and kms_key_name != '';
select
id,
name,
bucket,
kms_key_name
from
gcp_storage_object
where
bucket = 'steampipe-test'
and kms_key_name != '';

Get total objects and size of each bucket

Explore the total number of objects and their combined size within each storage bucket. This can help you understand your storage usage and manage your resources more effectively.

select
bucket,
count(*) as total_objects,
sum(size) as total_size_bytes
from
gcp_storage_object o,
gcp_storage_bucket b
where
o.bucket = b.name
group by
bucket;
select
bucket,
count(*) as total_objects,
sum(size) as total_size_bytes
from
gcp_storage_object o,
gcp_storage_bucket b
where
o.bucket = b.name
group by
bucket;

List of members and their associated IAM roles for each objects

Explore which members are associated with specific roles for each object in a GCP storage bucket. This can be particularly useful for evaluating access permissions and ensuring appropriate security measures are in place.

select
bucket,
name,
p -> 'members' as member,
p ->> 'role' as role,
p ->> 'version' as version
from
gcp_storage_object,
jsonb_array_elements(iam_policy -> 'bindings') as p
where
bucket = 'steampipe-test';
select
bucket,
name,
json_extract(p.value, '$.members') as member,
json_extract(p.value, '$.role') as role,
json_extract(p.value, '$.version') as version
from
gcp_storage_object,
json_each(iam_policy, '$.bindings') as p
where
bucket = 'steampipe-test';

List of storage objects whose retention period is less than 7 days

Explore which storage objects have a retention period of less than a week. This is useful for identifying potential data loss risks or ensuring compliance with data retention policies.

select
bucket,
name,
extract(
epoch
from
(retention_expiration_time - current_timestamp)
) as retention_period_secs
from
gcp_storage_object
where
extract(
epoch
from
(retention_expiration_time - current_timestamp)
) < 604800
and bucket = 'steampipe-test';
select
bucket,
name,
strftime('%s', retention_expiration_time) - strftime('%s', 'now') as retention_period_secs
from
gcp_storage_object
where
strftime('%s', retention_expiration_time) - strftime('%s', 'now') < 604800
and bucket = 'steampipe-test';

Get accsess controls on each object in a bucket

Explore the access controls assigned to each object within a specific storage bucket to understand the roles and permissions associated with different entities. This can assist in managing security and access within your storage environment.

select
bucket,
name as object_name,
a ->> 'entity' as entity,
a ->> 'role' as role,
a ->> 'email' as email,
a ->> 'domain' as domain,
a ->> 'projectTeam' as project_team
from
gcp_storage_object,
jsonb_array_elements(acl) as a
where
bucket = 'steampipe-test';
select
bucket,
name as object_name,
json_extract(a.value, '$.entity') as entity,
json_extract(a.value, '$.role') as role,
json_extract(a.value, '$.email') as email,
json_extract(a.value, '$.domain') as domain,
json_extract(a.value, '$.projectTeam') as project_team
from
gcp_storage_object,
json_each(acl) as a
where
bucket = 'steampipe-test';

Schema for gcp_storage_object

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
acljsonbAccess controls on the object.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
buckettext=The name of the bucket containing this object.
cache_controltextCache-Control directive for the object data.
component_countbigintNumber of underlying components that make up this object.
content_dispositiontextContent-Disposition of the object data.
content_encodingtextContent-Encoding of the object data.
content_languagetextContent-Language of the object data.
content_typetextContent-Type of the object data.
crc32ctextCRC32c checksum, as described in RFC 4960, Appendix B; encoded using base64 in big-endian byte order.
custom_timetimestamp with time zoneA timestamp in RFC 3339 format specified by the user for an object
customer_encryptionjsonbMetadata of customer-supplied encryption key, if the object is encrypted by such a key
etagtextEntity tag for the object
event_based_holdbooleanWhether or not the object is under event-based hold.
generationbigintThe content generation of this object.
iam_policyjsonbAn Identity and Access Management (IAM) policy, which specifies access controls for Google Cloud resources. A `Policy` is a collection of `bindings`. A `binding` binds one or more `members` to a single `role`. Members can be user accounts, service accounts, Google groups, and domains (such as G Suite). A `role` is a named list of permissions; each `role` can be an IAM predefined role or a user-created custom role. For some types of Google Cloud resources, a `binding` can also specify a `condition`, which is a logical expression that allows access to a resource only if the expression evaluates to `true`.
idtextThe ID of the object, including the bucket name, object name, and generation number.
kindtextThe kind of item this is.
kms_key_nametextCloud KMS Key used to encrypt this object, if the object is encrypted by such a key.
md5_hashtextMD5 hash of the data; encoded using base64
media_linktextMedia download link
metadatatextUser-provided metadata, in key/value pairs.
metagenerationbigintThe version of the metadata for this object at this generation.
nametext=The name of the object.
ownerjsonbThe owner of the object. This will always be the uploader of the object.
prefixtext=The prefix of the key of the object.
projecttext=, !=, ~~, ~~*, !~~, !~~*The GCP Project in which the resource is located.
retention_expiration_timetimestamp with time zoneA server-determined value that specifies the earliest time that the object's retention period expires.
self_linktextThe link to this object.
sizebigintContent-Length of the data in bytes.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
storage_classtextStorage class of the object.
temporary_holdbooleanWhether or not the object is under temporary hold.
time_createdtimestamp with time zoneThe creation time of the object.
time_deletedtimestamp with time zoneThe deletion time of the object.
time_storage_class_updatedtimestamp with time zoneThe time at which the object's storage class was last changed.
titletextTitle of the resource.
updatedtimestamp with time zoneThe modification time of 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)" -- gcp

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

steampipe_export_gcp --config '<your_config>' gcp_storage_object