BigQuery 的報表記錄查詢範例

支援這項功能的版本：Frontline Standard 和 Frontline Plus；Enterprise Standard 和 Enterprise Plus；Education Standard 和 Education Plus；Enterprise Essentials Plus。版本比較

本文包含您可以從 BigQuery 取得的常見報告查詢範例。這類查詢範例會假設您使用的是舊版 SQL。請將 api_project_name.dataset_name 改成您的專案名稱和資料集名稱。

進一步瞭解如何查詢 BigQuery 資料。

如需瞭解 Gmail 記錄欄位及其含義，請參閱「BigQuery 中的 Gmail 活動記錄結構定義」。

查詢範例

帳戶

管理員帳戶與委派帳戶數量，以及已停用、已鎖定和已停權的帳戶數量 (依日期顯示)

SELECT date,
    accounts.num_locked_users,
    accounts.num_disabled_accounts,
    accounts.num_delegated_admins,
    accounts.num_super_admins,
    accounts.num_suspended_users,
    accounts.num_users
    FROM api_project_name.dataset_name.usage
    WHERE accounts.num_users IS NOT NULL
    ORDER BY date ASC;

管理員

管理員最常執行的事件

SELECT count(*) as admin_actions, event_name
    FROM api_project_name.dataset_name.activity
    WHERE email IN (
      SELECT user_email
      FROM api_project_name.dataset_name.usage
      WHERE accounts.is_super_admin = TRUE
    )
    GROUP BY 2
    ORDER BY 1 DESC;

找出特定網域中的超級管理員人數

SELECT COUNT(DISTINCT user_email) as number_of_super_admins, date
    FROM api_project_name.dataset_name.usage
    WHERE accounts.is_super_admin = TRUE
    GROUP BY 2
    ORDER BY 2 DESC;

日曆

僅限標準 SQL

Google 日曆中每日活躍使用者與過去 30 天活躍使用者的比率。此範例會查詢多個表格。

每日活躍使用者

SELECT date, calendar.num_1day_active_users
    FROM api_project_name.dataset_name.usage
    WHERE calendar.num_1day_active_users IS NOT NULL
    ORDER BY date DESC

過去 30 天的活躍使用者

SELECT date, calendar.num_30day_active_users
    FROM api_project_name.dataset_name.usage
    WHERE calendar.num_30day_active_users IS NOT NULL
    ORDER BY date DESC;

各類日曆活動的數量

SELECT COUNT(DISTINCT calendar.calendar_id) AS count, event_name
    FROM api_project_name.dataset_name.activity
    WHERE calendar.calendar_id IS NOT NULL
    GROUP BY 2 ORDER BY 1 DESC;

資料遷移

資料遷移記錄事件將於 2025 年 5 月 26 日起逐步推出。因此，該日期前的記錄事件資料可能不準確。

管理員所有的設定操作

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, status.*, event_type, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND event_type = 'MIGRATION_SETUP' AND email = 'admin@example.com' ORDER BY timestamp desc LIMIT 1000;

特定執行作業 ID 的所有遷移相關事件

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND event_type = 'MIGRATION' AND data_migration.execution_id =  'execution_id' ORDER BY timestamp desc LIMIT 1000;

特定執行作業 ID 的所有失敗記錄

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND event_type = 'MIGRATION' AND data_migration.execution_id =  'execution_id' AND status.event_status = 'FAILED' ORDER BY timestamp desc LIMIT 1000;

特定來源類型 (例如 Microsoft Exchange Online) 的所有事件

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND data_migration.migration_type = 'Exchange Online Migration' ORDER BY timestamp desc LIMIT 1000;

特定來源使用者在 Enterprise Gmail Migration 中的所有事件

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND data_migration.migration_type = 'Enterprise Gmail Migration' AND data_migration.source_identifier like '%user@example.com%' ORDER BY timestamp desc LIMIT 1000;

特定郵件 (含指定來源 URI，例如 RFC ID) 的所有事件

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND data_migration.source_uri = 'rfc822Id' ORDER BY timestamp desc LIMIT 1000;

