Ví dụ về truy vấn cho nhật ký báo cáo trong BigQuery

Các phiên bản hỗ trợ tính năng này: Frontline Standard và Frontline Plus; Enterprise Standard và Enterprise Plus; Education Standard và Education Plus; Enterprise Essentials Plus. So sánh phiên bản của bạn

Bài viết này chứa các truy vấn mẫu cho những báo cáo thường gặp mà bạn có thể nhận được từ BigQuery. Các ví dụ về truy vấn này giả định SQL cũ. Thay thế api_project_name.dataset_name bằng tên dự án và tên tập dữ liệu của riêng bạn.

Tìm hiểu thêm về cách truy vấn dữ liệu BigQuery.

Để biết các trường nhật ký của Gmail và ý nghĩa của chúng, hãy xem Giản đồ cho nhật ký hoạt động của Gmail trong BigQuery.

Câu hỏi mẫu

Tài khoản

Số lượng tài khoản quản trị và tài khoản được uỷ quyền, cũng như số lượng tài khoản bị vô hiệu hoá, bị khoá và bị tạm ngưng theo ngày

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;

Quản trị viên

Các sự kiện thường xuyên nhất do quản trị viên thực hiện

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;

Tìm số lượng quản trị viên cấp cao trong một miền nhất định

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;

Lịch

Chỉ áp dụng cho SQL chuẩn

Tỷ lệ giữa số người dùng hoạt động hằng ngày và số người dùng hoạt động trong 30 ngày trên Google Lịch. Ví dụ này truy vấn trên nhiều bảng.

Số người dùng hoạt động hằng ngày

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

Số người dùng hoạt động trong 30 ngày

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;

Số lượng sự kiện trên lịch theo loại

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;

Di chuyển dữ liệu

Sự kiện trong nhật ký di chuyển dữ liệu sẽ được triển khai dần từ ngày 26 tháng 5 năm 2025. Dữ liệu sự kiện nhật ký trước ngày đó có thể không chính xác.

Mọi thao tác thiết lập và định cấu hình do quản trị viên thực hiện

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;

Tất cả các sự kiện liên quan đến hoạt động di chuyển cho một mã thực thi cụ thể

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;

Tất cả các lỗi cho một mã thực thi cụ thể

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;

Tất cả sự kiện cho một loại nguồn cụ thể, chẳng hạn như 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;

Tất cả sự kiện của một người dùng nguồn cụ thể trong quá trình Di chuyển Gmail doanh nghiệp

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;

Tất cả sự kiện cho một thông báo cụ thể có URI nguồn nhất định, chẳng hạn như mã nhận dạng RFC

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;

Tất cả sự kiện cho một loại dữ liệu nguồn cụ thể, chẳng hạn như Thư mục email 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;

Số lượng sự kiện được nhóm theo trạng thái và tên sự kiện

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;

Số lượng hằng ngày của tất cả sự kiện được nhóm theo trạng thái và loại đối tượng

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;

Drive

Số lượng mục trên Google Drive được chia sẻ, phân nhóm theo phương thức chia sẻ

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;

Mã nhận dạng, tiêu đề, chủ sở hữu và loại tệp. Số tệp được chia sẻ ra bên ngoài trong khoảng thời gian này.

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;

Thay đổi quyền chia sẻ và kết quả của việc thay đổi. Cho phép bạn biết những thay đổi về quyền nào đã dẫn đến thay đổi về chế độ hiển thị của tệp.

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";

Các loại sự kiện được phân tích theo loại tệp. Hữu ích cho báo cáo mức độ sử dụng theo loại tệp.

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;

Loại sự kiện và tên của từng bộ nhớ dùng chung

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;

Thông tin về người dùng bên ngoài miền của bạn

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;

Những thay đổi về quyền đã được cấp cho người dùng bên ngoài và thời điểm cấp

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;

Thông tin về việc giám sát bộ nhớ

Hữu ích khi tạo báo cáo về những người dùng sử dụng hơn X bộ nhớ trên Drive, với một ngưỡng được đặt (được xác định bằng mệnh đề AND accounts.drive_used_quota_in_mb > 0).

