How to extract information related to the DVCS connector from the database
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article lists DB queries to extract information related to the Jira to DVCS connector.
Solution
Queries
The queries below were created and tested on a Postgres database. You may need to rewrite them to fit your needs.
Organizations linked by the DVCS connector
SELECT "NAME", "HOST_URL" FROM "AO_E8B6CC_ORGANIZATION_MAPPING";
Repositories and their organizations linked by the DVCS connector
SELECT
om."NAME" AS "Organization name", rm."NAME" AS "Repository name"
FROM
"AO_E8B6CC_REPOSITORY_MAPPING" rm
INNER JOIN "AO_E8B6CC_ORGANIZATION_MAPPING" om ON rm."ORGANIZATION_ID" = om."ID";
Jira issues with commit details from a given organization
SELECT
ic."ISSUE_KEY"
FROM
"AO_E8B6CC_ISSUE_TO_CHANGESET" ic
INNER JOIN "AO_E8B6CC_CHANGESET_MAPPING" cm ON ic."CHANGESET_ID" = cm."ID"
INNER JOIN "AO_E8B6CC_REPO_TO_CHANGESET" rc ON cm."ID" = rc."CHANGESET_ID"
INNER JOIN "AO_E8B6CC_REPOSITORY_MAPPING" rm ON rc."REPOSITORY_ID" = rm."ID"
INNER JOIN "AO_E8B6CC_ORGANIZATION_MAPPING" om ON rm."ORGANIZATION_ID" = om."ID"
WHERE om."NAME" = '<organization_name>';Jira issues with commit details from a given repository
SELECT
ic."ISSUE_KEY" AS "Issue key", rm."NAME" AS "Repository name"
FROM
"AO_E8B6CC_ISSUE_TO_CHANGESET" ic
INNER JOIN "AO_E8B6CC_CHANGESET_MAPPING" cm ON ic."CHANGESET_ID" = cm."ID"
INNER JOIN "AO_E8B6CC_REPO_TO_CHANGESET" rc ON cm."ID" = rc."CHANGESET_ID"
INNER JOIN "AO_E8B6CC_REPOSITORY_MAPPING" rm ON rc."REPOSITORY_ID" = rm."ID"
WHERE
rm."NAME" = '<repository_name>';Find failed repository syncs for a specific repository
select * from "AO_E8B6CC_REPOSITORY_MAPPING" RM
JOIN "AO_E8B6CC_SYNC_AUDIT_LOG" AL
ON RM."ID" = AL."REPO_ID"
WHERE AL."TOTAL_ERRORS" !=0 AND RM."NAME" = '<repository name>';Was this helpful?