Get Users and Emails with EPM Automate and Groovy

Essbase scripts can't retrieve the users or their email addresses but now it is possible with server-side EPM Automate in a Groovy script

---

Have you ever needed to fetch a list of users and their email addresses from an Oracle EPM Planning (PBCS/EPBCS) environment? Unfortunately, Essbase scripts lack this capability. But don’t worry, there’s a solution! In this post, we’ll show you how to leverage server-side EPM Automate commands in a Groovy script to retrieve the elusive user information.

No Groovy class exists to fetch the users or their email addresses, but we leverage the power of server-side EPM Automate commands to solve this. Using EPM Automate, we have the ability to generate a Role Assignment Report, and therein lies the key to this capability.

To understand how to execute server-side EPM Automate commands, read the “How to run Server-side EPM Automate with Groovy” post first, and then return to this article.

---

Getting the List of Users and Emails

To obtain the list of users and emails, we will use the Role Assignment Report. By using server-side EPM Automate within the Groovy script, we can generate the report, which contains all the users who have been assigned a role in the EPM environment.

Role Assignment Report layout

I will pick the columns for User Login and Email (you can fetch the First Name and Last Name as well).

/*RTPS:*/

String timestamp = new Date().format("yyyyMMdd_HHmmss")

class ConnectionDetails {
  static String username = "USERNAME"
  static String password = "PASSWORD"
  static String url = "URL"
}

EpmAutomate automate = getEpmAutomate()
EpmAutomateStatus status = automate.execute("login", ConnectionDetails.username, ConnectionDetails.password , ConnectionDetails.url)
if (status.getStatus() != 0) {
 throwVetoException("Login status: ${status.getOutput()}")
}

status = automate.execute("roleAssignmentReport", "roleAssignmentReport_${timestamp}.csv")
if (status.getStatus() != 0) {
 throwVetoException("Export Security status: ${status.getOutput()}")
}

def uniqueUsers = [:]
csvIterator("roleAssignmentReport_${timestamp}.csv").withCloseable() { reader ->
 reader.each { String[] row ->
     if (row[0] == 'User Login') return

     def userLogin = row[0]
        def email = row[3]
     if (!uniqueUsers.containsValue(email) && !uniqueUsers.containsKey(userLogin)) {
   uniqueUsers.put(userLogin, email)
  }
    }
}

status = automate.execute("deleteFile", "roleAssignmentReport_${timestamp}.csv")
if (status.getStatus() != 0) {
 throwVetoException("Export Security status: ${status.getOutput()}")
}

status = automate.execute("logout")
if (status.getStatus() != 0) {
  throwVetoException("Logout status: ${status.getOutput()}")
}

uniqueUsers.each { k, v ->
  println("${k}:${v}")
}

We use the EPM Automate commands to generate the Role Assignment Report and save it to the server as a CSV with a unique name (I like to use the timestamp to make it unique).

Next, we utilize the csvIterator() function to read the file, ignoring the header row, and store the unique User Login and Email values in a Map.

Current list of users configured in my test environment

The output

epm_default_cloud_admin:
shehzad:shehzad@kazmi.com
testuser:test@user.com

One use case that might be of interest is that you can use the list of users to send mass emails and updates to users. The Role Assignment Report also has a role column, so the audience can be filtered based on that if necessary. However, anything beyond that won’t be possible unless there is an alternate mechanism to filter users as recipients.

---

In this post, you have learned how to use server-side EPM Automate commands in a Groovy script to fetch the list of users and their email addresses.

There are powerful use cases when we have the ability to retrieve the list of users and their email addresses (versus a static hard-coded list, if at all). For e.g.

• Sending emails to team members, using Roles to identify team members and facilitate effective collaboration, update sharing, and keeping everyone informed about planning and budgeting processes.

• Triggering email notifications to relevant stakeholders, providing real-time updates and alerts when certain thresholds are exceeded or when changes require immediate attention.

• Email summaries to the administrator for any activity that may require attention, such as exceptions being raised in rules.