How to extract information related to the GitHub DVCS connector from the database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Purpose
This article lists DB queries to extract information related to the Jira to GitHub DVCS connector.
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 GitHub 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 GitHub 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>';
Jira issues with commit details from a given GitHub repository
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"
WHERE
rm."NAME" = '<repository_name>';
Find failed repository syncs for a specific GitHub 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>';