Table: microsoft365_my_contact - Query Microsoft 365 Contacts using SQL

Microsoft 365 Contacts is a feature within the Microsoft 365 suite that allows users to store and manage personal contact information. It serves as a centralized location for users to save and access contact details such as names, email addresses, phone numbers, and more. Microsoft 365 Contacts aids in organizing and accessing contact information seamlessly across Microsoft 365 applications.

Table Usage Guide

The microsoft365_my_contact table provides insights into Contacts within Microsoft 365. As an IT administrator or a security analyst, explore contact-specific details through this table, including names, email addresses, phone numbers, and associated metadata. Utilize it to uncover information about contacts, such as the number of contacts, their details, and the verification of contact information.

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

Explore the basic contact information for individuals within your Microsoft 365 network. This can be particularly useful for quickly accessing contact details or for consolidating and organizing your contacts.

select
  display_name,
  mobile_phone,
  email_addresses
from
  microsoft365_my_contact;

select
  display_name,
  mobile_phone,
  email_addresses
from
  microsoft365_my_contact;

Get a contact by email

Discover the details of a specific contact by using their email address. This is useful for quickly accessing important information such as display name and mobile phone number.

select
  display_name,
  mobile_phone,
  email ->> 'address' as email_address
from
  microsoft365_my_contact,
  jsonb_array_elements(email_addresses) as email
where
  email ->> 'address' = 'user@domain.com';

select
  display_name,
  mobile_phone,
  json_extract(email.value, '$.address') as email_address
from
  microsoft365_my_contact,
  json_each(email_addresses) as email
where
  json_extract(email.value, '$.address') = 'user@domain.com';

List contacts belonging to the same organization

Discover the segments that share a common organization in your contacts. This is useful for identifying all the contacts related to a specific company, allowing for more efficient communication and organization.

select
  display_name,
  mobile_phone,
  email_addresses
from
  microsoft365_my_contact
where
  company_name = 'Turbot';

select
  display_name,
  mobile_phone,
  email_addresses
from
  microsoft365_my_contact
where
  company_name = 'Turbot';

Schema for microsoft365_my_contact

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
assistant_name	text		The name of the contact's assistant.
birthday	timestamp with time zone		The contact's birthday. The Timestamp type represents date and time information using ISO 8601 format and is always in UTC time.
business_address	text		The contact's business address.
business_home_page	text		The business home page of the contact.
business_phones	text		The contact's business phone numbers.
children	text		The names of the contact's children.
company_name	text		The name of the contact's company.
department	text		The contact's department.
display_name	text		The contact's display name.
email_addresses	jsonb		The contact's email addresses.
file_as	text		The name the contact is filed under.
generation	text		The contact's generation.
given_name	text		The contact's given name.
home_address	text		The contact's home address.
home_phones	text		The contact's home phone numbers.
id	text	=	The contact's unique identifier.
im_addresses	jsonb		The contact's instant messaging (IM) addresses.
initials	text		The contact's initials.
job_title	text		The contact’s job title.
manager	text		The name of the contact's manager.
middle_name	text		The contact's middle name.
mobile_phone	text		The contact's mobile phone number.
nick_name	text		The contact's nickname.
office_location	text		The location of the contact's office.
other_address	text		Other addresses for the contact.
parent_folder_id	text		The ID of the contact's parent folder.
personal_notes	text		The user's notes about the contact.
profession	text		The contact's profession.
spouse_name	text		The name of the contact's spouse/partner.
surname	text		The contact's surname.
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.
yomi_company_name	text		The phonetic Japanese company name of the contact.
yomi_given_name	text		The phonetic Japanese given name (first name) of the contact.
yomi_surname	text		The phonetic Japanese surname (last name) of the contact.

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_contact