Schema for Gmail logs in BigQuery
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.
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.
Learn how to specify and modify schemas in BigQuery.
| Field name | event_info | ||
|---|---|---|---|
| Type | RECORD | ||
| Mode | REQUIRED | ||
| Description | General information about the event | ||
| Field name | |||
|---|---|---|---|
| Type | STRING | ||
| Mode | NULLABLE | ||
| Description | The type of client or device where the action occurred, including WEB, IOS, ANDROID, IMAP, POP3, and API | ||
| Field name | event_info.client_context.session_context.delegate_user_email | ||
|---|---|---|---|
| Type | STRING | ||
| Mode | NULLABLE | ||
| Description | Email address of the delegated user who performed the action on the account owner's behalf | ||
| Field name | event_info.elapsed_time_usec | ||
|---|---|---|---|
| Type | INTEGER | ||
| Mode | NULLABLE | ||
| Description | Total time duration of the event, in microseconds | ||
| Field name | event_info.mail_event_type | ||
|---|---|---|---|
| Type | INTEGER | ||
| Mode | NULLABLE | ||
| Description |
Logged event type. The event type corresponds to the Event attribute in Gmail log events in Security Investigation Tool. Possible values are: 0: Unknown mail event type 1: Message sent 2: Message received 3: A Gmail user manually applied a spam classification to the message. For example, the user marked the message as spam, phishing, or not spam. 4: Gmail flagged the message as spam after delivery. Several factors can cause this, including poor sender reputation or new virus hashes. 5: Message quarantined 6: Message released from quarantine 7: Message opened for the first time 8: Message marked as unread 9: Message replied to for the first time 10: Message forwarded for the first time 11: Message autoforwarded with a Gmail account forwarding setting 12: Message moved to Inbox 13: Message moved to Trash 14: Message removed from Trash 15: Link in message body was clicked 16: Link in message attachment link was clicked during attachment preview 17: One or more message attachments were downloaded 18: One or more message attachments saved to Google Drive 19: One or more Google Drive items in the message were saved to the recipient's Google Drive 20: Classification label applied to message 21: Message classification label change 22: Classification label removed from message 23: Classification label applied to all message attachments 24: Classification label changed on all message attachments 25: Classification label removed from all message attachments 26: Message archived 27: Message permanently deleted 28: One or more message attachments previewed 29: Message saved as draft 30: Message couldn't be delivered, and bounced 31: Message viewed, including first and following readings. For details on a known iOS issue, go to Google Workspace known issues. 32: Message downloaded 33: An application accessed a message on behalf of a user 34: Delegate Granted Note: BiqQuery exports enabled between April 2024 and July 2024 don’t include historical View events between April 2024 and the date you enabled the export. BigQuery exports enabled in August 2024 and later include historical View events 6 months prior to the date you enabled the export. |
||
True if the event was successful, otherwise false. For example, the value is false if the message was rejected by a policy.
The message delivery action that the event represents. Possible values:
1: Message received by inbound SMTP server
2: Message accepted by Gmail and prepared for delivery. This step usually follows 1, or is the first step if you send from Gmail. For incoming messages, policies with reject dispositions are typically evaluated here. For example, an attachment compliance policy that rejects incoming messages.
3: Gmail acted on the message. For example, delivered to a Gmail mailbox or sent to another server. This step usually follows 2. Policies with dispositions other than reject are evaluated here. For example, an attachment compliance policy that strips attachments based on file type or other criteria.
10: Message sent out by outbound SMTP server
14: A temporary error occurred when Gmail tried to deliver the message, and the message has been scheduled for retry. Typically, this is caused by external or internal servers that are temporarily unavailable. Retry later. For example, Gmail tried to deliver the message to an external SMTP server, but received temporary error.
18: Message could not be delivered and bounced. Sometimes you can find out what happened by reading message_info.description. Common reasons include:
-
The recipient server didn’t accept the request
-
The message could not be delivered because of too many temporary errors (go to 14 in this table)
-
The message was rejected because of a deferred policy evaluation
-
The recipient is unrecognized and there’s no policy triggered to change the primary delivery route
19: Message was dropped by Gmail. Common reasons include:
-
If a sent message triggers admin quarantine consequences, the original message is dropped and a copy of the message is added to the Admin Quarantine
-
For a journaling message, the wrapped inner message is delivered but the original message is dropped
-
For inbound messages, Gmail can block and drop messages if, for example:
-
The message is not compliant with RFC 5322
-
The sender violates bulk senders guidelines
-
-
If a policy removed the primary delivery route and added other routes, the original message is dropped and copies are delivered to the added routes
-
If the recipient is an unrecognized address and there’s a policy that adds additional routes, the original message is dropped and copies are delivered to the added routes
45: Message was accepted for delivery by the Google Groups subsystem
46: Message's recipient address was a Google Group, and the recipient was expanded to each member of the Google Group that has message delivery enabled
48: Message received by inbound SMTP server for relay
49: Message sent through relay by outbound SMTP server
51: Message was written to Google Groups storage
54: Message was rejected by the Google Groups storage system
55: Message was re-inserted into Gmail by policies that modify the primary delivery route or envelope recipient
68: Message accepted by Gmail and prepared for delivery. This is similar to 2, but the message was sent through a Gmail server.
69: A user changed the message’s spam classification in Gmail. For example, a user marked it as spam, phishing, or not spam.
70: The message was reclassified as spam or phishing after it was delivered to Gmail.
71: A user took an action in the inbox after receiving the message. Post-delivery actions include opening a message, clicking a link in a message, and downloading an attachment. BigQuery export includes details about the action
Information about the message’s attachments. This record is repeated for every attachment.
Malware category, if detected when the message is handled. This field is unset if no malware is detected. Possible values:
- 1: A known malicious program type of malware
- 2: A virus or worm type of malware
- 3: Possible harmful email content
- 4: Possible unwanted email content
- 5: Other type of malware
Message authentication type (for example, SPF, DKIM). Possible values:
- 1: SPF
- 2: DKIM
- 3: DKIM_PROXY
- 4: XOAR_SPF
- 5: XOAR_DKIM
- 6: ARC_SPF
- 7: ARC_DKIM
SMTP reply code for inbound and outbound SMTP connections. Usually 2xx, 4xx, or 5xx.
Detailed reason for the SMTP reply code for inbound connections. Possible values:
- 1: Default reason messages are accepted or rejected
- 3: Malware
- 4: DMARC policy
- 5: Attachment not supported by Gmail
- 6: Receive limit exceede
- 7: Account over quote
- 8: Bad PTR report
- 9: Recipient doesn't exist
- 10: Customer policy
- 12: RFC violation
- 13: Blatant spam
- 14: Denial of service
- 15: Malicious or spam links
- 16: Low IP reputation
- 17: Low domain repuation
- 18: IP address listed in public real-time block list
- 19: Temporarily rejected due to DOS limits
- 20: Permanently rejected due to DOS limits
Type of connection made to the SMTP server. Only set for logs of events that explicitly handle SMTP connections. Values:
- 0: Not TLS
- 1: TLS
message_connection_info.smtp_user_agent_ip
message_info.destination.rcpt_response
Subcategory for each service. Go to message_info.destination.service for value definitions.
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.
Service
Selector
Description
gmail-ui
sent-on-behalf-of-user
Message was sent to Gmail and a copy was kept in the user's Gmail Sent box
gmail-ui
null
Message was sent to Gmail
mailing-list-server
spam-check
Message was sent to Google Groups and was checked for spam
mailing-list-server
null
Message was sent to Google Groups
mailing-list-server
moderation
Message was sent to Google Groups and is pending administrator's moderation
mailing-list-server
archive
Message was sent to Google Groups and is archived
gmail-for-work-catchall
Message had unrecognized recipients and was delivered according to a catch-all rule
smtp-outbound
gmail-delivery-server
Message was sent to outbound SMTP server and handled by Gmail delivery servers
smtp-outbound
google-apps-for-work
Message was sent to outbound SMTP server and handled by Google Workspace Basic
smtp-outbound
google-apps-for-work-starter
Message was sent to outbound SMTP server and handled by Google WorkspaceBasic
smtp-outbound
gmail-notification
Message was sent to outbound SMTP server and handled by Gmail notification
smtp-outbound
relay
Message was sent to outbound SMTP server and handled by Gmail relay servers
smtp-outbound
gmail
Message was sent to outbound SMTP server
smtp-outbound
gmail-for-work
Message was sent to outbound SMTP server and added by Gmail for business policies
smtp-outbound
null
Message was sent to outbound SMTP server
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.
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.
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.
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.
String that has information of all recipient information flattened, in this format:
"service_for_recipient1:selector_for_recipient1:address_for_recipient1,
service_for_recipient2:selector_for_recipient2:address_for_recipient2"
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 set type that the message belongs to. Go to message_info.message_set.type for more information.
Message set types are attributes that describe the message. For example, if the message was inbound, outbound, or internal. Possible values:
1: Message is inbound (received from outside your domains). This message set doesn’t appear with message set 10.
2: Message is outbound (sent to a recipient outside your domains). This message set doesn’t appear with message set 10.
4: Message contains objectionable content, as defined by one of your policies
6: Message triggered the walled garden rule you configured that restricts messages to authorized addresses or domains
7: Gmail classified the message as spam
8: Message being sent (outgoing message)
9: Message being received (incoming message)
10: Message that is internal to your domains
11: Message has a sender or recipients outside your domains. For received messages: If message set 27 is missing, the sender couldn't be authenticated. The message is treated as having a sender outside your domain.
12: Message has some recipients inside your domain and some recipients outside your domain. This message set might appear when:
- There are multiple recipients
- A message is being sent. For messages being received, recipients must all belong to the same domain
- Action type for the message is 2. Multi-recipient messages are split out into single-recipient messages
13: The type of the message set is unknown
15: The policy being checked against is tied to a Gmail user
18: Message doesn’t have a default route
19: The address list you configured for domain default routing matches the correspondent of the message
20: Message is from an address in your blocked senders list
21: Message was sent over TLS and the SSL certificate is valid.
22: Message was sent over TLS
24: The recipient of this message is unknown
25: Message is a non-delivery report responding to a message that was not delivered
26: Message triggered a rerouting rule, which you configured in domain default routing
27: Sender successfully passed SPF/DKIM/DMARC authentication. If the sender isn’t authenticated, the sender domain is untrusted and the message is not considered internal.
28: Exchange journal is archiving the message to Google Vault
29: Message was routed through SMTP relay
30: A recipient of the message matched one of the enumerated recipients (instead of a regular expression pattern) you configured for domain routing, or domain default routing
31: Message matched a domain default routing condition you configured
33: Message has to be transmitted through a secure connection, such as TLS
34: The policy being checked against is tied to a group instead of an individual Gmail user
35: Message could not be authenticated in SMTP relay because it has an empty SMTP envelope-from address or is possibly an Exchange Journal message. It will be checked later at SMTP RCPT command time.
36: Message has aggressive spam filtering enabled
37: Message is authenticated for SMTP relay
39: Sender is from an authenticated domain for relay
40: Message is from a Google Workspace user in the domain being authenticated for relay
41: Sender has successfully authenticated with SMTP AUTH, and Gmail is trying to authenticate SMTP relay for the sender's domain
42: Message was sent from an address that isn’t authenticated
43: Message was rerouted through an alias table
44: Message triggered a rule that changes the route of the mail flow
45: Message is to a catch-all account and is being relayed to an on-premise server. System-of-record policies won't be applied to it.
46: Message bypassed the spam filter
47: Message was detected to be spam by tag-and-deliver information in the inbound gateway settings
48: Message was not checked for spam (by SMTP) due to a spam-override policy
49: Always override spam rejection for the message
50: Message matches a domain routing condition you configured
51: Message triggered a rerouting rule that you configured for domain routing
57: Message was received from an inbound gateway rule that you configured
60: Message is protected with Gmail confidential mode
61: Message was received by Security sandbox
62: The address list you configured for domain default routing matches the SMTP envelope recipient instead of the correspondent of the message
63: Message triggered a domain-level rerouting rule, which you configured for domain routing, or domain default routing
Post-delivery action type. Possible values:
1: Message opened for the first time
2: Message marked as unread
3: Message replied
4: Message forwarded
5: Message auto-forwarded by a Gmail setting
6: Message moved to inbox
7: Message moved to trash
8: Message moved out of trash
9: A link in the message body was clicked
10: One or more message attachments were downloaded
11: A link in an attachment was clicked when the attachment was previewed
12: One or more message attachments were saved to Google Drive
13: A link in the add-on was clicked
14: One or more Google Drive items in the message were downloaded
15: One or more Google Drive items in the message were saved to the recipient's Google Drive
16: A classification label was applied to or changed for the message
17: A classification label was applied to or changed for message attachments
18: Message archived
19: Message permanently deleted
20: One or more message attachments were previewed
21: Eecipient blocked the message sender
22: Message saved as draft
23: Message viewed, including first and following readings
24: Message downloaded
25: An application accessed a message on behalf of a user
26: Delegate Granted
Malware type, if malware is detected during message handling. If no malware is detected, this field is not set. Possible values:
1: Known malicious program type of malware
2: Virus or worm type of malware
3: Possible harmful message content
4: Possible unwanted message content
5: Other type of malware
Entity type that was classified. Possible values:
1: Message body
2: Attachment
Classification event type. Possible values:
1: Label changed
2: Label newly applied
3: Label removed
The top-level S/MIME type of a message, indicated by the Content-Type: header. Possible values:
0: Message does not have a recognized S/MIME Content-Type
1: An S/MIME message with a detached signature, indicated by content type multipart/signed with parameter protocol=application/pkcs7-signature
2: An S/MIME message with an opaque signature, indicated by content type application/pkcs7-mime or application/x-pkcs7-mime with parameter smime-type=signed-data
3: An S/MIME message that is encrypted, indicated by content type application/pkcs7-mime or application/x-pkcs7-mime with parameter smime-type=enveloped-data
4:An S/MIME message that is compressed, indicated by content type application/pkcs7-mime or application/x-pkcs7-mime with parameter smime-type=compressed-data
For outbound messages only. When set and true, indicates the message should be encrypted.
When set, indicates that inbound S/MIME processing occurred. Not set if skipped. The value indicates the completion status. Note: Currently not set.
For outbound messages only. When set, indicates that S/MIME packaging was attempted. Not set if skipped. The value indicates the completion status.
If Gmail rejects an SMTP relay request, this error code provides information about the cause of the rejection. Possible values:
1: Authentication error
2: Daily rate limit exceeded
3: Peak rate limit exceeded
4: Abuse of SMTP relay
5: Per-user rate limit exceeded
From: header display name as it appears in the message headers, for example, John Doe. This field might be truncated if the log is too long or if there are too many triggered rules (triggered_rule_info) in the log.
A subcategory of the source server. For value descriptions, go to message_info.source.service.
The source service for the message. Use these two fields to determine which service sent the message and why the message was generated.
Service
Selector
Description
calendar
send
Notifications from Google Calendar
gmail-ui
read-receipt
Gmail read-receipt feature
gmail-ui
autoforward
Gmail auto-forward feature
gmail-ui
unsubscribe
Gmail unsubscribe feature
gmail-ui
canned-response
Message sent by Gmail Canned Response feature
gmail-ui
vacation-response
Gmail vacation response feature
gmail-ui
send
Message sent from Gmail web UI.
docs
share
Sharing notification from Google Drive
groups
groups-ui
Message sent from Google Groups
keep
invites
Invitation email sent by Google Keep
mailing-list-server
custom-replies
Auto-replies from Google Groups
mailing-list-server
null
Sent from Google Groups
mailing-list-server
moderation
Sent from Google Groups moderation
mailing-list-server
to-archive
Sent from Google Groups archive
google-apps-script
user
Sent from Google Apps Script
mail-fetcher
null
Message pulled by Gmail Mail Fetcher
gmail-for-work
quarantine-delivery
Message released from the Quarantine Manager
gmail-for-work
quarantine-notification
Non-delivery response sent to the original sender of a denied quarantined message
gmail-for-work
policy
Message triggered a setting configured by the domain administrator
gmail-for-work
comprehensive-mail-storage
Sent to Gmail servers due to a Comprehensive Mail Storage setting
smtp-inbound
null
Message inserted from Google's SMTP servers to Gmail delivery pipeline
smtp-msa
null
Message inserted from Google's SMTP servers (in authenticated mode) to the Gmail delivery pipeline
smtp-relay
gmail-for-work
Messages routed through the SMTP Relay setting
google-spreadsheets
google-forms-receipt
Notifications from Google Sheets
google-spreadsheets
google-forms-invite
Sharing invites from Google Sheets
unified-notifications
google-apps
Notification from Google Workspace
unified-notifications
null
Notification from a Google system
message_info.spam_info.classification_reason
| Type | INTEGER | Mode | NULLABLE |
|---|---|---|---|
| Description |
Reason the message was classified as spam, phishing, or other classification. Possible values: 1: Default spam classification reason 2: Message classified because of sender's past actions 3: Suspicious content 4: Suspicious link 5: Suspicious attachment 6: Custom policy defined in Google Workspace Gmail settings 7: DMARC 8: Domain in public RBLs 9: RFC standards violation 10: Gmail policy violation 11: Machine learning verdict 12: Sender reputation 13: Blatant spam |
||
The outcome of the Gmail spam classification. Possible values:
1: Not spam or malware
2: Spam
3: Phishing
4: Suspicious
5: Malware
MIME type category. Possible values:
1: Unrecognized file type
2: Microsoft Office documents, including word processing, spreadsheet, presentation, and database documents. Includes PDF files. The file might or might not be encrypted.
3: Video and multimedia, for example, MPEG, Quicktime, or WMV
4: Music and audio, for example, MP3, AAC, and WAV
5: Images, for example, JPEG, BMP, or GIF
6: Archives, for example, ZIP, TAR, or TGZ
7: Executables, for example EXE, COM, or JS
8: Encryped Office documents
9: Office documents that aren't encrypted
Action taken for the consequence. Possible values:
0: Consequence is a no-op
3: Put message in Admin Quarantine
4: Modify the primary delivery target
5: Add a delivery target
6: Added a message header
7: Overwrite the envelope recipient
9: Add message to specified message set
10: Modify the message labels
11: Prefix text to message subject
12: Add a footer to the message
13: Strip the message body
14: Store a copy of the message in the user’s mailbox, according to comprehensive mail storage setting
15: Replace attachment with canned text
16: Require secure message delivery
17: Message can’t be delivered and bounced
18: Archive to Google Vault for recipients
20: Encrypt outbound message using S/MIME
21: Change the recipient user when message is received at SMTP
Custom rule type. Possible values:
0: Walled garden
7: Objectionable content
8: Content compliance
10: Received mail routing
11: Sent mail routing
12: Spam override
14: Blocked senders
15: Append footer
16: Attachment compliance
17: TLS compliance
18: Domain default routing
19: Inbound email journal acceptance in Vault
20: Outbound relay
21: Quarantine summary
22: Alternate secure route
23: Alias table
24: Comprehensive mail storage
25: Routing rule
26: Inbound gateway
27: S/MIME
28: Third-party email archiving
Describes the custom rule spam classification results. Possible values:
0: No action—The rule honored the Gmail spam classification outcome
1: Spam—The rule classified the message as spam
2: Not spam—the rule classified the message as not spam
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.
Match expression set in the Admin console. 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.
String that triggered the rule. Sensitive information is hidden by * or . This field might be truncated if the log is too long, or the number of triggered rules (triggered_rule_info) in the log is too large.
Location of the string matched in the message. Possible values:
0: Unknown
1: Message body, including text format attachments
2: Binary format attachments
3: Message headers
4: Subject
5: Sender header
6: Recipient header
7: Raw message
Type of match. Possible values:
- 0: Undefined
- 1: Regular expression match
- 2: Predefined detector match
- 3: Simple content match
- 4: Non-ASCII match
Error encountered while uploading the message to the destination. Possible values:
- 0: Uncategorized transient error
- 1: Recipient account is too busy
- 2: DNS error resolving recipient domain
- 3: Recipient’s server refused connection
- 4: Recipient is out of storage
Was this helpful?
Need more help?
Try these next steps:
Professional email, online storage, shared calendars, video meetings and more. Start your free Google Workspace trial today.