Table: microsoft365_contact - Query Microsoft 365 Contacts using SQL

Microsoft 365 Contacts is a feature within Microsoft 365 that allows users to store and manage contact information, including names, email addresses, phone numbers, and more. This resource provides a centralized location for storing and accessing contact information, making it easier to manage communication within an organization. Microsoft 365 Contacts can be accessed and managed both through the Microsoft 365 web interface and programmatically via the Microsoft Graph API.

Table Usage Guide

The microsoft365_contact table provides insights into contact details within Microsoft 365. As a system administrator, explore contact-specific details through this table, including names, email addresses, phone numbers, and other related metadata. Utilize it to uncover information about contacts, such as their organizational position, department, and the last time their details were modified.

Important Notes

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

Examples

Basic info

Explore the basic contact information associated with a specific user in a Microsoft 365 organization. This query is useful for quickly accessing key details such as display name, mobile phone number, and email addresses.

select
  display_name,
  mobile_phone,
  email_addresses
from
  microsoft365_contact
where
  user_id = 'test@org.onmicrosoft.com';

select
  display_name,
  mobile_phone,
  email_addresses
from
  microsoft365_contact
where
  user_id = 'test@org.onmicrosoft.com';

Get a contact by email

Determine the mobile phone number and display name associated with a specific email address in your Microsoft365 contacts. This could be useful for quickly finding contact details when all you have is an email address.

select
  display_name,
  mobile_phone,
  email ->> 'address' as email_address
from
  microsoft365_contact,
  jsonb_array_elements(email_addresses) as email
where
  user_id = 'test@org.onmicrosoft.com'
  and email ->> 'address' = 'user@domain.com';

select
  display_name,
  mobile_phone,
  json_extract(email.value, '$.address') as email_address
from
  microsoft365_contact,
  json_each(email_addresses) as email
where
  user_id = 'test@org.onmicrosoft.com'
  and json_extract(email.value, '$.address') = 'user@domain.com';

List contacts belonging to the same organization

Explore which contacts belong to the same organization in your Microsoft 365 account. This is useful for consolidating contact information and understanding the relationships within your network.

select
  display_name,
  mobile_phone,
  email_addresses
from
  microsoft365_contact
where
  user_id = 'test@org.onmicrosoft.com'
  and company_name = 'Turbot';

select
  display_name,
  mobile_phone,
  email_addresses
from
  microsoft365_contact
where
  user_id = 'test@org.onmicrosoft.com'
  and company_name = 'Turbot';

Schema for microsoft365_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_contact