Table: onepassword_item - Query 1Password Items using SQL

1Password is a password management service that stores sensitive information, including passwords, software licenses, and notes, in a secure and encrypted format. It allows users to create and manage multiple vaults, each containing different sets of items. These items can range from login credentials, secure notes, credit card information, to identities.

Table Usage Guide

The onepassword_item table provides insights into 1Password Items within 1Password. As a security analyst, explore item-specific details through this table, including categories, vault IDs, and associated metadata. Utilize it to uncover information about items, such as their categories, the vaults they belong to, and the intricacies of their details.

Examples

Basic info

Explore which items in your OnePassword vault have been recently updated or edited. This can help you keep track of changes and ensure your data remains secure.

select
  id,
  vault_id,
  title,
  created_at,
  updated_at,
  last_edited_by,
  tags
from
  onepassword_item;

select
  id,
  vault_id,
  title,
  created_at,
  updated_at,
  last_edited_by,
  tags
from
  onepassword_item;

List items that have been updated in the last 30 days

Explore which items have been modified in the past month to keep track of recent changes.

select
  id,
  vault_id,
  title,
  created_at,
  updated_at,
  last_edited_by,
  tags
from
  onepassword_item
where
  updated_at > now() - interval '30 day';

select
  id,
  vault_id,
  title,
  created_at,
  updated_at,
  last_edited_by,
  tags
from
  onepassword_item
where
  updated_at > datetime('now', '-30 day');

List items with production tag

Identify instances where items are tagged as 'production' to focus on operational elements.

select
  id,
  title,
  category,
  version,
  tags
from
  onepassword_item
where
  tags @> '["production"]';

Error: The corresponding SQLite query is unavailable.

List the fields of all items with a specific section

Analyze the fields of items within a specific section, such as 'Metadata', to gain insights into categorized data.

select
  title,
  jsonb_pretty(f) as field
from
  onepassword_item,
  jsonb_array_elements(fields) as f
where
  f -> 'section' ->> 'label' = 'Metadata';

select
  title,
  f.value as field
from
  onepassword_item,
  json_each(fields) as f
where
  json_extract(json_extract(f.value, '$.section'), '$.label') = 'Metadata';

Schema for onepassword_item

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
category	text		The category of the item.
created_at	timestamp with time zone		Date and time when the item was created.
favorite	boolean		Whether the item is marked as a favorite.
fields	jsonb		The fields of the item.
files	jsonb		The files of the item.
id	text	=	The UUID of the item.
last_edited_by	text		UUID of the account that last changed the item.
sections	jsonb		The sections of the item.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
tags	jsonb		An array of strings of the tags assigned to the item.
title	text		The title of the item.
trashed	boolean		Checks if the item is trashed.
updated_at	timestamp with time zone		Date and time when the item was last changed.
urls	jsonb		Array of URL objects containing URLs for the item.
vault_id	text	=	The UUID of the vault the item is in.
version	bigint		The version of the item.

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

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

steampipe_export_onepassword --config '<your_config>' onepassword_item