Exempel på sökfrågor för rapportloggar i BigQuery

Utgåvor som stöds för den här funktionen: Frontline Standard, Enterprise Standard och Enterprise Plus. Education Standard och Education Plus, Enterprise Essentials Plus.  Jämför utgåvor

Den här artikeln innehåller exempelfrågor för vanliga rapporter som du kan få från BigQuery. Dessa exempel på sökfrågor förutsätter äldre SQL. Ersätt api_project_name.dataset_name med ditt eget projektnamn och datasetnamn.

Läs mer om att söka i BigQuery-data.

Loggfält för Gmail och deras innebörd finns i Schema för Gmail-aktivitetsloggar i BigQuery.

Exempelsökningar

Konton

Antalet administratörskonton och delegerade konton och antalet konton som är inaktiverade, spärrade och avstängda efter 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;

Administratörer

De vanligaste händelserna som en administratör har utfört

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;

Hitta antalet avancerade administratörer på en bestämd domän

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

Endast standard-SQL

Förhållandet mellan antalet aktiva användare per dag och antalet användare som har varit aktiva under de senaste 30 dagarna i Google Kalender. Frågan i detta exempel omfattar flera tabeller.

Dagliga aktiva användare

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

Aktiva användare under 30 dagar

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;

Antal kalenderhändelser per 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;

Drive

Antal Google Drive-objekt som delats, grupperade efter delningsmetod

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;

Filers id, namn, ägare och typ. Filer som delats externt under tidsperioden.

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;

Ändringar av delningsbehörighet och resultatet av detta. Ger möjlighet att se vilka ändringar av behörigheten som ledde till att filers synlighet ändrades.

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

Händelsetyper uppdelade efter filtyp. Användbart för övertagningsrapport efter filtyp.

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;

Händelsetyp och händelsenamn per delad enhet

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;

Information om användare utanför domänen

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;

Vilka behörighetsändringar som gäller externa användare och när de gjordes

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;

Information om övervakning av lagringsutrymme

Användbart för att skapa rapporter om användare som förbrukar mer än X lagringsutrymme med en fast tröskel (definieras med AND accounts.drive_used_quota_in_mb > 0 -satsen).

Den här sökfrågan kan definieras som en schemalagd fråga eller så kan den anropas regelbundet via 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;

Obs!

• Detta värde kan ändras så att det matchar filtret som kunden ställer in. Till exempel över 15 GB: AND accounts.drive_used_quota_in_mb > 15000
• Datumjämförelsen med CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) gör det möjligt att göra en datumjämförelse med det tillgängliga formatet från värdet datum.

• Denna fråga gäller även Gmail där vi kan hitta ett liknande värde: accounts.gmail_used_quota_in_mb

Gmail

Metodtips för Gmail med BigQuery

• Sök enbart efter de data du behöver. I de här exemplen används en gräns på 1 000 matchningar, men du kan ange en egen gräns.
• Ange en tidsram för sökfrågorna. En dag är en vanlig tidsram.

Ämnesmatchning
Meddelandeöversikt för upp till 1 000 poster som matchar ett angivet ämne

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

Mottagarmatchning
Antal unika meddelanden för en viss mottagare

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 = "[e-mail]")

Matchning av disposition och mottagare

Meddelandeöversikt för upp till 1 000 objekt som matchar båda:

• En angiven disposition (Modifiera, Avvisa, Karantänplacera)
• En angiven mottagare

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 = "[e-mail]" AND
     EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
          WHERE consequence.action = 17)
LIMIT 1000

Regelbeskrivning har aktiverats
Meddelandeöversikt för upp till 1 000 poster, vilket aktiverade den angivna regelbeskrivningen

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

Markerat som skräppost
Meddelandeöversikt för upp till 1 000 poster: 

• Markerat som spam
• För en angiven mottagare
• Av alla orsaker

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 = "[e-mail]"
LIMIT 1000

Krypteringsprotokoll – inte krypterad
Meddelandeöversikt per krypteringsprotokoll – ej krypterad

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

Krypteringsprotokoll – enbart TLS
Meddelandeöversikt per meddelande efter krypteringsprotokoll – endast 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

