Email from calculation script – Groovy on-premises Snippets Part II 3

I’m on a mission to go through my drafts and finish writing all of them before I move to my next project (Which I’m not sure when 😉 ), who knows what will happen 😉

This one is the second of the Groovy Snippet series and a more detailed version of what I presented during Kscope17.

We all have faced this question about sending notification after Essbase calculation completion. I’ve used a variety of options to fulfill this request.

1. Batch/Shell scripts
2. Automation tools

Then came Groovy 🙂

When I was looking for some uses cases for my presentation, I went back and looked at the article I wrote for OTN (now Oracle Developer Community). I was interested in that particular use case.

Imagine if you can send an email from Essbase calc script after it is done!!! Groovy opens up endless possibilities.

I’m going to walk you through 2 different approaches which can be used to send an email with or without attachments.

The primary challenge to make this work for TLS enabled providers like Office 365, AWS, and Gmail was to update mailapi and smtp jar files shipped with Essbase. Before we start coding, let’s update those jar files first.

Download 1.5.6 version of both jar files.

• mailapi 1.5.6
• smtp 1.5.6

You cannot use the 1.6 version because of the JRE version difference. (when can we see the latest JRE in EPM)

Stop Essbase and replace the jar files under <EPM_HOME>\products\Essbase\EssbaseServer\java\lib, once done restart Essbase.

I’m using a property file for username, password, SMTP server, and port number.

Let’s look at Commons Email example. You’ll need commons-email jar file to run this sample code. I liked this format better than javax one. If you don’t want to download an extra jar file, you can look at the javax examples.

Groovy Email with Commons Email

import org.apache.commons.mail.*
// needs for multiple email address
// import javax.mail.internet.*

email = new SimpleEmail()

def emailprop=new FileReader('C:/groovy_scripts/Properties/email.properties').readLines()
//username is 1st line
usrname=new String(emailprop[0].decodeBase64())

//password
psswd=new String(emailprop[1].decodeBase64())

//smtp host
smtphost=emailprop[2]

//smtp port
smtpport=emailprop[3].toInteger()

// mulitple email format
// recipientAddress=[new InternetAddress ('ckattookaran@intekgrate.com'),new InternetAddress ('celvinvincent@gmail.com')]

email.setHostName(smtphost)
email.setSmtpPort(smtpport)
email.setStartTLSRequired(true)
//email.setSSLOnConnect(true)

email.setAuthenticator(new DefaultAuthenticator(usrname, psswd))

email.setFrom(usrname)
// email.setTo(recipientAddress)
email.addTo('hyperionadmin@intekgrate.com') //use a DL for multiple emails
email.setSubject('Groovy CDF email from Essbase Calc script')
email.setMsg("""Email from Calc script:
$job for $mkt finished at ${new Date()}.""")
email.send()
<pre>

Line 9 and 12 username and password is getting decoded. SMTP properties are set from line 14 to 26.

You can use this to send to multiple recipients; I’m sending this to a DL.

Essbase script

//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear";
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINE";

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF
"compile" /* compile, run */
"-file"
"C:\groovy_scripts\apacheemail.groovy"
"job,mkt" /* variables in Groovy Script */
;

FIX("Jan",
"FY17",
"Actual",
"100-10",
@LEVMBRS("Market",0))
"Sales"(
@CalcMgrGroovyNumber("C:\groovy_scripts\apacheemail.groovy", "", @LIST("job","mkt"),@LIST("BudCalc",@NAME(@Currmbr("Market"))));
)
ENDFIX

What happens if you run this script, any guesses?

Before running the script

Essbase data after the script execution.

I did get the emails. However, I lost the actual data!!!!

Keep in mind if you are going to use the @CalcMgrGroovyNumber function, use that in a FIX statement with default members.

New script

//ESS_LOCALE English_UnitedStates.Latin1@Binary
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear";
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINE";

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF
"compile" /* compile, run */
"-file"
"C:\groovy_scripts\apacheemail.groovy"
"job,mkt" /* variables in Groovy Script */
;

FIX("Jan",
"NoYear",
"Actual",
"100-10",
@LEVMBRS("Market",0))
"Sales"(
@CalcMgrGroovyNumber("C:\groovy_scripts\apacheemail.groovy", "", @LIST("job","mkt"),@LIST("BudCalc",@NAME(@Currmbr("Market"))));
)
ENDFIX

To send attachments with a groovy script you’ll need the activation.jar file, download the file and place it under <EPM_HOME>\products\Essbase\EssbaseServer\java\udf folder, once done restart Essbase.

Groovy script for sending attachments

import org.apache.commons.mail.*

