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

Edições que têm 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 na nuvem do Google Cloud porque há um custo para exportar e consultar seu inventário do Drive no BigQuery. Ative o faturamento de um projeto.

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

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

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

Esquema e definições
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 de 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 dos Editores de arquivos Google. 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.
Checksum md5_checksum A soma de verificação MD5 do conteúdo do item.
Criptografia do 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á na 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.
É um esquema de modelo is_template Um booleano que indica se o item é um modelo.

* 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 de 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 fornecido 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.

Esquema de compartilhamento externo

Esquema e definições de compartilhamento externo

Os dados pessoais compartilhados externamente são adicionados ao conjunto de dados do BigQuery (o mesmo conjunto de dados do esquema de exportação de inventário do Drive) com o seguinte esquema:

Nome Campo do esquema Descrição
Tem compartilhamento externo access.has_external_sharing Mostra se o arquivo foi compartilhado com usuários externos ou contas de serviço, incluindo aqueles com acesso indireto por meio de participação em grupos.
  • Esse campo será definido como True se o arquivo tiver compartilhamentos externos, incluindo instâncias em que:
    • Todos os compartilhamentos são contas de serviço.
    • O acesso é permitido pelos controles de política.
    • O arquivo é publicado publicamente.
    • O arquivo é compartilhado com todos.
  • O campo será definido como False se o arquivo não for compartilhado externamente ou se os controles de política bloquearem o acesso de todos os usuários externos.
Tem compartilhamento externo (exceto contas de serviço) access.has_external_sharing_excluding_service_accounts Indica se o arquivo tem pelo menos um compartilhamento externo fora da organização. Isso será definido como False se todos os usuários externos forem contas de serviço.
É publicado externamente access.is_published_externally Especifica se o arquivo foi publicado externamente.
É compartilhado com todos access.is_shared_with_everyone Mostra se o arquivo é compartilhado com todos.
Pessoas externas com quem o item foi compartilhado

access.external_sharees.type

Mostra o tipo de usuário externo. A lista a seguir mostra exemplos de cada tipo de permissão:

  • USUÁRIO
  • GROUP
  • UNSPECIFIED: a maioria das contas nessa categoria não foi reivindicada

access.external_sharees.email

Mostra o e-mail do usuário externo.

access.external_sharees.is_service_account

Indica se o usuário externo é uma conta de serviço.

access.external_sharees.access_blocked_by_policy_control

Mostra se o acesso está bloqueado por uma política (por exemplo, DLP ou regras de confiabilidade). Se for True, o usuário compartilhado será excluído dos cálculos de has_external_sharing e has_external_sharing_excluding_service_accounts.

Informações da DLP access.dlp_info.matching_dlp_rules Lista as regras de DLP correspondentes aplicadas a este item.

Considerações sobre desempenho: o cálculo dos campos de compartilhamento externo pode levar mais tempo se você tiver grupos com vários usuários aninhados. Esse atraso pode afetar a atualização dos dados (por exemplo, em relatórios diários).

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 marcador 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;