turbot/googleworkspace
steampipe plugin install googleworkspace

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, or created_time you must set use_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 on use_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,
hidden
from
googleworkspace_drive;
select
name,
id,
created_time,
hidden
from
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,
hidden
from
googleworkspace_drive
where
hidden;
select
name,
id,
created_time,
hidden
from
googleworkspace_drive
where
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_only
from
googleworkspace_drive
where
not domain_users_only;
select
name,
id,
created_time,
domain_users_only
from
googleworkspace_drive
where
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_only
from
googleworkspace_drive
where
created_time <= current_date - interval '90 days'
and use_domain_admin_access;
select
name,
id,
created_time,
domain_users_only
from
googleworkspace_drive
where
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_only
from
googleworkspace_drive
where
query = 'createdTime > ''2021-08-01T07:00:00'' and name contains ''steampipe'''
and use_domain_admin_access;
select
name,
id,
created_time,
domain_users_only
from
googleworkspace_drive
where
query = 'createdTime > ''2021-08-01T07:00:00'' and name contains ''steampipe'''
and use_domain_admin_access;

Schema for googleworkspace_drive

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
admin_managed_restrictionsbooleanIndicates whether administrative privileges on this shared drive are required to modify restrictions, or not.
background_image_filejsonbAn image file and cropping parameters from which a background image for this shared drive is set.
background_image_linktextA short-lived link to this shared drive's background image.
capabilitiesjsonbDescribes the capabilities the current user has on this shared drive.
color_rgbtextThe color of this shared drive as an RGB hex string.
copy_requires_writer_permissionbooleanIndicates whether the options to copy, print, or download files inside this shared drive, should be disabled for readers and commenters, or not.
created_timetimestamp with time zone>, >=, =, <, <=The time at which the shared drive was created.
domain_users_onlybooleanIndicates 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_onlybooleanIndicates whether access to items inside this shared drive is restricted to its members, or not.
hiddenbooleanIndicates whether the shared drive is hidden from default view, or not.
idtext=The ID of this shared drive which is also the ID of the top level folder of this shared drive.
nametext=The name of this shared drive.
querytext=Query string for [searching](https://developers.google.com/drive/api/v3/ref-search-terms#drive_properties) shared drives.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
theme_idtextThe ID of the theme from which the background image and color will be set.
use_domain_admin_accessboolean=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