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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
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.
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