Table: jenkins_job - Query Jenkins Jobs using SQL

Jenkins is an open-source automation server that enables developers to build, test, and deploy their software. It facilitates continuous integration and continuous delivery (CI/CD) by automating parts of the development process, with a focus on testing and deployment. A Jenkins Job is a runnable task that is controlled and monitored by Jenkins.

Table Usage Guide

The jenkins_job table provides insights into Jobs within Jenkins. As a DevOps engineer, explore job-specific details through this table, including job name, description, URL, and build status. Utilize it to monitor the status of various tasks, identify any jobs that may have failed, and verify the details of each job.

Examples

List Maven projects

Explore which Jenkins jobs are configured as Maven projects to manage and understand your build process better. This can help streamline your development workflow and troubleshoot potential issues.

select
  full_display_name,
  url,
  properties
from
  jenkins_job
where
  class = 'hudson.maven.MavenModuleSet';

select
  full_display_name,
  url,
  properties
from
  jenkins_job
where
  class = 'hudson.maven.MavenModuleSet';

List child jobs of a Multibranch Pipeline

Explore the different tasks under a multi-branch pipeline to understand its structure and workflow. This query is useful in identifying the various jobs within a pipeline, their status, and their respective URLs for easy access and management.

select
  j ->> 'name' name,
  j ->> 'color' color,
  j ->> 'url' url
from
  jenkins_job m,
  jsonb_array_elements(properties -> 'jobs') as j
where
  m.class = 'org.jenkinsci.plugins.workflow.multibranch.WorkflowMultiBranchProject';

select
  json_extract(j.value, '$.name') name,
  json_extract(j.value, '$.color') color,
  json_extract(j.value, '$.url') url
from
  jenkins_job m,
  json_each(m.properties, '$.jobs') as j
where
  m.class = 'org.jenkinsci.plugins.workflow.multibranch.WorkflowMultiBranchProject';

Jobs in queue

Discover the segments that are currently in the Jenkins job queue, allowing you to prioritize tasks and manage workflow effectively.

select
  full_display_name,
  url
from
  jenkins_job
where
  (properties ->> 'inQueue') :: boolean;

select
  full_display_name,
  url
from
  jenkins_job
where
  json_extract(properties, '$.inQueue') = 'true';

Top bad health-scored jobs

Identify instances where jobs have a poor health score in order to prioritize and address them. This helps in maintaining the overall health and efficiency of the system.

select
  properties -> 'healthReport' -> 0 ->> 'score' as health_report_score,
  name,
  properties -> 'healthReport' -> 0 ->> 'description' as health_report_description
from
  jenkins_job
order by
  health_report_score desc;

select
  json_extract(
    json_extract(properties, '$.healthReport[0]'),
    '$.score'
  ) as health_report_score,
  name,
  json_extract(
    json_extract(properties, '$.healthReport[0]'),
    '$.description'
  ) as health_report_description
from
  jenkins_job
order by
  health_report_score desc;

Last successful build of a job

Uncover the details of the most recent successful build for a job in Jenkins. This can be useful to identify potential issues and improve future builds.

select
  full_display_name,
  properties -> 'lastSuccessfulBuild' ->> 'URL' as last_successful_build
from
  jenkins_job
order by
  full_display_name;

select
  full_display_name,
  json_extract(
    json_extract(properties, '$.lastSuccessfulBuild'),
    '$.URL'
  ) as last_successful_build
from
  jenkins_job
order by
  full_display_name;

Jobs that last build failed

Identify instances where the most recent job execution was unsuccessful, enabling you to analyze and rectify the issues causing the failure.

select
  full_display_name as job,
  properties ->> 'color' as color,
  properties -> 'healthReport' -> 0 ->> 'score' as health_report_score,
  properties -> 'healthReport' -> 0 ->> 'description' as health_report_description,
  properties -> 'lastUnsuccessfulBuild' ->> 'URL' as last_unsuccessful_build
from
  jenkins_job
where
  properties -> 'last_build' ->> 'Number' != '0'
  and properties -> 'last_build' ->> 'Number' = properties -> 'lastUnsuccessfulBuild' ->> 'Number'
order by
  full_display_name;

select
  full_display_name as job,
  json_extract(properties, '$.color') as color,
  json_extract(
    json_extract(properties, '$.healthReport'),
    '$[0].score'
  ) as health_report_score,
  json_extract(
    json_extract(properties, '$.healthReport'),
    '$[0].description'
  ) as health_report_description,
  json_extract(
    json_extract(properties, '$.lastUnsuccessfulBuild'),
    '$.URL'
  ) as last_unsuccessful_build
from
  jenkins_job
where
  json_extract(
    json_extract(properties, '$.last_build'),
    '$.Number'
  ) != '0'
  and json_extract(
    json_extract(properties, '$.last_build'),
    '$.Number'
  ) = json_extract(
    json_extract(properties, '$.lastUnsuccessfulBuild'),
    '$.Number'
  )
order by
  full_display_name;

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_job