steampipe plugin install okta

Table: okta_user - Query Okta Users using SQL

Okta User is a resource within that represents an authenticated entity in the Okta service. A user can be an end user (person) or a service user (software). Each user has a profile that stores the user’s data.

Table Usage Guide

The okta_user table provides insights into user profiles within Okta. As a security analyst, explore user-specific details through this table, including user status, last login, and assigned roles. Utilize it to uncover information about users, such as those with high-risk access levels, inactive users, and the verification of user profiles.

Important Notes

  • This table supports an optional filter column to query results based on Okta supported filters.

Examples

Basic info

Explore the basic user information in your Okta system to understand the status and type of each user. This can help in managing user accounts and ensuring the correct access levels are granted.

select
email,
id,
login,
created,
status,
type
from
okta_user;
select
email,
id,
login,
created,
status,
type
from
okta_user;

Get profile, group, and assigned role details for each user

Explore the various roles, profiles, and group affiliations of each user to understand their access levels and responsibilities within the system. This can assist in managing user permissions and ensuring appropriate access control.

select
id,
email,
jsonb_pretty(profile) as profile,
jsonb_pretty(user_groups) as user_groups,
jsonb_pretty(assigned_roles) as assigned_roles
from
okta_user;
select
id,
email,
profile,
user_groups,
assigned_roles
from
okta_user;

List users with SUPER_ADMIN role access

Explore which users have been granted the highest level of access, the SUPER_ADMIN role, in order to maintain a secure and controlled environment. This is especially useful in managing system security and monitoring potential risks.

select
id,
login,
jsonb_pretty(assigned_roles) as assigned_roles
from
okta_user
where
assigned_roles @> '[{"type":"SUPER_ADMIN"} ]' :: jsonb;
select
id,
login,
assigned_roles
from
okta_user
where
json_extract(assigned_roles, '$[*].type') = 'SUPER_ADMIN';

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

Identify users who may not be actively using the service by pinpointing those who haven't logged in for over a month. This can be useful in engagement analysis or for conducting user clean-ups.

select
id,
email,
last_login
from
okta_user
where
last_login < current_timestamp - interval '30 days';
select
id,
email,
last_login
from
okta_user
where
last_login < datetime('now', '-30 days');

List active users that have been last updated before a specific date using a filter

Analyze the active users who have last updated their details before a certain date. This can be useful to pinpoint users who may need to update their information, improving account security and accuracy.

select
id,
email,
created,
status
from
okta_user
where
filter = 'lastUpdated lt "2021-08-05T00:00:00.000Z" and status eq "ACTIVE"';
select
id,
email,
created,
status
from
okta_user
where
filter = 'lastUpdated lt "2021-08-05T00:00:00.000Z" and status = "ACTIVE"';

Schema for okta_user

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
activatedtimestamp with time zoneTimestamp when transition to ACTIVE status completed.
assigned_rolesjsonbList of roles assigned to user.
createdtimestamp with time zoneTimestamp when user was created.
domaintext=, !=, ~~, ~~*, !~~, !~~*The okta domain name.
emailtext=Primary email address of user.
filtertext=Filter string to [filter](https://developer.okta.com/docs/reference/api/users/#list-users-with-a-filter) users. Input filter query should not be encoded.
idtext=Unique key for user.
last_logintimestamp with time zoneTimestamp of last login.
last_updatedtimestamp with time zone>, >=, =, <, <=Timestamp when user was last updated.
logintext=Unique identifier for the user (username).
password_changedtimestamp with time zoneTimestamp when password last changed.
profilejsonbUser profile properties.
self_linktextA self-referential link to this user.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statustext=Current status of user. Can be one of the STAGED, PROVISIONED, ACTIVE, RECOVERY, LOCKED_OUT, PASSWORD_EXPIRED, SUSPENDED, or DEPROVISIONED.
status_changedtimestamp with time zoneTimestamp when status last changed.
titletextThe title of the resource.
transitioning_to_statustextTarget status of an in-progress asynchronous status transition.
typejsonbUser type that determines the schema for the user's profile.
user_groupsjsonbList of groups of which the user is a member.

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

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

steampipe_export_okta --config '<your_config>' okta_user