Truy vấn này có thể được xác định là một truy vấn theo lịch hoặc có thể được gọi định kỳ bằng 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;

Lưu ý:

  • Bạn có thể sửa đổi giá trị này cho phù hợp với bộ lọc mà khách hàng đang đặt. Ví dụ: trên 15 GB: AND accounts.drive_used_quota_in_mb > 15000
  • Việc so sánh ngày bằng CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) giúp bạn so sánh ngày với định dạng có sẵn từ giá trị date.

  • Cụm từ tìm kiếm này cũng áp dụng cho Gmail, nơi chúng ta có thể tìm thấy một giá trị tương tự: accounts.gmail_used_quota_in_mb

Gmail

Các phương pháp hay nhất để sử dụng Gmail với BigQuery

  • Chỉ truy vấn dữ liệu bạn cần. Các ví dụ này có giới hạn là 1.000 kết quả trùng khớp,nhưng bạn có thể đặt giới hạn riêng.
  • Đặt khung thời gian cho các truy vấn của bạn. Một ngày là khung thời gian điển hình.

Khớp theo tiêu đề
Chế độ xem tóm tắt thư cho tối đa 1.000 bản ghi khớp với một tiêu đề được chỉ định

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

Số lượng người nhận trùng khớp
Đếm số lượng tin nhắn riêng biệt cho một người nhận cụ thể

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")

Kết quả và thông tin người nhận trùng khớp

Chế độ xem tóm tắt tin nhắn cho tối đa 1.000 bản ghi khớp với cả hai điều kiện:

  • Một trạng thái được chỉ định (Sửa đổi, Từ chối, Cách ly)
  • Một người nhận được chỉ định
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

Mô tả quy tắc đã kích hoạt
Chế độ xem tóm tắt tin nhắn cho tối đa 1.000 bản ghi, trong đó mô tả quy tắc đã chỉ định được kích hoạt

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

Được đánh dấu là thư rác
Chế độ xem tóm tắt thư cho tối đa 1.000 bản ghi:

  • Bị đánh dấu là spam
  • Đối với một người nhận cụ thể
  • Vì mọi lý do
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

Giao thức mã hoá – không được mã hoá
Chế độ xem tóm tắt thư theo giao thức mã hoá – không được mã hoá

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

Giao thức mã hoá – Chỉ TLS
Chế độ xem tóm tắt thư theo giao thức mã hoá – Chỉ 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

So khớp mã nhận dạng thư
Chế độ xem thông tin chi tiết về thư cho một mã nhận dạng thư nhất định (bao gồm cả "<>" xung quanh mã nhận dạng thư)

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

Disposition – Reject message (Trạng thái – Từ chối thư)
Reject message (Từ chối thư):

  • Quy tắc nào gây ra lỗi từ chối?
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

Xử lý – Sửa đổi thư
Sửa đổi thư:

  • Quy tắc nào đã gây ra thay đổi này?
  • Danh mục phụ của nội dung sửa đổi (ví dụ: tiêu đề hoặc chủ đề)?
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

Cách ly thư
Quy tắc nào đã cách ly thư?

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

Truy vấn kết hợp
Đếm tất cả tin nhắn bị bắt bởi một quy tắc cụ thể (có tên là "mô tả quy tắc") trong 30 ngày qua:

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"

Liệt kê tất cả thư nhận được mà không có chế độ mã hoá TLS trong ngày gần nhất:

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

Liệt kê 10 miền hàng đầu mà tài khoản của tôi đã trao đổi thư trong 30 ngày qua:

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

Tỷ lệ giữa số người dùng hoạt động hằng ngày và số người dùng hoạt động trong 30 ngày trên Gmail

Số người dùng hoạt động hằng ngày:

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;

Số người dùng hoạt động trong 7 ngày:

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;

Số người dùng hoạt động trong 30 ngày:

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 sự kiện gần đây nhất trong nhật ký Gmail có ít nhất một nhãn phân loại được liên kết với thư

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;

Tất cả sự kiện trong nhật ký có sẵn cho một thư email cụ thể

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;

