Table: googledirectory_user - Query Google Directory Users using SQL

Google Directory is a service within Google Workspace that provides a centralized way to manage and access user account information. It allows administrators to manage users, groups, and devices, as well as to configure security settings for the domain. Google Directory helps to maintain the integrity of the domain's data by providing a structured way to manage user account information.

Table Usage Guide

The googledirectory_user table provides insights into user accounts within Google Workspace. As an IT administrator, explore user-specific details through this table, including email addresses, names, and administrative status. Utilize it to uncover information about users, such as their last login time, whether their account is suspended, and the organizational units to which they belong.

Examples

Basic info

Explore which users have administrative privileges in your Google Directory and when they were created. This can be useful for auditing purposes and ensuring that only authorized individuals have admin access.

select
  full_name,
  id,
  primary_email,
  creation_time,
  is_delegated_admin,
  customer_id
from
  googledirectory_user;

select
  full_name,
  id,
  primary_email,
  creation_time,
  is_delegated_admin,
  customer_id
from
  googledirectory_user;

Get user by ID

Discover the details of a specific user in the Google Directory, such as their full name, primary email, and creation time. This can be useful for administrators who need to verify user information or investigate account activity.

select
  full_name,
  id,
  primary_email,
  creation_time,
  is_delegated_admin,
  customer_id
from
  googledirectory_user
where
  id = '119982672925259996273';

select
  full_name,
  id,
  primary_email,
  creation_time,
  is_delegated_admin,
  customer_id
from
  googledirectory_user
where
  id = '119982672925259996273';

Get user by primary email

Discover the details of a specific user by using their primary email. This can be particularly useful for gaining insights into user's profile details, creation time, and customer ID in a business context.

select
  full_name,
  id,
  primary_email,
  creation_time,
  is_delegated_admin,
  customer_id
from
  googledirectory_user
where
  primary_email = 'mscott@dundermifflin.com';

select
  full_name,
  id,
  primary_email,
  creation_time,
  is_delegated_admin,
  customer_id
from
  googledirectory_user
where
  primary_email = 'mscott@dundermifflin.com';

List administrators

Discover the users who hold administrative or delegated administrative roles in your Google Directory. This can be useful for auditing access control and ensuring only authorized individuals have elevated permissions.

select
  id,
  full_name,
  primary_email,
  is_admin,
  is_delegated_admin
from
  googledirectory_user
where
  is_admin
  or is_delegated_admin;

select
  id,
  full_name,
  primary_email,
  is_admin,
  is_delegated_admin
from
  googledirectory_user
where
  is_admin
  or is_delegated_admin;

List users without two-step verification

Discover the segments that have users who haven't enabled two-step verification. This can be beneficial for enhancing the security measures within your organization.

select
  id,
  full_name,
  primary_email,
  is_enrolled_in_2sv,
  is_enforced_in_2sv
from
  googledirectory_user
where
  not is_enrolled_in_2sv
  or not is_enforced_in_2sv;

select
  id,
  full_name,
  primary_email,
  is_enrolled_in_2sv,
  is_enforced_in_2sv
from
  googledirectory_user
where
  not is_enrolled_in_2sv
  or not is_enforced_in_2sv;

List users who have not logged in for more than 30 days

The query is used to identify users who have been inactive for over a month. This can be useful for IT administrators to manage user accounts and security, by potentially flagging these accounts for follow-up or deactivation.

select
  id,
  full_name,
  primary_email,
  last_login_time
from
  googledirectory_user
where
  last_login_time < current_timestamp - interval '30 days';

select
  id,
  full_name,
  primary_email,
  last_login_time
from
  googledirectory_user
where
  last_login_time < datetime('now', '-30 days');

List users using the query filter

Discover the segments that include users with a specific attribute in their name. This is useful in scenarios where you need to identify and group users based on shared characteristics for targeted communication or management.

select
  id,
  full_name,
  primary_email,
  last_login_time
from
  googledirectory_user
where
  query = 'givenName:steampipe*';

select
  id,
  full_name,
  primary_email,
  last_login_time
from
  googledirectory_user
where
  query = 'givenName:steampipe*';

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_user