Przykładowe zapytania o dzienniki raportów w BigQuery

Ta funkcja jest dostępna w tych wersjach: Frontline Standard i Frontline Plus; Enterprise Standard i Enterprise Plus; Education Standard i Education Plus; Enterprise Essentials Plus. Porównanie wersji

Ten artykuł zawiera przykładowe zapytania dotyczące typowych raportów, które można uzyskać z BigQuery. Zapytania te opierają się na założeniu, że użytkownik korzysta ze starszej wersji SQL. Zastąp api_project_name.dataset_name nazwą własnego projektu i zbioru danych.

Dowiedz się więcej o zapytaniach o dane BigQuery.

Szczegółowe informacje o polach dziennika Gmaila i ich znaczeniu znajdziesz w artykule na temat schematu dzienników Gmaila w BigQuery.

Przykładowe zapytania

Konta

Liczba kont administratora i kont z przekazanym dostępem oraz liczba kont wyłączonych, zablokowanych i zawieszonych do określonej daty

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;

Administratorzy

Najczęstsze czynności wykonywane przez administratorów

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;

Znajdź liczbę superadministratorów w danej domenie

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;

Kalendarz

Tylko standardowy SQL

Stosunek liczby aktywnych użytkowników dziennie do liczby użytkowników aktywnych w ciągu ostatnich 30 dni w Kalendarzu Google. W tym przykładzie zapytania dotyczą wielu tabel.

Aktywni użytkownicy dziennie

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

Użytkownicy aktywni w ciągu 30 dni

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;

Liczba wydarzeń z kalendarza według typu

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;

Migracja danych

Zdarzenia z dziennika migracji danych będą wdrażane stopniowo od 26 maja 2025 r. Dane zdarzeń z dziennika sprzed tej daty mogą być niedokładne.

Wszystkie czynności związane z konfiguracją wykonywane przez administratora

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;

Wszystkie zdarzenia związane z migracją dotyczące konkretnego identyfikatora wykonania

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;

Wszystkie błędy dotyczące konkretnego identyfikatora wykonania

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;

Wszystkie zdarzenia dotyczące konkretnego typu źródła, np. 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;

Wszystkie zdarzenia dotyczące konkretnego użytkownika źródłowego w ramach migracji Gmaila w wersji 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;

Wszystkie zdarzenia dotyczące konkretnej wiadomości z danym identyfikatorem URI źródła, np. identyfikatorem 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;

Wszystkie zdarzenia dotyczące określonego typu danych źródłowych, np. z folderu e-maili w 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;

Liczba zdarzeń pogrupowanych według stanu i nazwy

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;

Dzienna liczba wszystkich zdarzeń pogrupowanych według stanu i typu obiektu

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;

Dysk

Liczba udostępnionych elementów z Dysku Google pogrupowanych według metody udostępniania

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;

Identyfikator, tytuł, właściciel i typ pliku – pliki udostępnione zewnętrznie w danym przedziale czasu.

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;

Zmiany uprawnień do udostępniania i rezultaty tych zmian – pozwala zrozumieć, jakie zmiany uprawnień spowodowały zmianę widoczności pliku.

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

Typy zdarzeń pogrupowane według typu pliku – przydatne w przypadku raportu przystosowania według typu pliku.

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;

Typ i nazwa zdarzenia w przypadku każdego dysku współdzielonego

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;

Informacje o użytkownikach spoza domeny

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;

Jakie zmiany uprawnień zostały zastosowane na kontach użytkowników zewnętrznych i kiedy to nastąpiło

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;

Informacje o monitorowaniu miejsca na dane

Przydatne przy tworzeniu raportów o użytkownikach, którzy zużywają ponad X miejsca na Dysku przy ustawionym progu (zdefiniowanym przez klauzulę AND accounts.drive_used_quota_in_mb > 0).

Zapytanie to można zdefiniować jako zapytanie zaplanowane lub na przykład wywoływać je okresowo za pomocą interfejsu 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;

Uwagi:

Tę wartość można modyfikować zgodnie z filtrem ustawionym przez klienta. Na przykład ponad 15 GB: AND accounts.drive_used_quota_in_mb > 15000
Zapytanie CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) umożliwia porównanie daty z wykorzystaniem dostępnego formatu wartości atrybutu date.
To zapytanie dotyczy też Gmaila, w którym możemy znaleźć podobną wartość: accounts.gmail_used_quota_in_mb

Gmail

Sprawdzone metody korzystania z danych Gmaila w BigQuery

W zapytaniu uwzględniaj tylko potrzebne dane. W przedstawionych przykładach liczba wyników jest ograniczona do 1000,ale możesz ustawić własny limit.
Ustaw przedział czasowy w zapytaniach. Typowy przedział czasowy to jeden dzień.

Dopasowanie tematu
Podsumowanie wiadomości zawierające maksymalnie 1000 rekordów dopasowanych na podstawie tematu.

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

Dopasowanie odbiorcy
Liczba odrębnych wiadomości do określonego adresata.

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

Dopasowanie dyspozycji i adresata

Podsumowanie wiadomości zawierające maksymalnie 1000 rekordów dopasowanych na podstawie obu tych parametrów:

dyspozycja (zmodyfikowanie, odrzucenie, kwarantanna);
określony odbiorca,

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

Wyzwolony opis reguły
Podsumowanie wiadomości zawierające maksymalnie 1000 rekordów, które wyzwoliły określony opis reguły.

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

Oznaczone jako spam
Podsumowanie wiadomości zawierające maksymalnie 1000 rekordów:

Oznaczone jako spam
do określonego adresata,
z dowolnego powodu.

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

Protokół szyfrowania – niezaszyfrowane
Podsumowanie wiadomości według protokołu szyfrowania – niezaszyfrowane

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

