Beispielabfragen für Protokolle zur Berichterstellung in BigQuery

Unterstützte Versionen für diese Funktion: Frontline Standard und Frontline Plus; Enterprise Standard und Enterprise Plus; Education Standard und Education Plus; Enterprise Essentials Plus. Versionen vergleichen

Dieser Artikel enthält Beispielabfragen für häufig verwendete Berichte, die Sie mit BigQuery erstellen können. Für die Abfragen wird Legacy-SQL vorausgesetzt. Ersetzen Sie api_project_name.dataset_name durch Ihren eigenen Projektnamen und Dataset-Namen.

Weitere Informationen zum Abfragen von BigQuery-Daten

Informationen zu Gmail-Protokollfeldern und ihrer Bedeutung finden Sie unter Schema für Gmail-Aktivitätsprotokolle in BigQuery.

Beispielabfragen

Konten

Anzahl der Administratorkonten und delegierten Konten sowie die Anzahl der deaktivierten, gesperrten und vorübergehend gesperrten Konten

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;

Administratoren

Häufigste Aufgaben, die von einem Administrator ausgeführt werden

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;

Anzahl der Super Admins in einer bestimmten Domain

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

Nur Standard-SQL

Verhältnis von täglich aktiven Nutzern zu in den letzten 30 Tagen aktiven Nutzern in Google Kalender. In diesem Beispiel werden mehrere Tabellen in die Abfrage einbezogen.

Täglich aktive Nutzer

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

Aktive Nutzer – 30-Tage-Übersicht

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;

Anzahl der Kalendertermine nach Typ

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;

Datenmigration

Protokollereignisse für Datenmigration werden ab dem 26. Mai 2025 nach und nach eingeführt. Protokollereignisdaten vor diesem Datum sind möglicherweise nicht korrekt.

Alle Einrichtungs- und Konfigurationsaktionen, die von einem Administrator ausgeführt werden

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;

Alle migrationsbezogenen Ereignisse für eine bestimmte Ausführungs-ID

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;

Alle Fehler für eine bestimmte Ausführungs-ID

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;

Alle Ereignisse für einen bestimmten Quellentyp, z. B. 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;

Alle Termine für einen bestimmten Quellnutzer in der Enterprise Gmail Migration

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;

Alle Ereignisse für eine bestimmte Nachricht mit dem angegebenen Quell-URI, z. B. eine RFC-ID

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;

Alle Ereignisse für einen bestimmten Quelldatentyp, z. B. „Exchange Online-E‑Mail-Ordner“

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;

Anzahl der Ereignisse, gruppiert nach Status und Ereignisname

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;

Tägliche Anzahl aller Ereignisse, gruppiert nach Status und Objekttyp

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

Anzahl der freigegebenen Google Drive-Dateien nach Freigabemethode

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;

Datei-ID, Titel, Eigentümer und Typ. Dateien, die innerhalb des Zeitraums extern freigegeben wurden.

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;

Änderungen der Freigabeberechtigungen und deren Ergebnis. Damit können Sie analysieren, welche Änderungen der Freigabeberechtigungen zur geänderten Dateisichtbarkeit geführt haben.

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

Ereignistypen aufgeschlüsselt nach Dateitypen. Nützlich für den Akzeptanzbericht nach Dateityp.

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;

Ereignistyp und -name für jede geteilte Ablage

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;

Informationen zu Nutzern außerhalb Ihrer Domain

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;

Welche Berechtigungen für externe Nutzer wurden wann geändert?

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;

Informationen zum Speicher-Monitoring

Nützlich zum Erstellen von Berichten zu Nutzern, die mehr Drive-Speicherplatz als einen festgelegten Grenzwert verwenden. Dieser Wert wird mit der AND accounts.drive_used_quota_in_mb > 0-Klausel definiert.

Diese Abfrage kann als geplante Abfrage definiert oder beispielsweise mithilfe der API regelmäßig aufgerufen werden.

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;

Hinweise:

Dieser Wert kann entsprechend dem Filter des Kunden geändert werden. Bei über 15 GB wäre das zum Beispiel: AND accounts.drive_used_quota_in_mb > 15000
Durch den Abgleich des Datums mit CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) kann ein Datumsvergleich mit dem verfügbaren Format aus dem date-Wert vorgenommen werden.
Diese Abfrage ist auch für Gmail relevant, da es dort einen ähnlichen Wert gibt: accounts.gmail_used_quota_in_mb

