turbot/googleworkspace
steampipe plugin install googleworkspace

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,
snippet
from
googleworkspace_gmail_my_message
order by
internal_date
limit
10;
select
id,
thread_id,
internal_date,
size_estimate,
snippet
from
googleworkspace_gmail_my_message
order by
internal_date
limit
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,
snippet
from
googleworkspace_gmail_my_message
where
query = 'is:unread newer_than:2d'
order by
internal_date;
select
id,
thread_id,
internal_date,
size_estimate,
snippet
from
googleworkspace_gmail_my_message
where
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,
snippet
from
googleworkspace_gmail_my_message
where
query = 'from:someuser@example.com'
order by
internal_date;
select
id,
thread_id,
internal_date,
size_estimate,
snippet
from
googleworkspace_gmail_my_message
where
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,
snippet
from
googleworkspace_gmail_my_message
where
query = 'in:draft'
order by
internal_date;
select
id,
thread_id,
internal_date,
size_estimate,
snippet
from
googleworkspace_gmail_my_message
where
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,
snippet
from
googleworkspace_gmail_my_message
where
query = 'in:chats'
order by
internal_date;
select
id,
thread_id,
internal_date,
size_estimate,
snippet
from
googleworkspace_gmail_my_message
where
query = 'in:chats'
order by
internal_date;

Schema for googleworkspace_gmail_my_message

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
history_idtextThe ID of the last history record that modified this message.
idtext=The immutable ID of the message.
internal_datetimestamp with time zone>, >=, =, <, <=The internal message creation timestamp which determines ordering in the inbox.
label_idsjsonbA list of IDs of labels applied to this message.
payloadjsonbThe parsed email structure in the message parts.
querytext=A string to filter messages matching the specified query.
rawtextThe entire email message in an RFC 2822 formatted and base64url encoded string.
sender_emailtext=Specifies the email address of the sender.
size_estimatebigintEstimated size in bytes of the message.
snippettextA short part of the message text.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
thread_idtextThe 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