What's Google Drive inventory reporting & how can I use it?

What is Google Drive Inventory Reporting?

Google Drive Inventory Reporting is a feature that allows you to export a detailed list of all Google Drive files within your domain into Big Query, regardless of when they were created. This means you have access to a complete overview of all files created since the inception of your domain, including detailed information (metadata) about each file, such as its title, owner, size, and sharing settings, among others.

Now, you can access and analyze this Big Query table quickly and efficiently.

Use cases for Google Drive Inventory Reporting

Now that Google gives you this exhaustive list, you can perform an SQL request to filter it as you wish.

Here are some examples:

List all files shared publicly

SELECT 
  id, 
  title, 
  owner.user.email

FROM 
  `your_table_name`

UNNEST
  (access.permissions) AS permission

WHERE 
  permission.permission_id = 'anyoneWithLink'

List all files larger than 5GB

SELECT 
  id, 
  title, 
  owner.user.email, 
  size_bytes 

FROM 
  `your_table_name`

WHERE 
  size_bytes > 5 * 1024 * 1024 * 1024

List all files not modified for the last 5 years

SELECT 
  id, 
  title, 
  owner.user.email, 
  last_modified_time_micros 

FROM 
  `your_table_name`

WHERE 
  last_modified_time_micros < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5*365 DAY)

List all files larger than 5GB and not modified for the last 5 years

SELECT 
  id, 
  title, 
  owner.user.email, 
  size_bytes, 
  last_modified_time_micros 

FROM 
  `your_table_name`

WHERE 
  size_bytes > 5 * 1024 * 1024 * 1024
  AND last_modified_time_micros < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5*365 DAY)

List all files belonging to a designated user and sort them by file size from largest to smallest

SELECT 
  id, 
  title,
  mime_type,
  owner.user.email, 
  ROUND(size_bytes / (1024 * 1024 * 1024), 2) AS size_gb  
FROM 
  `your_table_name`
WHERE 
  owner.user.email = "designated_user@example.com"
ORDER BY 
  size_bytes DESC

List all files created before January 1, 2014

SELECT 
  id, 
  title, 
  owner.user.email, 
  create_time_micros 

FROM 
  `your_table_name`

WHERE 
  create_time_micros < TIMESTAMP('2014-01-01')

In your BigQuery export, you'll notice a field called "read_time_micros". This field corresponds to the time the file's metadata was last read and added to the table, not the last time the file was opened by one of your domain's users. At the moment, Google does not provide the availability to access this information.

Folgo x Inventory Reporting

Now that Google provides you with an exhaustive table that you can filter as you wish, as shown in the examples above, you can export your tailored list into a spreadsheet and perform bulk actions, such as deleting files or removing unwanted permissions.

To do so, simply click on "Save Results" and select "Google Sheets".

How to Enable and Use Drive Inventory Reporting?

Eligible Google Workspace administrators can enable Drive Inventory Reporting through the Admin console. Here's how:

Go to the Admin console in your Google Workspace account.
Head to Reporting > Data Integrations.
Toggle on Drive Inventory Export to enable the feature.

Once enabled, the inventory data will begin populating and can be exported to BigQuery for further analysis.

To find out more, you can check Google's documentation.

Conclusion

Google Drive Inventory Reporting provides comprehensive Drive data to administrators. With Folgo, you can export custom lists for bulk actions such as file deletion or permission changes. This simplifies the management of large volumes of data and improves efficiency in Google Workspace.