How do I get list of Bamboo projects and plans with their users using MS SQL query

[Nirmalkumar Shete]

How do I get list of Bamboo projects and plans with their users using MS SQL  query

[Daniel Santos · Accepted solution]

Hi @Nirmalkumar Shete

From your question is not clear what do you mean by "their users", but I will consider you are talking about what users or groups have permissions on those plan/projects.

This query will give you the build projects/plans:

SELECT
    P.PROJECT_ID,
    P.PROJECT_KEY,
    P.TITLE,
    B.BUILD_ID,
    B.FULL_KEY,
    B.TITLE
FROM
    PROJECT P
JOIN
    BUILD B
    ON P.PROJECT_ID=B.PROJECT_ID
WHERE
    BUILD_TYPE='CHAIN' ORDER BY FULL_KEY

This one will give you the deployment projects/environments:

SELECT
    DP.DEPLOYMENT_PROJECT_ID,
    DP.NAME,
    DE.ENVIRONMENT_ID,
    DE.NAME,
    DP.PLAN_KEY
FROM
    DEPLOYMENT_PROJECT DP
JOIN
    DEPLOYMENT_ENVIRONMENT DE 
    ON DP.DEPLOYMENT_PROJECT_ID=DE.PACKAGE_DEFINITION_ID

The users and groups with permissions for them you can get from this page:

• How to retrieve user and group access mappings / permissions from the Bamboo database.
  ⚠️Please notice that you need to change true to 1 for those queries to work in SQL Server

I hope that will allow you to retrieve the information you need from the Bamboo DB.

[Nirmalkumar Shete]

Thank you Daniel, this is what I was looking for..

[Daniel Santos]

I'm glad it was helpful! =]