BigQuery report and log examples

This feature is available with the G Suite Enterprise and G Suite Enterprise for Education editions. Compare editions

You can run queries for ad-hoc analysis using the Google BigQuery console. Replace mydomain:Reports with your own dataset project name. Most of these queries assume legacy SQL.

If you want to use standard SQL dialect, replace [mydomain:Reports.usage_201703] with `mydomain.Reports.usage_201703` (note the apostrophe and period).

Here are some examples:

Accounts

Number of admin and delegated accounts, and number disabled, locked out, and suspended by date 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 [mydomain:Reports.usage_201703] 
WHERE NOT IS_NULL(accounts.num_locked_users)
ORDER BY date ASC;

Administrators

Most frequent events performed by an admin.

SELECT count(*) as admin_actions, event_name
FROM [mydomain:Reports.activity_201703]
WHERE accounts.is_super_admin group by 2 order by 1 desc limit 5

Find the number of super admins in a given domain.

SELECT count(*) as number_of_super_admins
FROM [mydomain:Reports.activity_201703]
WHERE accounts.is_super_admin = true

Calendar

Standard SQL only

Ratio of daily active users to 30 day active users in Google Calendar. This example queries across multiple tables.

SELECT date,
calendar.num_1day_active_users/calendar.num_30day_active_users
FROM 
`mydomain.Reports.usage_*`
WHERE calendar.num_1day_active_users > 0
AND calendar.num_30day_active_users > 0
ORDER BY date asc;

Number of calendar events by type.

SELECT count(distinct calendar.calendar_id), event_name
FROM [mydomain:Reports.activity_201703]
WHERE NOT IS_NULL(calendar.calendar_id)
GROUP BY 2 ORDER BY 1 desc;

Drive

Number of Google Drive items shared, grouped by sharing method.

SELECT count_distinct(drive.doc_id) AS count, drive.visibility 
FROM [mydomain:Reports.activity_201703] 
GROUP BY 2 ORDER BY 1 desc; 

File ID, title, owner and type. Files that were shared externally within the time window.

SELECT drive.doc_id, drive.doc_title, drive.owner, drive.doc_type
FROM [mydomain:Reports.activity_201703] 
where drive.visibility = "shared_externally"
LIMIT 100;

Sharing permission changes and their result. Gives you the ability to understand what permission changes yielded the change in file visibility.

SELECT drive.doc_title, drive.visibility_change,drive.old_visibility, drive.visibility,
FROM [mydomain:Reports.activity_201703] 
WHERE record_type = "DRIVE"
AND NOT IS_NULL(drive.old_visibility)
AND drive.old_visibility != "unknown";

Event types broken down by file type. Useful for adoption report by file type.

SELECT drive.doc_type, event_type, count(*)
FROM [mydomain:Reports.activity_201703]
WHERE record_type = "DRIVE"
GROUP by 1,2 ORDER BY 3 desc;

Event type and name for each shared drive.

SELECT drive.shared_drives_id, event_type, event_name, record_type,
count(distinct drive.doc_id)
FROM [mydomain:Reports.activity_201703] 
WHERE NOT IS_NULL(drive.shared_drives_id)
GROUP BY 1,2,3,4 ORDER BY 5 DESC;

Information on users outside of your domain.

SELECT email, event_name, count(*)
FROM [mydomain:Reports.activity_201701]
WHERE email != ""
AND email NOT LIKE "%mydomain.com%"
GROUP BY 1,2 ORDER BY 3 DESC;

What and when permission changes have been granted to external users.

SELECT drive.target_user, event_name, count(*)
FROM [mydomain:Reports.activity_201703]
WHERE NOT IS_NULL(drive.target_user)
AND drive.target_user NOT LIKE "%mydomain.com%"
GROUP BY 1,2 ORDER BY 3 DESC;

Gmail

Ratio of daily active users to 30-day active users in Gmail.

SELECT date,
gmail.num_1day_active_users/gmail.num_7day_active_users
FROM [mydomain:Reports.usage_201703] 
WHERE gmail.num_1day_active_users > 0
AND gmail.num_7day_active_users > 0

Groups

Google Group membership changes and user behavior.

SELECT
  TIMESTAMP_MICROS(time_usec),
  event_name,
  `groups`.group_email,
  email,
  `groups`.status
FROM
  `mydomain:Reports.activity_2017*`
WHERE
  record_type = 'groups'
ORDER BY 1 DESC
LIMIT
  1000

Hangouts

Number of video calls by date.

SELECT date, gplus.total_video_call_minutes,gplus.num_video_calls
FROM [mydomain:Reports.usage_201703]
WHERE NOT IS_NULL(gplus.num_video_calls)
ORDER BY date ASC

Rules

Triggered DLP rules by name, matched application, and actions.

SELECT rules.rule_name, rules.application, rules.actions, rules.resource_owner_email
FROM [mydomain:Reports.activity_201703] 
WHERE event_type = "rule_match_type"
AND rules.rule_name != ""
LIMIT 1000

Tokens

Number of times a third-party app has been enabled to access Google Drive.

SELECT token.client_id, token.scope, token.app_name, count(*)
FROM [mydomain:Reports.activity_201703]
WHERE token.scope LIKE "%drive%"
GROUP BY 1,2,3 ORDER BY 4 DESC;

Admin console sign-in attempts

Detailed information on failed sign-ins to the Google Admin console.

SELECT time_usec, email, ip_address, event_name, login.login_type, login.login_failure_type
FROM  [mydomain:Reports.activity_201703] 
WHERE login.login_type IS NOT NULL
AND login.login_failure_type IS NOT NULL
AND event_type = "login";

Schema for usage tables

The schema can change. You can see an updated and complete list of parameters and fields in the Reports API documentation

Was this helpful?
How can we improve it?