Scenario

For auditing purposes, you may wish to obtain a list of pages and spaces that are integrated with JIRA.

Pages can be integrated with JIRA in the following ways:

• Using the JIRA Issues Macro
• Using the JIRA Chart Macro
• Using a Gadget to display information from JIRA

Additionally, spaces can be linked to JIRA projects.

Obtaining the list of pages integrated with JIRA

Execute the following SQL:

SELECT s.SPACENAME, s.SPACEKEY, c.TITLE
FROM SPACES s
JOIN CONTENT c ON c.SPACEID = s.SPACEID
JOIN BODYCONTENT b ON b.CONTENTID = c.CONTENTID
WHERE (b.BODY LIKE '%ac:name="jira"%'
OR b.BODY LIKE '%ac:name="jirachart"%'
OR b.BODY LIKE '%ac:name="gadget"%')
AND c.PREVVER IS NULL
AND s.SPACETYPE = 'global'
ORDER BY SPACEKEY, TITLE

Notes

• This SQL will not include personal spaces. Remove AND s.SPACETYPE = 'global' from the WHERE clause to include personal spaces
• Gadgets may be from other sources, and may not necessarily point to a JIRA instance. Remove OR b.BODY LIKE '%ac:name="gadget"%' if you'd like to exclude gadgets.

Obtain a list of Spaces integrated with JIRA

First, we must determine the active application link IDs, and determine which spaces are linked to JIRA projects. Execute the following SQL against your database:

SELECT * FROM bandana WHERE bandanakey = 'applinks.global.application.ids';

This will return an XML value, with individual IDs being set as <string> elements - for example:

<list>
  <string>b3119108-6864-39cb-85f5-20fe363cdf6e</string>
  <string>144880e9-a353-312f-9412-ed028e8166fa</string>
  <string>ee7d92ec-35e8-372f-90df-e9657f277c84</string>
</list>

Let's use those to find spaces which are linked to JIRA at least one JIRA project:

SELECT
    *
FROM
    bandana
WHERE
    bandanakey LIKE '%applinks.local%'
AND BANDANAVALUE LIKE '%jira%'
AND (
        BANDANAVALUE LIKE '%b3119108-6864-39cb-85f5-20fe363cdf6e%'
    OR  BANDANAVALUE LIKE '%144880e9-a353-312f-9412-ed028e8166fa%'
    OR  BANDANAVALUE LIKE '%ee7d92ec-35e8-372f-90df-e9657f277c84%' );

Understanding the ouptut

This query will return something like the following:

204898879

_GLOBAL

applinks.local.MYSPACEKEY.confluence_space.linked.entities

<list>
    <string>
        {
            "typeI18n":"applinks.jira.project",
            "name":"MY PROJECT NAME",
            "applicationId":"ee7d92ec-35e8-372f-90df-e9657f277c84",
            "type":"jira.project",
            "key":"MYPROJECTKEY"
        }
    </string>
</list>

Notes:

• The BANDANAKEY contains the space key of the linked space, in this case MYSPACEKEY
• The space is linked to a project in JIRA named MY PROJECT NAME
• The space is linked to a project in JIRA with a project key of MYPROJECTKEY

To obtain information about the application link used for a given space, use it's value as a part of the BANDANAKEY. For the example above:

SELECT * FROM bandana WHERE bandanakey LIKE 'applinks.admin.ee7d92ec-35e8-372f-90df-e9657f277c84%';

The following information will be returned (among other fields used)

• The application type (such as JIRA)
• The name of the application link
• The display URL used
• The application URL used (listed as the RPC URL)