特定來源資料類型 (例如 Exchange Online 電子郵件資料夾) 的所有事件

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND data_migration.source_type = 'Exchange Online Email Folder' ORDER BY timestamp desc LIMIT 1000;

依狀態和事件名稱分組的事件數

SELECT status.event_status as status, event_name, count(*) AS count FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' GROUP BY 1,2 ORDER BY 1,2 limit 1000;

依狀態和物件類型分組的每日事件總數

SELECT EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date, status.event_status as status, data_migration.source_type, count(*) AS count FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' GROUP BY 1,2,3 ORDER BY 1,2,3 limit 1000;

雲端硬碟

共用的 Google 雲端硬碟項目數量 (依共用方式分組)

SELECT COUNT(DISTINCT drive.doc_id) AS count, drive.visibility
    FROM api_project_name.dataset_name.activity
    WHERE drive.doc_id IS NOT NULL
    GROUP BY 2 ORDER BY 1 DESC;

檔案 ID、標題、擁有者和類型。特定時間範圍內對外共用的檔案。

SELECT TIMESTAMP_MICROS(time_usec) AS date, drive.doc_id, drive.doc_title,
    drive.owner, drive.doc_type
    FROM api_project_name.dataset_name.activity
    WHERE drive.visibility = "shared_externally"
    ORDER BY 1 DESC
    LIMIT 100;

共用權限變更與其結果。您可以瞭解權限變更情況，以及這對於檔案瀏覽權限的影響。

SELECT TIMESTAMP_MICROS(time_usec) AS date, drive.doc_title,
    drive.visibility_change,drive.old_visibility, drive.visibility,
    FROM api_project_name.dataset_name.activity
    WHERE record_type = "drive"
    AND drive.old_visibility IS NOT NULL
    AND drive.old_visibility != "unknown";

依檔案類型細分的事件類型。適合用來製作依檔案類型分類的採用度報表。

SELECT drive.doc_type, event_type, count(*)
    FROM  api_project_name.dataset_name.activity
    WHERE record_type = "DRIVE"
    GROUP by 1,2 ORDER BY 3 desc;

各共用雲端硬碟的事件類型和名稱

SELECT drive.shared_drive_id, event_type, event_name, record_type,
    count(distinct drive.doc_id) AS count
    FROM api_project_name.dataset_name.activity
    WHERE record_type = "drive"
    AND drive.shared_drive_id IS NOT NULL
    GROUP BY 1,2,3,4 ORDER BY 5 DESC;

網域外使用者的資訊

SELECT email, event_name, count(*) AS count
    FROM api_project_name.dataset_name.activity
    WHERE email != ""
    AND email NOT LIKE "%mydomain.com%"
    GROUP BY 1,2 ORDER BY 3 DESC;

授予外部使用者的權限有何變更與變更時間

SELECT drive.target_user, event_name, count(*) AS count
    FROM api_project_name.dataset_name.activity
    WHERE drive.target_user IS NOT NULL
    AND drive.target_user NOT LIKE "%mydomain.com%"
    GROUP BY 1,2 ORDER BY 3 DESC;

儲存空間監控資訊

適合以雲端硬碟儲存空間用量超過 X 的使用者做為對象，製作設有閾值 (使用 AND accounts.drive_used_quota_in_mb > 0 子句定義) 的使用者資料報表。

您可以將這項查詢定義成排程查詢，也可以使用 API 定期呼叫這項查詢。

SELECT date,
    user_email,
    accounts.drive_used_quota_in_mb,
    FROM api_project_name.dataset_name.usage
    WHERE accounts.drive_used_quota_in_mb IS NOT NULL
    AND accounts.drive_used_quota_in_mb > 0
    AND user_email != ""
    AND date = CAST(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AS STRING)
    ORDER BY 3,1 DESC;

注意：

  • 您可以根據客戶設定的篩選條件修改這個值。舉例來說，如果篩選條件是超過 15 GB，即可改成：AND accounts.drive_used_quota_in_mb > 15000
  • 使用 CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) 比較日期，就能以支援的 date 值格式比較日期。

  • 這項查詢也適用於 Gmail，我們可以尋找以下類似值：accounts.gmail_used_quota_in_mb