Gmail

Bewährte Vorgehensweisen für Gmail mit BigQuery

Fragen Sie nur Daten ab, die Sie benötigen. In diesen Beispielen werden maximal 1.000 Treffer angezeigt,Sie können das Limit jedoch selbst festlegen.
Legen Sie einen Zeitraum für Ihre Abfragen fest. Ein häufig verwendeter Wert ist ein Tag.

Übereinstimmender Betreff
Bis zu 1.000 Nachrichten zusammengefasst anzeigen,deren Betreff einen bestimmten Text enthält

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

Übereinstimmender Empfänger
Nachrichten zählen, die allein an einen bestimmten Empfänger gesendet wurden

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

Übereinstimmende Disposition und übereinstimmender Empfänger

Bis zu 1.000 Nachrichten zusammengefasst anzeigen,die mit

einer bestimmten Disposition (Modifizieren, Ablehnen, Quarantäne) und
einem bestimmten Empfänger übereinstimmen.

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

Regelbeschreibung ausgelöst
Bis zu 1.000 Nachrichten zusammengefasst anzeigen, die eine bestimmte Regelbeschreibung ausgelöst haben

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

Als Spam markiert
Bis zu 1.000 Nachrichten zusammengefasst anzeigen:

Als Spam markiert
an einen bestimmten Empfänger gesendet oder
Aus allen Gründen

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

Verschlüsselungsprotokoll – nicht verschlüsselt
Alle Nachrichten mit der Angabe „Verschlüsselungsprotokoll – nicht verschlüsselt“ zusammengefasst anzeigen

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

Verschlüsselungsprotokoll – Nur TLS
Nur Nachrichten mit der Einstellung "Verschlüsselungsprotokoll – Nur TLS" zusammengefasst anzeigen

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

Nachrichten-ID stimmt überein
Nachrichtendetails zu einer bestimmten Nachrichten-ID anzeigen (schließen Sie die Nachrichten-ID in „<>“ ein)

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
         gmail.event_info.success,
         gmail.event_info.elapsed_time_usec,
         gmail.message_info.subject,
         gmail.message_info.source.address as source,
         gmail.message_info.source.service as source_service,
         gmail.message_info.source.selector as source_selector,
         destination.address as destination,
         destination.service,
         destination.selector as destination_selector,
         gmail.message_info.rfc2822_message_id,
         gmail.message_info.payload_size,
         gmail.message_info.num_message_attachments,
         gmail.message_info.connection_info.smtp_tls_state,
         gmail.message_info.description
    FROM your_dataset_id.activity d, d.gmail.message_info.destination
    WHERE gmail.message_info.rfc2822_message_id = "<message id>"
    LIMIT 1000

Disposition – Nachricht ablehnen
Nachricht ablehnen:

Welche Regel hat die Ablehnung ausgelöst?

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

Disposition: Nachricht ändern
Nachricht ändern:

Welche Regel hat die Änderung ausgelöst?
In welche Unterkategorie fällt die Änderung (Header, Betreff usw.)?

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

Nachricht unter Quarantäne stellen
Welche Regel hat veranlasst, dass die Nachricht unter Quarantäne gestellt wurde?

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

Kumulierende Abfragen
Alle Nachrichten der letzten 30 Tage zählen, die der Beschreibung einer bestimmten Regel mit dem Namen „rule description“ entsprechen:

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"

Alle empfangenen Nachrichten des letzten Tages auflisten, die nicht mit TLS verschlüsselt waren:

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

Die zehn Domains anzeigen, mit denen über mein Konto in den letzten 30 Tagen die meisten E-Mails ausgetauscht wurden:

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

Verhältnis von täglich aktiven Nutzern zu in den letzten 30 Tagen aktiven Nutzern in Gmail

Aktive Nutzer – Tagesübersicht:

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;

Aktive Nutzer – 7-Tage-Übersicht:

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;

Aktive Nutzer – 30-Tage-Übersicht:

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;

Die 100 neuesten Gmail-Protokollereignisse, bei denen mindestens ein Klassifizierungslabel mit der E‑Mail-Nachricht verknüpft ist

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;

