How to get favorites list of Confluence users using Sql for Confluence

This article explains how to find the favorites list of all Confluence users using the SQL for Confluence app.

Instructions

In a Confluence page, search for the SQL Query macro or enter {sql and select the macro.

Enter the following query in the macro and select Output format as Wiki:

SELECT u.username, c.title FROM label l, content_label t, content c, user_mapping u WHERE l.labelid=t.labelid AND t.contentid=c.contentid AND t.owner = u.user_key;

The query retrieves a list of all users along with their respective favorite pages. The output of the above query is as follows:

The query used in this example was written for PostgreSQL database. You may need to modify the query depending on the database used in your organization.