Table: circleci_build - Query CircleCI Builds using SQL

CircleCI is a Continuous Integration and Delivery platform that helps software teams rapidly release code with confidence. It automates the build, test, and delivery of applications, allowing developers to concentrate on creating new features and fixing bugs. CircleCI provides a rich set of features including parallel execution, exclusive job execution, and insights into your pipelines.

Table Usage Guide

The circleci_build table provides insights into builds within CircleCI. As a DevOps engineer, explore build-specific details through this table, including status, workflows, and execution time. Utilize it to uncover information about builds, such as those with failed tests, the workflows associated with each build, and the execution time of each build.

Examples

Last 10 successful builds

Analyze the settings to understand the most recent successful builds in your circleci project. This allows you to keep track of the progress and success rate of your builds, which can aid in improving future build processes.

select
  username as "organization",
  reponame,
  branch,
  build_time_millis,
  status,
  author_name,
  build_url
from
  circleci_build
where
  status = 'success'
order by
  stop_time desc
limit
  10;

select
  username as "organization",
  reponame,
  branch,
  build_time_millis,
  status,
  author_name,
  build_url
from
  circleci_build
where
  status = 'success'
order by
  stop_time desc
limit
  10;

Number of failed builds in a repository

Determine the areas in which the number of failed builds in a repository is high. This can help in identifying problematic repositories that may require extra attention or resources.

select
  concat(username, '/', reponame) as repository,
  count(1) as failed_builds
from
  circleci_build b
where
  status = 'failed'
group by
  concat(username, '/', reponame)
order by
  failed_builds desc;

select
  username || '/' || reponame as repository,
  count(1) as failed_builds
from
  circleci_build b
where
  status = 'failed'
group by
  username || '/' || reponame
order by
  failed_builds desc;

Average execution time duration of successful builds of a repository (in seconds)

Analyze the performance of a specific repository by determining the average time taken for successful builds. This information can be useful in pinpointing efficiency issues or assessing the effectiveness of recent changes.

select
  ROUND(avg(build_time_millis / 1000)) as average_duration
from
  circleci_build
where
  status = 'success'
  and username = 'fluent-cattle'
  and reponame = 'sp-plugin-test'
group by
  status;

select
  ROUND(avg(build_time_millis / 1000)) as average_duration
from
  circleci_build
where
  status = 'success'
  and username = 'fluent-cattle'
  and reponame = 'sp-plugin-test'
group by
  status;

Schema for circleci_build

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
all_commit_details	jsonb		Commit details.
author_email	text		Author email.
author_name	text		Author name.
branch	text		Branch used to build the code.
build_num	bigint		Sequential number of build.
build_parameters	jsonb		Any parameter optional or required to build.
build_time_millis	bigint		Duration of the build.
build_url	text		Build URL.
canceled	boolean		Indicates if the build was canceled.
committer_date	timestamp with time zone		Committer Date.
committer_email	text		Committer email.
committer_name	text		Committer name.
failed	boolean		Indicates if the build failed.
infrastructure_fail	boolean		Indicates if the build failed due to infrastructure.
is_first_green_build	boolean		Indicates if this is the first succeeded build of the project.
lifecycle	text		Build lifecycle.
login_id	text	=, !=, ~~, ~~, !~~, !~~	Unique identifier for the account login.
organization_slug	text		Organization that pipeline belongs to, in the form of: <vcs_type>/<org_name>.
outcome	text		Result of the build.
parallel	bigint		Number of parallel executions.
platform	text		Platform version where build ran.
previous	jsonb		Previous build.
project_slug	text		A unique identification for the project in the form of: <vcs_type>/<org_name>/<repo_name>.
queued_at	timestamp with time zone		Timestamp of when the build was queued.
reponame	text		Repository name.
retries	jsonb		Number of build retries.
retry_of	bigint		Precedent build of the retry.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
ssh_users	jsonb		SSH users with access to the build, if any.
start_time	timestamp with time zone		Start time of the build.
status	text		Status of the build.
stop_time	timestamp with time zone		Stop time of the build.
subject	text		Commit message that triggered the build.
timed_out	boolean		Indicates if the build got timed out.
usage_queued_at	timestamp with time zone		Usage queued time.
user	jsonb		User who triggered the build to run.
username	text		Organization username.
vcs_revision	text		VCS Revision.
vcs_tag	text		VCS Tag.
vcs_url	text		VCS URL.
workflows	jsonb		Workflow which ran the build.

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_build