BigQuery 的報告記錄查詢範例

支援這項功能的版本：Frontline Standard、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;

雲端硬碟

共用的 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 = "[e-mail]")

比對處理方式和收件者

郵件摘要檢視畫面最多可顯示 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 = "[e-mail]" 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 = "[e-mail]"
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 = ""
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;

網路論壇

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;

相關主題

• BigQuery 說明文件