Contoh kueri untuk log pelaporan di BigQuery

Edisi yang didukung untuk fitur ini: Frontline Standard dan Frontline Plus; Enterprise Standard dan Enterprise Plus; Education Standard dan Education Plus; Enterprise Essentials Plus. Bandingkan edisi Anda

Artikel ini berisi contoh kueri untuk laporan umum yang dapat Anda peroleh dari BigQuery. Contoh kueri ini mengasumsikan legacy SQL. Ganti api_project_name.dataset_name dengan nama project dan nama set data Anda sendiri.

Pelajari lebih lanjut cara membuat kueri data BigQuery.

Untuk kolom log Gmail dan artinya, buka Skema untuk log aktivitas Gmail di BigQuery.

Contoh kueri

Akun

Jumlah akun admin dan yang didelegasikan, serta jumlah akun yang dinonaktifkan, dikunci, dan ditangguhkan menurut tanggal

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;

Administrator

Peristiwa yang paling sering dilakukan oleh admin

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;

Menemukan jumlah admin super dalam domain tertentu

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;

Kalender

Khusus SQL Standar

Rasio pengguna aktif harian hingga pengguna aktif 30 hari di Google Kalender. Contoh ini berisi kueri di beberapa tabel.

Pengguna aktif harian

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

Pengguna aktif 30 hari

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;

Jumlah acara kalender menurut jenis

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;

Migrasi data

Peristiwa log migrasi data akan diluncurkan secara bertahap mulai 26 Mei 2025. Data peristiwa log sebelum tanggal tersebut mungkin tidak akurat.

Semua tindakan penyiapan dan konfigurasi yang dilakukan oleh admin

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;

Semua peristiwa terkait migrasi untuk ID eksekusi tertentu

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;

Semua kegagalan untuk ID eksekusi tertentu

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;

Semua peristiwa untuk jenis sumber tertentu, seperti 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;

Semua peristiwa untuk pengguna sumber tertentu di Migrasi Gmail Enterprise

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;

Semua peristiwa untuk pesan tertentu dengan URI sumber tertentu, seperti ID 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;

Semua peristiwa untuk jenis data sumber tertentu, seperti Folder 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;

Jumlah peristiwa yang dikelompokkan menurut status dan nama peristiwa

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;

Jumlah harian semua peristiwa yang dikelompokkan berdasarkan status dan jenis objek

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

Jumlah item Google Drive yang dibagikan, dikelompokkan menurut metode berbagi

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, judul, pemilik, dan jenis file. File yang dibagikan secara eksternal dalam jangka waktu tersebut.

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;

Perubahan izin berbagi dan hasilnya. Memberi Anda kemampuan untuk memahami perubahan izin yang menyebabkan perubahan visibilitas file.

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

Jenis acara yang dikelompokkan menurut jenis file. Berguna untuk laporan adopsi menurut jenis file.

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;

Jenis dan nama peristiwa untuk setiap drive bersama

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;

Informasi tentang pengguna di luar domain Anda

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;

Izin apa dan kapan perubahan izin telah diberikan kepada pengguna eksternal

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;

Informasi tentang pemantauan penyimpanan

Berguna untuk membuat laporan tentang pengguna yang menghabiskan lebih dari X penyimpanan drive, dengan suatu nilai minimum yang ditetapkan (ditentukan dengan klausa AND accounts.drive_used_quota_in_mb > 0).

Kueri ini dapat ditentukan sebagai kueri terjadwal atau, misalnya, dapat dipanggil secara berkala menggunakan 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;

Catatan:

  • Nilai ini dapat diubah agar sesuai dengan filter yang disetel pelanggan. Misalnya, lebih dari 15 GB: AND accounts.drive_used_quota_in_mb > 15000
  • Perbandingan tanggal dengan CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) memungkinkan Anda memiliki perbandingan tanggal dengan format yang tersedia dari nilai date.

  • Kueri ini juga berlaku untuk Gmail, tempat kita dapat menemukan nilai yang serupa: accounts.gmail_used_quota_in_mb

Gmail

Praktik terbaik untuk Gmail dengan BigQuery

  • Hanya minta informasi data yang Anda perlukan. Contoh ini memiliki batas 1.000 pencocokan, namun Anda dapat menetapkan batas sendiri.
  • Tetapkan jangka waktu untuk kueri Anda. Satu hari adalah jangka waktu yang umum.

Pencocokan subjek
Tampilan ringkasan pesan untuk maksimum 1.000 data yang cocok dengan subjek tertentu

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

Pencocokan penerima
Menghitung jumlah pesan yang berbeda untuk penerima tertentu

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

Pencocokan disposisi dan penerima

Tampilan ringkasan pesan untuk maksimum 1.000 data yang cocok dengan keduanya:

  • Disposisi tertentu (Ubah, Tolak, Karantina)
  • Penerima tertentu
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

Deskripsi aturan dipicu
Tampilan ringkasan pesan untuk maksimum 1.000 data, yang memicu deskripsi aturan yang ditentukan

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

Ditandai sebagai spam
Tampilan ringkasan pesan untuk maksimum 1.000 data:

  • Ditandai sebagai spam
  • Untuk penerima tertentu
  • Untuk semua alasan
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

