When you load data into a table or create an empty table in BigQuery, you must specify a schema. The schema in this article defines and describes the fields associated with Gmail logs in BigQuery.
Learn how to specify and modify schemas in BigQuery.
Schema updates
We occasionally update the schema in this article. When new fields are added to the template table, the next daily table generated from the template has the new fields. If you want to query new fields, query daily tables generated after the template was updated.
Gmail Schema
event_info
Type | RECORD | Mode | REQUIRED |
---|---|---|---|
Description | General information of the event |
event_info.elapsed_time_usec
Type | INTEGER | Mode | NULLABLE |
---|---|---|---|
Description | Total time duration of the event, in microseconds |
event_info.success
Type | BOOLEAN | Mode | REQUIRED |
---|---|---|---|
Description |
True if the event was successful, otherwise false For example, the value is false if the message was rejected by a policy. |
event_info.timestamp_usec
Type | INTEGER | Mode | REQUIRED |
---|---|---|---|
Description | Time when this event started, in the form of a UNIX timestamp, in microseconds |
message_info
Type | RECORD | Mode | NULLABLE |
---|---|---|---|
Description | General information about the message |
message_info.action_type
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
The action this event represents.
|
message_info.attachment
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description |
Information about the message’s attachments This record is repeated for every attachment. |
message_info.attachment.file_extension_type
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | File extension (not mime part type), not including the period. |
message_info.attachment.malware_family
Type | INTEGER | Mode | NULLABLE | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Malware category, if detected when the message is handled. This field is unset if no malware is detected.
|
message_info.attachment.sha256
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | SHA256 hash of the attachment |
message_info.connection_info
Type | RECORD | Mode | NULLABLE |
---|---|---|---|
Description | Information about the connection the message was sent over |
message_info.connection_info.authenticated_domain
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description | List of authenticated domain names and authentication mechanisms |
message_info.connection_info.authenticated_domain.name
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Authenticated domain name |
message_info.connection_info.authenticated_domain.type
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Message authentication type (for example, SPF, DKIM).
|
message_info.connection_info.client_host_zone
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Client host zone of the mail sender |
message_info.connection_info.client_ip
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | IP address of the mail client that started the message |
message_info.connection_info.dkim_pass
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description | Indicates if the message was authenticated using at least one DKIM signature |
message_info.connection_info.dmarc_pass
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description | Indicates if the message passed DMARC policy evaluation |
message_info.connection_info.dmarc_published_domain
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Domain name used to evaluate the DMARC policy |
message_info.connection_info.failed_smtp_out_connect_ip
Type | STRING | Mode | REPEATED |
---|---|---|---|
Description | List of all IPs in the remote MX record that Gmail attempted to connect to but failed |
message_info.connection_info.ip_geo_city
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Nearest city computed based on the relay IP |
message_info.connection_info.ip_geo_country
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | ISO country code based on the relay IP |
message_info.connection_info.is_internal
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description | Indicates if the message was sent within domains owned by the customer |
message_info.connection_info.is_intra_domain
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description | Indicates if the message was sent within the same domain |
message_info.connection_info.smtp_in_connect_ip
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Remote IP address for MTA client connections (inbound SMTP to Gmail) |
message_info.connection_info.smtp_out_connect_ip
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Remote IP address for SMTP connections from Gmail |
message_info.connection_info.smtp_out_remote_host
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | For outgoing SMTP connections, the domain the message started from; the destination domain or the smarthost |
message_info.connection_info.smtp_reply_code
Type | INTEGER | Mode | NULLABLE |
---|---|---|---|
Description |
SMTP reply code for inbound and outbound SMTP connections Usually 2xx, 4xx, or 5xx. |
message_info.connection_info.smtp_response_reason
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Detailed reason for the SMTP reply code for inbound connections
|
message_info.connection_info.smtp_tls_cipher
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Name of the TLS cipher being used for secure connections to the SMTP server. Examples: AES128-SHA, ECDHE-ECDSA-AES256-GCM-SHA384, and DES-CBC3-SHA. |
message_info.connection_info.smtp_tls_state
Type | INTEGER | Mode | NULLABLE | ||||||
---|---|---|---|---|---|---|---|---|---|
Description |
Type of connection made to the SMTP server. Only set for logs of events that explicitly handle SMTP connections.
|
message_info.connection_info.smtp_tls_version
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description |
Version of TLS being used for secure connections to the SMTP server (for example, TLSv1.2). |
message_connection_info.smtp_user_agent_ip
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | IP address of the mail user agent for inbound SMTP connections |
message_info.connection_info.spf_pass
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description | Indicates if the message was authenticated with SPF |
message_info.connection_info.tls_required_but_unavailable
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description | TLS is required for an outbound SMTP connection, but no valid certificate was present |
message_info.description
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Human-readable description of what happened to the message |
message_info.destination
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description |
Information about message recipients This record is repeated for every recipient. |
message_info.destination.address
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Email address of the recipient |
message_info.destination.rcpt_response
Type | INTEGER | Mode | NULLABLE |
---|---|---|---|
Description |
Response of the SMTP RCPT command. See message_info.connection_info.smtp_response_reason for an explanation of the values. |
message_info.destination.selector
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description |
Subcategory for each service See message_info.destination.service for an explanation of the values. |
message_info.destination.service
Type | STRING | Mode | NULLABLE | |||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
The service at the message destination. There are many service and selector pairs for destinations. You can use these two fields to determine which service the message was sent to.
|
message_info.destination.smime_decryption_success
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
For inbound messages only When set, indicates that S/MIME decryption was attempted for this recipient.The value indicates the completion status. Not set if skipped |
message_info.destination.smime_extraction_success
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
For inbound messages only When set, indicates that S/MIME extraction was attempted for this recipient. The value indicates the completion status. Not set if skipped |
message_info.destination.smime_parsing_success
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
For inbound messages only When set, indicates that S/MIME parsing was attempted for this recipient. The value indicates the completion status. Not set if skipped |
message_info.destination.smime_signature_verification_success
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
For inbound messages only When set, indicates that S/MIME signature verification was attempted for this recipient. The value indicates the completion status. Not set if skipped |
message_info.flattened_destinations
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description |
String that has information of all recipient information flattened, in this format: |
message_info.flattened_triggered_rule_info
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | String that has information of all triggered rules, in JSON format |
message_info.is_policy_check_for_sender
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
True if the policy rules were evaluated for the sender (the message was processed for outbound delivery) False if the policy rules were evaluated for the recipient (the message was processed for inbound delivery) |
message_info.is_spam
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description | True if the message was classified as spam |
message_info.link_domain
Type | STRING | Mode | REPEATED |
---|---|---|---|
Description | Domains extracted from link URLs in the message body |
message_info.message_set
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description |
Message set type that the message belongs to. See message_info.message_set.type. |
message_info.message_set.type
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Message set types are attributes that describe the message. For example, if the message was inbound, outbound, or internal.
|
message_info.num_message_attachments
Type | INTEGER | Mode | NULLABLE |
---|---|---|---|
Description | Number of message attachments |
message_info.payload_size
Type | INTEGER | Mode | NULLABLE |
---|---|---|---|
Description | Size of the message payload, in bytes |
message_info.rfc2822_message_id
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | RFC 2822 message ID for the message. To see this, select Show Original for the Gmail message. |
message_info.smime_content_type
Type | INTEGER | Mode | NULLABLE | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
The top-level S/MIME type of a message, indicated by the Content-Type header.
|
message_info.smime_encrypt_message
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
For outbound messages only When set and true, indicates the message should be encrypted. |
message_info.smime_extraction_success
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
When set, indicates that inbound S/MIME processing occurred. Not set if skipped. The value indicates the completion status. Note: Currently not set. |
message_info.smime_packaging_success
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
For outbound messages only When set, indicates that S/MIME packaging was attempted. Not set if skipped. The value indicates the completion status. |
message_info.smime_sign_message
Type | BOOLEAN | Mode | NULLABLE |
---|---|---|---|
Description |
For outbound messages only When set and true, indicates message should be signed. |
message_info.smtp_relay_error
Type | INTEGER | Mode | NULLABLE | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
If Gmail rejects an SMTP relay request, this error code provides information about the cause of the rejection.
|
message_info.source
Type | RECORD | Mode | NULLABLE |
---|---|---|---|
Description | Information about the sender |
message_info.source.address
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Email address of the sender |
message_info.source.from_header_address
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | From header address as it appears in the message headers, for example, [email protected] |
message_info.source.from_header_displayname
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description |
From header display name as it appears in the message headers, for example, John Doe Note: This field may be truncated if the log is too long, or the number of triggered rules (triggered_rule_info) in the log is too big. |
message_info.source.selector
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description |
A subcategory of the source server See message_info.source.service for value descriptions. |
message_info.source.service
Type | STRING | Mode | NULLABLE | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
The source service for the message. Use these two fields to determine which service sent the message and why the message was generated.
|
message_info.spam_info
Type | RECORD | Mode | NULLABLE |
---|---|---|---|
Description | Spam classification information |
message_info.spam_info.classification_reason
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Reason the message was classified as spam, phishing, or other classification.
|
message_info.spam_info.classification_timestamp_usec
Type | INTEGER | Mode | NULLABLE |
---|---|---|---|
Description | Message spam classification timestamp |
message_info.spam_info.disposition
Type | INTEGER | Mode | NULLABLE | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
The outcome of the Gmail spam classification
|
message_info.spam_info.ip_whitelist_entry
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description |
The IP whitelist entry that informed the classification, when the message is classified by a custom rule in Gmail settings. |
message_info.structured_policy_log_info
Type | RECORD | Mode | NULLABLE |
---|---|---|---|
Description | Structured information about policies that were evaluated for the message. This includes information about journaling and detected file types. |
message_info.structured_policy_log_info.detected_file_types
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description | Information about file types |
message_info.structured_policy_log_info.detected_file_types.category
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
MIME type category
|
message_info.structured_policy_log_info.detected_file_types.mime_type
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | MIME type of the file |
message_info.structured_policy_log_info.exchange_journal_info
Type | RECORD | Mode | NULLABLE |
---|---|---|---|
Description | Information about Exchange journaling of the message |
message_info.structured_policy_log_info.exchange_journal_info.recipients
Type | STRING | Mode | REPEATED |
---|---|---|---|
Description | Domain recipients for the journaled message known to Google |
message_info.structured_policy_log_info.exchange_journal_info.rfc822_message_id
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | RFC 822 message ID of the journaled message |
message_info.structured_policy_log_info.exchange_journal_info.timestamp
Type | INTEGER | Mode | NULLABLE |
---|---|---|---|
Description | The timestamp of the journaled message, in seconds |
message_info.structured_policy_log_info.exchange_journal_info.unknown_recipients
Type | STRING | Mode | REPEATED |
---|---|---|---|
Description | Domain recipients unknown to Google for the journaled message |
message_info.subject
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Message subject.
Note: This field may be truncated if the log is too long, or the number of triggered rules (triggered_rule_info) in the log is too big. |
message_info.triggered_rule_info
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description | Information about policy rules triggered for the message |
message_info.triggered_rule_info.consequence
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description | Information about a consequence applied to the message due to the triggered rule |
message_info.triggered_rule_info.consequence.action
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Action taken for the consequence
|
message_info.triggered_rule_info.consequence.reason
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Reason the consequence was applied. Usually contains the unique description of a rule that triggered the consequence. |
message_info.triggered_rule_info.consequence.subconsequence
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description | Information about a sub-consequence of the primary consequence |
message_info.triggered_rule_info.consequence.subconsequence.action
Type | INTEGER | Mode | NULLABLE |
---|---|---|---|
Description |
Action taken for the sub-consequence See consequence action for an description of possible values. |
message_info.triggered_rule_info.consequence.subconsequence.reason
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Reason the sub-consequence was applied. Usually contains the unique description of a rule that triggered the consequence. |
message_info.triggered_rule_info.policy_holder_address
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Email address of the policyholder whose policy triggered the rules |
message_info.triggered_rule_info.rule_name
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | Custom rule description provided by an administrator in the Admin Console |
message_info.triggered_rule_info.rule_type
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Custom rule type
|
message_info.triggered_rule_info.spam_label_modifier
Type | INTEGER | Mode | NULLABLE | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Describes the custom rule spam classification results
|
message_info.triggered_rule_info.string_match
Type | RECORD | Mode | REPEATED |
---|---|---|---|
Description | The rule was triggered because of a string match. For example, a content compliance rule that contains the information about the string matches. |
message_info.triggered_rule_info.string_match.attachment_name
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description |
Name of the attachment where a matching string was found in the text extracted from a binary file. Note: This field is currently not populated. |
message_info.triggered_rule_info.string_match.match_expression
Type | STRING | Mode | NULLABLE | |||
---|---|---|---|---|---|---|
Description |
Match expression that an administrator set in the Admin Console.
|
message_info.triggered_rule_info.string_match.matched_string
Type | STRING | Mode | NULLABLE | |||
---|---|---|---|---|---|---|
Description |
String that triggered the rule. Sensitive information is hidden by * or .
|
message_info.triggered_rule_info.string_match.predefined_detector_name
Type | STRING | Mode | NULLABLE |
---|---|---|---|
Description | If this was a match of predefined detectors, shows the name of the predefined detector |
message_info.triggered_rule_info.string_match.source
Type | INTEGER | Mode | NULLABLE | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Location of the string matched in the message
|
message_info.triggered_rule_info.string_match.type
Type | INTEGER | Mode | NULLABLE | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Type of match
|
message_info.upload_error_category
Type | INTEGER | Mode | NULLABLE | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description |
Error encountered while uploading the message to the destination
|