Table: googleworkspace_people_directory_people - Query Google Workspace Directory People using SQL

Google Workspace Directory People is a resource within Google Workspace that allows you to manage and access user profiles in your organization's directory. It provides a centralized way to view and manage information about people in your organization, including their email addresses, phone numbers, and other profile details. Google Workspace Directory People helps you stay informed about the users in your organization and take appropriate actions when necessary.

Table Usage Guide

The googleworkspace_people_directory_people table provides insights into user profiles within Google Workspace Directory People. As an IT administrator, explore user-specific details through this table, including email addresses, phone numbers, and other profile details. Utilize it to uncover information about users, such as their roles, the groups they belong to, and their profile's metadata.

Examples

Basic info

This query is useful for obtaining key information about individuals in a Google Workspace directory, such as their display name, primary email address, job title, and primary contact number. It's a practical tool for HR teams or managers who need to quickly access or compile this information for communication or organizational purposes.

select
  display_name,
  primary_email_address,
  case
    when org -> 'metadata' ->> 'primary' = 'true' then org ->> 'title'
  end as job_title,
  case
    when ph -> 'metadata' ->> 'primary' = 'true' then ph ->> 'value'
  end as primary_contact
from
  googleworkspace_people_directory_people
  left join jsonb_array_elements(organizations) as org on true
  left join jsonb_array_elements(phone_numbers) as ph on true;

select
  display_name,
  primary_email_address,
  case
    when json_extract(org.value, '$.metadata.primary') = 'true' then json_extract(org.value, '$.title')
  end as job_title,
  case
    when json_extract(ph.value, '$.metadata.primary') = 'true' then json_extract(ph.value, '$.value')
  end as primary_contact
from
  googleworkspace_people_directory_people,
  json_each(organizations) as org,
  json_each(phone_numbers) as ph;

Schema for googleworkspace_people_directory_people

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
addresses	jsonb	The person's street addresses.
biography	jsonb	The person's biography.
birthday	jsonb	The date of the birthday.
calendar_urls	jsonb	The person's calendar URLs.
client_data	jsonb	The person's client data.
cover_photos	jsonb	The person's cover photos.
display_name	text	The display name formatted according to the locale specified by the viewer's account.
email_addresses	jsonb	The person's email addresses.
events	jsonb	The person's events.
external_ids	jsonb	The person's external IDs.
gender	text	The gender for the person.
given_name	text	The given name of the user contact.
interests	jsonb	The person's interests.
locations	jsonb	The person's locations.
memberships	jsonb	The person's group memberships.
metadata	jsonb	Metadata about the person.
nicknames	jsonb	The person's nicknames.
occupations	jsonb	The person's occupations.
organizations	jsonb	The person's past or current organizations.
phone_numbers	jsonb	The person's phone numbers.
photos	jsonb	The person's photos.
primary_email_address	text	The primary email address of the user contact.
resource_name	text	The resource name for the contact group, assigned by the server.

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)" -- googleworkspace

You can pass the configuration to the command with the --config argument:

steampipe_export_googleworkspace --config '<your_config>' googleworkspace_people_directory_people