What is the SQL to find out what Confluence Spaces have links back to JIRA

[jay.shain@dxc.com]

I am looking for an SQL Query that will help me find out which Confluence Spaces have any ties back to JIRA projects, issues, etc. 

Can you please advise.

Thanks,

[Amith Mathur {Appfire}]

Hi jay.shain@dxc.com,

Here are few links with the respective information, which will be helpful to retrieve the list of Confluence pages which has the links to JIRA or use the macro. 

• How to report on links in a Confluence instance

• How to obtain a list of all pages and spaces integrated with JIRA

Additionally, you can execute the below query against your Confluence database to find all the external links as well:

SELECT s.spacename as Space, c.title as Page, l.destspacekey as SpaceOrProtocol, l.destpagetitle as Destination
FROM LINKS l
JOIN CONTENT c ON c.contentid = l.contentid
JOIN SPACES s ON s.spaceid = c.spaceid
WHERE c.prevver IS NULL and l.destspacekey in ('http','https')
ORDER BY l.destspacekey;

Thanks,
Amith Mathur