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

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_my_drive_file