turbot/jumpcloud
steampipe plugin install jumpcloud

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,
created
from
jumpcloud_user;
select
display_name,
username,
email,
activated,
created
from
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,
created
from
jumpcloud_user
where
suspended;
select
display_name,
username,
email,
activated,
created
from
jumpcloud_user
where
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,
created
from
jumpcloud_user
where
mfa -> 'configured' is null
or not (mfa -> 'configured') :: boolean;
select
display_name,
username,
email,
activated,
created
from
jumpcloud_user
where
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,
created
from
jumpcloud_user
where
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,
created
from
jumpcloud_user
where
jumpcloud_user.id not in (
select
user_id
from
user_associated_with_groups
);

Schema for jumpcloud_user

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_lockedbooleanTrue, if the user account is locked.
activatedbooleanTrue, if the user account is active.
allow_public_keybooleanIf true, public keys are allowed for the user.
attributesjsonbA list of attributes for the user.
companytextThe name of the company.
cost_centertextSpecifies the cost center.
createdtimestamp with time zoneSpecifies the timestamp when the user is created.
departmenttextSpecifies the department the employee is part of.
descriptiontextSpecifies the description provided by the user.
display_nametextSpecifies the user's preferred full name.
emailtextThe 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_identifiertextA unique identifier of the user inside an organization.
employee_typetextThe employment type of the employee.
enable_manage_uidbooleanIf true, a managed UID is generated for the user.
enable_user_portal_multifactorbooleanIf true, MFA is enabled while logging in to the user portal.
external_dntextThe external DN provided for the user.
external_source_typetextSpecifies the external source type of the user.
externally_managedbooleanSpecifies whether the user is externally managed.
first_nametextThe user's first name.
idtext=A unique identifier for the user.
job_titletextThe user's job title.
last_nametextThe user's last name.
locationtextThe user's location.
mfajsonbSpecifies the MFA configuration for the user.
middle_nametextThe user's middle name.
organizationtextThe name of the organization the user is working with.
organization_idtextSpecifies the ID of the organization.
password_expiration_datetimestamp with time zoneSpecifies the timestamp when the password will expire.
password_expiredbooleanTrue, if the password has expired.
password_never_expiresbooleanIf true, the password never gets expired.
passwordless_sudobooleanIf true, password is not required while using sudo.
public_keytextThe public key for the user.
ssh_keysjsonbA list of SSH public keys for the user.
suspendedbooleanTrue, if the user account is suspended.
tagsjsonbA list of tags attached with the user.
titletextTitle of the resource.
totp_enabledbooleanIf true, TOTP is enabled for the user.
usernametextThe 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