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.
- Batch/Shell scripts
- 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.
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 š
Hi Celvin,
Thank you so much for all your blogs. Just a small question, you have mentioned we will need commons-email jar file to make this possible, so can you provide the link to download the jar file, please?
https://commons.apache.org/proper/commons-email/
Dear Celvin,
I appreciate and thanks very much for your blog, itās very informative.
When coming to your point āHowever, I lost the actual data!!!! ā ā Even after you change the year as “NoYear” in the fix also it will erase the data if it was exist.
The solution to regain the data is mentioned below.
The data which is erased was passed to the groovy, so the same to be returned back to HBR from groovy. ā Its working fine for me.
// ********** Send E mail **********
transport.sendMessage(msg, msg.getAllRecipients());
println(“Email sent!”);
// ********** Return the ORF Status value to Hyperion **********
int IntVal;
double myDouble = Double.parseDouble(val);
IntVal = (int) myDouble;
return IntVal;
println(“Return the ORF Status value to Hyperion..!”);
}