Table: googleworkspace_drive - Query Google Workspace Drives using SQL
Google Workspace Drive is a cloud-based storage solution provided by Google. It allows users to store, share, and synchronize files across devices. Google Workspace Drive supports various file formats and offers features like real-time collaboration, powerful search, and seamless integration with Google Workspace apps.
Table Usage Guide
The googleworkspace_drive
table provides insights into Drives within Google Workspace. As a system administrator or a DevOps engineer, explore specific details about each drive through this table, including the drive's type, name, theme, and other metadata. Utilize it to understand the distribution and organization of files, monitor the usage of storage space, and manage access control for sensitive files.
Important Notes
- To filter the resource using
name
, orcreated_time
you must setuse_domain_admin_access
setting as true** in the where clause, and for that you must have admin access in the domain. See Shared drive-specific query terms for information onuse_domain_admin_access
setting.
Examples
Basic info
Discover the segments that are hidden within your Google Workspace Drive. This allows you to assess elements within your Drive, such as identifying instances where files or folders have been hidden, to better manage your resources.
select name, id, created_time, hiddenfrom googleworkspace_drive;
select name, id, created_time, hiddenfrom googleworkspace_drive;
List hidden drives
Discover the segments that contain hidden drives within the Google Workspace. This can be beneficial in identifying any potentially unauthorized or suspicious activity.
select name, id, created_time, hiddenfrom googleworkspace_drivewhere hidden;
select name, id, created_time, hiddenfrom googleworkspace_drivewhere hidden = 1;
List drives that allows access to users outside the domain
Explore which Google Workspace drives permit access to users outside of the domain. This is useful for assessing potential security risks and ensuring data is shared appropriately.
select name, id, created_time, domain_users_onlyfrom googleworkspace_drivewhere not domain_users_only;
select name, id, created_time, domain_users_onlyfrom googleworkspace_drivewhere domain_users_only = 0;
List drives older than 90 days
Determine the areas in which Google Workspace drives have been in use for more than 90 days. This can be useful for identifying potentially outdated or unused resources, contributing to more efficient resource management.
select name, id, created_time, domain_users_onlyfrom googleworkspace_drivewhere created_time <= current_date - interval '90 days' and use_domain_admin_access;
select name, id, created_time, domain_users_onlyfrom googleworkspace_drivewhere created_time <= date('now', '-90 day') and use_domain_admin_access;
List drives using the query filter
Explore which Google Workspace drives were created after August 1, 2021, and contain 'steampipe' in their names. This is useful for administrators who want to monitor the creation and naming of drives within their domain.
select name, id, created_time, domain_users_onlyfrom googleworkspace_drivewhere query = 'createdTime > ''2021-08-01T07:00:00'' and name contains ''steampipe''' and use_domain_admin_access;
select name, id, created_time, domain_users_onlyfrom googleworkspace_drivewhere query = 'createdTime > ''2021-08-01T07:00:00'' and name contains ''steampipe''' and use_domain_admin_access;
Schema for googleworkspace_drive
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
admin_managed_restrictions | boolean | Indicates whether administrative privileges on this shared drive are required to modify restrictions, or not. | |
background_image_file | jsonb | An image file and cropping parameters from which a background image for this shared drive is set. | |
background_image_link | text | A short-lived link to this shared drive's background image. | |
capabilities | jsonb | Describes the capabilities the current user has on this shared drive. | |
color_rgb | text | The color of this shared drive as an RGB hex string. | |
copy_requires_writer_permission | boolean | Indicates whether the options to copy, print, or download files inside this shared drive, should be disabled for readers and commenters, or not. | |
created_time | timestamp with time zone | >, >=, =, <, <= | The time at which the shared drive was created. |
domain_users_only | boolean | Indicates whether access to this shared drive and items inside this shared drive is restricted to users of the domain to which this shared drive belongs. | |
drive_members_only | boolean | Indicates whether access to items inside this shared drive is restricted to its members, or not. | |
hidden | boolean | Indicates whether the shared drive is hidden from default view, or not. | |
id | text | = | The ID of this shared drive which is also the ID of the top level folder of this shared drive. |
name | text | = | The name of this shared drive. |
query | text | = | Query string for [searching](https://developers.google.com/drive/api/v3/ref-search-terms#drive_properties) shared drives. |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
theme_id | text | The ID of the theme from which the background image and color will be set. | |
use_domain_admin_access | boolean | = | Issue the request as a domain administrator; if set to true, then all shared drives of the domain in which the requester is an administrator are returned. Please refer Refer https://developers.google.com/drive/api/v3/ref-search-terms#drive_properties. |
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)" -- googleworkspace
You can pass the configuration to the command with the --config
argument:
steampipe_export_googleworkspace --config '<your_config>' googleworkspace_drive