Table: jenkins_freestyle_project - Query Jenkins Freestyle Projects 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 Freestyle Project in Jenkins is a type of project that provides maximum flexibility and simplicity for the users. It is the simplest and the most flexible way to design a build, especially when the build process involves several different steps.
Table Usage Guide
The jenkins_freestyle_project
table provides insights into Freestyle Projects within Jenkins. As a DevOps engineer, explore project-specific details through this table, including project name, description, URL, and build details. Utilize it to uncover information about projects, such as their configurations, the status of the builds, and the details of the last build.
Examples
Freestyle project jobs in queue
Explore which freestyle project jobs are currently in queue to manage your workload and prioritize tasks effectively. This helps in optimizing your project pipeline and ensuring smooth operations.
select full_display_name, urlfrom jenkins_freestyle_projectwhere in_queue;
select full_display_name, urlfrom jenkins_freestyle_projectwhere in_queue = 1;
Top bad health-scored freestyle project jobs
Determine the areas in which freestyle project jobs have poor health scores. This helps prioritize and address issues to improve overall project performance.
select health_report -> 0 ->> 'score' as health_report_score, name, health_report -> 0 ->> 'description' as health_report_descriptionfrom jenkins_freestyle_projectorder by health_report_score desc;
select json_extract(json_extract(health_report, '$[0]'), '$.score') as health_report_score, name, json_extract( json_extract(health_report, '$[0]'), '$.description' ) as health_report_descriptionfrom jenkins_freestyle_projectorder by health_report_score desc;
Health color of a freestyle project and its downstream projects
This query helps to assess the health status of a specific project and its related downstream projects in a Jenkins environment. It is particularly useful for monitoring project health in real time, enabling proactive issue detection and resolution.
select job.name as job_name, job.color as job_health_colorfrom jenkins_freestyle_project as jobwhere job.full_name = 'corp-project/build-and-test'unionselect ds_job ->> 'name' as job_name, ds_job ->> 'color' as job_health_colorfrom jenkins_freestyle_project as job, jsonb_array_elements(downstream_projects) as ds_jobwhere job.full_name = 'corp-project/build-and-test';
select job.name as job_name, job.color as job_health_colorfrom jenkins_freestyle_project as jobwhere job.full_name = 'corp-project/build-and-test'unionselect json_extract(ds_job.value, '$.name') as job_name, json_extract(ds_job.value, '$.color') as job_health_colorfrom jenkins_freestyle_project as job, json_each(downstream_projects) as ds_jobwhere job.full_name = 'corp-project/build-and-test';
Top 10 freestyle projects with most builds
Analyze your Jenkins freestyle projects to identify the top ten with the most builds. This can help prioritize maintenance efforts and understand where your resources are most heavily utilized.
select jsonb_array_length(builds) number_of_builds, full_display_namefrom jenkins_freestyle_projectorder by number_of_builds desclimit 10;
select json_array_length(builds) as number_of_builds, full_display_namefrom jenkins_freestyle_projectorder by number_of_builds desclimit 10;
Freestyle project's last successful build
This query helps you identify the last successful build of each freestyle project in your Jenkins environment, which can assist in tracking project progress and ensuring builds are completing successfully. It is particularly useful for maintaining build quality and identifying issues early by pinpointing the projects where the most recent build was successful.
select full_display_name, last_successful_build ->> 'URL' as last_successful_buildfrom jenkins_freestyle_projectorder by full_display_name;
select full_display_name, json_extract(last_successful_build, '$.URL') as last_successful_buildfrom jenkins_freestyle_projectorder by full_display_name;
Freestyle projects where the last build failed
Identify freestyle projects in Jenkins where the most recent build was unsuccessful. This can help in quickly pinpointing problematic projects, allowing for timely troubleshooting and resolution.
select full_display_name as freestyle, color, health_report -> 0 ->> 'score' as health_report_score, health_report -> 0 ->> 'description' as health_report_description, last_unsuccessful_build ->> 'URL' as last_unsuccessful_buildfrom jenkins_freestyle_projectwhere last_build ->> 'Number' != '0' and last_build ->> 'Number' = last_unsuccessful_build ->> 'Number'order by full_display_name;
select full_display_name as freestyle, color, json_extract(health_report, '$[0].score') as health_report_score, json_extract(health_report, '$[0].description') as health_report_description, json_extract(last_unsuccessful_build, '$.URL') as last_unsuccessful_buildfrom jenkins_freestyle_projectwhere json_extract(last_build, '$.Number') != '0' and json_extract(last_build, '$.Number') = json_extract(last_unsuccessful_build, '$.Number')order by full_display_name;
Schema for jenkins_freestyle_project
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
actions | jsonb | Data about the freestyle trigger. | |
buildable | boolean | Boolean to indicate whether the freestyle is able to run a build. | |
builds | jsonb | List of builds of the freestyle. | |
color | text | Color indicating the health of the freestyle based on the result of recent builds. | |
concurrent_build | boolean | Boolean to indicate whether the freestyle is able to run builds in parallel. | |
description | text | An optional description that can be added to the freestyle. | |
display_name | text | Human readable name of the freestyle. | |
downstream_projects | jsonb | Jobs called after build execution. | |
first_build | jsonb | First build of the freestyle. | |
full_display_name | text | Human readable name of the freestyle, including folder. | |
full_name | text | = | Unique key for the freestyle. |
health_report | jsonb | Health data about recent builds. | |
in_queue | boolean | Boolean to indicate whether the freestyle is waiting on queue to run a build. | |
keep_dependencies | boolean | Boolean to indicate whether the freestyle will keep dependencies used on last build. | |
last_build | jsonb | Last build of the freestyle. | |
last_completed_build | jsonb | Last completed build of the freestyle. | |
last_failed_build | jsonb | Last failed build of the freestyle. | |
last_stable_build | jsonb | Last stable build of the freestyle. | |
last_successful_build | jsonb | Last successful build of the freestyle. | |
last_unstable_build | jsonb | Last unstable build of the freestyle. | |
last_unsuccessful_build | jsonb | Last unsuccessful build of the freestyle. | |
name | text | Name of the freestyle. | |
next_build_number | bigint | Number that will be assigned to build on next | |
property | jsonb | Properties of the freestyle. | |
scm | jsonb | Source code management set on this freestyle. | |
title | text | The title of the resource. | |
upstream_projects | jsonb | Jobs that calls this freestyle after their build finishes. | |
url | text | Full URL to the freestyle. |
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_freestyle_project