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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
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.
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