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

Owned by Former user (Deleted)
Last updated: Jan 10, 2020 by Smita Nair
1 min read

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

Instructions

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

  2. 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.