turbot/microsoft365
steampipe plugin install microsoft365

Table: microsoft365_my_drive_file - Query Microsoft 365 My Drive Files using SQL

My Drive Files in Microsoft 365 is a feature that allows users to store, sync, and share their files across devices. It provides a secure platform to access files from anywhere and collaborate with others. My Drive Files also offers features like file versioning, file restore, and the ability to access files offline.

Table Usage Guide

The microsoft365_my_drive_file table provides insights into My Drive Files within Microsoft 365. As a data analyst or IT administrator, explore file-specific details through this table, including file names, sizes, creation and modification dates, and more. Utilize it to uncover information about file usage, such as which files are taking up the most space, the frequency of file modifications, and the distribution of file types.

Important Notes

  • If not authenticating with the Azure CLI, this table requires the user_id argument to be configured in the connection config.

Examples

Basic info

Gain insights into the files present in your Microsoft 365 drive by identifying their names, IDs, paths, and creation dates. This can help manage and organize your files more effectively.

select
name,
id,
path,
created_date_time
from
microsoft365_my_drive_file;
select
name,
id,
path,
created_date_time
from
microsoft365_my_drive_file;

List all empty folders

Explore which folders in your Microsoft 365 drive are empty. This can help manage storage and identify unused folders for potential clean-up.

select
name,
id,
path,
created_date_time
from
microsoft365_my_drive_file
where
folder ->> 'childCount' = '0';
select
name,
id,
path,
created_date_time
from
microsoft365_my_drive_file
where
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, ensuring you're always working with the most current information.

select
name,
id,
path,
created_date_time
from
microsoft365_my_drive_file
where
created_date_time > '2021-08-15T00:00:00+05:30';
select
name,
id,
path,
created_date_time
from
microsoft365_my_drive_file
where
datetime(created_date_time) > datetime('2021-08-15T00:00:00+05:30');

Schema for microsoft365_my_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_idtextID 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_my_drive_file