Matchning av meddelande-id
Vyn Meddelandeinformation för ett visst meddelande-id (inkludera ”<>” runt meddelande-id:t)

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 – avvisa meddelande
Avvisa meddelande:

• Vilken regel orsakade avvisandet?

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 – ändra meddelande
Ändra meddelande: 

• Vilken regel orsakade modifieringen?
• Vilken underkategori av modifiering (exempelvis rubriker, ämne)?

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

Karantänmeddelande
Vilken regel har karantänplacerat ett meddelande?

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

Sammansatta sökningar
Räkna alla meddelanden som fångats av en specifik regel (rule description) under de senaste 30 dagarna:

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"

Lista alla meddelanden som tagits emot utan TLS-kryptering under det senaste dygnet:

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

Lista de 10 toppdomänerna som mitt konto har utbytt e-post med under de senaste 30 dagarna:

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

Förhållandet mellan antalet aktiva användare per dag och antalet användare som varit aktiva under de senaste 30 dagarna i Gmail

Dagliga aktiva användare:

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;

Aktiva användare under 7 dagar:

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;

Aktiva användare under 30 dagar:

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;

Grupper

Ändrat medlemskap och användarbeteende i Google Grupper

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

Om du vill ha en tidsstämpel av typen ÅÅÅÅ-MM-DD kan det första SELECT-uttryckselementet ersättas med:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

Datumen kan filtreras i WHERE-satsen på något av följande sätt:

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

Antal videosamtal och totalt antal minuter efter 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

Dagliga aktiva användare

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

Aktiva användare under 30 dagar

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

Regler

DLP-regler som utlösts efter namn, matchad app och åtgärd

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;

Token

Antal gånger en app från tredje part har getts åtkomstbehörighet till 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;

Inloggningsförsök på administratörskonsolen

Detaljerad information om misslyckade inloggningsförsök på Googles administratörskonsol

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 användningstabeller

Schemat kan ändras. En uppdaterad och fullständig lista över parametrar och fält finns i dokumentationen till Reports API. 

Filtrera efter datum

Du kan filtrera efter datum när du söker i tabellerna Aktivitet eller Användning. De har olika format när datumet presenteras:

• I aktivitetstabellen visas tidsstämplarna i Unix-mikrosekunder. Detta är ett heltal (en siffra) som kan konverteras till ett datum med funktionen TIMESTAMP_MICROSS().
• I användningstabellen visas värdena för date i datumformat, så denna konvertering är inte nödvändig.

För båda tabellerna kan du välja att filtrera efter ett visst datum (eller datumintervall) med hjälp av en av följande metoder.

Aktivitetstabellen

Du kan filtrera efter ett visst datum i tabellen Unix Micros (aktivitetstabell) genom att definiera WHERE-satsen och TIMESTAMP()-funktionen och göra en enkel jämförelse med operatorerna mer än (>) och mindre än (<):

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

Konceptet här gäller att sätta gränser för indatavärdet time_usec genom att jämföra returvärdet från funktionen TIMESTAMP_MICRO() med returvärdet för funktionen TIMESTAMP() med ett datum som läggs till som en strängtypparameter. Detta följer standarderna i Timeline-funktioner i standard-SQL och använder enkla jämförelseoperatorer (>) och (<), tillsammans med AND-tillägget för WHERE-satsen för att stänga ett särskilt tidsfönster.

Användningstabellen

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;

Vi kan skicka strängtypvärdet date i tabellen till funktionen TIMESTAMP() och använda jämförelseoperatorerna (>) och (<) på samma sätt som i det första exemplet.

 

Filtrera efter domänalias och underdomäner: utesluta och inkludera

Inkludera eller exkludera vissa domäner från dina sökresultat genom att använda ett filter för e-postadressen på WHERE-satsen med hjälp av jokertecken (%) för att filtrera domänerna.

Hur du använder AND- eller OR-uttrycket beror på om du filtrerar bort (exkluderar) eller enbart inkluderar vissa resultat.

Uteslut vissa domäner från resultat

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

Inkludera enbart vissa domäner i resultaten

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

Granskningsloggar för regler

Använd den här sökfrågan för att spåra användarnas försök att dela känslig data

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;

Relaterat ämne

• BigQuery-dokumentation