Table: googledirectory_group_member - Query Google Directory Group Members using SQL

Google Directory is a service within Google Workspace that provides a centralized way to manage and organize users, groups, and devices in an organization. It allows administrators to manage access to services and delegate administrative tasks. Google Directory Group Member represents a member of a group within the Google Directory.

Table Usage Guide

The googledirectory_group_member table provides insights into each member of a group within Google Directory. As an IT administrator, explore member-specific details through this table, including roles, type, and associated metadata. Utilize it to uncover information about group members, such as their roles within the group, the type of member (user, group, or service account), and other relevant details.

Important Notes

• You must specify the group_id in the where clause to query this table.

Examples

Basic info

Explore which roles are assigned to different members of a specific Google Directory group. This is useful for managing access permissions and ensuring the right individuals have the appropriate roles.

select
  group_id,
  id,
  email,
  role
from
  googledirectory_group_member
where
  group_id = '01ksv4uv1gexk1h';

select
  group_id,
  id,
  email,
  role
from
  googledirectory_group_member
where
  group_id = '01ksv4uv1gexk1h';

List all owners of a group

Discover the segments that have a specific ownership within a group. This can be useful for managing group permissions and understanding the distribution of roles within a group.

select
  group_id,
  id,
  email,
  role
from
  googledirectory_group_member
where
  group_id = '01ksv4uv1gexk1h'
  and role = 'OWNER';

select
  group_id,
  id,
  email,
  role
from
  googledirectory_group_member
where
  group_id = '01ksv4uv1gexk1h'
  and role = 'OWNER';

List role counts for a group

Explore which roles within a specific group have the highest membership count. This can help in understanding the distribution of roles within the group, allowing for better management and organization.

select
  role,
  count(*)
from
  googledirectory_group_member
where
  group_id = '01ksv4uv1gexk1h'
group by
  role
order by
  count desc;

select
  role,
  count(*)
from
  googledirectory_group_member
where
  group_id = '01ksv4uv1gexk1h'
group by
  role
order by
  count(*) desc;

List all groups and their members

Explore the relationships between various groups and their respective members to understand the structure and dynamics within your organization. This can be particularly useful for managing access permissions, coordinating team activities, or identifying communication patterns.

select
  g.id as group_id,
  g.name as group_name,
  m.email as member_email
from
  googledirectory_group as g,
  googledirectory_group_member as m
where
  g.id = m.group_id
order by
  g.name,
  m.email;

select
  g.id as group_id,
  g.name as group_name,
  m.email as member_email
from
  googledirectory_group as g
  join googledirectory_group_member as m on g.id = m.group_id
order by
  g.name,
  m.email;

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

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

steampipe_export_googledirectory --config '<your_config>' googledirectory_group_member