Voorbeeldquery's voor rapportlogboeken in BigQuery

Deze functie wordt ondersteund in de volgende versies: Frontline Standard, Frontline Plus, Enterprise Standard, Enterprise Plus, Education Standard, Education Plus en Enterprise Essentials Plus. Versies vergelijken

Dit artikel bevat voorbeeldquery's voor veelgebruikte rapporten die u kunt bekijken in BigQuery. In deze voorbeeldquery's wordt uitgegaan van verouderde SQL. Vervang api_project_name.dataset_name door uw eigen projectnaam en datasetnaam.

Meer informatie over zoeken in BigQuery-gegevens.

Ga naar Schema voor Gmail-activiteitslogboeken in BigQuery voor de Gmail-logboekvelden en de betekenis ervan.

Voorbeelden van zoekopdrachten

Accounts

Het aantal beheerdersaccounts en gemachtigde accounts, en het aantal uitgezette, geblokkeerde en opgeschorte (per datum) accounts

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;

Beheerders

Meest voorkomende gebeurtenissen die door een beheerder zijn uitgevoerd

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;

Het aantal hoofdbeheerders in een bepaald domein vinden

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;

Agenda

Alleen standaard SQL

De verhouding van gebruikers die in de afgelopen dag actief waren tot de gebruikers die in de afgelopen 30 dagen actief waren in Google Agenda. Dit voorbeeld zoekt in meerdere tabellen.

Dagelijks actieve gebruikers

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

Gebruikers die in de afgelopen 30 dagen actief waren

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;

Aantal agenda-afspraken per type

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;

Gegevensmigratie

Gebeurtenissen in het logboek Gegevensmigratie worden geleidelijk uitgerold vanaf 26 mei 2025. Gebeurtenisgegevens in het logboek van vóór die datum zijn misschien niet nauwkeurig.

Alle installatie- en configuratieacties die door een beheerder zijn uitgevoerd

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 migratiegerelateerde gebeurtenissen voor een bepaalde uitvoerings-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 fouten voor een bepaalde uitvoerings-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 gebeurtenissen voor een bepaald brontype, zoals 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 gebeurtenissen voor een bepaalde brongebruiker in 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 gebeurtenissen voor een bepaald bericht met een bepaalde bron-URI, zoals een 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 gebeurtenissen voor een bepaald brongegevenstype, zoals Exchange Online-e-mailmap

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;

Aantal gebeurtenissen gegroepeerd op status en gebeurtenisnaam

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;

Dagelijks aantal van alle gebeurtenissen gegroepeerd op status en objecttype

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

Aantal gedeelde Google Drive-items, gegroepeerd op deelmethode

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;

Bestands-ID, titel, eigenaar en type. Bestanden die binnen de periode extern zijn gedeeld.

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;

Wijzigingen aan rechten voor delen en de resultaten ervan. Zo kun je nagaan welke wijzigingen in de rechten hebben geleid tot de wijziging in de zichtbaarheid van het bestand.

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

Gebeurtenistypen uitgesplitst per bestandstype. Handig voor een gebruiksrapport per bestandstype.

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;

Gebeurtenistype en naam voor elke gedeelde Drive

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;

Informatie over gebruikers buiten uw domein

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;

Welke en wanneer rechtenwijzigingen zijn verleend aan externe gebruikers

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;

Informatie over opslagcontrole

Handig als u rapporten wilt maken over gebruikers die meer dan X Drive-opslag gebruiken, met een vastgestelde drempelwaarde (opgegeven met de clausule AND accounts.drive_used_quota_in_mb > 0).

Deze query kan worden gedefinieerd als een geplande query of kan bijvoorbeeld periodiek worden aangeroepen via de 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;

Opmerkingen:

  • De waarde kan worden aangepast zodat deze overeenkomt met het filter dat de klant instelt. Bijvoorbeeld meer dan 15 GB: AND accounts.drive_used_quota_in_mb > 15000
  • Dankzij de datumvergelijking met CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) kunnen datums worden vergeleken met de beschikbare indeling van de waarde date.

  • Deze query geldt ook voor Gmail, waar u een vergelijkbare waarde kunt vinden: accounts.gmail_used_quota_in_mb

Gmail

Praktische tips voor Gmail met BigQuery

  • Zoek alleen naar de gegevens die u nodig heeft. In deze voorbeelden is een limiet van duizend resultaten gebruikt, maar u kunt uw eigen limiet instellen.
  • Stel een periode in voor de zoekopdracht. Een typische periode is één dag.

Overeenkomst onderwerp
Weergave met berichtoverzichten voor maximaal 1000 resultaten die overeenkomen met een bepaald onderwerp

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

Overeenkomst ontvanger
Het aantal verschillende berichten voor een bepaalde ontvanger

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

Overeenkomst ordening en ontvanger

Weergave met berichtoverzichten voor maximaal duizend resultaten die voldoen aan deze beide criteria:

  • Een bepaalde ordening ('Gewijzigd', 'Geweigerd', 'In de quarantaine geplaatst')
  • Een bepaalde ontvanger
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

Regelbeschrijving getriggerd
Weergave met berichtoverzichten voor maximaal 1000 resultaten waardoor de opgegeven regelbeschrijving is getriggerd

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

Gemarkeerd als spam
Weergave met berichtoverzichten voor maximaal 1000 resultaten:

  • Gemarkeerd als spam
  • Voor een bepaalde ontvanger
  • Wegens elke reden
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

