Need to extract Perforce changelists information from Bamboo

[Gregory Kremer]

We need to extract information on all Perforce changelists in Bamboo. Atlassian Tech Support advised us that there is no such out-of-the-box feature exists. Has anyone done such report? I'd greatly appreciate any help/suggestion.

[robhit]

Hi Gregory,

Thanks for your question!

Let me know if the below query helps you go fetch the required result:

SELECT    d.title AS 'Project Name',    c.title AS 'Build Plan Name',    c.full_key AS 'Plan Full Key',    CASE        WHEN c.build_type = 'CHAIN_BRANCH' THEN 'Branch Plan'        ELSE 'Main Plan'    END AS 'Plan Type',    a.build_state,    c.UPDATED_DATE AS 'build_updated_date',    a.build_number,    e.COMMIT_FILE_NAME,    g.CHANGESET_ID AS 'CHANGESET',    f.COMMIT_DATE,    f.COMMIT_COMMENT_CLOB AS 'Commit_Comment',    CASE        WHEN h.LINKED_USER_NAME IS NULL THEN 'Author is not linked with a user in Bamboo'        ELSE h.LINKED_USER_NAME    END AS 'Linked_Author_Name',    h.AUTHOR_NAME,    h.AUTHOR_EMAILFROM    buildresultsummary a,    build c,    project d,    commit_files e,    user_commit f,    repository_changeset g,    author hWHERE    c.build_type IN ('CHAIN' , 'CHAIN_BRANCH')        AND c.full_key = a.build_key        AND c.project_id = d.project_id        AND e.commit_id = f.commit_id        AND f.REPOSITORY_CHANGESET_ID = g.REPOSITORY_CHANGESET_ID        AND f.COMMIT_REVISION = g.CHANGESET_ID        AND g.BUILDRESULTSUMMARY_ID = a.BUILDRESULTSUMMARY_ID        AND h.AUTHOR_ID = f.AUTHOR_IDORDER BY d.title , c.full_key , build_updated_date DESC;

Thanks,

Robhit 

[Gregory Kremer]

Robhit,

Thank you for your suggestion. Unfortunately it is not what I needed.