Schema and example queries for Drive inventory exports in BigQuery

Supported editions for this feature: Frontline Plus; Enterprise Standard and Enterprise Plus; Education Standard and Education Plus; Enterprise Essentials Plus; Cloud Identity Premium. Compare your edition

After you set up Drive inventory exports, you can analyze the dataset in BigQuery. This page describes the table schema and example queries for common reports you can get from BigQuery.

Note: You must set up billing for your Google Cloud project because there is a cost to export and query your Drive inventory in BigQuery. Enable billing for a project.

Drive inventory export schema

Schema and definitions

The Drive inventory export is added to the BigQuery dataset with the following schema:

Name	Schema field	Description
Item ID	`id`	The unique Drive item identifier of the item.
Version	`version`	The latest version number of the file. The file version is a monotonically increasing number. This reflects every change made to the file on the server, even those not visible to the user.
Owner ID	`owner.user.id`	For items owned by a user, the user ID of the file owner.
Owner email	`owner.user.email`	For items owned by a user, the email of the file owner.
Shared drive ID	`owner.shared_drive.id`	For items in a shared drive, the shared drive ID.
Creator	`creator`	The email and user ID of the item's creator. This field isn't populated if the creator no longer has access to the item.
Snapshot timestamps	`snapshot_start_time_micros` and `snapshot_end_time_micros`	The timestamps of when the report generation started and ended.
Read Time	`read_time_micros`	The timestamp of when the Drive item metadata was read.
Title	`title`	The user-specified file title.
Description	`description`	A short description of the item.
Trashed Status	`trashed`	Whether or not the item was moved to the trash but not yet deleted.
Mime Type	`mime_type`	The MIME type of the item. See Google Workspace and Google Drive supported MIME types.
File Extension	`file_extension`	The final component of the full file extension, which is extracted from the name field. This is only available for items with binary content in Google Drive
Creation Time	`create_time_micros`	The timestamp of when the item was created.
Last Modified Time	`last_modified_time_micros`	The timestamp of the last time the item was modified by anyone.
File Size	`file_size_bytes`	The size in bytes of blobs and Docs Editors files. Not reported for items that have no size, like shortcuts and folders.
Consumed Quota	`consumed_quota_bytes`	The number of storage quota bytes used by the item. This includes the head revision as well as previous revisions that have been kept.
Shared Drive ID	`shared_drive_id`	For items in a shared drive, the shared drive ID.
Organizational Unit	`org_unit_path`	The organizational unit of the item's owner.
Checksum	`md5_checksum`	The MD5 checksum for the content of the item.
Client-Side Encryption	`client_side_encrypted`	A boolean indicating if the item is client-side encrypted .
Parent folder	`parent`	The ID of the parent folder for the item.
Applied Labels*	`applied_labels.id`	The ID of the label applied to the item.
	`applied_labels.title`	The resource name of the label applied to the item.
	`applied_labels.field_values.id`	The ID of a field, unique within a label or library.
	`applied_labels.field_values.type`	The type of field. Can be one of the following: `Text` `Integer` `Date` `Selection` `User`
	`applied_labels.field_values.display_name`	The text to show in the UI that identifies the field.
Permissions	`access.permissions.permission_id`	The ID of this permission. This is a unique identifier for the grantee. Note: The permission ID is not the same as the user ID.
	`access.permissions.name`	The "pretty" name of the value of the permission. The following is a list of examples for each type of permission: `user` - User's full name, as defined for their Google account, such as "Dana Cruz" `group` - Name of the group, such as "Example Admins" `domain` - String domain name, such as "example.com" `anyone` - No name reported
	`access.permissions.type`	The type of the permission. Valid values are: `user` `group` `domain` `anyone`
	`access.permissions.role`	The role granted by this permission. Valid values are: `owner` `organizer` `fileOrganizer` `writer` `commenter` `reader` Note: See roles and permission for details on the type.
	`access.permissions.view`	If the permission belongs to a view, the value is `published`.
	`access.permissions.allow_file_discovery`	Whether the permission allows the file to be searchable. Only reported for permissions of type `domain` or `anyone`.
	`access.permissions.email`	The email address of the user or group to which this permission refers.
	`access.permissions.domain`	The domain name of the entity this permission refers to. This is present when the permission type is `user`, `group` or `domain`.
	`access.permissions.user_id`	The user ID associated with email on the permissions.
	`access.permissions.permission_details .permission_type`	The permission type for the user.
	`access.permissions.permission_details.role`	The primary role for the user.
	`access.permissions.permission_details .inherited_from`	The ID of the item from which this permission is inherited. This is only populated for items in shared drives.
	`access.permissions.permission_details .inherited`	A boolean indicating whether the permission is inherited.

* Refers to labels applied to items in Drive.

Query examples

These query examples assume legacy SQL. Replace project_name.dataset_name with your own project name and dataset name. Learn more about querying BigQuery data.

Expand all | Collapse all

Find items that are findable with Drive search

Replace project_name.dataset_name with your project name and dataset name.

Returns a table with File ID, title, owner email, and type.

SELECT id, title, mime_type, owner.user.email
FROM project_name.dataset_name
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(access.permissions) AS permission
  WHERE permission.allow_file_discovery = true
)

Find items with a specific label

Replace project_name.dataset_name with your project name and dataset name. Replace label_id with your label ID, which you can get in the label manager.

This query returns a table with File ID, title, owner email, and type.

SELECT id, title, mime_type, owner.user.email
FROM project_name.dataset_name
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(applied_labels) AS label
  WHERE label.id = label_id
)

Find users who are above a Drive storage use threshold

Replace project_name.dataset_name with your project name and dataset name. Replace threshold with your threshold in MB. For example, to find users with over 15 GB of Drive storage use, the second-to-last line is HAVING total_storage_mb > 15000.

This query returns a table user email and total Drive storage use in MB in descending order.

SELECT owner.user.email AS user_email,
  SUM(size_bytes) / (1000000) AS total_storage_mb
FROM project_name.dataset_name
GROUP BY user_email
HAVING total_storage_mb > threshold
ORDER BY total_storage_mb DESC

Find which item types use the most space

Replace project_name.dataset_name with your project name and dataset name.

This query returns a table of item types by descending size.

SELECT mime_type, SUM(consumed_quota_bytes) AS total_consumed
FROM project_name.dataset_name
GROUP BY mime_type
ORDER BY total_consumed DESC

Find items encrypted with client-side encryption (CSE)

Replace project_name.dataset_name with your project name and dataset name.

SELECT id, owner.user.email, title
FROM project_name.dataset_name
WHERE mime_type LIKE '%.encrypted'

Replace project_name.dataset_name with your project name and dataset name. Replace user@example.com with your specific user.

Note: This query doesn't find sharing for the user through groups or target audience membership.

SELECT id, title, mime_type, owner.user.email as owner_email
FROM project_name.dataset_name
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(access.permissions) AS permissions
  WHERE permissions.email = user@example.com
)

Was this helpful?

How can we improve it?

Schema and example queries for Drive inventory exports in BigQuery

Drive inventory export schema

Schema and definitions

Query examples

Related topics

Was this helpful?

Need more help?

Try these next steps:

What is the issue with this selection?

Share additional info or suggestions