Versleutelingsprotocol: niet versleuteld
Weergave met berichtoverzichten per versleutelingsprotocol: niet versleuteld

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

Versleutelingsprotocol: alleen TLS
Weergave met berichtoverzichten per versleutelingsprotocol: alleen 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

Overeenkomst bericht-ID
Weergave met berichtoverzichten met een bepaalde bericht-ID (plaats < voor en > achter de bericht-ID)

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

Ordening: Bericht weigeren
Bericht weigeren:

  • Welke regel veroorzaakte dat het bericht werd geweigerd?
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

Ordening: Bericht wijzigen
Bericht wijzigen:

  • Welke regel zorgde dat het bericht werd gewijzigd?
  • Welke subcategorie van wijzigingen (bijvoorbeeld kopteksten of onderwerp)?
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

Bericht in quarantaine plaatsen
Welke regel heeft een bericht in quarantaine geplaatst?

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

Query's samenstellen
Het aantal berichten dat voldoet aan een specifieke regel (genaamd 'rule description' (regelbeschrijving)) in de afgelopen 30 dagen:

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 berichten zonder TLS-versleuteling weergeven die in de afgelopen dag zijn ontvangen:

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

De top tien van domeinen waarmee mijn account in de afgelopen dertig dagen heeft gemaild:

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

De verhouding van dagelijks actieve gebruikers tot gebruikers die in de afgelopen 30 dagen actief waren in Gmail

Dagelijks actieve gebruikers:

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;

Gebruikers die in de afgelopen 7 dagen actief waren:

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;

Gebruikers die in de afgelopen 30 dagen actief waren:

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;

De 100 meest recente Gmail-logboekgebeurtenissen met minstens één classificatielabel dat aan het e-mailbericht is gekoppeld

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 beschikbare logboekgebeurtenissen voor een specifiek e-mailbericht

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;

Groepen

Wijzigingen in het lidmaatschap van Google-groepen en gebruikersgedrag

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

Als u het tijdstempel JJJJ-MM-DD wilt, kunt u de eerste SELECT-instructie vervangen door:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

De datums kunnen op de volgende manieren worden gefilterd in de WHERE-clausule:

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

Aantal videogesprekken en totaal aantal gespreksminuten per 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

Dagelijks actieve gebruikers

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

Gebruikers die in de afgelopen 30 dagen actief waren

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

Regels

Geactiveerde DLP-regels op naam, overeenkomende app en acties

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

Het aantal keren dat een app van derden is aangezet om toegang te krijgen tot 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;

Inlogpogingen bij de beheerdersconsole

Gedetailleerde informatie over mislukte inlogpogingen bij de Google Beheerdersconsole

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 voor gebruikstabellen

Het schema kan veranderen. U vindt een geüpdatete en volledige lijst met parameters en velden in de documentatie van de Reports API.

Filteren op datum

U kunt filteren op datum als u een query uitvoert op de tabel activity of usage. De manier waarop de datum wordt gepresenteerd verschilt tussen deze tabellen:

  • In de tabel activity worden de tijdstempels opgeslagen in Unix-microseconden. Dit is een geheel getal dat u kunt omzetten naar een datum met de functie TIMESTAMP_MI CROS().
  • In de tabel usage worden de date-waarden weergegeven met een datumnotatie, dus deze conversie is niet nodig.

Voor beide tabellen kunt u op de volgende manieren filteren op een specifieke datum (of periode).

De activiteitentabel

Als u wilt filteren op een specifieke datum met de structuur Unix Micros (tabel activity), kunt u de WHERE-component en de functie TIMESTAMP() definiëren om een eenvoudige vergelijking uit te voeren met de operatoren groter dan (>) en kleiner dan (<):

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

Het concept hier is om limieten te plaatsen in de invoerwaarde time_usec door de retourwaarde van de functie TIMESTAMP_MICROS() te vergelijken met de retourwaarde van de functie TIMESTAMP() met een datum die is toegevoegd als parameter van het type tekenreeks. Dit volgt de normen in Tijdstempelfuncties in standaard SQL en gebruikt eenvoudige vergelijkingsoperatoren (>) en (<), samen met de AND-extensie van de WHERE-clausule om een specifiek tijdvenster te sluiten.

De tabel Gebruik

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;

We kunnen de datumwaarde van het tekenreekstype in de tabel doorgeven aan de functie TIMESTAMP() en de vergelijkingsoperatoren (>) en (<) op dezelfde manier gebruiken als in het eerste voorbeeld.

Filteren op domeinaliassen en subdomeinen: uitsluiten en opnemen

Als u bepaalde domeinen wilt uitsluiten van (of opnemen in) de queryresultaten, kunt u een filter voor het e-mailadres toepassen in de WHERE-clausule met jokertekens (%) om de domeinen te filteren.

Hoe u de instructie AND of OR gebruikt, is afhankelijk van of u bepaalde resultaten uitfiltert (uitsluit) of alleen opneemt.

Bepaalde domeinen uitsluiten van de resultaten

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

Alleen bepaalde domeinen opnemen in de resultaten

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

Controlelogboeken voor regels

Deze query gebruiken om pogingen van gebruikers om gevoelige gegevens te delen bij te houden

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 en gerelateerde merken en logo's zijn handelsmerken van Google LLC. Alle andere bedrijfs- en productnamen zijn handelsmerken van de bedrijven waarmee ze in verband worden gebracht.