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-Logfeldern und ihrer Bedeutung finden Sie unter Schema für Gmail-Aktivitätsprotokolle in BigQuery.

Beispielabfragen

Konten

Anzahl der Administrator- und delegierten Konten sowie Anzahl der deaktivierten, gesperrten und gesperrten Konten nach Datum

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 Ereignisse, 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 ermitteln

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 pro Tag zu in den letzten 30 Tagen aktiven Nutzern in Google Kalender. In diesem Beispiel werden mehrere Tabellen in die Abfrage einbezogen.

Aktive Nutzer pro Tag

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 Ereignisse 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. einer 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 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-Elemente, gruppiert 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, Inhaber und Typ. Dateien, die im angegebenen Zeitraum 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. So können Sie nachvollziehen, welche Berechtigungsänderungen zu der Änderung der 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 nach Dateityp aufgeschlüsselt. Nützlich für den Adoptionsbericht 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 Berechtigungsänderungen externen Nutzern gewährt wurden und wann

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 Klausel AND accounts.drive_used_quota_in_mb > 0 definiert.

Diese Abfrage kann als geplante Abfrage definiert oder beispielsweise regelmäßig über die API 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. Beispiel für mehr als 15 GB: AND accounts.drive_used_quota_in_mb > 15000
  • Der Datumsvergleich mit CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) ermöglicht einen Datumsvergleich mit dem verfügbaren Format des date-Werts.

  • Diese Abfrage kann auch für Gmail verwendet werden, wo wir einen ähnlichen Wert finden können: 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 bestimmen.
  • 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,die

  • Als Spam markiert
  • an einen bestimmten Empfänger gesendet oder
  • aus beliebigen Gründen als Spam markiert wurden.
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 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 eine Nachricht unter Quarantäne gestellt?

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-Nachricht

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 der Google-Gruppenmitgliedschaft und Nutzerverhalten

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 Gesamtanrufminuten 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

Aktive Nutzer pro Tag

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

Anzahl der Aktivierungen einer Drittanbieter-App für den Zugriff auf 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;

Anmeldeversuche in der Admin-Konsole

Detaillierte Informationen zu fehlgeschlagenen Anmeldeversuchen 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. Eine aktualisierte und vollständige Liste der Parameter und Felder finden Sie in der Reports API-Dokumentation.

Nach Datum filtern

Sie können die Tabellen activity oder usage nach Datum filtern, wenn Sie sie abfragen. Beide haben bei der Darstellung des Datums eigene Formate:

  • In der Tabelle activity werden die 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 Datums-Werte in einem Datumsformat angezeigt. Daher ist diese Umrechnung nicht erforderlich.

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

Aktivitätstabelle

Wenn Sie nach einem bestimmten Datum mit der Struktur „Unix Micros“ (Tabelle activity) filtern möchten, können Sie die WHERE-Anweisung und die Funktion TIMESTAMP() definieren, um einen einfachen Vergleich mit den Operatoren „größer als“ (>) und „kleiner als“ (<) durchzuführen:

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

Hier werden Grenzwerte für den Eingabewert time_usec festgelegt, indem der Rückgabewert der Funktion TIMESTAMP_MICROS() mit dem Rückgabewert der Funktion TIMESTAMP() verglichen wird, wobei ein Datum als Parameter vom Typ „String“ hinzugefügt wird. Dies entspricht den Standards für Zeitstempelfunktionen in Standard-SQL. Es werden einfache Vergleichsoperatoren (>) und (<) sowie die AND-Erweiterung der WHERE-Anweisung verwendet, um ein Zeitfenster zu schließen.

Nutzungstabelle

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;

Wir können den Stringwert date in der Tabelle an die Funktion TIMESTAMP() übergeben und die Vergleichsoperatoren (>) und (<) auf dieselbe Weise wie im ersten Beispiel verwenden.

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 Anweisung AND oder OR verwenden, hängt davon ab, ob Sie bestimmte Ergebnisse herausfiltern (ausschließen) oder nur bestimmte Ergebnisse einbeziehen 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 für Regeln

Mit dieser Abfrage können Sie Versuche von Nutzern nachvollziehen, 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;


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