Table: jumpcloud_user - Query JumpCloud Users using SQL
JumpCloud is a cloud-based directory service that connects users to their workstations, applications, files, and networks. It is designed to control and manage user access to both internal and external IT resources such as WiFi and VPN networks, servers, and web applications. JumpCloud supports various platforms including Mac, Windows, and Linux, and offers features such as LDAP-as-a-Service, RADIUS-as-a-Service, device management, and single sign-on.
Table Usage Guide
The jumpcloud_user
table provides insights into user profiles within JumpCloud. As a system administrator, explore user-specific details through this table, including profile information, status, and associated metadata. Utilize it to manage and monitor user access to IT resources, ensuring the security and efficiency of your IT environment.
Examples
Basic info
Explore which JumpCloud users are activated and when they were created. This can be used to manage user accounts and track their activity.
select display_name, username, email, activated, createdfrom jumpcloud_user;
select display_name, username, email, activated, createdfrom jumpcloud_user;
List suspended users
Discover the segments that contain suspended users to manage system access and maintain security. This helps in identifying potential threats and ensuring only authorized users have access.
select display_name, username, email, activated, createdfrom jumpcloud_userwhere suspended;
select display_name, username, email, activated, createdfrom jumpcloud_userwhere suspended = 1;
List users with MFA disabled
Explore which users have not enabled multi-factor authentication (MFA) to identify potential security risks and enforce stronger access controls.
select display_name, username, email, activated, createdfrom jumpcloud_userwhere mfa -> 'configured' is null or not (mfa -> 'configured') :: boolean;
select display_name, username, email, activated, createdfrom jumpcloud_userwhere json_extract(mfa, '$.configured') is null or not json_extract(mfa, '$.configured');
List users not associated with any group
Determine the areas in which users are not linked to any group. This is useful to identify potential issues with user management and ensure all users are properly grouped for access control and permissions management.
with user_associated_with_groups as ( select distinct member ->> 'id' as user_id from jumpcloud_user_group, jsonb_array_elements(members) as member)select display_name, username, email, activated, createdfrom jumpcloud_userwhere id not in ( select user_id from user_associated_with_groups );
with user_associated_with_groups as ( select distinct json_extract(member.value, '$.id') as user_id from jumpcloud_user_group, json_each(members) as member)select display_name, username, email, activated, createdfrom jumpcloud_userwhere jumpcloud_user.id not in ( select user_id from user_associated_with_groups );
Schema for jumpcloud_user
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
account_locked | boolean | True, if the user account is locked. | |
activated | boolean | True, if the user account is active. | |
allow_public_key | boolean | If true, public keys are allowed for the user. | |
attributes | jsonb | A list of attributes for the user. | |
company | text | The name of the company. | |
cost_center | text | Specifies the cost center. | |
created | timestamp with time zone | Specifies the timestamp when the user is created. | |
department | text | Specifies the department the employee is part of. | |
description | text | Specifies the description provided by the user. | |
display_name | text | Specifies the user's preferred full name. | |
text | The users e-mail address, which is also used for log ins. E-mail addresses have to be unique across all JumpCloud accounts, there cannot be two users with the same e-mail address. | ||
employee_identifier | text | A unique identifier of the user inside an organization. | |
employee_type | text | The employment type of the employee. | |
enable_manage_uid | boolean | If true, a managed UID is generated for the user. | |
enable_user_portal_multifactor | boolean | If true, MFA is enabled while logging in to the user portal. | |
external_dn | text | The external DN provided for the user. | |
external_source_type | text | Specifies the external source type of the user. | |
externally_managed | boolean | Specifies whether the user is externally managed. | |
first_name | text | The user's first name. | |
id | text | = | A unique identifier for the user. |
job_title | text | The user's job title. | |
last_name | text | The user's last name. | |
location | text | The user's location. | |
mfa | jsonb | Specifies the MFA configuration for the user. | |
middle_name | text | The user's middle name. | |
organization | text | The name of the organization the user is working with. | |
organization_id | text | Specifies the ID of the organization. | |
password_expiration_date | timestamp with time zone | Specifies the timestamp when the password will expire. | |
password_expired | boolean | True, if the password has expired. | |
password_never_expires | boolean | If true, the password never gets expired. | |
passwordless_sudo | boolean | If true, password is not required while using sudo. | |
public_key | text | The public key for the user. | |
ssh_keys | jsonb | A list of SSH public keys for the user. | |
suspended | boolean | True, if the user account is suspended. | |
tags | jsonb | A list of tags attached with the user. | |
title | text | Title of the resource. | |
totp_enabled | boolean | If true, TOTP is enabled for the user. | |
username | text | The technical user name. |
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)" -- jumpcloud
You can pass the configuration to the command with the --config
argument:
steampipe_export_jumpcloud --config '<your_config>' jumpcloud_user