email = new MultiPartEmail()

def emailprop=new FileReader('C:/groovy_scripts/Properties/email.properties').readLines()
//username is 1st line
usrname=new String(emailprop[0].decodeBase64())

//password
psswd=new String(emailprop[1].decodeBase64())

//smtp host
smtphost=emailprop[2]

//smtp port
smtpport=emailprop[3].toInteger()

logdir='E:/Oracle/Middleware/user_projects/AzgardEpmSystem/diagnostics/logs/essbase/essbase'

email.setHostName(smtphost)
email.setSmtpPort(smtpport)
email.setStartTLSRequired(true)
//email.setSSLOnConnect(true)

email.setAuthenticator(new DefaultAuthenticator(usrname, psswd))

email.setFrom(usrname)
email.addTo('hyperionadmin@intekgrate.com')
email.setSubject('Groovy CDF email from Essbase Calc script')
email.setMsg("""Email from Calc script:
$job for $mkt finished at ${new Date()}.""")

// Create the attachment
EmailAttachment attachment = new EmailAttachment()
attachment.setPath("$logdir/CDFLogger0.log")
attachment.setDisposition(EmailAttachment.ATTACHMENT)
attachment.setDescription("CDFLogger")
attachment.setName("CDFLogger.log")

Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )

// add the attachment
email.attach(attachment);

email.send()

I’m sending the CDFLogger log file in this script, and you can see that Commons Email provides some nice features like set the name and description of the attachment.

Above file was send when Massachusetts calc was done (You can see that from the log file itself 😉 )

Javax version

import java.util.Properties
import javax.mail.*
import javax.mail.internet.*

def emailprop=new FileReader('C:/groovy_scripts/email.properties').readLines()
//username is 1st line
usrname=new String(emailprop[0].decodeBase64())

//password
psswd=new String(emailprop[1].decodeBase64())

//smtp host
smtphost=emailprop[2]

//smtp port
smtpport=emailprop[3]

logdir='C:/Oracle/Middleware/user_projects/epmsystem1/diagnostics/logs/essbase/essbase'

props = new Properties()
props.put('mail.smtp.auth', 'true')
props.put('mail.smtp.starttls.enable', 'true')
props.put('mail.smtp.host', smtphost)
props.put('mail.smtp.port', smtpport)
session = Session.getInstance(props,new Authenticator() {
PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(usrname, psswd)
}
})
message = new MimeMessage(session)
message.setFrom(new InternetAddress(usrname))
message.setRecipients(Message.RecipientType.TO,new InternetAddress(usrname))
message.setSubject('Groovy CDF email from Essbase Calc script')
message.setText("""Email from Calc script:
$job for $mkt finished at ${new Date()}.""")

Transport.send(message)

Javax version for sending attachments

import java.util.Properties
import javax.mail.*
import javax.mail.internet.*
import javax.activation.*

def emailprop=new FileReader('C:/groovy_scripts/email.properties').readLines()
//username is 1st line
usrname=new String(emailprop[0].decodeBase64())

//password
psswd=new String(emailprop[1].decodeBase64())

//smtp host
smtphost=emailprop[2]

//smtp port
smtpport=emailprop[3]

logdir='C:/Oracle/Middleware/user_projects/epmsystem1/diagnostics/logs/essbase/essbase'

props = new Properties()
props.put('mail.smtp.auth', 'true')
props.put('mail.smtp.starttls.enable', 'true')
props.put('mail.smtp.host', smtphost)
props.put('mail.smtp.port', smtpport)
session = Session.getInstance(props,new Authenticator() {
PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication(usrname, psswd)
}
})
message = new MimeMessage(session)
message.setFrom(new InternetAddress(usrname))
message.setRecipients(Message.RecipientType.TO,new InternetAddress(usrname))
message.setSubject('Groovy CDF email from Essbase Calc script')

// Create the message part
BodyPart messageBodyPart = new MimeBodyPart()

// Now set the actual message
messageBodyPart.setText("""Email from Calc script:
$job for $mkt finished at ${new Date()}.""")

// Create a multipar message
Multipart multipart = new MimeMultipart()

// Set text message part
multipart.addBodyPart(messageBodyPart)

// Part two is attachment
messageBodyPart = new MimeBodyPart()
DataSource source = new FileDataSource("$logdir/CDFLogger0.log")
messageBodyPart.setDataHandler(new DataHandler(source))
messageBodyPart.setFileName('CDFLogger0.log')
multipart.addBodyPart(messageBodyPart)

Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )

// Send the complete message parts
message.setContent(multipart)

Transport.send(message)

Happy sending emails 🙂