Esquema e consultas de exemplo para exportações de inventário do Drive no BigQuery

Edições compatíveis com esse recurso: Frontline Plus, Enterprise Standard e Enterprise Plus, Education Standard e Education Plus, Enterprise Essentials Plus, Cloud Identity Premium. Comparar sua edição

Depois de configurar as exportações do inventário do Drive, você pode analisar o conjunto de dados no BigQuery. Esta página descreve o esquema da tabela e exemplos de consultas para relatórios comuns que você pode acessar no BigQuery.

Observação:é necessário configurar o faturamento do seu projeto do Google Cloud porque há um custo para exportar e consultar seu inventário do Drive no BigQuery. Ativar o faturamento para um projeto .

Esquema de exportação de inventário do Drive

Esquema e definições

A exportação de inventário do Drive é adicionada ao conjunto de dados do BigQuery com o seguinte esquema:

Nome Campo do esquema Descrição
Código do item id O identificador exclusivo do item no Drive.
Versão version O número da versão mais recente do arquivo. A versão do arquivo é um número monotonicamente crescente. Isso reflete todas as alterações feitas no arquivo no servidor, mesmo as que não estão visíveis para o usuário.

Proprietário

owner.user.id

Nos itens que pertencem a um usuário, o ID do usuário do proprietário do arquivo.

owner.user.email

Nos itens que pertencem a um usuário, o e-mail do proprietário do arquivo.

owner.shared_drive.id

Para itens em um drive compartilhado, o ID do drive compartilhado.
Criador creator O e-mail e o ID do usuário do criador do item. Esse campo não será preenchido se o criador não tiver mais acesso ao item.
Carimbos de data/hora dos snapshots snapshot_start_time_micros e snapshot_end_time_micros Os carimbos de data/hora de início e término da geração do relatório.
Tempo de leitura read_time_micros O carimbo de data/hora de quando os metadados do item do Drive foram lidos.
Título title

O título do arquivo especificado pelo usuário.
Descrição description Uma curta descrição do item.
Status na lixeira trashed Se o item foi ou não movido para a lixeira, mas ainda não foi excluído.
Tipo MIME mime_type O tipo MIME do item. Consulte os tipos MIME compatíveis com o Google Workspace e o Google Drive.
Extensão do arquivo file_extension O componente final da extensão de arquivo completa, que é extraído do campo de nome. Disponível apenas para itens com conteúdo binário no Google Drive
Horário da criação create_time_micros O carimbo de data/hora de quando o item foi criado.
Horário da última modificação last_modified_time_micros O carimbo de data/hora da última vez em que o item foi modificado por alguém.
Bytes de tamanho size_bytes O tamanho em bytes de blobs e arquivos do Editor de documentos. Não informado para itens sem tamanho, como atalhos e pastas.
Cota consumida consumed_quota_bytes O número de bytes da cota de armazenamento usados pelo item. Isso inclui a revisão do cabeçalho, bem como as revisões anteriores que foram mantidas.
Unidade organizacional org_unit_path A unidade organizacional do proprietário do item.
Soma de verificação md5_checksum A soma de verificação MD5 do conteúdo do item.
Criptografia no lado do cliente client_side_encrypted Um booleano que indica se o item é criptografado do lado do cliente .
Pasta mãe parent O ID da pasta pai do item.

A pasta mãe não será preenchida nos seguintes casos:

  1. O arquivo está em uma pasta de nível superior do Meu Drive de um usuário.
  2. A pasta mãe não pertence ao domínio.
Rótulos aplicados* applied_labels.id ID do rótulo aplicado ao item.
applied_labels.title O nome do recurso do rótulo aplicado ao item.
applied_labels.field_values.id

É o ID de um campo, exclusivo em um rótulo ou biblioteca.

applied_labels.field_values.type

O tipo de campo. Será um dos seguintes valores:

applied_labels.field_values.display_name

 O texto a ser mostrado na interface que identifica o campo.
ID do destino do atalho shortcut_target_id O ID do arquivo ou da pasta para onde este atalho aponta.
Permissões access.permissions.permission_id

O ID dessa permissão. Este é um identificador exclusivo do beneficiário.

Observação: o ID da permissão não é o mesmo que o ID do usuário.
access.permissions.name

O nome "bonito" do valor da permissão. Esta é uma lista de exemplos para cada tipo de permissão:

  • user: nome completo do usuário, conforme definido para a Conta do Google, como "Dana Cruz"
  • group: nome do grupo, como "Administradores de exemplo"
  • domain: nome de domínio de string, como "exemplo.com"
  • anyone: nenhum nome relatado
access.permissions.type

O tipo de permissão. Os valores válidos são:

  • user
  • group
  • domain
  • anyone
