EPM GROOVY – How to Capture Failed Jobs from Console to CSV Files

The EPBCS REST API provides the option to export all the job details to csv files. This is more helpful for consultants who support the application or for service admins who would like to see the summary of failed jobs in a csv file. I wanted to give it a try and explore this feature. We will use an internal REST connection, which I explained in one of my old posts. Please refer here.

As this is more for administrators, probably getting an email with errored-out jobs on an everyday basis could be a nice option.

DEFINE THE PARAMETERS

The post title must be misleading. Job console output doesn’t just capture the errored out job but provides options to choose whichever job we would like to get in a file, including all jobs with their job ids. One of the options I personally liked is that the csv file provides the log messages as well. This is particularly helpful where we use a lot of hybrid groovy rules and we can see the Essbase scripts in the log.

String jobType = 'JOBCONSOLE_EXPORT'
String userNames = ''
String fileName = 'jobConsole_Custom_Export.zip'
String ndays = 1 // valid options 1, 2, 7, 30, 60, All
String jobNames = ''
String jobTypes = 'RULES'
String jobStatusCodes = '3,8,9' //3-> failed with errors, 8-> completed with errors, 9-> Completed With Warnings
String exportErrorDetails = 'false'

The parameters need to be converted to JSON format as executing a job supports “application/json”. The EPM team added a json convertor in the package, so it is much easier to convert a hashmap to a json format from the calculation manager.

def parameters = ["userNames":userNames, "fileName":fileName, "ndays":ndays,"jobNames":jobNames, "jobTypes": jobTypes, "jobStatusCodes":jobStatusCodes,"exportErrorDetails":exportErrorDetails ]
def body = json(["jobType": jobType,"parameters":parameters ])

json string output from job console:

CALL JOBCONSOLE EXPORT JOB

Once the connection is set up, trigger the REST call to export the file in the inbox/outbox explorer. I am not fetching the status of the call since it was discussed in the previous posts.

HttpResponse<String> executeJob = operation.application.getConnection("internalConnection").post("/HyperionPlanning/rest/v3/applications/MyApp/jobs")
							.header("Content-Type", "application/json")
							.body(body).asString()

Job console log:

ATTACH THE OUTPUT FILE IN AN EMAIL

I have always wanted to check the document attachment feature from the send email feature. I needed to add slight changes to the send email template. For now, we can only attach <10 MB file in the send email feature that comes out of the box.

String mailTo = "[email protected]" //not using rtp

if (mailTo) {
	def mailStatus = sendEmail(mailTo, fileName) as Integer
	if(mailStatus == 0){
    	println "Email successfully triggered"
    }
}

def sendEmail(String sendTo, String attachment) {		
    String connection =  "internalConnection"
	String subject = "Job Console Output from EPBCS"
    String body = "Please refer to the attachment for errored out jobs"
    
    String contents = "to=${sendTo}&subject=${subject}&body=${body}&attachments=${attachment}"
    //Initiate email
	HttpResponse<String> jsonResponse = operation.application.getConnection(connection).post("/interop/rest/v1/services/sendmail")
    									.header("Content-type", "application/x-www-form-urlencoded")
                                		.body(contents).asString()
    //capture error and throw error
	if(!(200..299).contains(jsonResponse.status))
         throwVetoException("Error occured: $jsonResponse.statusText")

     //trace the process using JobId status until it is complete
     final int IN_PROGRESS = -1
     ReadContext ctx = JsonPath.parse(jsonResponse.body)
     String jobId = ctx.read('$.links[1].href').toString().split("/").last() as String
     int status = ctx.read('$.status')
     for(long delay = 50; status == IN_PROGRESS; delay = Math.min(1000, delay * 2)) {
         sleep(delay)
         HttpResponse<String> pingResponse = operation.application.getConnection(connection).get("/interop/rest/v1/services/jobs/"+jobId).asString()
         status = JsonPath.parse(pingResponse.body).read('$.status')
     }

    return status
}

Attach the template to the rule. Final Rule as below

 /*RTPS: */

String jobType = 'JOBCONSOLE_EXPORT'
String userNames = ''
String fileName = 'jobConsole_Custom_Export.zip'
String ndays = 1 // valid options 1, 2, 7, 30, 60, All
String jobNames = ''
String jobTypes = 'RULES'
String jobStatusCodes = '3,8,9' //3-> failed with errors, 8-> completed with errors, 9-> Completed With Warnings
String exportErrorDetails = 'false'

def parameters = ["userNames":userNames, "fileName":fileName, "ndays":ndays,"jobNames":jobNames, "jobTypes": jobTypes, "jobStatusCodes":jobStatusCodes,"exportErrorDetails":exportErrorDetails ]
def body = json(["jobType": jobType,"parameters":parameters ])

println body


HttpResponse<String> executeJob = operation.application.getConnection("restConnection").post("/HyperionPlanning/rest/v3/applications/MyApp/jobs")
							.header("Content-Type", "application/json")
							.body(body).asString()

%Template(name:="Groovy Template - Send Email",application:="MyApp",plantype:="Plan1",dtps:=())

EPM GROOVY – How to Capture Failed Jobs from Console to CSV Files

DEFINE THE PARAMETERS

CALL JOBCONSOLE EXPORT JOB

ATTACH THE OUTPUT FILE IN AN EMAIL

OUTPUT FROM OUTLOOK