How to fix missing Favorites for XOG imported users and others

[Nika Hadzhikidi]

Hello Clarity users,

Good news : due to numerous requests, I developed a complete SQL solution to fix missing Favorites - Edit rights for XOG users, please find the files and the steps to follow below:

How to fix missing Favorites for XOG imported users and other users that have an issue to access/edit favorites
Symptom:
For all users created by XOG the Favorites menu is not configurable and throwing error when clicking on Configure
Error 401 Not Authorized

 

This is caused by a known Clarity issue:
CLRT-66180 - XOG: when we xog in resources first, then users, the users don't get the Users Favorites Menu - Edit' right
Details regarding this issue can be found in Knowledgebase Article #TEC597397, fixed in 13.2

Steps to fix the issue:

1. On your Test environment, recreate the issue with few of the affected users
2. Make sure you always use UTF8 encoding when executing the script(verify the captions in the script are displaying properly for all not English characters beforehand)
3. Connect to your Test database and make sure Autocommit is disabled in your database query tool
4. Run the appropriate script for your database (Oracle or MSSQL server)
5. Once script has finished executing, make sure you see no database specific errors occurred in the query output. Then commit the changes
6. Reconnect and test with same users as in step 1.
7. When confirmed that everything is fine, apply to higher environments

 

This script can also be used for users that have not been created by XOG for which Favorites menu is not opening at all when scrolling over or giving error 401. The root cause for the issue will be different.
In this case please modify the select condition before step 4. in order to include the specific user. Example:

This part:
select s.user_id from prj_resources r, srm_resources s, cmn_sec_users u
where r.pruid like 'XOG%'
and r.prid = s.id
and s.user_id = u.id
and s.is_active=1
and u.user_status_id = 200
order by s.user_id;

can be modified to:
select s.user_id from prj_resources r, srm_resources s, cmn_sec_users u
where  r.prid = s.id
--and r.pruid like 'XOG%'
and s.user_id = u.id
and s.is_active=1
and u.user_status_id = 200
and s.unique_name='{indicate here the resource username}'
order by s.user_id

The above will include only the specific user, provided that the resource is active. If you need any assistance with this, please contact CA Support.

Disclaimer:
This script is tested on environments under 25k users. For more users affected, please contact CA Support for assistance with running the solution.

 

I have also posted this solution as kb article, it should be posted in a couple of weeks. For any questions please let me know! Many thanks.

 

Kind Regards
Nika

Attachment(s)

User_favorites_fix.zip   18 KB 1 version

[System]

Nika_Hadzhikidi:

Hello Clarity users,

Good news : due to numerous requests, I developed a complete SQL solution to fix missing Favorites - Edit rights for XOG users, please find the files and the steps to follow below:

How to fix missing Favorites for XOG imported users and other users that have an issue to access/edit favorites
Symptom:
For all users created by XOG the Favorites menu is not configurable and throwing error when clicking on Configure
Error 401 Not Authorized

 

This is caused by a known Clarity issue:
CLRT-66180 - XOG: when we xog in resources first, then users, the users don't get the Users Favorites Menu - Edit' right
Details regarding this issue can be found in Knowledgebase Article #TEC597397, fixed in 13.2

Steps to fix the issue:

1. On your Test environment, recreate the issue with few of the affected users
2. Make sure you always use UTF8 encoding when executing the script(verify the captions in the script are displaying properly for all not English characters beforehand)
3. Connect to your Test database and make sure Autocommit is disabled in your database query tool
4. Run the appropriate script for your database (Oracle or MSSQL server)
5. Once script has finished executing, make sure you see no database specific errors occurred in the query output. Then commit the changes
6. Reconnect and test with same users as in step 1.
7. When confirmed that everything is fine, apply to higher environments

 

This script can also be used for users that have not been created by XOG for which Favorites menu is not opening at all when scrolling over or giving error 401. The root cause for the issue will be different.
In this case please modify the select condition before step 4. in order to include the specific user. Example:

This part:
select s.user_id from prj_resources r, srm_resources s, cmn_sec_users u
where r.pruid like 'XOG%'
and r.prid = s.id
and s.user_id = u.id
and s.is_active=1
and u.user_status_id = 200
order by s.user_id;

can be modified to:
select s.user_id from prj_resources r, srm_resources s, cmn_sec_users u
where  r.prid = s.id
--and r.pruid like 'XOG%'
and s.user_id = u.id
and s.is_active=1
and u.user_status_id = 200
and s.unique_name='{indicate here the resource username}'
order by s.user_id

The above will include only the specific user, provided that the resource is active. If you need any assistance with this, please contact CA Support.

Disclaimer:
This script is tested on environments under 25k users. For more users affected, please contact CA Support for assistance with running the solution.

 

I have also posted this solution as kb article, it should be posted in a couple of weeks. For any questions please let me know! Many thanks.

 

Kind Regards
Nika

 

Great info! Thanks Nika!

[navzjoshi00]

Thanks for sharing this useful bit, Nika

NJ

[Will Lloyd]

I am experiencing this same problem (in version PPM 14.3) - users unable to Configure their Favorites - but I had manually added them as Resources. I had raised a ticket, and the On-Demand folks ran a script to attempt to resolve the issue, but there was no affect from their attempts.

 

Any thoughts as to how to tackle this?

[Nika Hadzhikidi]

HI Will -

 

Do you mind sharing your issue number? We at CA PPM Support must take a look. In some cases the solution above is still appropriate, we will have to check it though.

 

Thanks -Nika

[Will Lloyd]

Hi Nika,

 

It is Issue Number 00601145.

 

Thanks,

Will