Contoh kueri untuk log laporan di BigQuery

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

Artikel ini berisi contoh kueri untuk laporan umum yang dapat diperoleh 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 meminta informasi data BigQuery.

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

Contoh kueri

Akun

Jumlah admin dan akun 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

Aktivitas 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 di 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;

Drive

Jumlah item Google Drive yang dibagikan, yang dikelompokkan berdasarkan 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 di dalam suatu periode.

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. Memungkinkan Anda memahami perubahan izin yang menyebabkan adanya perubahan pada 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 peristiwa diperinci berdasarkan 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;

Jenis dan waktu perubahan izin yang 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 diartikan 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 = "[e-mail]")

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 = "[e-mail]" 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 = "[e-mail]"
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 = ""
LIMIT 1000

Disposisi—Tolak pesan
Menolak 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
Mengubah 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 berdasarkan 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 menurut 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 lengkap parameter serta kolom yang diperbarui dalam dokumentasi Reports API. 

Memfilter menurut tanggal

Anda dapat memfilter menurut tanggal saat mengkueri aktivitas atau tabel penggunaan. Keduanya memiliki format yang berbeda saat menampilkan tanggal:

• Tabel aktivitas 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 Mikrodetik Unix (tabel aktivitas), Anda dapat menentukan klausa WHERE dan fungsi TIMESTAMP() untuk melakukan perbandingan sederhana dengan operator lebih besar dari (>) dan lebih kecil dari (<):

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

Konsepnya adalah memberlakukan batas dalam nilai time_usec input dengan membandingkan nilai hasil dari fungsi TIMESTAMP_MICROS() dengan nilai hasil dari fungsi TIMESTAMP() dengan penambahan tanggal sebagai parameter jenis string. Hal ini mengikuti standar di Fungsi stempel waktu di SQL Standar, dan menggunakan operator perbandingan sederhana (>) dan (<) bersama dengan ekstensi AND dari klausa WHERE untuk menutup periode 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 memasukkan nilai tanggal jenis string yang ada di tabel ke fungsi TIMESTAMP() dan menggunakan operator perbandingan (>) dan (<) dengan cara yang sama seperti di contoh pertama.

 

Memfilter menurut alias domain dan subdomain: Mengecualikan dan menyertakan

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

Cara menggunakan pernyataan AND atau OR bergantung pada apakah Anda tidak memasukkan (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 dalam 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;

Topik terkait

• Dokumentasi BigQuery