Purpose

For auditing purposes, you may wish to see what links are being used on each page within a Confluence instance. 

Generating the report with SQL

• Links are stored in the LINKS table of the database
• Each link is attributed by a specific content ID
• Each piece of content is linked to a space ID.

The following is a good starting point for generating such a report. You may need to adjust this query so it returns some more detailed information, or excludes certain types of links or spaces.

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
ORDER BY l.destspacekey

How to understand this output:

Understanding the output:

This will grab the:

• Space of the page a link appears on
• The title of the page a link appears on
• The destination space key (or protocol, if it's an external link; such as mailto or https)
• The destination page title (or URL if it's an external link - note that the URL will not contain the protocol, as that's recorded in the space key column).
• So you might see output like this:

This would tell us we've got two links:

1. On the "Welcome to Confluence" page in the "DS" space, there's a link to the page "Prettify the page with an image (step 4 of 9)", which resides in the "DS" space
2. On the "Test Page" in the "TEST" space, there's a link to "http://www.google.com/"