Table: microsoft365_drive_file - Query Microsoft 365 Drive Files using SQL

A Drive File in Microsoft 365 is a digital document or other item stored in a user's OneDrive or SharePoint Online site. It can be a Word document, Excel spreadsheet, PowerPoint presentation, PDF, image, video, or other file type. Drive Files can be shared with others, collaborated on in real-time, and accessed from any device connected to the internet.

Table Usage Guide

The microsoft365_drive_file table provides insights into Drive Files within Microsoft 365. As an IT administrator, explore file-specific details through this table, including ownership, sharing permissions, and activity. Utilize it to uncover information about files, such as those shared externally, the access levels granted to different users, and the modification history of files.

Important Notes

You must specify the user_id in the where or join clause (where user_id=, join microsoft365_drive_file d on d.user_id=) to query this table.

Examples

Basic info

Explore which files are created by a specific user in Microsoft 365. This helps in auditing and managing user data effectively.

select
  name,
  id,
  path,
  created_date_time
from
  microsoft365_drive_file
where
  user_id = 'test@org.onmicrosoft.com';

select
  name,
  id,
  path,
  created_date_time
from
  microsoft365_drive_file
where
  user_id = 'test@org.onmicrosoft.com';

List all empty folders

Identify instances where certain folders within a Microsoft365 drive are empty, allowing for potential clean-up or reorganization.

select
  name,
  id,
  path,
  created_date_time
from
  microsoft365_drive_file
where
  user_id = 'test@org.onmicrosoft.com'
  and folder ->> 'childCount' = '0';

select
  name,
  id,
  path,
  created_date_time
from
  microsoft365_drive_file
where
  user_id = 'test@org.onmicrosoft.com'
  and json_extract(folder, '$.childCount') = '0';

List files modified after a specific date

Explore which files have been modified after a specific date to keep track of recent changes and updates within your Microsoft 365 Drive. This can be beneficial for version control, audit trails, or simply staying updated on team activities.

select
  name,
  id,
  path,
  created_date_time
from
  microsoft365_drive_file
where
  user_id = 'test@org.onmicrosoft.com'
  and created_date_time > '2021-08-15T00:00:00+05:30';

select
  name,
  id,
  path,
  created_date_time
from
  microsoft365_drive_file
where
  user_id = 'test@org.onmicrosoft.com'
  and datetime(created_date_time) > datetime('2021-08-15T00:00:00+05:30');

Schema for microsoft365_drive_file

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
created_by	jsonb		Identity of the user, device, and application which created the item.
created_date_time	timestamp with time zone		Date and time of item creation.
ctag	text		An eTag for the content of the item. This eTag is not changed if only the metadata is changed. This property is not returned if the item is a folder.
description	text		Provides a user-visible description of the item.
drive_id	text	=	The unique id of the drive.
etag	text		ETag for the entire item (metadata + content).
file	jsonb		File metadata, if the item is a file.
folder	jsonb		Folder metadata, if the item is a folder.
id	text	=	The unique identifier of the item within the Drive.
last_modified_by	jsonb		Identity of the user, device, and application which last modified the item.
last_modified_date_time	timestamp with time zone		Date and time the item was last modified.
name	text		The name of the item (filename and extension).
parent_Reference	jsonb		Parent information, if the item has a parent.
path	text		URL that displays the resource in the browser.
size	bigint		Size of the item in bytes.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
tenant_id	text	=, !=, ~~, ~~, !~~, !~~	The Azure Tenant ID where the resource is located.
title	text		Title of the resource.
user_id	text	=	ID or email of the user.
web_dav_url	text		WebDAV compatible URL for the item.
web_url	text		URL that displays the resource in the browser.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- microsoft365

You can pass the configuration to the command with the --config argument:

steampipe_export_microsoft365 --config '<your_config>' microsoft365_drive_file