Gmail

將 Gmail 和 BigQuery 搭配使用的最佳做法

  • 只查詢您所需的資料。以下範例的比對數量上限為 1,000 筆記錄，但您可以自行設定上限。
  • 為查詢設定時間範圍。一般會將時間範圍設為一天。

主旨比對
郵件摘要檢視畫面最多可顯示 1,000 筆與指定主旨相符的記錄

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address,
         gmail.message_info.rfc2822_message_id
    FROM your_dataset_id.activity
    WHERE gmail.message_info.subject LIKE "%test%"
    LIMIT 1000

收件者比對
計算指定收件者的非重複郵件數量

SELECT COUNT(DISTINCT gmail.message_info.rfc2822_message_id)
    FROM your_dataset_id.activity d
    WHERE
      EXISTS(
       SELECT 1 FROM d.gmail.message_info.destination WHERE destination.address = "recipient@example.com")

比對處理方式和收件者

郵件摘要檢視畫面最多可顯示 1,000 筆符合下列兩項條件的記錄：

  • 指定處理方式 (修改、拒絕、隔離)
  • 指定收件者
SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         destination.address as destination,
         gmail.message_info.rfc2822_message_id
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE
         destination.address = "recipient@example.com" AND
         EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
              WHERE consequence.action = 17)
    LIMIT 1000

觸發規則說明
郵件摘要檢視畫面最多可顯示 1,000 筆觸發指定規則說明的記錄

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         destination.address as destination,
         gmail.message_info.rfc2822_message_id
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE
        EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
           WHERE consequence.reason LIKE '%description%')
    LIMIT 1000

標示為垃圾郵件
郵件摘要檢視畫面最多可顯示 1,000 筆記錄：

  • 標示為垃圾內容
  • 指定收件者
  • 不限理由
SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         destination.address as destination,
         gmail.message_info.rfc2822_message_id
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE gmail.message_info.is_spam AND
              destination.address = "recipient@example.com"
    LIMIT 1000

加密通訊協定 - 未加密
「加密通訊協定 - 未加密」的郵件摘要檢視畫面

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         destination.address as destination,
         gmail.message_info.rfc2822_message_id
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE gmail.message_info.connection_info.smtp_tls_state = 0
    LIMIT 1000

加密通訊協定 - 僅限傳輸層安全標準 (TLS)
「加密通訊協定 - 僅限傳輸層安全標準 (TLS)」的郵件摘要檢視畫面

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         destination.address as destination,
         gmail.message_info.rfc2822_message_id
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE gmail.message_info.connection_info.smtp_tls_state = 1
    LIMIT 1000

郵件 ID 比對
指定郵件 ID (包括以「<>」包夾的郵件 ID) 的郵件詳細資料檢視畫面

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.event_info.success,
         gmail.event_info.elapsed_time_usec,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         gmail.message_info.source.service as source_service,
         gmail.message_info.source.selector as source_selector,
         destination.address as destination,
         destination.service,
         destination.selector as destination_selector,
         gmail.message_info.rfc2822_message_id,
         gmail.message_info.payload_size,
         gmail.message_info.num_message_attachments,
         gmail.message_info.connection_info.smtp_tls_state,
         gmail.message_info.description
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE gmail.message_info.rfc2822_message_id = "<message id>"
    LIMIT 1000

處理方式 - 拒絕郵件
拒絕郵件：

  • 依據什麼規則拒絕郵件？
SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         destination.address as destination,
         gmail.message_info.rfc2822_message_id,
         (SELECT ARRAY_AGG(consequence.reason)
         FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence)
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE gmail.message_info.rfc2822_message_id = "<message id>" AND
         EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
            WHERE consequence.action = 17)
    LIMIT 1000

處理方式 - 修改郵件
修改郵件：

  • 依據什麼規則修改郵件？
  • 修改什麼子分類 (例如標頭或主旨)？
SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         destination.address as destination,
         gmail.message_info.rfc2822_message_id,
         (SELECT ARRAY_AGG((consequence.action, consequence.reason))
         FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence)
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE gmail.message_info.rfc2822_message_id = "<message id>" AND
       EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
           WHERE consequence.action NOT IN (0, 17, 3))
    LIMIT 1000

隔離郵件
是依據什麼規則隔離郵件？

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         destination.address as destination,
         gmail.message_info.rfc2822_message_id,
         (SELECT ARRAY_AGG(consequence.reason)
         FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence)
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE gmail.message_info.rfc2822_message_id = "<message id>" AND
         EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
            WHERE consequence.action = 3)
    LIMIT 1000

複合式查詢
計算最近 30 天內符合特定規則 (依據規則說明) 的所有郵件：

SELECT
      COUNT(gmail.message_info.rfc2822_message_id) AS message_cnt
    FROM
      `your_dataset_id.activity`,
    UNNEST (gmail.message_info.triggered_rule_info) AS triggered_rule
    WHERE
      _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
      AND triggered_rule.rule_name LIKE "rule description"

列出最近 1 天內未透過 TLS 加密方式接收的所有郵件：

SELECT gmail.message_info.subject,
        gmail.message_info.rfc2822_message_id
    FROM `your_dataset_id.activity`
    WHERE
        _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND
        gmail.message_info.connection_info.smtp_tls_state = 0

列出最近 30 天內與我的帳戶往來郵件最頻繁的 10 個網域：

SELECT
      COUNT(DISTINCT gmail.message_info.rfc2822_message_id) as message_cnt,
      IF(gmail.message_info.is_policy_check_for_sender,
           REGEXP_EXTRACT(gmail.message_info.source.address , "(@.*)"),
           REGEXP_EXTRACT(destination.address , "(@.*)")) AS domain
    FROM `your_dataset_id.activity` d, d.gmail.message_info.destination
    WHERE
        _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    GROUP BY domain
    ORDER BY message_cnt desc
    LIMIT 10

Gmail 每日活躍使用者與 30 天內活躍使用者的比率

每日活躍使用者人數：

SELECT date,
    gmail.num_1day_active_users
    FROM api_project_name.dataset_name.usage
    WHERE gmail.num_1day_active_users > 0
    ORDER BY 1 DESC;

過去 7 天的活躍使用者人數：

SELECT date,
    gmail.num_7day_active_users
    FROM api_project_name.dataset_name.usage
    WHERE gmail.num_7day_active_users > 0
    ORDER BY 1 DESC;

過去 30 天的活躍使用者人數：

SELECT date,
    gmail.num_30day_active_users
    FROM api_project_name.dataset_name.usage
    WHERE gmail.num_30day_active_users > 0
    ORDER BY 1 DESC;

最近的 100 個 Gmail 記錄事件，其中每則電子郵件訊息至少有一個分類標籤

SELECT
      resource_details[OFFSET(0)].id AS MESSAGE_ID,
      gmail.message_info.subject AS SUBJECT,
      gmail.event_info.mail_event_type AS MAIL_EVENT_TYPE,
      gmail.message_info.source.address AS SENDER,
      resource_details[OFFSET(0)].applied_labels AS LABELS
    FROM workspace_audit_logs.activity
    WHERE gmail.event_info.mail_event_type > 0 and ARRAY_LENGTH(resource_details) > 0
    ORDER by time_usec desc
    LIMIT 100;

特定電子郵件訊息目前可查的所有記錄事件

SELECT
      gmail.event_info,
      gmail.message_info,
      resource_details
    FROM workspace_audit_logs.activity
    WHERE gmail.message_info.rfc2822_message_id = "<XYZ>"
    ORDER by time_usec desc;

群組

Google 群組的成員異動和使用者行為

SELECT TIMESTAMP_MICROS(time_usec) AS date,
      event_name,
      admin.group_email,
      event_type,
      email,
      record_type,
      admin.user_email,
      admin.new_value,
      admin.old_value,
      admin.setting_name
    FROM project_name.dataset_name.activity
    WHERE `admin`.group_email IS NOT NULL
    AND CONCAT(TIMESTAMP_MICROS(time_usec)) LIKE "%YYYY-MM-DD%"
    ORDER BY 1 DESC
    LIMIT
      1000

