ตัวอย่างการค้นหาบันทึกการรายงานใน BigQuery

รุ่นที่รองรับฟีเจอร์นี้ได้แก่ Frontline Standard; Enterprise Standard และ Enterprise Plus; Education Standard และ Education Plus; Enterprise Essentials Plus  เปรียบเทียบรุ่นของคุณ

บทความนี้มีตัวอย่างการค้นหาสำหรับรายงานทั่วไปที่คุณใช้ได้จาก BigQuery ตัวอย่างการค้นหาเหล่านี้จะถือว่าใช้ SQL เดิม แทนที่ api_project_name.dataset_name ด้วยชื่อโปรเจ็กต์และชื่อชุดข้อมูลของคุณเอง

ดูข้อมูลเพิ่มเติมเกี่ยวกับการค้นหาข้อมูล BigQuery

สําหรับช่องบันทึกของ Gmail และความหมาย ให้ไปที่สคีมาสําหรับบันทึกกิจกรรมของ Gmail ใน BigQuery

ตัวอย่างการค้นหา

บัญชี

จำนวนบัญชีผู้ดูแลระบบและบัญชีที่ได้รับมอบสิทธิ์ รวมถึงจำนวนบัญชีที่ปิดใช้ ถูกล็อก และถูกระงับตามวันที่

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 มาตรฐานเท่านั้น

อัตราส่วนของผู้ที่ใช้งานรายวันเทียบกับผู้ที่ใช้งานภายใน 30 วันใน Google ปฏิทิน ตัวอย่างนี้จะค้นหาในหลายตาราง

จำนวนผู้ใช้ที่ใช้งานรายวัน

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;

รหัสไฟล์ รวมทั้งชื่อไฟล์ เจ้าของ และประเภทของไฟล์ ไฟล์ที่แชร์กับภายนอกในช่วงเวลาที่กำหนด

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;

ข้อมูลเกี่ยวกับการตรวจสอบพื้นที่เก็บข้อมูล

มีประโยชน์ในการจัดทํารายงานเกี่ยวกับผู้ใช้ที่ใช้พื้นที่เก็บข้อมูล Google ไดรฟ์มากกว่า 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

แนวทางปฏิบัติที่ดีที่สุดสำหรับ 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 = "[e-mail]")

การจับคู่การกำจัดและผู้รับ

มุมมองสรุปข้อความบันทึกที่ตรงกับเกณฑ์การค้นหาทั้งสองนี้ไม่เกิน 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 = "[e-mail]" 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 = "[e-mail]"
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

การจับคู่รหัสข้อความ
มุมมองรายละเอียดของข้อความสําหรับรหัสข้อความที่ระบุ (ใส่ "<>" ครอบรหัสข้อความ)

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"

แสดงข้อความทั้งหมดที่ได้รับโดยไม่ได้เข้ารหัส TLS ในช่วง 1 วันที่ผ่านมา

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

แสดง 10 โดเมนแรกที่บัญชีของฉันแลกเปลี่ยนอีเมลด้วยภายในช่วง 30 วันที่ผ่านมา

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

อัตราส่วนของจำนวนผู้ใช้ที่ใช้งานรายวันเทียบกับผู้ที่ใช้งานภายใน 30 วันใน Gmail

จำนวนผู้ใช้ที่ใช้งานรายวัน:

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 Groups และพฤติกรรมของผู้ใช้

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

Google Meet

จำนวนวิดีโอคอลและเวลาโทรทั้งหมดตามวันที่

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

กฎ

กฎ DLP ที่ทำงาน โดยกรองตามชื่อ แอปพลิเคชันที่ตรงกัน และการดำเนินการ

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 

กรองตามวันที่

คุณสามารถกรองตามวันที่เมื่อค้นหาตารางกิจกรรมหรือตารางการใช้งาน โดยทั้ง 2 อย่างนี้มีรูปแบบที่ต่างกันในการนําเสนอวันที่ ดังนี้

• ตารางกิจกรรมจะจัดเก็บการประทับเวลาไว้ในรูปแบบไมโครวินาทีของ Unix ซึ่งเป็นค่าจํานวนเต็ม (ตัวเลข) ที่แปลงเป็นวันที่ได้โดยใช้ฟังก์ชัน TIMESTAMP_MICROS()
• ตารางการใช้งานจะแสดงค่าวันที่โดยใช้รูปแบบวันที่ จึงไม่จําเป็นต้องทำการแปลงข้อมูล

ไม่ว่าจะเป็นตารางรูปแบบใด คุณก็สามารถเลือกกรองตามวันที่ (หรือช่วงวันที่) ที่ต้องการได้โดยใช้วิธีใดวิธีหนึ่งต่อไปนี้

ตารางกิจกรรม

หากต้องการกรองตามวันที่ที่ต้องการโดยใช้โครงสร้าง 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

แนวคิดนี้จะกำหนดขีดจำกัดในค่า time_usec ของอินพุตโดยเปรียบเทียบค่าที่แสดงผลจากฟังก์ชัน TIMESTAMP_MICROS() กับค่าที่แสดงผลจากฟังก์ชัน TIMESTAMP() ซึ่งมีวันที่เพิ่มเข้ามาเป็นพารามิเตอร์ประเภทสตริง ซึ่งเป็นไปตามมาตรฐานในฟังก์ชันการประทับเวลาใน 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;

เราสามารถส่งค่าวันที่ของประเภทสตริงที่แสดงในตารางไปยังฟังก์ชัน 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;

หัวข้อที่เกี่ยวข้อง

• เอกสารประกอบของ BigQuery