Skip to Content

Hello Community,

especially in context of the 8.0 upgrade it might become an important topic to cleanup the user favorites in order to remove deleted tasks (forms) instead of those items remaining grayed out forever.

Here is a quick guide of what to do in order to automatically remove obsolete favorites from users by job:

 

 

  1. Create a job.
  2. Add a script with the code shown below.
  3. Add a pass of type “To Identity Store”
    1. In Source tab select “SAP Identity Management Database” as source and add the SQL Query shown below (the “with (nolock)” part has to be removed if the database is not MS SQL).
    2. In Destination Tab set the following attributes:
      1. MSKEYVALUE = %MSKEYVALUE%
      2. changetype = modify
      3. MX_USER_PREFS = $FUNCTION.z_removeObsoleteUserpreferences(%MX_USER_PREFS%)$$

SQL Query to get all users from database, that have user preferences stored inside the database and the Base64 encoded value of the preferences in attribute MX_USER_PREFS. If the value is too long for the aValue field, another field is used (aLong). Hence, a case and a datatype cast is required in the query. This is an example working on ORACLE, but TO_CHAR won’t work on MS SQL:

select a.mskeyvalue, a.mcdisplayname,
 case when b1.along is null then to_char(a.MX_USER_PREFS)
  else to_char(b1.along) end MX_USER_PREFS from
(select mcmskey, mcMSKEYVALUE as MSKEYVALUE, mcDisplayName, avalue as MX_USER_PREFS
 from idmv_value_basic %NOLOCK%, idmv_entry_simple %NOLOCK% 
  where mcEntryType = 'MX_PERSON' and mcMSKEY = mskey and attrName = 'MX_USER_PREFS') a
left join
(select mskey, avalue, along from mxi_values %NOLOCK% where attr_id =
 (select attr_id from mxi_attributes %NOLOCK% where attrname = 'MX_USER_PREFS' )) b1
on a.mcmskey = b1.mskey

Script z_removeObsoleteUserpreferences

/*
 * 2018-05-30 SAP NN: Loop through user preferences and remove deleted tasks
 * Input: List of preferences
 * Output: Cleaned up list of preferences
 */

function z_removeObsoleteUserpreferences(Par) {

if (Par) {
 var prefs = uFromBase64(Par);
 var result = "";
 uWarning("Preferences to be checked: [" + prefs + "]");
 /* sample user preferences text data
 #--No header--
 #Mon May 28 16:41:01 CEST 2018
 EntrySearchComp.ResultTable.VisibleRows=50
 EntrySearchComp.TableSort.4.sortColumn=column_3
 EntrySearchComp.TableSort.4.sortDirection=UP
 EntrySearchComp.Favorite.4.4=1011922|-100|Change Identity
 EntrySearchComp.Favorite.4.3=1011910|0|Create Identity
 EntrySearchComp.Favorite.4.2=1012630|-100|Display Identity
 EntrySearchComp.Favorite.1.2=1012693|0|Create Business Role
 EntrySearchComp.Favorite.1.1=1012729|-100|Modify Business Role
 RequestComp.RequestTable.VisibleRows=50
 */
 var prefArray = prefs.split("\n");

result += prefArray[0] + "\n" + prefArray[1]; // first lines static (header and time stamp)

// loop through props and remove obsolete tasks
 // start from 3rd line (first 2 lines are static)
 // last line is empty

for (ipa = 2; ipa < (prefArray.length - 1); ipa++) {

if (prefArray[ipa].indexOf("EntrySearchComp.Favorite.") != -1) { // only check the value if it is a favorite

var taskid = prefArray[ipa].split("=")[1].split("|")[0];
 uInfo("Checking existence of task [" + taskid + "]");
 var count = uSelect("select count(taskid) from mxp_tasks where mcObsoletedGuid is Null and taskid = " + taskid);
 if (count == "0") {
 uWarning("Task [" + taskid + "] got removed from DB");
 } else {
 uInfo("Task [" + taskid + "] still valid task");
 result += "\n" + prefArray[ipa];
 }
 } else {
 uInfo("Entry [" + prefArray[ipa] + "] not a task preference");
 result += "\n" + prefArray[ipa];
 }
 }

result += "\n";
 uWarning("Result for [" + prefs + "]: [" + result + "]");
 return uToBase64(result);
 } else {
 uSkip(1, 1, "no prefs on user");
 }
}


What this script does is:

  1. Decode the value of user attribute MX_USER_PREFS.
  2. Loop through the text value from 3rd line (first 2 lines are static text).
    1. If the current preference is a favorite, check whether the task still exists in database (yes we are looking for task IDs, not for task GUIDs, but the IDs in the user preferences are updated by the transport tool, when they change).
    2. If the task ID does not exist anymore, do nothing.
    3. If it is not a favorite or the task id does exist, add the current line to the result set.
  3. Return the new Base64 encoded value for MX_USER_PREFS

 

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Matt Pollicove

    Great post, Norman, thanks for sharing! Looking forward to using this. As useful as it can be in production, it will be almost more useful in my DEV and SANDBOX environments where things change all too quickly!

    Cheers,

    Matt

    (0) 
  2. Clotilde Martinez

    Great post, very easy to implement !

    My only problem is that my admin user has its preferences MD5 encrypted, so they won’t clear up 🙁 I’ll try to do it otherwise.

     

    Cheers,

     

    Clotilde

    (0) 
  3. Norman Nuernberger
    Post author

    Hi Clotilde,

    yes, that’s happening when the value is too long for aValue. The actual value is than moved to a field called aLong in mxi_values. Hence you need a CASE in the query (take aLong if aValue is empty).

    Ill update the blog accordingly.

    Regards

    Norman

    (0) 

Leave a Reply