Page Comparison

This article explains how to get a list of Confluence pages and the respective spaces where the SQL for Confluence app is used.

Instructions

1. Login to Log into Confluence, navigate to the required page and edit the same.Select where you want to display the usage information.

2. Edit the page, and, enter {sql or search for the SQL for Confluence macro.

   Info
   You must know the Data source profile name that can be used to run queries given in the following instructions.

   a.

   If you

   are aware of Database profile names than add SQL macro and select

   do not know which profile name can be used, contact your Confluence Administrator before proceeding.

   
   

3. Edit the macro and select the Data source profile as shown in the below image.
       Image Modifiedb. If you are not aware of the database profile name. Then follow the below steps:
   1. Contact the Confluence Admin or if user has adequate permission then login as Admin.
   2. Go to General Configuration.
   3. Click on the app SQL for Confluence (Pro Edition) under Bob Swift Configuration.
   4. Click on View and modify data source profiles tab.
        Image Removed

4. Run below SQL In your SQL profile table, there is a number associated with the table mentioned in the query. Replace XXXX with that number and run the query to get the list of DB database profiles created in your Confluence instance. In your SQL profile table, there will be a number for this table so replace XXXX value with the number.

   Code Block
   theme Midnight
   select profile_name from AO_xxxxx_SQLPROFILES;

   Image Removed

   
   

5. Click Save setting to get the list of Database profiles from the above querysettings.
   Image RemovedImage Added

6. Click Save  on the confluence your Confluence page.
   Replace the Image Added

7. Replace Mysql_confluence with your database profile name in place of Mysql_confluence in the below query to get the list of space details. Execute the below query for each of the database profile names. and run the query. The query retrieves a list of all spaces, for each database profile name (taken from the query results above), that uses the macro.

   Code Block
   theme Midnight
   SELECT c.title, s.spacekey FROM CONTENT c JOIN BODYCONTENT bc ON c.contentid = bc.contentid JOIN SPACES s ON c.spaceid = s.spaceid WHERE c.prevver IS NULL AND c.contenttype IN ('PAGE', 'BLOGPOST') AND bc.body LIKE '%ac:name="dataSource">Mysql_confluence%';

   
   

8. Click Save settings to save the SQL query.

   
9. Click on the Save button on the confluence Save your Confluence page.
   

10. This SQL query will Run the following query to list all the pages containing SQL macro in your confluence space.   the space (taken from the query results above) that uses the macro:

    Code Block
    theme Midnight
    Select CONTENT.CONTENTID, CONTENT.Title, CONTENT.SpaceID, CREATOR, LASTMODDATE from CONTENT, BODYCONTENT WHERE PREVVER IS NULL AND (CONTENT.CONTENTTYPE = 'PAGE' OR CONTENT.CONTENTTYPE = 'BLOGPOST') AND CONTENT.CONTENTID = BODYCONTENT.CONTENTID AND CONTENT.CONTENT_STATUS = 'current' AND (BODYCONTENT.BODY like '%<ac:structured-macro ac:name="sql"%');

    
    

11. Click Save settings.
    Image Modified
12. Click Save to publish Publish the page to view the entire list of spaces and pages where the macro is used.