turbot/microsoft365
steampipe plugin install microsoft365

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
created_byjsonbIdentity of the user, device, and application which created the item.
created_date_timetimestamp with time zoneDate and time of item creation.
ctagtextAn 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.
descriptiontextProvides a user-visible description of the item.
drive_idtext=The unique id of the drive.
etagtextETag for the entire item (metadata + content).
filejsonbFile metadata, if the item is a file.
folderjsonbFolder metadata, if the item is a folder.
idtext=The unique identifier of the item within the Drive.
last_modified_byjsonbIdentity of the user, device, and application which last modified the item.
last_modified_date_timetimestamp with time zoneDate and time the item was last modified.
nametextThe name of the item (filename and extension).
parent_ReferencejsonbParent information, if the item has a parent.
pathtextURL that displays the resource in the browser.
sizebigintSize of the item in bytes.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Tenant ID where the resource is located.
titletextTitle of the resource.
user_idtext=ID or email of the user.
web_dav_urltextWebDAV compatible URL for the item.
web_urltextURL 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