Protokol enkripsi—tidak dienkripsi
Tampilan ringkasan pesan menurut protokol enkripsi—tidak dienkripsi

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

Protokol enkripsi—hanya TLS
Tampilan ringkasan pesan menurut protokol enkripsi—hanya 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

Pencocokan ID pesan
Tampilan detail pesan untuk ID pesan tertentu (sertakan "<>" sebelum dan sesudah ID pesan)

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

Disposisi—Tolak pesan
Tolak pesan:

  • Apa saja aturan yang menyebabkan penolakan?
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

Disposisi—Ubah pesan
Ubah pesan:

  • Apa saja aturan yang menyebabkan modifikasi?
  • Apa saja subkategori modifikasinya (misalnya, header atau subjek)?
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

Karantina pesan
Aturan mana saja yang mengarantina pesan?

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

Gabungkan kueri
Menghitung semua pesan yang dikumpulkan oleh aturan tertentu (disebut sebagai "deskripsi aturan") dalam 30 hari terakhir:

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"

Mencantumkan semua pesan yang diterima tanpa enkripsi TLS di hari terakhir:

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

Mencantumkan 10 domain teratas yang bertukar email dengan akun saya dalam 30 hari terakhir:

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

Rasio pengguna aktif harian hingga pengguna aktif 30 hari di Gmail

Pengguna aktif harian:

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;

Pengguna aktif 7 hari:

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;

Pengguna aktif 30 hari:

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 peristiwa log Gmail terbaru dengan minimal satu label klasifikasi yang terkait dengan pesan email

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;

Semua peristiwa log yang tersedia untuk pesan email tertentu

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;

Grup

Perilaku pengguna dan perubahan keanggotaan Google Grup

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

Jika Anda ingin memiliki stempel waktu YYYY-MM-DD, elemen pernyataan SELECT pertama dapat diganti dengan:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

Tanggal dapat difilter di klausa WHERE dengan salah satu cara berikut:

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

Jumlah panggilan video dan total menit panggilan menurut tanggal

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

Pengguna aktif harian

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

Pengguna aktif 30 hari

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

Aturan

Aturan DLP yang dipicu berdasarkan nama, aplikasi yang cocok, dan tindakan

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;

Token

Frekuensi pengaktifan aplikasi pihak ketiga untuk mengakses 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;

Upaya login ke konsol Admin

Informasi mendetail tentang login yang gagal ke konsol Google Admin

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;

Skema untuk tabel penggunaan

Skema dapat berubah. Anda dapat melihat daftar parameter dan kolom yang diupdate dan lengkap di dokumentasi Reports API.

Memfilter menurut tanggal

Anda dapat memfilter menurut tanggal saat membuat kueri tabel activity atau usage. Keduanya memiliki format yang berbeda saat menampilkan tanggal:

  • Tabel activity menyimpan stempel waktu dalam mikrodetik Unix. Ini adalah nilai bilangan bulat (angka) yang dapat dikonversi menjadi tanggal dengan fungsi TIMESTAMP_MICROS().
  • Tabel penggunaan menampilkan nilai tanggal dengan format tanggal, sehingga konversi ini tidak diperlukan.

Di kedua tabel, Anda dapat memilih untuk memfilter menurut tanggal tertentu (atau rentang tanggal) menggunakan salah satu metode berikut.

Tabel aktivitas

Untuk memfilter menurut tanggal tertentu dengan struktur Unix Micros (tabel activity), Anda dapat menentukan klausa WHERE dan fungsi TIMESTAMP() untuk melakukan perbandingan sederhana dengan operator lebih besar (>) dan lebih kecil (<):

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

Konsep di sini adalah menempatkan batas dalam nilai time_usec input dengan membandingkan nilai yang ditampilkan dari fungsi TIMESTAMP_MICROS() dengan nilai yang ditampilkan dari fungsi TIMESTAMP() dengan tanggal yang ditambahkan sebagai parameter jenis string. Hal ini mengikuti standar dalam Fungsi stempel waktu di SQL Standar, dan menggunakan operator perbandingan sederhana (>) dan (<), bersama dengan ekstensi AND dari klausa WHERE untuk menutup jendela waktu tertentu.

Tabel penggunaan

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;

Kita dapat meneruskan nilai date jenis string yang ada dalam tabel ke fungsi TIMESTAMP() dan menggunakan operator perbandingan (>) dan (<) dengan cara yang sama seperti pada contoh pertama.

Memfilter menurut alias domain dan subdomain: Kecualikan dan sertakan

Untuk mengecualikan atau menyertakan domain tertentu dari hasil kueri, terapkan filter untuk alamat email di klausa WHERE menggunakan karakter pengganti (%) untuk memfilter domain.

Cara Anda menggunakan pernyataan AND atau OR bergantung pada apakah Anda mengecualikan (mengecualikan) atau hanya menyertakan hasil tertentu.

Mengecualikan domain tertentu dari hasil

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

Hanya menyertakan domain tertentu dalam hasil

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

Log audit aturan

Gunakan kueri ini untuk melacak upaya pengguna membagikan data sensitif

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, serta merek dan logo terkait adalah merek dagang Google LLC. Semua nama perusahaan dan produk lainnya adalah merek dagang milik setiap perusahaan terkait.