Need a way to update all PPM user email addresses

Company is merging with another company, we will need to update all PPM user email addresses to reflect new company email address, does anyone have a tool/script/program/idea how we could do this efficiently?

  • Hi,

    It depends whether the new email address will only see their domain change, or whether the whole email address will be different.

    If it's only the domain change, it's pretty straightforward: To replace emails from @hpe.com to .com for example, the SQL Query would be:

    update KNTA_USERS
    SET email_address = REPLACE(email_address, '@hpe.com', '@microfocus.com')

    Of course this SQL assumes that all emails are lower case, so yours may vary, feel free to call LOWER(..) where needed if it's not the case.

    Now, if the emails in the merged company are completely different from the current ones, you first need to capture the "old/new" emails somewhere (in a file or in a PPM DB Table) and them you could either write a JSP report to process the file contents in Java or a PL/SQL script to process the table contents in DB and update each email address one by one.

  • Thank you very much!  I assumed this would be the case, but thought I would ask to see if there was any other possibilities. Right now we don't know what the new email addresses are going to look like, this is just pre-work to start finding out what are options are to deal with it.

    I'm curious about this part of your reply, "write a JSP report to process the file contents in Java"  Can you give me more info on this? I was assuming we would put the email addresses in a table and write a PL/SQL script to process as you mentioned, but now I'm interested to know more about this other option.

  • In PPM you can create some JSP reports, which will basically run a JSP localy on PPM server, and use the HTML code generated by the JSP as the report result.

    This is a very common way to implement "batch processing" or data sync jobs in PPM: You write a JSP that does all the business logic - potentially calling some compiled java classes in it if you don't want to write all you Java code in the JSP file itself - and your JSP will only generate some very simple output, like "Success", or some logs of how the processing went. 
    Then you schedule your report to run at a specific interval, and that's it, you'll get some custom code running regularly on PPM to perform some abitrary processing, possibly running SQL to read/update/insert/delete data in PPM DB. That being said, it's a very bad practice to directly update data in PPM DB, you should rather use the REST/SOAP APIs to do any data modfication whenever available. 

    In your case, since this is a one-shot update, there's no strong reason to make it a scheduled report that will be run repeatedly - you might just create a JSP with your update logic, put it in deploy\itg.war\web\knta\rpt, and call it manually once by loading it in the browser through its URL without even having to create the JSP report.

    Now, if you plan to have the email correspondance stored in a dedicated DB table, it will be must simpler to just create a PL/SQL script that does the update and call it once after that DB table gets populated.

    If you want to learn more about JSP Reports, the simplest thing to do is to look at the existing JSP reports that come built-in PPM: Check report types such as "Time Sheet Summary", you can see the speciall command that is used to execute these reports: ksc_run_jsp_report /path/to.jsp 
    You can also find lots of existing JSP reports files in deploy/itg.war/web/knta[/module_name]/rpt .There are some interesting "rpt:" taglib tags in them that you can leverage to easily do things such as run a SQL query against PPM DB and get the results to display them in the JSP output.

  • Great, thank you very much for the information!
  • Hello,

    Do you have any integration with LDAP?  If so, there is a OOB report you can run to import the users from LDAP into the PPM DB via the knta_users_int table.  

    Thanks. 

  • Hello, 

    Thanks for the suggestion.  

    I see in PPM 9.52 > Front end > Create > Report > Report Category = Administrative > Import Users

    Is this the report your suggesting?  Do you know if there are any instructions about how to go about using this? I don't see any data in knta_users_int.

    Thank you

  • Hi,

    You can find all the help for this report in the PPM Online Help, in the "User Open Interface" section:

    https://admhelp.microfocus.com/ppm/en/latest/Help/Content/RG/OpenInt/Book_2_01.htm

    You can either import information from LDAP, or insert the data directly into the _INT table(s) (which are staging tables) and then run the report to import/update all the users information in one go based on the content of the _INT table(s).

    Everything should be clear after reading the help.

     

  • Yes correct.

    If you have LDAP in your organization, you can then configure the server.conf file with the log in credentials for it.  Then as he said, import that data into PPM via the knta_users_int table. The table is populated by the report.

    You just want to do a preview import, so make sure to set "Run Import" to "No".