如要取得 YYYY-MM-DD 格式的時間戳記，可以將第一個 SELECT 陳述式元素替換成：
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

您可以使用 WHERE 子句篩選日期，方法如下：

SELECT TIMESTAMP_MICROS(time_usec) AS date,
      event_name,
      admin.group_email,
      event_type,
      email,
      record_type,
      admin.user_email,
      admin.new_value,
      admin.old_value,
      admin.setting_name
    FROM project_name.dataset_name.activity
    WHERE `admin`.group_email IS NOT NULL
    AND EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) > "2020-06-30"
    AND EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) < "2020-08-31"
    ORDER BY 1 DESC
    LIMIT
      1000
 
SELECT TIMESTAMP_MICROS(time_usec) AS date,
      event_name,
      admin.group_email,
      event_type,
      email,
      record_type,
      admin.user_email,
      admin.new_value,
      admin.old_value,
      admin.setting_name
    FROM project_name.dataset_name.activity
    WHERE `admin`.group_email IS NOT NULL
    AND TIMESTAMP_MICROS(time_usec) > TIMESTAMP("2020-07-21")
    AND TIMESTAMP_MICROS(time_usec) < TIMESTAMP("2020-07-23")
    ORDER BY 1 DESC
    LIMIT
      1000

Google Meet

視訊通話數量和總通話分鐘數 (依日期顯示)

SELECT date, meet.num_calls, meet.total_call_minutes
    FROM `api_project_name.dataset_name.usage`
    WHERE meet.num_calls IS NOT NULL
    ORDER BY date ASC

每日活躍使用者

SELECT date, meet.num_1day_active_users
    FROM `api_project_name.dataset_name.usage`
    WHERE meet.num_1day_active_users IS NOT NULL
    ORDER BY date DESC

過去 30 天的活躍使用者

SELECT date, meet.num_30day_active_users
    FROM `api_project_name.dataset_name.usage`
    WHERE meet.num_30day_active_users IS NOT NULL
    ORDER BY date DESC

規則

觸發的資料遺失防護規則 (依名稱、比對相符的應用程式和動作顯示)

SELECT TIMESTAMP_MICROS(time_usec) AS date, rules.rule_name, rules.application,
    rules.resource_title, rules.actions, rules.resource_owner_email,
    rules.data_source, rules.matched_trigger
    FROM api_project_name.dataset_name.activity
    WHERE rules.rule_name IS NOT NULL
    ORDER BY 1 DESC LIMIT 1000;

符記數

啟用第三方應用程式存取 Google 雲端硬碟的次數

SELECT token.client_id, scope, token.app_name, count(*) AS count
    FROM api_project_name.dataset_name.activity
    LEFT JOIN UNNEST(token.scope) AS scope
    WHERE scope LIKE "%drive%"
    GROUP BY 1,2,3 ORDER BY 4 DESC;

管理控制台登入嘗試

Google 管理控制台登入失敗的詳細資料

SELECT TIMESTAMP_MICROS(time_usec) AS date, email, ip_address,
    event_name, login.login_type, login.login_failure_type
    FROM api_project_name.dataset_name.activity
    WHERE login.login_type IS NOT NULL
    AND login.login_failure_type IS NOT NULL
    AND event_type = "login"
    ORDER BY date DESC;

使用情形資料表的架構

此架構可能會改變。如要查看最新的完整參數與欄位清單，請參閱 Reports API 說明文件。

依日期篩選

在查詢活動或使用情形資料表時，您可以依日期進行篩選。這兩種資料表顯示日期的格式並不相同：

  • 活動資料表儲存的時間戳記為 Unix 毫秒格式。您可以使用 TIMESTAMP_MICROS() 函式將這個整數值 (數字) 轉換成日期。
  • 使用情形資料表顯示的 date 值是日期格式，因此不需要轉換。

