Table: googleworkspace_gmail_my_message - Query Google Workspace Gmail Messages using SQL
Gmail is a service within Google Workspace that provides a robust and secure platform for sending, receiving, and storing email. It offers an intuitive interface for users to manage their emails, including features such as spam filtering, conversation view, and powerful search. Gmail is designed to be accessed on any device, providing flexibility and continuity for users on the go.
Table Usage Guide
The googleworkspace_gmail_my_message
table provides insights into Gmail Messages within Google Workspace. As a system administrator, explore message-specific details through this table, including the sender, recipient, subject, and timestamp. Utilize it to uncover information about messages, such as those marked as spam, the communication patterns, and the verification of message headers.
Examples
Basic info
Explore your recent Gmail messages to gain insights into their content and size. This aids in managing your inbox by identifying large or outdated threads.
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messageorder by internal_datelimit 10;
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messageorder by internal_datelimit 10;
List unread messages received in last 2 days
Explore which unread messages have been received in the last two days. This helps to prioritize the most recent and potentially urgent communications that require your attention.
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messagewhere query = 'is:unread newer_than:2d'order by internal_date;
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messagewhere query = 'is:unread newer_than:2d'order by internal_date;
List messages from a specific user
Explore the communication history of a specific user to understand the context and frequency of their interactions. This is particularly useful in situations where you need to track the activity of a particular individual for audit or investigation purposes.
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messagewhere query = 'from:someuser@example.com'order by internal_date;
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messagewhere query = 'from:someuser@example.com'order by internal_date;
List draft messages
Explore your draft messages in Gmail to understand their content and size. This can help in managing your drafts more effectively by identifying large drafts or older drafts that may need attention.
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messagewhere query = 'in:draft'order by internal_date;
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messagewhere query = 'in:draft'order by internal_date;
List chat messages
Explore your chat history to gain insights into the frequency and content of your interactions. This can be useful for assessing communication patterns and identifying key conversations.
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messagewhere query = 'in:chats'order by internal_date;
select id, thread_id, internal_date, size_estimate, snippetfrom googleworkspace_gmail_my_messagewhere query = 'in:chats'order by internal_date;
Schema for googleworkspace_gmail_my_message
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
history_id | text | The ID of the last history record that modified this message. | |
id | text | = | The immutable ID of the message. |
internal_date | timestamp with time zone | >, >=, =, <, <= | The internal message creation timestamp which determines ordering in the inbox. |
label_ids | jsonb | A list of IDs of labels applied to this message. | |
payload | jsonb | The parsed email structure in the message parts. | |
query | text | = | A string to filter messages matching the specified query. |
raw | text | The entire email message in an RFC 2822 formatted and base64url encoded string. | |
sender_email | text | = | Specifies the email address of the sender. |
size_estimate | bigint | Estimated size in bytes of the message. | |
snippet | text | A short part of the message text. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
thread_id | text | The ID of the thread the message belongs to. |
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_message