Summary

This article outlines how you can find the historical data for projects and plans in Bamboo.

Environment

The SQL queries are tested in the Postgres database.

Solution

You can use the below queries to find:

• Total Build Projects
  

  select count(*)
    from PROJECT;

• Total Build plans
  

  select count(*)
    from BUILD
   where BUILD_TYPE = 'CHAIN';

          You can remove the count and just use * if you do not want to know the count of projects and plans.

• Active Build plans (6 months, Successful & failed)
  

  SELECT brs.build_key, brs.build_state, count(buildresultsummary_id), max(b.title) as build_name, max(p.title) as project_name
  	FROM public.buildresultsummary brs left join BUILD b on brs.build_key = b.full_key 
  	left join project p on b.project_id = p.project_id
  	where brs.build_type = 'CHAIN' and build_date > NOW()- interval '6 months' and BRS.BUILD_STATE  in ('Failed','Successful')
  	group by brs.build_key, brs.build_state;

• Active Build Plans (6 months) including branches: As projects contain plans, you will need to find out which plans haven't run over a particular period of time. In case you are referring to build plans inside the project, then you can use the below SQL query to identify build plans (including plan branches) that have not been built for the last 180 days:
  

  SELECT distinct(brs.build_key), b.title as build_name, p.title as project_name
  	FROM public.buildresultsummary brs left join BUILD b on brs.build_key = b.full_key 
  	left join project p on b.project_id = p.project_id
  	where brs.build_type = 'CHAIN' and build_date > NOW()- interval '6 months' ;

• Number of jobs (jobs inside the build plans)
  

  select *
    from BUILD B
    join BUILD_DEFINITION BD
      on BD.BUILD_ID = B.BUILD_ID
   where B.BUILD_TYPE = 'JOB'
     and B.FULL_KEY like '<PLAN_KEY>-%';

• Number of builds from day 1
  

  SELECT count(buildresultsummary_id) from buildresultsummary where build_type ='CHAIN'

• Number of builds since last 6 months
  

  select count(buildresultsummary_id) from buildresultsummary where build_type ='CHAIN' and build_date > NOW()- interval '6 months';

• Total Deployment projects
  

  select *
    from DEPLOYMENT_PROJECT;

• Total Deployment environments
  

  select DP.DEPLOYMENT_PROJECT_ID,
         DP.NAME DEPLOYMENT_PROJECT_NAME,
         DE.ENVIRONMENT_ID,
         DE.NAME ENVIRONMENT_NAME,
         DE.XML_DEFINITION_DATA
    from DEPLOYMENT_PROJECT DP
    join DEPLOYMENT_ENVIRONMENT DE
      on DP.DEPLOYMENT_PROJECT_ID = DE.PACKAGE_DEFINITION_ID;

• Active Deployment projects and environments (6 months)
  

  SELECT distinct(dr.environment_id), de.name as environment_name, de.package_definition_id, dp.name as project_name
  	FROM public.deployment_result dr 
  	left join deployment_environment de on dr.environment_id = de.environment_id
  	left join deployment_project dp on dp.deployment_project_id = de.package_definition_id
  	where executed_date > NOW()- interval '6 months';

• Number of deployments from day 1
  

  SELECT count(deployment_result_id)
  	FROM public.deployment_result dr

• Number of deployments since last 6 months
  

  SELECT count(deployment_result_id)
  	FROM public.deployment_result dr  where executed_date > NOW()- interval '6 months';

Since these queries are tested on Postgres DB, if the query fails to run due to syntax, please connect with your database team to tweak the query based on your database type.