Table: googleworkspace_gmail_my_draft - Query Google Workspace Gmail Drafts using SQL
Gmail Drafts in Google Workspace is a feature that allows users to save and manage draft messages before they are sent. These drafts include not only the content of the potential email but also metadata such as the draft's ID, message ID, and thread ID. Gmail Drafts serves as a useful tool for managing email communications and tracking unsent messages within a Google Workspace environment.
Table Usage Guide
The googleworkspace_gmail_my_draft
table provides insights into draft messages within Google Workspace's Gmail. As an IT administrator, explore draft-specific details through this table, including content, status, and associated metadata. Utilize it to uncover information about drafts, such as those that have been left unsent or abandoned, and the details of these drafts, to better manage email communications within your organization.
Examples
Basic info
Explore which drafts in your Google Workspace Gmail account have a large estimated size. This can help manage your storage space and identify drafts that may be too large to send.
select draft_id, message_id, message_thread_id, message_internal_date, message_size_estimate, message_snippetfrom googleworkspace_gmail_my_draft;
select draft_id, message_id, message_thread_id, message_internal_date, message_size_estimate, message_snippetfrom googleworkspace_gmail_my_draft;
List unread draft messages
Explore which draft messages are still unread. This can help in prioritizing responses and ensuring important communications are not missed.
select draft_id, message_id, message_thread_id, message_internal_date, message_size_estimate, message_snippetfrom googleworkspace_gmail_my_draftwhere query = 'is:unread';
select draft_id, message_id, message_thread_id, message_internal_date, message_size_estimate, message_snippetfrom googleworkspace_gmail_my_draftwhere query = 'is:unread';
List draft messages older than 30 days
Explore which draft messages have been left untouched for over a month. This query is useful in identifying stale drafts that might need attention or deletion.
select draft_id, message_id, message_thread_id, message_internal_date, message_size_estimate, message_snippetfrom googleworkspace_gmail_my_draftwhere message_internal_date <= (current_date - interval '30' day);
select draft_id, message_id, message_thread_id, message_internal_date, message_size_estimate, message_snippetfrom googleworkspace_gmail_my_draftwhere message_internal_date <= date('now', '-30 day');
List draft messages without a body
Uncover the details of draft emails that lack content. This query is particularly useful when you want to clean up your drafts folder by identifying and removing empty draft messages.
select draft_id, message_id, message_thread_id, message_internal_date, message_size_estimate, message_snippetfrom googleworkspace_gmail_my_draftwhere message_snippet is null;
select draft_id, message_id, message_thread_id, message_internal_date, message_size_estimate, message_snippetfrom googleworkspace_gmail_my_draftwhere message_snippet is null;
Schema for googleworkspace_gmail_my_draft
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
draft_id | text | = | The immutable ID of the draft. |
message_history_id | text | The ID of the last history record that modified this message. | |
message_id | text | The immutable ID of the message. | |
message_internal_date | timestamp with time zone | >, >=, =, <, <= | The internal message creation timestamp which determines ordering in the inbox. |
message_label_ids | jsonb | A list of IDs of labels applied to this message. | |
message_payload | jsonb | The parsed email structure in the message parts. | |
message_raw | text | The entire email message in an RFC 2822 formatted and base64url encoded string. | |
message_size_estimate | bigint | Estimated size in bytes of the message. | |
message_snippet | text | A short part of the message text. | |
message_thread_id | text | The ID of the thread the message belongs to. | |
query | text | = | A string to filter messages matching the specified query. |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. |
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_gmail_my_draft