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_timefrom googleworkspace_drive_my_file;
select name, id, mime_type, created_timefrom 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_namefrom googleworkspace_drive_my_filewhere 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_namefrom googleworkspace_drive_my_filewhere 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_linkfrom googleworkspace_drive_my_filewhere query = 'modifiedTime > "2021-08-15T00:00:00" and (mimeType contains "image/" or mimeType contains "video/")';
select name, id, mime_type, created_time, web_view_linkfrom googleworkspace_drive_my_filewhere 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_linkfrom googleworkspace_drive_my_filewhere query = 'name contains "steampipe"';
select name, id, mime_type, created_time, web_view_linkfrom googleworkspace_drive_my_filewhere query = 'name contains "steampipe"';
Schema for googleworkspace_drive_my_file
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
app_properties | jsonb | A collection of arbitrary key-value pairs which are private to the requesting app. | |
capabilities | jsonb | Describes capabilities the current user has on this file. | |
content_hints | jsonb | Additional information about the content of the file. | |
content_restrictions | jsonb | Restrictions for accessing the content of the file. | |
copy_requires_writer_permission | boolean | Indicates whether the options to copy, print, or download this file, should be disabled for readers and commenters, or not. | |
created_time | timestamp with time zone | >, >=, =, <, <= | The time at which the file was created. |
description | text | A short description of the file. | |
drive_id | text | ID of the shared drive the file resides in. | |
explicitly_trashed | boolean | Indicates whether the file has been explicitly trashed, as opposed to recursively trashed from a parent folder. | |
export_links | jsonb | Links for exporting Docs Editors files to specific formats. | |
file_extension | text | The final component of fullFileExtension. | |
folder_color_rgb | text | The color for a folder or shortcut to a folder as an RGB hex string. | |
full_file_extension | text | The full file extension extracted from the name field. | |
has_augmented_permissions | boolean | Indicates whether there are permissions directly on this file, or not. | |
has_thumbnail | boolean | Indicates whether this file has a thumbnail, or not. | |
head_revision_id | text | The ID of the file's head revision. | |
icon_link | text | A static, unauthenticated link to the file's icon. | |
id | text | = | The ID of the file. |
image_media_metadata | jsonb | Additional metadata about image media, if available. | |
is_app_authorized | boolean | Indicates whether the file was created or opened by the requesting app, or not. | |
last_modifying_user | jsonb | The last user to modify the file. | |
link_share_metadata | jsonb | Contains details about the link URLs that clients are using to refer to this item. | |
md5_checksum | text | The MD5 checksum for the content of the file. | |
mime_type | text | =, != | 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_me | boolean | Indicates whether the file has been modified by this user, or not. | |
modified_by_me_time | timestamp with time zone | The last time the file was modified by the use. | |
modified_time | timestamp with time zone | The last time the file was modified by anyone. | |
name | text | = | Specifies the name of the file. |
original_file_name | text | The original filename of the uploaded content if available, or else the original value of the name field. | |
owned_by_me | boolean | Indicates whether the user owns the file, or not. | |
owners | jsonb | The owner of this file. Only certain legacy files may have more than one owner. | |
parents | jsonb | The IDs of the parent folders which contain the file. | |
permission_ids | jsonb | List of permission IDs for users with access to this file. | |
permissions | jsonb | The full list of permissions for the file. | |
properties | jsonb | A collection of arbitrary key-value pairs which are visible to all apps. | |
query | text | = | 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_used | bigint | The number of storage quota bytes used by the file. | |
resource_key | text | A key needed to access the item via a shared link. | |
shared | boolean | Indicates whether the file has been shared, or not. | |
shared_with_me_time | timestamp with time zone | The time at which the file was shared with the user. | |
sharing_user | jsonb | The user who shared the file with the requesting user, if applicable. | |
shortcut_details | jsonb | Shortcut file details. Only populated for shortcut files, which have the mimeType field set to application/vnd.google-apps.shortcut. | |
size | bigint | The size of the file's content in bytes. | |
spaces | jsonb | The list of spaces which contain the file. | |
starred | boolean | Indicates whether the user has starred the file, or not. | |
thumbnail_link | text | A short-lived link to the file's thumbnail, if available. | |
thumbnail_version | bigint | The thumbnail version for use in thumbnail cache invalidation. | |
trashed | boolean | Indicates whether the file has been trashed, either explicitly or from a trashed parent folder, or not. | |
trashed_time | timestamp with time zone | The time that the item was trashed. | |
trashing_user | jsonb | Specifies the user who trashed the file explicitly. | |
version | bigint | A monotonically increasing version number for the file. | |
video_media_metadata | jsonb | Additional metadata about video media. | |
viewed_by_me | boolean | Indicates whether the the file has been viewed by this user, or not. | |
viewed_by_me_time | timestamp with time zone | The last time the file was viewed by the user. | |
web_content_link | text | A link for downloading the content of the file in a browser. | |
web_view_link | text | A link for opening the file in a relevant Google editor or viewer in a browser. | |
writers_can_share | boolean | Indicates 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