SELECT
title,
description,
CONCAT('https://gemini.google.com/gem/', id) AS url,
owner.user.email AS owner_user_email,
COALESCE(
REGEXP_EXTRACT(org_unit_path, r'^/[^/]+/([^/]+)'),
REGEXP_EXTRACT(org_unit_path, r'^/([^/]+)')
) AS org_unit,
version,
owner.shared_drive.id AS owner_shared_drive_id,
creator.user.email AS creator_user_email,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', create_time_micros) AS create_time,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', last_modified_time_micros) AS last_modified_time,
permission.permission_id,
permission.name AS permission_name,
permission.type AS permission_type,
permission.role AS permission_role,
permission.view,
permission.allow_file_discovery,
permission.email AS permission_email,
permission.domain AS permission_domain,
permission.inherited_permissions_disabled,
detail.permission_type AS details_permission_type,
detail.role AS details_role,
detail.inherited_from AS details_inherited_from,
detail.inherited AS details_inherited,
detail.allow_file_discovery AS details_allow_file_discovery,
size_bytes,
parent,
id
FROM `drive-inventory-bq-export.DriveInventory.inventory`
LEFT JOIN UNNEST(access.permissions) AS permission
LEFT JOIN UNNEST(permission.permission_details) AS detail
WHERE
mime_type = 'application/vnd.google-gemini.gem' AND trashed = FALSE
ORDER BY
org_unit,
owner_user_email,
id
Name CPUs RAM Gbps Cost
e2-standard-4 4 16 0.2
e2-highmem-16 16 128 16 1
m4-megamem-56
n1-highmem-32 32 208 32 2.5
n1-highmem-64 64 416 32 5
n1-highmem-96 96 768 32 8
n2-highmem-64 64 512 32 5.1
n2-highmem-80 80 640 32 6.5
n2-highmem-96 96 768 32 7