Table: jenkins_build - Query Jenkins Builds using SQL

Jenkins is an open-source automation server that enables developers to build, test, and deploy their software. It provides numerous plugins to support building, deploying, and automating any project. A Jenkins Build is a single execution of a Jenkins Job, which includes steps and post-build actions, and contains all the results of the steps.

Table Usage Guide

The jenkins_build table provides insights into Jenkins Builds within the Jenkins automation server. As a DevOps engineer, explore build-specific details through this table, including build status, duration, and associated metadata. Utilize it to uncover information about builds, such as those with failed tests, the duration of each build, and the verification of build results.

Examples

Artifacts created by a build

Analyze the artifacts produced by a specific build process to understand what files were generated. This can help in assessing the output of a build process and identifying any unexpected or missing files.

select
  artifact ->> 'fileName' as file_name
from
  jenkins_build as build,
  jsonb_array_elements(artifacts) as artifact
where
  build.number = 128
  and job_full_name = 'build-and-unit-test';

select
  json_extract(artifact.value, '$.fileName') as file_name
from
  jenkins_build as build,
  json_each(build.artifacts) as artifact
where
  build.number = 128
  and job_full_name = 'build-and-unit-test';

Amount of failed builds by freestyle job

Determine the areas in which your freestyle projects are experiencing the most build failures. This can help you identify problematic projects and prioritize them for troubleshooting and optimization.

select
  j.full_name as job,
  count(1) as failed_builds
from
  jenkins_freestyle_project as j
  join jenkins_build b on b.job_full_name = j.full_name
where
  b.result = 'FAILURE'
group by
  j.full_name
order by
  failed_builds desc;

select
  j.full_name as job,
  count(1) as failed_builds
from
  jenkins_freestyle_project as j
  join jenkins_build b on b.job_full_name = j.full_name
where
  b.result = 'FAILURE'
group by
  j.full_name
order by
  failed_builds desc;

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

Determine the average duration of successful builds for a specific job to gain insights into performance efficiency and identify potential areas for process optimization.

select
  ROUND(avg(duration) / 1000) as average_duration
from
  jenkins_build
where
  job_full_name = 'corp-project/build-and-test'
  and result = 'SUCCESS'
group by
  result;

select
  ROUND(avg(duration) / 1000) as average_duration
from
  jenkins_build
where
  job_full_name = 'corp-project/build-and-test'
  and result = 'SUCCESS'
group by
  result;

Builds that took longer than estimated to execute (in seconds)

Determine the instances where certain build processes took longer than anticipated in a specific production project. This could be useful in identifying inefficiencies and areas for improvement in the production process.

select
  full_display_name,
  result,
  (duration - estimated_duration) / 1000 as difference,
  url
from
  jenkins_build
where
  job_full_name = 'corp-project/production/deploy-to-prod'
  and duration > estimated_duration
order by
  timestamp desc;

select
  full_display_name,
  result,
  (duration - estimated_duration) / 1000 as difference,
  url
from
  jenkins_build
where
  job_full_name = 'corp-project/production/deploy-to-prod'
  and duration > estimated_duration
order by
  timestamp desc;

Schema for jenkins_build

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
actions	jsonb		Data about the build trigger.
artifacts	jsonb		Files created as a result of the build execution.
building	boolean		Boolean to indicate whether the build is executing.
built_on	text		Node where the build was executed.
change_set	jsonb		SCM changes between builds.
culprits	jsonb		People involved to the build.
description	text		An optional description that can be added to the build.
display_name	text		The name of the build, defaults to the build number.
duration	double precision		Actual amount of time took for the build execution.
estimated_duration	double precision		The expected amount of building time.
executor	jsonb		The executor where the build ran.
finger_print	jsonb		MD5 checksum fingerprint of the artifact file.
full_display_name	text		Stands for the job name plus the display name.
id	text		Same as the build number, but as string.
job_full_name	text	=	Full name of the job which defines the build. This column is required on any query because a build cannot exist without a job
keep_log	boolean		Boolean to indicate whether the build kept the log.
maven_artifacts	jsonb		Maven artifacts generated during the build execution, if any.
maven_version_used	text		Version of Maven used to execute the build.
number	bigint	=	Unique key for the build.
queue_id	bigint		The queue ID assigned to the build. Each queue ID is unique.
result	text		Result of the build execution.
timestamp	bigint		Time when the build started.
title	text		The title of the resource.
url	text		Full URL to 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)" -- jenkins

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

steampipe_export_jenkins --config '<your_config>' jenkins_build