How to extract information related to the GitHub DVCS connector from the database

Still need help?

The Atlassian Community is here for you.

Ask the community

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>';
DescriptionDVCS GitHub information from the database
ProductJira

Last modified on Nov 23, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.