Alle verfügbaren Protokollereignisse für eine bestimmte 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;

Gruppen

Änderungen an Gruppenmitgliedschaft und Nutzerverhalten in 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

Wenn Sie einen Zeitstempel im Format JJJJ-MM-TT verwenden möchten, kann das erste SELECT-Anweisungselement durch Folgendes ersetzt werden:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

Sie können die Daten in der WHERE-Klausel auf eine der folgenden Arten filtern:

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

Anzahl der Videoanrufe und Gesamtzahl der Anrufminuten nach Datum

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

Täglich aktive Nutzer

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

Aktive Nutzer – 30-Tage-Übersicht

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

Regeln

Ausgelöste DLP-Regeln nach Name, übereinstimmender Anwendung und Aktionen

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;

Tokens

Wie oft wurde eine Drittanbieter-App für den Zugriff auf Google Drive aktiviert?

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;

Detaillierte Informationen zu fehlgeschlagenen Anmeldungen in der Google Admin-Konsole

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;

Schema für Nutzungstabellen

Das Schema kann sich ändern. In der Dokumentation zur Reports API finden Sie eine aktualisierte und vollständige Liste der Parameter und Felder.

Nach Datum filtern

Sie können nach dem Datum filtern, wenn die Tabellen Aktivität oder Nutzung abgefragt werden. Beide haben bei der Darstellung des Datums eigene Formate:

In der Tabelle Aktivität werden Zeitstempel in Unix-Mikrosekunden gespeichert. Das ist eine Ganzzahl, die mit der Funktion TIMESTAMP_MICROS() in ein Datum umgewandelt werden kann.
In der Tabelle Nutzung werden die Werte für das Datum im Datumsformat angezeigt. Eine Umwandlung ist daher nicht erforderlich.

In beiden Tabellen können Sie mit einer der folgenden Methoden nach einem bestimmten Datum oder Zeitraum filtern.

Tabelle „Aktivität“

Wenn Sie nach einem bestimmten Datum mit Unix-Mikrosekunden (Tabelle Aktivität) filtern möchten, können Sie die Klausel WHERE und die Funktion TIMESTAMP() definieren. Führen Sie einen einfachen Vergleich mit den Operatoren größer als (>) und kleiner als (<) aus:

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

Das Ziel hier ist, den Eingabewert time_usec zu begrenzen, indem der Rückgabewert der Funktion TIMESTAMP_MICROS() mit dem Rückgabewert der Funktion TIMESTAMP() verglichen wird, der das Datum als String-Parameter hinzugefügt wurde. Das entspricht den Standards im Hilfedokument TIMESTAMP-Funktionen in Standard-SQL. Dabei werden einfache Vergleichsoperatoren (>) und (<) zusammen mit der Erweiterung AND aus der WHERE-Klausel verwendet, um ein Zeitfenster zu schließen.

Tabelle „Nutzung“

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;

Sie können den in der Tabelle enthaltenen String-Wert für das Datum in die Funktion TIMESTAMP() übergeben und die Vergleichsoperatoren (>) und (<) genauso verwenden wie im ersten Beispiel.

Nach Domain-Aliassen und Subdomains filtern: ausschließen und einschließen

Wenn Sie bestimmte Domains in die Abfrageergebnisse einschließen oder aus ihnen ausschließen möchten, können Sie in der WHERE-Klausel einen Filter für die E-Mail-Adresse anwenden und Platzhalter (%) verwenden, um die Domains zu filtern.

Wie Sie die AND- bzw. OR-Anweisungen verwenden, hängt davon ab, ob Sie bestimmte Ergebnisse herausfiltern (ausschließen) oder nur einschließen möchten.

Bestimmte Domains von Ergebnissen ausschließen

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

Nur bestimmte Domains in Ergebnisse einschließen

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

Audit-Logs zu Regeln

Mit dieser Abfrage können Sie Versuche von Nutzern erfassen, sensible Daten zu teilen.

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;

Weitere Informationen

BigQuery-Dokumentation

Google, Google Workspace sowie zugehörige Marken und Logos sind Marken von Google LLC. Alle anderen Unternehmens- und Produktnamen sind Marken der jeweiligen Unternehmen.

Beispielabfragen für Protokolle zur Berichterstellung in BigQuery Mit Sammlungen den Überblick behalten Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.