How to report on external links?

[Ruth Simpson Frost]

The example output in this article includes a row for an external link. What is needed in the given code to include external links?

My colleague, a system administrator, used the code given in this article, modifying it only to specify space keys:

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 ('DMEdoc', 'DSE', 'MPC')
ORDER BY l.destspacekey;

However, this code generated a report that didn't include any external links. All 3 of the specified wiki spaces do have external links, so we need to know what code to add to include the external links.

[Stephen Sifers · Accepted solution]

Hello Ruth,

Thank you for explaining what you're attempting to pull from your Confluence instance and the steps you’ve taken so far.

To understand what you’re seeing, could you please share the results of the query you posted? Along with an example page which has an external link?

The above will help us to see what is being returned along with that's missing. From this, we can attempt to recreate the results on our end.

We look forward to your response to help find a solution for this.

Regards,
Stephen Sifers

[Ruth Simpson Frost]

Hi Stephen,

Sorry for my delay in responding.

I don't see a way to upload a file to this page, so I have copied some representative rows in the XLSX report from my sys admin:

Space Page SpaceOrProtocol Destination
HPC DME Documentation DME Glossary DMEdoc Preparing to Use Globus with DME
HPC DME Documentation DME Glossary DMEdoc Preparing a Metadata File for Bulk Upload
HPC DME Documentation DME User Guide DMEdoc shared info - guide intro
HPC DME Documentation DME User Guide DMEdoc @self

Here is an example page with an external link:
https://wiki.nci.nih.gov/display/DMEdoc
(This is the "DME User Guide" page mentioned above.)

It has a link pointing to the following external page:
https://confluence.atlassian.com/conf510/export-content-to-word-pdf-html-and-xml-829077162.html

Thanks,
Ruth

[Stephen Sifers]

Hello Ruth,

Thank you for the followup and additional information provided.

I took the example page you provided and re-created it within Confluence to test the SQL query. I found running the SQL query as following provided a result as shown below:

SQL Query:

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 c.title = 'PageName'
ORDER BY l.destspacekey

Returned Result:

With this information, the query looks to be working along with results being returned as expected. If you’re not getting the results as above, there may be a database issue or query issue causing the information not to return properly.

Could you please include a screenshot of your query response? To upload an image within a reply, select the camera icon within a response and either browse to the file or paste the data within the prompted window.

We look forward to your response to help find the disconnect between the query results.

Regards,
Stephen Sifers

[Ruth Simpson Frost]

Thank you. I have asked my colleague, the system administrator, to respond directly. 

[Chuck Solie]

Thanks!  The query as written didn't give me results, but with a minor change got it working and added a bit more output:

SELECT s.spacekey AS SpaceKey, s.spacename as Space, c.title as Page, 
CONCAT( l.destspacekey, ':', l.destpagetitle ) as Destination, 
CONCAT('https://your.confluence.com/pages/viewpage.action?pageId=', 
c.CONTENTID) AS SourcePage 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 c.CONTENTTYPE = 'PAGE' and s.spacekey 
IN ('TargetSpace1', 'TargetSpace2') AND l.destspacekey 
NOT IN ('TargetSpace1', 'TargetSpace2', 'wikicontent') 
ORDER BY SpaceKey,Page,l.destspacekey limit 500;


+----------+-----------------------+---------------------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+
| SpaceKey | Space                 | Page                      | Destination                                                                                     | SourcePage |
+----------+-----------------------+---------------------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+
| TargetSpace1 | My Documentation | Adding a User | mailto:someone@somewhere.com | https://your.confluence.com/pages/viewpage.action?pageId=390471366 |
| TargetSpace1 | My Documentation | Adding a User | mailto:someone@somewhere.com | https://your.confluence.com/pages/viewpage.action?pageId=380208893 |
| TargetSpace1 | My Documentation | My Glossary | http://toolkit.globus.org/toolkit/docs/4.1/glossary.html | https://your.confluence.com/pages/viewpage.action?pageId=373006937 |
| TargetSpace1 | My Documentation | My Glossary | mailto:someone@somewhere.com | https://your.confluence.com/pages/viewpage.action?pageId=373006937 |
| TargetSpace1 | My Documentation | My User Guide | https://confluence.atlassian.com/conf510/export-content-to-word-pdf-html-and-xml-829077162.html | https://your.confluence.com/pages/viewpage.action?pageId=373006788 |
+----------+-----------------------+---------------------------+-------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+

[Stephen Sifers]

Hello Chuck,

Excellent work adapting the SQL query. I used Postgres 9.5 for the example I provided above.

From the results you provided, I see external links included in the results as well.

With this said, are we still having the issue of external links not display on the output of this query?

We look forward to your response so we can resolve this together.

Regards,
Stephen Sifers

[Ruth Simpson Frost]

Thank you both, Chuck and Stephen. I have what I need now. 

[Chuck Solie]

Hi Stephen,  Consider this resolved.  It makes sense that some modification was needed as we are using MySQL.  The query works well, thanks for getting us the information!

I'll just throw in a similar query that includes links to both internal and external endpoints:

SELECT s.spacekey AS SpaceKey, s.spacename as Space, c.title as Page, l.destspacekey 
as SpaceOrProtocol, REPLACE( l.destpagetitle, '//', '' ) as Destination, 
CONCAT('https://your.confluence.com/pages/viewpage.action?pageId=', c.CONTENTID) 
AS SourcePage 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 c.CONTENTTYPE = 'PAGE' 
and s.spacekey IN ('TargetSpace1', 'TargetSpace2') ORDER BY SpaceKey,Page,SpaceOrProtocol;