Unterstützte Versionen für diese Funktion: Frontline Standard; Enterprise Standard und Enterprise Plus; Education Standard und Education Plus; Enterprise Essentials Plus. G Suite-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 Projekt- und Dataset-Namen.
Weitere Informationen zum Abfragen von BigQuery-Daten
Informationen zu den Gmail-Protokollfeldern und ihrer Bedeutung finden Sie unter Schema für Gmail-Aktivitätsprotokolle in BigQuery.
Beispielabfragen
KontenAnzahl 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;
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;
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 – Tagesübersicht
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
Anzahl der in den letzten 30 Tagen aktiven Nutzer
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;
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 Klausel AND accounts.drive_used_quota_in_mb > 0
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. Beispiel: Über 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 aus dem date-Wert. -
Diese Abfrage gilt auch für Gmail, wo wir einen ähnlichen Wert finden:
accounts.gmail_used_quota_in_mb
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
Übereinstimmende Nachrichten-ID
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 = ""
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
Durch welche Regel wurde 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;
Ä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
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
Aktive Nutzer – Tagesübersicht
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
Anzahl der in den letzten 30 Tagen aktiven Nutzer
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
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;
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;
Das Schema kann sich ändern. In der Dokumentation zur Reports API (in englischer Sprache) finden Sie eine aktualisierte und vollständige Liste der Parameter und Felder.
Sie können nach dem Datum filtern, wenn die Tabellen Aktivität oder Nutzung abfragen. 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.
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.%")
Verwenden Sie diese Abfrage, um Versuche von Nutzern zu 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;