access.permissions.role

O papel concedido pela permissão. Os valores válidos são:

  • owner
  • organizer
  • fileOrganizer
  • writer
  • commenter
  • reader

Observação: consulte papéis e permissões para detalhes sobre o tipo.
access.permissions.view

Se a permissão pertencer a uma visualização, o valor será published.

access.permissions.allow_file_discovery

 Define se a permissão permite que o arquivo seja pesquisável. Informado apenas para permissões do tipo domain ou anyone.
access.permissions.email O endereço de e-mail do usuário ou grupo a que esta permissão se refere.
access.permissions.domain O nome de domínio da entidade a que essa permissão se refere. Isso está presente quando o tipo de permissão é user, group ou domain.
access.permissions.user_id O ID do usuário associado ao e-mail nas permissões.
access.permissions.permission_details
.permission_type		 O tipo de permissão do usuário.
access.permissions.permission_details.role A função principal do usuário.
access.permissions.permission_details
.inherited_from		 O ID do item de onde essa permissão é herdada. Esse campo só é preenchido para itens em drives compartilhados.
access.permissions.permission_details
.inherited		 Um booleano que indica se a permissão é herdada.
access.permissions.inherited_permissions
_disabled		 Indica se as permissões herdadas estão desativadas para este item. Só é verdadeiro para pastas com acesso limitado.

* Refere-se aos rótulos aplicados aos itens no Drive.

Esquema de drives compartilhados

Tabela de drives compartilhados

A tabela detalha o esquema dos campos específicos do drive compartilhado na exportação do inventário do Drive.

Nome Campo do esquema Descrição
Código do drive compartilhado shared_drive_id O identificador exclusivo do drive compartilhado.
Nome do drive compartilhado name O nome do drive compartilhado, conforme informado pelo usuário.
Tempo de leitura read_time_micros O carimbo de data/hora de quando os metadados do drive compartilhado foram lidos, em microssegundos.

Exemplos de consultas

Estes exemplos de consultas pressupõe o uso de SQL legado. Saiba mais sobre como consultar dados do BigQuery.

Substitua project_name.dataset_name pelo nome do projeto e do conjunto de dados.

Essa consulta retorna uma tabela com o ID do arquivo, o título, o e-mail do proprietário e o tipo.

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
)

Encontrar itens com um rótulo específico

Substitua project_name.dataset_name pelo nome do projeto e do conjunto de dados. Substitua label_id pelo ID do rótulo, que pode ser encontrado no gerenciador de rótulos.

Essa consulta retorna uma tabela com o ID do arquivo, o título, o e-mail do proprietário e o tipo.

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
)

Encontrar usuários que estão acima de um limite de uso de armazenamento no Drive

Substitua project_name.dataset_name pelo nome do projeto e do conjunto de dados. Substitua limite pelo seu limite em MB. Por exemplo, para encontrar usuários com mais de 15 GB de armazenamento no Drive, a segunda linha é HAVING total_storage_mb > 15000.

Essa consulta retorna o e-mail do usuário e o uso total de armazenamento no Drive em MB em ordem decrescente.

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

Saber quais tipos de itens usam mais espaço

Substitua project_name.dataset_name pelo nome do projeto e do conjunto de dados.

Essa consulta retorna uma tabela de tipos de itens por tamanho decrescente.

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

Encontrar itens criptografados com a criptografia do lado do cliente (CSE)

Substitua project_name.dataset_name pelo nome do projeto e do conjunto de dados.

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

Encontrar itens compartilhados com um usuário específico

Substitua project_name.dataset_name pelo nome do projeto e do conjunto de dados. Substitua user@example.com pelo seu usuário específico.

Observação: essa consulta não encontra compartilhamentos do usuário por grupos ou associação ao público-alvo.

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
)

Encontrar itens com nomes de drives compartilhados

Substitua project_name.dataset_name pelo nome do projeto e do conjunto de dados.

Essa consulta retorna uma tabela com o título do item do Drive e o nome do drive compartilhado.

SELECT i.title, s.name
FROM project_name.dataset_name.inventory AS i
JOIN project_name.dataset_name.shared_drives AS s
ON i.owner.shared_drive.id = s.shared_drive_id;

Encontrar contagens de itens em um drive compartilhado

Substitua project_name.dataset_name pelo nome do projeto e do conjunto de dados.

Essa consulta retorna uma tabela com o nome do drive compartilhado e a contagem de itens.

SELECT  s.name as shared_drive, count(*) as item_count
FROM project_name.dataset_name.inventory AS i
JOIN project_name.dataset_name.shared_drives AS s
ON i.owner.shared_drive.id = s.shared_drive_id
GROUP BY shared_drive;