turbot/microsoft365
steampipe plugin install microsoft365

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
assistant_nametextThe name of the contact's assistant.
birthdaytimestamp with time zoneThe contact's birthday. The Timestamp type represents date and time information using ISO 8601 format and is always in UTC time.
business_addresstextThe contact's business address.
business_home_pagetextThe business home page of the contact.
business_phonestextThe contact's business phone numbers.
childrentextThe names of the contact's children.
company_nametextThe name of the contact's company.
departmenttextThe contact's department.
display_nametextThe contact's display name.
email_addressesjsonbThe contact's email addresses.
file_astextThe name the contact is filed under.
generationtextThe contact's generation.
given_nametextThe contact's given name.
home_addresstextThe contact's home address.
home_phonestextThe contact's home phone numbers.
idtext=The contact's unique identifier.
im_addressesjsonbThe contact's instant messaging (IM) addresses.
initialstextThe contact's initials.
job_titletextThe contact’s job title.
managertextThe name of the contact's manager.
middle_nametextThe contact's middle name.
mobile_phonetextThe contact's mobile phone number.
nick_nametextThe contact's nickname.
office_locationtextThe location of the contact's office.
other_addresstextOther addresses for the contact.
parent_folder_idtextThe ID of the contact's parent folder.
personal_notestextThe user's notes about the contact.
professiontextThe contact's profession.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
spouse_nametextThe name of the contact's spouse/partner.
surnametextThe contact's surname.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Tenant ID where the resource is located.
titletextTitle of the resource.
user_idtext=ID or email of the user.
yomi_company_nametextThe phonetic Japanese company name of the contact.
yomi_given_nametextThe phonetic Japanese given name (first name) of the contact.
yomi_surnametextThe 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