Nhóm

Thay đổi về thành viên trong nhóm trên Google và hành vi của người dùng

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

Nếu bạn muốn có dấu thời gian YYYY-MM-DD, bạn có thể thay thế phần tử SELECT đầu tiên bằng:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

Bạn có thể lọc ngày trong mệnh đề WHERE theo một trong những cách sau:

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

Số lượng cuộc gọi video và tổng số phút gọi theo ngày

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

Số người dùng hoạt động hằng ngày

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

Số người dùng hoạt động trong 30 ngày

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

Quy tắc

Các quy tắc DLP được kích hoạt theo tên, ứng dụng khớp và hành động

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;

Mã thông báo

Số lần ứng dụng bên thứ ba được phép truy cập vào Google Drive

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;

Số lần đăng nhập vào Bảng điều khiển dành cho quản trị viên

Thông tin chi tiết về các lần đăng nhập không thành công vào Bảng điều khiển dành cho quản trị viên của 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;

Giản đồ cho bảng sử dụng

Giản đồ có thể thay đổi. Bạn có thể xem danh sách đầy đủ và mới nhất về các tham số và trường trong tài liệu về Reports API.

Lọc theo ngày

Bạn có thể lọc theo ngày khi truy vấn bảng activity hoặc usage. Cả hai đều có định dạng riêng khi trình bày ngày:

  • Bảng activity lưu trữ dấu thời gian tính bằng micrô giây theo thời gian Unix. Đây là một giá trị số nguyên (một số) có thể được chuyển đổi thành ngày bằng hàm TIMESTAMP_MICROS().
  • Bảng mức sử dụng hiển thị các giá trị ngày theo định dạng ngày, nên bạn không cần chuyển đổi.

Đối với cả hai bảng, bạn có thể chọn lọc theo một ngày (hoặc phạm vi ngày) cụ thể bằng một trong những phương thức sau.

Bảng hoạt động

Để lọc theo một ngày cụ thể bằng cấu trúc Unix Micros (bảng activity), bạn có thể xác định mệnh đề WHERE và hàm TIMESTAMP() để thực hiện so sánh đơn giản bằng các toán tử lớn hơn (>) và nhỏ hơn (<):

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

Khái niệm ở đây là đặt giới hạn cho giá trị đầu vào time_usec bằng cách so sánh giá trị trả về của giá trị này từ hàm TIMESTAMP_MICROS() với giá trị trả về của hàm TIMESTAMP() có thêm ngày dưới dạng tham số kiểu chuỗi. Điều này tuân theo các tiêu chuẩn trong Hàm dấu thời gian trong SQL chuẩn và sử dụng các toán tử so sánh đơn giản (>) và (<), cùng với phần mở rộng AND của mệnh đề WHERE để đóng một khung thời gian cụ thể.

Bảng mức sử dụng

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;

Chúng ta có thể truyền giá trị date thuộc loại chuỗi có trong bảng vào hàm TIMESTAMP() và sử dụng các toán tử so sánh (>) và (<) theo cách tương tự như trong ví dụ đầu tiên.

Lọc theo bí danh miền và miền con: Loại trừ và đưa vào

Để loại trừ hoặc thêm một số miền vào kết quả truy vấn, hãy áp dụng bộ lọc cho địa chỉ email trong mệnh đề WHERE, sử dụng ký tự đại diện (%) để lọc các miền.

Cách bạn sử dụng câu lệnh VÀ hoặc HOẶC phụ thuộc vào việc bạn đang lọc ra (loại trừ) hay chỉ bao gồm một số kết quả nhất định.

Loại trừ một số miền khỏi kết quả

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

Chỉ đưa một số miền nhất định vào kết quả

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

Nhật ký kiểm tra quy tắc

Sử dụng truy vấn này để theo dõi những lần người dùng cố gắng chia sẻ dữ liệu nhạy cảm

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 cũng như các nhãn hiệu và biểu trưng có liên quan là các nhãn hiệu của Google LLC. Tất cả các tên sản phẩm và công ty khác là nhãn hiệu của những công ty mà chúng liên kết.