Protokół szyfrowania – tylko TLS
Podsumowanie wiadomości według protokołu szyfrowania – tylko 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

Dopasowanie identyfikatora wiadomości
Szczegóły wiadomości o określonym identyfikatorze (umieść identyfikator wiadomości między znakami „<>”):

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

Dyspozycja – odrzucenie wiadomości
Odrzucenie wiadomości:

Która reguła spowodowała odrzucenie?

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

Dyspozycja – modyfikacja wiadomości
Modyfikacja wiadomości:

Która reguła spowodowała modyfikację?
Do jakiej podkategorii należy wprowadzona zmiana (np. nagłówki, temat)?

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

Kwarantanna wiadomości
Która reguła spowodowała umieszczenie wiadomości w kwarantannie?

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

Zapytania złożone
Liczba wszystkich wiadomości wychwyconych przez określoną regułę (o nazwie „opis reguły”) w ciągu ostatnich 30 dni:

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"

Wszystkie wiadomości bez szyfrowania TLS otrzymane w ciągu ostatniego dnia:

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

Dziesięć domen, z którymi poczta z Twojego konta była najczęściej wymieniana w ciągu ostatnich 30 dni:

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

Stosunek liczby aktywnych użytkowników dziennie do liczby użytkowników aktywnych w ciągu ostatnich 30 dni w Gmailu

Liczba aktywnych użytkowników dziennie:

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;

Liczba użytkowników aktywnych w ciągu 7 dni.

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;

Liczba użytkowników aktywnych w ciągu 30 dni.

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 najnowszych zdarzeń z dziennika Gmaila z co najmniej 1 etykietą klasyfikacji powiązaną z wiadomością e-mail

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;

Wszystkie dostępne zdarzenia z dziennika dotyczące konkretnej wiadomości e-mail

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;

Grupy

Zmiany dotyczące członkostwa w grupach dyskusyjnych Google oraz zachowanie użytkowników tych 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

Jeśli chcesz mieć sygnaturę czasową w formacie RRRR-MM-DD, zastąp pierwszy element instrukcji SELECT tym elementem:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

Daty w klauzuli WHERE mogą być filtrowane w następujący sposób:

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

Liczba rozmów wideo i łączna liczba minut połączenia według daty

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

Aktywni użytkownicy dziennie

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

Użytkownicy aktywni w ciągu 30 dni

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

Reguły

Uruchomione reguły DLP według nazwy, dopasowanej aplikacji i czynności

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;

Tokeny

Liczba przypadków, w których aplikacja zewnętrzna uzyskała dostęp do Dysku 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;

Szczegółowe informacje o nieudanych próbach logowania w konsoli administracyjnej 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;

Schemat tabel użytkowania

Schemat może się zmieniać. Aktualną i pełną listę parametrów oraz pól znajdziesz w dokumentacji interfejsu Reports API.

Filtruj wg daty

Możesz filtrować wyniki według daty, tworząc zapytania w tabelach aktywności lub użytkowania. Każda z nich prezentuje datę w innym formacie:

Tabela aktywności przechowuje sygnatury czasowe w mikrosekundach uniksowego znacznika czasu. Jest to liczba całkowita, którą można przekonwertować na datę przy użyciu funkcji TIMESTAMP_MICROS().
Tabela użytkowania prezentuje wartości date w formacie daty, więc konwersja nie jest potrzebna.

W przypadku obu tabel możesz przefiltrować wyniki według konkretnej daty (lub zakresu dat), korzystając z jednej z poniższych metod.

Tabela aktywności

Aby filtrować dane według konkretnej daty za pomocą struktury Unix Micro (tabela aktywności), możesz zdefiniować klauzulę WHERE i funkcję TIMESTAMP() w celu przeprowadzenia prostego porównania z operatorami większe niż (>) i mniejsze niż (<):

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

Polega to na ograniczeniu wartości wejściowej time_usec poprzez porównanie wartości zwracanej przez funkcję TIMESTAMP_MICROS() z wartością zwracaną przez funkcję TIMESTAMP(), w której data jest dodana jako parametr będący ciągiem znaków. Jest to zgodne ze standardami opisanymi w artykule Funkcje sygnatury czasowej w standardowej wersji SQL (tekst w języku angielskim). Do zamknięcia okna czasowego wykorzystywane są tu proste operatory porównania (> i <) wraz z rozszerzeniem AND klauzuli WHERE.

Tabela użytkowania

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;

Możemy przekazać wartość date typu ciąg znaków podaną w tabeli do funkcji TIMESTAMP() i użyć operatorów porównania (> i <) w taki sam sposób jak w pierwszym przykładzie.

Filtrowanie według aliasów domen i subdomen: wyklucz i uwzględnij

Aby uwzględnić w wynikach zapytania określone domeny (lub je wykluczyć), w klauzuli WHERE zastosuj filtr adresu e-mail, używając symboli zastępczych (%) do filtrowania domen.

Sposób korzystania z operatorów AND i OR zależy od tego, czy chcesz odfiltrować (wykluczyć) czy uwzględnić pewne wyniki.

Wykluczanie z wyników określonych domen

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

Uwzględniaj w wynikach tylko konkretne domeny

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

Dzienniki kontrolne reguł

Użyj tego zapytania, aby monitorować próby udostępnienia poufnych danych przez użytkowników

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;

Temat pokrewny

Dokumentacja BigQuery

Google, Google Workspace i inne powiązane nazwy są znakami towarowymi Google LLC. Wszystkie inne nazwy firm i produktów są znakami towarowymi należącymi do ich właścicieli.

Przykładowe zapytania o dzienniki raportów w BigQuery Zadbaj o dobrą organizację dzięki kolekcji Zapisuj i kategoryzuj treści zgodnie ze swoimi preferencjami.