BigQuery 報告和記錄範例

這項功能適用於 G Suite Enterprise 和 G Suite Enterprise for Education。版本比較

您可以查詢透過 Google BigQuery 主控台所做的自訂分析,並以自己的資料集專案名稱取代 mydomain:Reports。這類查詢大多會假設您使用舊版 SQL。

如要使用標準 SQL 語法,請以 `mydomain.Reports.usage_201703` 取代 [mydomain:Reports.usage_201703] (請注意單引號和句號)。

範例如下:

帳戶

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

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 [mydomain:Reports.usage_201703] 
WHERE NOT IS_NULL(accounts.num_locked_users)
ORDER BY date ASC;

管理員

管理員最常執行的事件。

SELECT count(*) as admin_actions, event_name
FROM [mydomain:Reports.activity_201703]
WHERE accounts.is_super_admin group by 2 order by 1 desc limit 5

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

SELECT count(*) as number_of_super_admins
FROM [mydomain:Reports.activity_201703]
WHERE accounts.is_super_admin = true

日曆

僅限標準 SQL

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

SELECT date,
calendar.num_1day_active_users/calendar.num_30day_active_users
FROM 
`mydomain.Reports.usage_*`
WHERE calendar.num_1day_active_users > 0
AND calendar.num_30day_active_users > 0
ORDER BY date asc;

日曆活動數量 (依類型顯示)。

SELECT count(distinct calendar.calendar_id), event_name
FROM [mydomain:Reports.activity_201703]
WHERE NOT IS_NULL(calendar.calendar_id)
GROUP BY 2 ORDER BY 1 desc;

雲端硬碟

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

SELECT count_distinct(drive.doc_id) AS count, drive.visibility 
FROM [mydomain:Reports.activity_201703] 
GROUP BY 2 ORDER BY 1 desc; 

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

SELECT drive.doc_id, drive.doc_title, drive.owner, drive.doc_type
FROM [mydomain:Reports.activity_201703] 
where drive.visibility = "shared_externally"
LIMIT 100;

共用權限變更與其結果。可讓您瞭解權限變更會對檔案瀏覽權限造成什麼改變。

SELECT drive.doc_title, drive.visibility_change,drive.old_visibility, drive.visibility,
FROM [mydomain:Reports.activity_201703] 
WHERE record_type = "DRIVE"
AND NOT IS_NULL(drive.old_visibility)
AND drive.old_visibility != "unknown";

依檔案類型歸類事件類型。有利於建立依檔案類型分類的採用度報告。

SELECT drive.doc_type, event_type, count(*)
FROM [mydomain:Reports.activity_201703]
WHERE record_type = "DRIVE"
GROUP by 1,2 ORDER BY 3 desc;

事件類型與各共用雲端硬碟名稱。

SELECT drive.shared_drives_id, event_type, event_name, record_type,
count(distinct drive.doc_id)
FROM [mydomain:Reports.activity_201703] 
WHERE NOT IS_NULL(drive.shared_drives_id)
GROUP BY 1,2,3,4 ORDER BY 5 DESC;

網域外使用者的資訊。

SELECT email, event_name, count(*)
FROM [mydomain:Reports.activity_201701]
WHERE email != ""
AND email NOT LIKE "%mydomain.com%"
GROUP BY 1,2 ORDER BY 3 DESC;

對外部使用者進行的權限變更內容與時間。

SELECT drive.target_user, event_name, count(*)
FROM [mydomain:Reports.activity_201703]
WHERE NOT IS_NULL(drive.target_user)
AND drive.target_user NOT LIKE "%mydomain.com%"
GROUP BY 1,2 ORDER BY 3 DESC;

Gmail

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

SELECT date,
gmail.num_1day_active_users/gmail.num_7day_active_users
FROM [mydomain:Reports.usage_201703] 
WHERE gmail.num_1day_active_users > 0
AND gmail.num_7day_active_users > 0

網上論壇

Google 群組的成員更動與使用者行為。

SELECT
  TIMESTAMP_MICROS(time_usec),
  event_name,
  `groups`.group_email,
  email,
  `groups`.status
FROM
  `mydomain:Reports.activity_2017*`
WHERE
  record_type = 'groups'
ORDER BY 1 DESC
LIMIT
  1000

Hangouts

視訊通話數量 (依日期顯示)。

SELECT date, gplus.total_video_call_minutes,gplus.num_video_calls
FROM [mydomain:Reports.usage_201703]
WHERE NOT IS_NULL(gplus.num_video_calls)
ORDER BY date ASC

規則

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

SELECT rules.rule_name, rules.application, rules.actions, rules.resource_owner_email
FROM [mydomain:Reports.activity_201703] 
WHERE event_type = "rule_match_type"
AND rules.rule_name != ""
LIMIT 1000

憑證

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

SELECT token.client_id, token.scope, token.app_name, count(*)
FROM [mydomain:Reports.activity_201703]
WHERE token.scope LIKE "%drive%"
GROUP BY 1,2,3 ORDER BY 4 DESC;

管理控制台登入嘗試

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

SELECT time_usec, email, ip_address, event_name, login.login_type, login.login_failure_type
FROM  [mydomain:Reports.activity_201703] 
WHERE login.login_type IS NOT NULL
AND login.login_failure_type IS NOT NULL
AND event_type = "login";

用法表格的架構

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

這對您有幫助嗎?
我們應如何改進呢?