Table: circleci_organization - Query CircleCI Organizations using SQL

A CircleCI Organization is a workspace for teams to collaborate on projects. It can include multiple projects and users. The organization in CircleCI provides a centralized way to manage projects and users, and also allows the configuration of settings at the organization level.

Table Usage Guide

The circleci_organization table provides insights into organizations within CircleCI. As a DevOps engineer, explore organization-specific details through this table, including vcs settings, trial usage, and associated metadata. Utilize it to uncover information about organizations, such as their settings, the usage of trial periods, and the verification of vcs settings.

Examples

Organizations I have access to

Explore the different organizations you have access to and their associated version control systems, allowing you to better manage your resources and align your operations with your access privileges.

select
  name,
  vcs_type
from
  circleci_organization
order by
  name;

select
  name,
  vcs_type
from
  circleci_organization
order by
  name;

Context environment variables not updated for more then 90 days across my organizations

Determine areas in your organization where context environment variables have not been updated for more than 90 days. This is useful for maintaining up-to-date configurations and ensuring optimal performance.

select
  c.organization_slug,
  v.context_id,
  c.name as context,
  v.variable,
  v.created_at,
  v.updated_at
from
  circleci_context_environment_variable v
  join circleci_context c on c.id = v.context_id
where
  updated_at < current_date - interval '90' day;

select
  c.organization_slug,
  v.context_id,
  c.name as context,
  v.variable,
  v.created_at,
  v.updated_at
from
  circleci_context_environment_variable v
  join circleci_context c on c.id = v.context_id
where
  updated_at < date('now', '-90 day');

Schema for circleci_organization

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
avatar_url	text		Avatar icon of the organization.
id	text		Unique key for the organization.
login_id	text	=, !=, ~~, ~~, !~~, !~~	Unique identifier for the account login.
name	text		The organization name.
slug	text		A unique identification for the organization in the form of: <vcs_type>/<org_name>.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
vcs_type	text		Version control system of the organization.

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

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

steampipe_export_circleci --config '<your_config>' circleci_organization