無論查詢上述何種資料表，您都可以透過下列其中一種方法，選擇篩選特定日期 (或日期範圍) 的資料。

活動資料表

如要使用 Unix Micros (活動資料表) 架構篩選特定日期的資料，您可以定義 WHERE 子句和 TIMESTAMP() 函式，運用大於 (>) 和小於 (<) 運算子進行簡單比較：

SELECT TIMESTAMP_MICROS(time_usec) as date, record_type
    FROM api_project_name.dataset_name.activity
    WHERE TIMESTAMP_MICROS(time_usec) > TIMESTAMP("2020-07-01")
    AND TIMESTAMP_MICROS(time_usec) < TIMESTAMP("2020-07-07")
    ORDER BY 1 DESC LIMIT 1000

這裡的概念是將 TIMESTAMP_MICROS() 函式傳回的值與 TIMESTAMP() 函式 (加入日期做為字串類型參數) 傳回的值進行比較，從而為 time_usec 輸入值設下限制。這樣就能依照標準 SQL 中的時間戳記函式標準，使用簡單的比較運算子 (> 和 <) 搭配 AND 擴充的 WHERE 子句，設定具體的時間範圍。

使用情形資料表

SELECT date, meet.num_calls,
    FROM api_project_name.dataset_name.usage
    WHERE meet.num_calls IS NOT NULL
    AND TIMESTAMP(date) > TIMESTAMP("2020-07-01")
    AND TIMESTAMP(date) < TIMESTAMP("2020-07-07")
    ORDER BY date DESC;

我們可以將資料表中的字串類型 date 值傳遞至 TIMESTAMP() 函式，然後採用和第一個例子同樣的方式使用比較運算子 (> 和 <)。

依網域別名和子網域篩選資料：排除與納入

如要排除或納入特定網域的查詢結果，您可以在 WHERE 子句中加入電子郵件地址的篩選條件，然後使用萬用字元 (%) 來篩選網域。

至於該使用 AND 還是 OR 陳述式，則取決於您是要「過濾」 (排除) 特定結果，還是「只顯示」特定結果。

排除特定網域的結果

WHERE email NOT LIKE ("%@sub.%")
    AND email NOT LIKE ("%@test.%")

只顯示特定網域的結果

WHERE email LIKE ("%@sub.%")
    OR email LIKE ("%@test.%")

規則稽核記錄

使用這項查詢來追蹤使用者嘗試分享機密資料的行為

SELECT TIMESTAMP_MICROS(time_usec) AS Date,
    rules.resource_owner_email AS User,
    rules.rule_name AS ruleName,
    rules.rule_type AS ruleType,
    rules.rule_resource_name AS ruleResourceName,
    rules.resource_id AS resourceId,
    rules.resource_title AS resourceTitle,
    rules.resource_type AS resourceType,
    rules.resource_owner_email AS resourceOwner,
    CAST(recipients AS STRING) AS Recipients,
    rules.data_source AS dataSource,
    rules.actor_ip_address AS actorIpAddress,
    rules.severity AS severity,
    rules.scan_type AS scanType,
    rules.matched_trigger AS matchedTriggers,
    detect.display_name AS matchedDetectorsName,
    detect.detector_id AS matchedDetectorsId,
    detect.detector_type AS matchedDetectorsType,
    triggers.action_type AS triggeredActions,
    suppressors.action_type AS suppressedActions,
    FROM api_project_name.dataset_name.activity
    LEFT JOIN UNNEST(rules.resource_recipients) as recipients
    LEFT JOIN UNNEST(rules.matched_detectors) as detect
    LEFT JOIN UNNEST(rules.triggered_actions) as triggers
    LEFT JOIN UNNEST(rules.suppressed_actions) as suppressors
    WHERE rules.rule_name IS NOT NULL
    AND triggers.action_type != "ALERT"
    ORDER BY 1 DESC
    LIMIT 1000;


Google、Google Workspace 和其他相關符號及標誌均為 Google LLC 的商標，所有其他公司和產品名稱則是與個別公司關聯的商標。