How to get usage information of the SQL for Confluence app

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

Instructions

1. Login to Confluence, navigate to the required page and edit the same.
2. Select the Data source profile.
   a.If you are aware of Database profile names than add SQL macro and select the Data source profile as shown in the below image.
       
   b. 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.
        

3. Run below SQL query to get the list of DB 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.

   select profile_name from AO_xxxxx_SQLPROFILES;

   
   Click Save setting to get the list of Database profiles from the above query.
   

4. Click Save on the confluence page.
   
   

5. Replace the 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. 

   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%';

   Click Save settings to save the SQL query.

   
6. Click on the Save button on the confluence page.
   

7. This SQL query will list all the pages containing SQL macro in your confluence space.   

   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"%');
   

   

8. Click Save to publish the page.