turbot/googleworkspace
steampipe plugin install googleworkspace

Table: googleworkspace_drive_my_file - Query Google Workspace Drive Files using SQL

Google Workspace Drive is a cloud storage service within Google Workspace that allows users to store, sync, and share files. It provides a centralized way to manage files, including documents, spreadsheets, presentations, and more. Google Workspace Drive helps users collaborate on files in real time and access them from any device.

Table Usage Guide

The googleworkspace_drive_my_file table provides insights into files within Google Workspace Drive. As a Google Workspace administrator, explore file-specific details through this table, including ownership, sharing settings, and associated metadata. Utilize it to uncover information about files, such as those shared externally, the permissions associated with each file, and the verification of sharing policies.

Examples

Basic info

Discover the segments that have recently been created within your Google Workspace Drive. This can help you keep track of new additions and manage your files more efficiently.

select
name,
id,
mime_type,
created_time
from
googleworkspace_drive_my_file;
select
name,
id,
mime_type,
created_time
from
googleworkspace_drive_my_file;

List files shared by other users

Gain insights into files shared with you by other users in Google Workspace. This is particularly useful for understanding the scope of shared resources and identifying who has shared files with you.

select
name,
id,
mime_type,
created_time,
owned_by_me,
shared,
sharing_user ->> 'displayName' as sharing_user_name
from
googleworkspace_drive_my_file
where
not owned_by_me
and sharing_user is not null;
select
name,
id,
mime_type,
created_time,
owned_by_me,
shared,
json_extract(sharing_user, '$.displayName') as sharing_user_name
from
googleworkspace_drive_my_file
where
not owned_by_me
and sharing_user is not null;

List image or video files modified after a specific date

Analyze your Google Workspace Drive to pinpoint specific image or video files that have been modified after a certain date. This can be useful to track recent changes or updates to media files in your drive.

select
name,
id,
mime_type,
created_time,
web_view_link
from
googleworkspace_drive_my_file
where
query = 'modifiedTime > "2021-08-15T00:00:00" and (mimeType contains "image/" or mimeType contains "video/")';
select
name,
id,
mime_type,
created_time,
web_view_link
from
googleworkspace_drive_my_file
where
strftime('%Y-%m-%dT%H:%M:%S', created_time) > "2021-08-15T00:00:00"
and (
mime_type like '%image/%'
or mime_type like '%video/%'
);

List files using the query filter

Explore which files in your Google Workspace Drive contain the term "Steampipe". This can be particularly useful for quickly locating specific documents or resources related to Steampipe within your workspace.

select
name,
id,
mime_type,
created_time,
web_view_link
from
googleworkspace_drive_my_file
where
query = 'name contains "steampipe"';
select
name,
id,
mime_type,
created_time,
web_view_link
from
googleworkspace_drive_my_file
where
query = 'name contains "steampipe"';

Schema for googleworkspace_drive_my_file

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
app_propertiesjsonbA collection of arbitrary key-value pairs which are private to the requesting app.
capabilitiesjsonbDescribes capabilities the current user has on this file.
content_hintsjsonbAdditional information about the content of the file.
content_restrictionsjsonbRestrictions for accessing the content of the file.
copy_requires_writer_permissionbooleanIndicates whether the options to copy, print, or download this file, should be disabled for readers and commenters, or not.
created_timetimestamp with time zone>, >=, =, <, <=The time at which the file was created.
descriptiontextA short description of the file.
drive_idtextID of the shared drive the file resides in.
explicitly_trashedbooleanIndicates whether the file has been explicitly trashed, as opposed to recursively trashed from a parent folder.
export_linksjsonbLinks for exporting Docs Editors files to specific formats.
file_extensiontextThe final component of fullFileExtension.
folder_color_rgbtextThe color for a folder or shortcut to a folder as an RGB hex string.
full_file_extensiontextThe full file extension extracted from the name field.
has_augmented_permissionsbooleanIndicates whether there are permissions directly on this file, or not.
has_thumbnailbooleanIndicates whether this file has a thumbnail, or not.
head_revision_idtextThe ID of the file's head revision.
icon_linktextA static, unauthenticated link to the file's icon.
idtext=The ID of the file.
image_media_metadatajsonbAdditional metadata about image media, if available.
is_app_authorizedbooleanIndicates whether the file was created or opened by the requesting app, or not.
last_modifying_userjsonbThe last user to modify the file.
link_share_metadatajsonbContains details about the link URLs that clients are using to refer to this item.
md5_checksumtextThe MD5 checksum for the content of the file.
mime_typetext=, !=The MIME type of the file. Google Drive will attempt to automatically detect an appropriate value from uploaded content if no value is provided.
modified_by_mebooleanIndicates whether the file has been modified by this user, or not.
modified_by_me_timetimestamp with time zoneThe last time the file was modified by the use.
modified_timetimestamp with time zoneThe last time the file was modified by anyone.
nametext=Specifies the name of the file.
original_file_nametextThe original filename of the uploaded content if available, or else the original value of the name field.
owned_by_mebooleanIndicates whether the user owns the file, or not.
ownersjsonbThe owner of this file. Only certain legacy files may have more than one owner.
parentsjsonbThe IDs of the parent folders which contain the file.
permission_idsjsonbList of permission IDs for users with access to this file.
permissionsjsonbThe full list of permissions for the file.
propertiesjsonbA collection of arbitrary key-value pairs which are visible to all apps.
querytext=A search query combining one or more search terms to [filter](https://developers.google.com/drive/api/v3/search-files) the file results.
quota_bytes_usedbigintThe number of storage quota bytes used by the file.
resource_keytextA key needed to access the item via a shared link.
sharedbooleanIndicates whether the file has been shared, or not.
shared_with_me_timetimestamp with time zoneThe time at which the file was shared with the user.
sharing_userjsonbThe user who shared the file with the requesting user, if applicable.
shortcut_detailsjsonbShortcut file details. Only populated for shortcut files, which have the mimeType field set to application/vnd.google-apps.shortcut.
sizebigintThe size of the file's content in bytes.
spacesjsonbThe list of spaces which contain the file.
starredbooleanIndicates whether the user has starred the file, or not.
thumbnail_linktextA short-lived link to the file's thumbnail, if available.
thumbnail_versionbigintThe thumbnail version for use in thumbnail cache invalidation.
trashedbooleanIndicates whether the file has been trashed, either explicitly or from a trashed parent folder, or not.
trashed_timetimestamp with time zoneThe time that the item was trashed.
trashing_userjsonbSpecifies the user who trashed the file explicitly.
versionbigintA monotonically increasing version number for the file.
video_media_metadatajsonbAdditional metadata about video media.
viewed_by_mebooleanIndicates whether the the file has been viewed by this user, or not.
viewed_by_me_timetimestamp with time zoneThe last time the file was viewed by the user.
web_content_linktextA link for downloading the content of the file in a browser.
web_view_linktextA link for opening the file in a relevant Google editor or viewer in a browser.
writers_can_sharebooleanIndicates whether users with only writer permission can modify the file's permissions, or not.

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_my_file