How to identify which users have configured or personalized a specific portlet

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

This article provides a SQL query to identify Clarity users who have added or personalized a specific portlet on their pages. This is useful for administrators who need to audit portlet usage or identify who may be affected by changes to a particular portlet.

Environment

Clarity PPM 16.x

Resolution

Use the following query to retrieve the details.

Important :- Replace 'your_portlet_code' with the actual Portlet code from your system.

SELECT DISTINCT 
    u.ID AS user_id, 
    u.USER_NAME AS username, 
    u.FIRST_NAME, 
    u.LAST_NAME, 
    u.EMAIL_ADDRESS, 
    p.PORTLET_CODE, 
    pp.PAGE_ID, 
    pp.PRINCIPAL_TYPE, 
    pp.STATE AS portlet_state, 
    pp.LAST_UPDATED_DATE AS last_configured_date 
FROM CMN_PORTLETS p 
JOIN CMN_PAGE_PORTLETS pp ON pp.PORTLET_ID = p.ID 
JOIN CMN_SEC_USERS u ON u.ID = pp.PRINCIPAL_ID 
WHERE p.PORTLET_CODE = 'your_portlet_code' 
AND pp.PRINCIPAL_TYPE = 'USER' 
UNION 
SELECT DISTINCT 
    u.ID, 
    u.USER_NAME, 
    u.FIRST_NAME, 
    u.LAST_NAME, 
    u.EMAIL_ADDRESS, 
    p.PORTLET_CODE, 
    pp.PAGE_ID, 
    pps.PRINCIPAL_TYPE, 
    pps.STATE, 
    pps.LAST_UPDATED_DATE 
FROM CMN_PORTLETS p 
JOIN CMN_PAGE_PORTLETS pp ON pp.PORTLET_ID = p.ID 
JOIN CMN_PAGE_PORTLET_STATUS pps ON pps.PAGE_PORTLET_ID = pp.ID 
JOIN CMN_SEC_USERS u ON u.ID = pps.PRINCIPAL_ID 
WHERE p.PORTLET_CODE = 'your_portlet_code' 
AND pps.PRINCIPAL_TYPE = 'USER' 
ORDER BY last_configured_date DESC, username;

Additional Information

PRINCIPAL_TYPE: This query filters for 'USER' to exclude group-level or system-level configurations.
LAST_UPDATED_DATE: This helps identify how recently the user interacted with or configured the portlet.