turbot/googleworkspace
steampipe plugin install googleworkspace

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_snippet
from
googleworkspace_gmail_my_draft;
select
draft_id,
message_id,
message_thread_id,
message_internal_date,
message_size_estimate,
message_snippet
from
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_snippet
from
googleworkspace_gmail_my_draft
where
query = 'is:unread';
select
draft_id,
message_id,
message_thread_id,
message_internal_date,
message_size_estimate,
message_snippet
from
googleworkspace_gmail_my_draft
where
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_snippet
from
googleworkspace_gmail_my_draft
where
message_internal_date <= (current_date - interval '30' day);
select
draft_id,
message_id,
message_thread_id,
message_internal_date,
message_size_estimate,
message_snippet
from
googleworkspace_gmail_my_draft
where
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_snippet
from
googleworkspace_gmail_my_draft
where
message_snippet is null;
select
draft_id,
message_id,
message_thread_id,
message_internal_date,
message_size_estimate,
message_snippet
from
googleworkspace_gmail_my_draft
where
message_snippet is null;

Schema for googleworkspace_gmail_my_draft

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
draft_idtext=The immutable ID of the draft.
message_history_idtextThe ID of the last history record that modified this message.
message_idtextThe immutable ID of the message.
message_internal_datetimestamp with time zone>, >=, =, <, <=The internal message creation timestamp which determines ordering in the inbox.
message_label_idsjsonbA list of IDs of labels applied to this message.
message_payloadjsonbThe parsed email structure in the message parts.
message_rawtextThe entire email message in an RFC 2822 formatted and base64url encoded string.
message_size_estimatebigintEstimated size in bytes of the message.
message_snippettextA short part of the message text.
message_thread_idtextThe ID of the thread the message belongs to.
querytext=A string to filter messages matching the specified query.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe 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