Table: duo_user - Query Duo Security Users using SQL

Duo Security is a cloud-based access security provider protecting the world's fastest-growing companies and thousands of organizations worldwide. It provides comprehensive security solutions to address the security needs of users, devices, and applications, ensuring that only trusted users and devices can access protected applications. The Duo Security Users are the entities that have been granted access to the Duo-protected applications.

Table Usage Guide

The duo_user table provides detailed insights into Duo Security Users within the Duo Security System. As a security analyst, you can explore user-specific details through this table, including user status, last login information, and associated metadata. Utilize it to uncover information about users, such as those with active or inactive status, the last login details of users, and the verification of user identities.

Examples

List all users

select
  user_id,
  username,
  email
from
  duo_user
order by
  username;

select
  user_id,
  username,
  email
from
  duo_user
order by
  username;

Users who have not yet enrolled

select
  user_id,
  username,
  email
from
  duo_user
where
  not is_enrolled
order by
  username;

select
  user_id,
  username,
  email
from
  duo_user
where
  not is_enrolled
order by
  username;

select
  user_id,
  username,
  email,
  last_login
from
  duo_user
order by
  last_login desc
limit
  10;

select
  user_id,
  username,
  email,
  last_login
from
  duo_user
order by
  last_login desc
limit
  10;

Users who have never logged in

select
  user_id,
  username,
  email
from
  duo_user
where
  last_login is null
order by
  username;

select
  user_id,
  username,
  email
from
  duo_user
where
  last_login is null
order by
  username;

Users who have not logged in for more than 30 days

select
  user_id,
  username,
  email,
  last_login,
  age(last_login) as age
from
  duo_user
where
  age(last_login) > interval '30 days'
order by
  age desc;

Error: SQLite does not support the AGE function
or INTERVAL keyword used in PostgreSQL.

Users who are locked out

select
  user_id,
  username,
  email,
  last_login,
  status
from
  duo_user
where
  status = 'locked_out'
order by
  username;

select
  user_id,
  username,
  email,
  last_login,
  status
from
  duo_user
where
  status = 'locked_out'
order by
  username;

User statistics by status

select
  status,
  count(*)
from
  duo_user
group by
  status
order by
  status;

select
  status,
  count(*)
from
  duo_user
group by
  status
order by
  status;

Users and their group memberships

select
  u.username,
  g ->> 'name' as groupname
from
  duo_user as u,
  jsonb_array_elements(u.groups) as g
order by
  username,
  groupname;

select
  u.username,
  json_extract(g.value, '$.name') as groupname
from
  duo_user as u,
  json_each(u.groups) as g
order by
  username,
  groupname;

Users and their phones

select
  u.username,
  p ->> 'number' as phone_number,
  p ->> 'extension' as phone_extension
from
  duo_user as u,
  jsonb_array_elements(u.phones) as p
order by
  username,
  phone_number,
  phone_extension;

select
  u.username,
  json_extract(p.value, '$.number') as phone_number,
  json_extract(p.value, '$.extension') as phone_extension
from
  duo_user as u,
  json_each(u.phones) as p
order by
  username,
  phone_number,
  phone_extension;

Users and their hardware tokens

select
  u.username,
  t ->> 'serial' as token_serial
from
  duo_user as u,
  jsonb_array_elements(u.tokens) as t
order by
  username,
  token_serial;

select
  u.username,
  json_extract(t.value, '$.serial') as token_serial
from
  duo_user as u,
  json_each(u.tokens) as t
order by
  username,
  token_serial;

Schema for duo_user

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
aliases	jsonb		Map of the user's username alias(es). Up to eight aliases may exist.
created	timestamp with time zone		The user's creation date.
email	text		The user's email address.
firstname	text		The user's given name.
groups	jsonb		List of groups to which this user belongs.
is_enrolled	boolean		Is true if the user has a phone, hardware token, U2F token, WebAuthn security key, or other WebAuthn method available for authentication. Otherwise, false.
last_directory_sync	timestamp with time zone		The last update to the user via directory sync, or null if the user has never synced with an external directory or if the directory that originally created the user has been deleted from Duo.
last_login	timestamp with time zone		The last time this user logged in, or null if the user has not logged in.
lastname	text		The user's surname.
notes	text		Notes about this user.
phones	jsonb		A list of phones that this user can use.
realname	text		The user's real name (or full name).
status	text		The user's status: active, bypass, disabled, locked out, pending deletion.
tokens	jsonb		A list of tokens that this user can use.
user_id	text	=	The user's ID.
username	text	=	The user's username.

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

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

steampipe_export_duo --config '<your_config>' duo_user