attach scheduled report to task

[russellprice]

Hi

We currently have a scheduled task which is triggered every week to check cert expiries. We also have a scheduled report which emails the current certs which are due to expire.

I was wondering wether there was a way to get the scheduled report to automaticaly attach itself to the scheduled task so that there is no need to search for the email in your inbox?

Thanks

Russell

[russellprice]

You are correct, that field is an integer with a calculation to work out the amount of days left until expiry - current.u_expiration_days = gs.dateDiff(gs.now(), current.u_expiration.getDisplayValue(), true)/86400;

Ok, we already have the related list on the form, I was asking whether I can get this information added to the report?

[Michael Ritchie · Accepted solution]

Again getting a report attached to a task is tricky so hopefully this solution will satisfy your requirements.  Idea is that your list of expired certs will show up as Affected CI's on this custom task.  From there you can drill into them to see details on the certificate and its "Affected by Task" list.  In my testing of this solution, I created an incident, but it doesn't matter which type of task you are creating.

The description field gets set by a list of certificate names:

The Affected CI list is populated with this same list of certificates:

• Navigate to your existing scheduled job and deactivate it.
• Navigate to System Definition\Scheduled Jobs and click New
• Choose "Automatically run a script of your choosing"
• Give it a name that makes sense to you
• Set the schedule to run it
• Paste in the following code.  I put in comments so you know whats happening to make tweaks

// Enter the SysID of the template used to create the task
var templateID = "90bc17044f751b00d1676bd18110c716";

var templateRec = new GlideRecord("sys_template");
if (templateRec.get(templateID)) {
	var taskTableName = templateRec.table;
	
	// Initialize task record and apply template
	var taskRec = new GlideRecord(taskTableName);
	taskRec.newRecord();
	GlideTemplate.get(templateID).apply(taskRec);
	
	// Get a list of expired certificates and add as Affected CI
	var certNameList = [];
	var certRec = new GlideRecord("u_cmdb_ci_certificates");
	certRec.addEncodedQuery("u_expiration_days<30^install_status=102^EQ");
	certRec.query();
	while (certRec.next()) {
		certNameList.push(certRec.getDisplayValue());
		var affectedCI = new GlideRecord("task_ci");
		affectedCI.ci_item = certRec.sys_id;
		affectedCI.task = taskRec.sys_id;
		affectedCI.insert();
	}
	
	// Set list of certificates into task description field
	var certDescription = "List of expired certificates:\n";
	certDescription = certDescription + certNameList.join(", ");
	if (gs.nil(taskRec.description)) {
		taskRec.description = certDescription;
	} else {
		taskRec.description = taskRec.description + "\n" + certDescription;
	}
	
	// Insert Task
	taskRec.insert();
	
} else {
	gs.log("Cannot create expired certificate task because no template found");
}

Please let me know if you have any questions.

[russellprice]

You are awesome personified sir! This works very well.

However I do have a follow on question, how do I control what columns are displayed in affected CIs? there are some fields I would like to include from the u_cmdb_ci_certificates table. Is this what you were saying about defined related lists?

[Michael Ritchie]

Click one of the "hamburger" column icons and choose configure and then list layout.

From there click Configuration Item in green and then in the middle of the slushbucket click the top button "Expand selected reference field" and from there you can choose which fields to include.  Please note that the reference on the affected CI table is to the cmdb_ci table, a parent table to your u_cmdb_ci_certificates table.  This means that custom attributes on your cert table may not be available.

What I meant by defined related list was that you could have another related list tab that shows every related task to the list of affected CI's.  If this list is large it could slow down the loading of the task though.  But definitely possible.

Please mark any of the posts helpful or the correct answer to your question so others can benefit.

[Manik]

Hi Russel,

There is no way OTB to attach a scheduled report to the task but this can be done by copying the attachment to the task through a Scheduled Job by writing a script.

You can search the generated report in sys_attachment table and use GlideSysAttachment to copy the attachment to the record you wish to.

Eg:

var attachment = new GlideSysAttachment();
var att = GlideSysAttachment.copy('incident', 'db9124fbdb9b0b003aa3361b7c961964', 'incident', 'a61301bd4fc047002c4f3cb28110c7a6');

Before this you would need to query sys_attachment table with the table as 'sys_email' and as i am assuming this report generates once in a week, you can add a filter on the name and date too.

Please let me know if it helps you to get started.

Thanks ,

Manik

PS - Please hit like or mark correct and helpful if it helps