Table: duo_phone - Query Duo Security Phones using SQL

Duo Security is a cloud-based security solution that verifies the identity of users and the health of their devices before granting them access to applications. It provides a two-factor authentication service to protect against account takeover and data theft. Duo Security helps you secure access to all applications, for any user and device, from anywhere.

Table Usage Guide

The duo_phone table provides insights into Phones within Duo Security. As a security engineer, explore phone-specific details through this table, including phone number, platform, and type. Utilize it to uncover information about phones, such as their status, the type of phone, and the platform it's running on.

Examples

List all phones

select
  number,
  extension,
  type,
  platform,
  model,
  phone_id
from
  duo_phone
order by
  number,
  extension;

select
  number,
  extension,
  type,
  platform,
  model,
  phone_id
from
  duo_phone
order by
  number,
  extension;

Phones and their users

select
  p.number,
  p.extension,
  u ->> 'username' as username
from
  duo_phone as p,
  jsonb_array_elements(p.users) as u
order by
  number,
  extension,
  username;

select
  p.number,
  p.extension,
  json_extract(u.value, '$.username') as username
from
  duo_phone as p,
  json_each(p.users) as u
order by
  number,
  extension,
  username;

Phones that are not yet activated

select
  number,
  extension,
  phone_id
from
  duo_phone
where
  not activated
order by
  number,
  extension;

select
  number,
  extension,
  phone_id
from
  duo_phone
where
  not activated
order by
  number,
  extension;

Phones by platform

select
  platform,
  count(*)
from
  duo_phone
group by
  platform
order by
  platform;

select
  platform,
  count(*)
from
  duo_phone
group by
  platform
order by
  platform;

Users of phones that have been tampered with

select
  u ->> 'username' as username,
  p.number,
  p.extension
from
  duo_phone as p,
  jsonb_array_elements(p.users) as u
where
  p.tampered = 'Tampered'
order by
  username,
  number,
  extension;

select
  json_extract(u.value, '$.username') as username,
  p.number,
  p.extension
from
  duo_phone as p,
  json_each(p.users) as u
where
  p.tampered = 'Tampered'
order by
  username,
  number,
  extension;

Users of phones without encryption

select
  u ->> 'username' as username,
  p.number,
  p.extension,
  p.encrypted
from
  duo_phone as p,
  jsonb_array_elements(p.users) as u
where
  p.encrypted is null
  or p.encrypted != 'Encrypted'
order by
  username,
  number,
  extension;

select
  json_extract(u.value, '$.username') as username,
  p.number,
  p.extension,
  p.encrypted
from
  duo_phone as p,
  json_each(p.users) as u
where
  p.encrypted is null
  or p.encrypted != 'Encrypted'
order by
  username,
  number,
  extension;

Schema for duo_phone

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
activated	boolean		Has this phone been activated for Duo Mobile yet?
capabilities	jsonb		List of strings, each a factor that can be used with the device: push, phone, sms, mobile_otp.
encrypted	text		The encryption status of an Android or iOS device file system. One of: Encrypted, Unencrypted, or Unknown. Blank for other platforms.
extension	text	=	An extension, if necessary.
fingerprint	text		Whether an Android or iOS phone is configured for biometric verification. One of: Configured, Disabled, or Unknown. Blank for other platforms.
model	text		The phone's model.
name	text		Free-form label for the phone.
number	text	=	The phone number. A phone with a smartphone platform but no number is a tablet.
phone_id	text	=	The phone's ID.
platform	text		The phone platform. One of: 'unknown', 'google android', 'apple ios', 'windows phone 7', 'rim blackberry', 'java j2me', 'palm webos', 'symbian os', 'windows mobile', or 'generic smartphone'.
postdelay	bigint		The time (in seconds) to wait after the extension is dialed and before the speaking the prompt.
predelay	bigint		The time (in seconds) to wait after the number picks up and before dialing the extension.
screenlock	text		Whether screen lock is enabled on an Android or iOS phone. One of: Locked, Unlocked, or Unknown. Blank for other platforms.
sms_passcodes_sent	boolean		Have SMS passcodes been sent to this phone?
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
tampered	text		Whether an iOS or Android device is jailbroken or rooted. One of: Not Tampered, Tampered, or Unknown. Blank for other platforms.
type	text		The type of phone. One of: unknown, mobile, or landline.
users	jsonb		List of users to which this phone belongs.

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_phone