Automatic Removal of Obsolete Tasks

[normann]

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