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	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
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.
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.
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