Clarity: What does Create and Update Jaspersoft Users do technically?

Products

• Clarity PPM On Premise

Issue/Introduction

Create and Update Jaspersoft Users creates and updates the user details in Jaspersoft when a user in clarity is given the Advanced Reporting rights. 

Cause

When you create a user in Clarity and grant the user Advanced Reporting rights and then run the Create and Update Jaspersoft Users Job, the Job actually takes the value from column USER_UID of table CMN_SEC_USERS of Clarity Schema  and insert/updates the value in column USERNAME of table JIUSER in Jaspersoft Schema. 

 

So if you update the Resource ID in clarity then you might see the Resource_ID of clarity is not matching the USER ID of Jaspersoft, So its not recommended to change the Resource ID once you have created it 

Clarity Resource Details Example                                                           Jaspersoft User Properties

 

 

Environment

Release : 15.x

Component : CA PPM JASPERSOFT

Resolution

This technical document is to explain the technicalities of the Create and Update Jaspersoft Users Job and no resolution is needed.

However if you run into a situation where you need to update the Resource ID in clarity from Clarity UI until the resource is not financially activated and you have already ran the Create and Update Jaspersoft Users Job then you would need to go to perform the below steps 

 

1. Stop all the clarity services 

2. Take the backup of TABLE CMN_SEC_USERS (CREATE TABLE BKP_CMN_SEC_USERS AS   SELECT * FROM CMN_SEC_USERS;)

3. Execute the below Update Statement ( replace the 5008000 number with the problematic user ID ) 

UPDATE CMN_SEC_USERS

SET USER_UID = (SELECT UNIQUE_NAME from SRM_RESOURCES R ,CMN_SEC_USERS A where A.ID = R.USER_ID and A.ID = 5008000)

         WHERE ID = 5008000

 

Additional Information

Note:

1. To perform this in SAAS On Demand Instance please contact the Broadcom Technical Support Team

2. In clarity version earlier to 15.6 the USER_NAME of CMN_SEC_USERS was mapped to USERS_UID and below update should help and USER_NAME and USER_UID should match but DOT(.) and (@) will be replaced with (_)

3. Run the select command to find out the problematic user and update 

• SELECT ID,USER_NAME,USER_UID from CMN_SEC_USERS WHERE USER_NAME = '$USER_NAME';

Note: Replace the $USER_NAME with a valid user name and not if the format of user_uid matches as mentioned in step 2 if it doesn't match then run the update statement below 

• Take the backup of TABLE CMN_SEC_USERS (CREATE TABLE BKP_CMN_SEC_USERS AS   SELECT * FROM CMN_SEC_USERS;)
• UPDATE CMN_SEC_USERS SET USER_UID = 'First_name_LastName_Domain_com' where ID = '$ID'

Note: Replace the $ID with valid id fetched from the select statement above