這項功能適用於 Cloud Identity 進階版。版本比較
本文包含您可以從 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 和 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 = ""
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
視訊通話數量和總通話分鐘數 (依日期顯示)
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;