EPM RADIANCE

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

·

Raja Stalvin

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.

Groovy
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.

Groovy
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. 

Groovy
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.

Groovy
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

Groovy
/*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])

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: = ())

OUTPUT FROM OUTLOOK