I have a complex scenario at my hands here what client wants from me is an api endpoint to hit from their third party app to the servicenow instance and they want when they hit this api all the attachments related to sn_hr_core_case table should be downloaded on their local machine now what approach i have followed is as of now is created a scripted rest api which is calling two script includes since the major problem is the ammount of data is more that 10k on my instance what i am doing is first creating the batches of around 1000 attachments each and then zipping the files the zipping part is done in custom action which is going in the loop when rest api hits and at max it can process upto 3000 attachments that is three batches of zip files more than that the rest api execution time limit is going over
Rest api Code -
(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
try {
var builder = new HRAttachmentBatchBuilder();
var executor = new HRAttachmentZipExecutor();
var instanceURL = gs.getProperty("glide.servlet.uri");
var zipFiles = [];
var totalAttachments = 0;
while (true) {
var batch = builder.nextBatch();
if (!batch || !batch.ids || batch.ids.length === 0) {
break;
}
try {
var zipResult = executor.execute(batch);
if (!zipResult || !zipResult.success) {
zipFiles.push({
batch: batch.batch_number,
status: "failure",
message: zipResult ? zipResult.message : "Unknown Error"
});
continue;
}
totalAttachments += zipResult.attachment_count;
zipFiles.push({
batch: batch.batch_number,
status: "success",
file_name: zipResult.file_name,
sys_id: zipResult.sys_id,
attachment_count: zipResult.attachment_count,
download_link:
instanceURL +
"api/now/attachment/" +
zipResult.sys_id + "/file"
});
} catch (batchEx) {
zipFiles.push({
batch: batch.batch_number,
status: "failure",
message: batchEx.message || batchEx.toString()
});
}
}
response.setStatus(200);
response.setBody({
status: "success",
total_batches: zipFiles.length,
total_attachments: totalAttachments,
batch_size: parseInt(
gs.getProperty("x_hr_export.batch_size", "1000"),
10
),
zip_files: zipFiles
});
} catch (ex) {
gs.error("HR Attachment Export API Error");
gs.error(ex);
response.setStatus(500);
response.setBody({
status: "failure",
message: ex.message || ex.toString()
});
}
})(request, response);
Zipper Script include -
Zipper Script include -
var HRAttachmentZipExecutor = Class.create();
HRAttachmentZipExecutor.prototype = {
initialize: function() {
this.ACTION_NAME = "global.zip_files_on_hr_case";
},
execute: function(batch) {
if (!batch || !batch.ids || batch.ids.length === 0) {
return null;
}
//--------------------------------------------------
// Build GlideRecord from sys_ids
//--------------------------------------------------
var attachmentGR = new GlideRecord("sys_attachment");
attachmentGR.addQuery("sys_id", "IN", batch.ids.join(","));
attachmentGR.query();
var count = attachmentGR.getRowCount();
//--------------------------------------------------
// Re-query because getRowCount() consumes query
//--------------------------------------------------
attachmentGR.query();
//--------------------------------------------------
// Execute Flow Action
//--------------------------------------------------
var inputs = {};
inputs.attach = attachmentGR;
inputs.zip_file_name =
"HR_Cases_Attachments_Batch_" +
batch.batch_number +
".zip";
var start = new GlideDateTime();
try {
var result = sn_fd.FlowAPI
.getRunner()
.action(this.ACTION_NAME)
.inForeground()
.withInputs(inputs)
.run();
var end = new GlideDateTime();
var duration =
(end.getNumericValue() - start.getNumericValue()) / 1000;
gs.log("Batch " + batch.batch_number +
" completed in " + duration + " seconds.");
} catch (ex) {
var failEnd = new GlideDateTime();
var failDuration =
(failEnd.getNumericValue() - start.getNumericValue()) / 1000;
gs.log("Batch " + batch.batch_number +
" failed after " + failDuration +
" seconds. Error: " + ex);
return {
success: false,
message: ex.getMessage ? ex.getMessage() : ex.toString()
};
}
//--------------------------------------------------
// Read outputs
//--------------------------------------------------
var outputs = result.getOutputs();
if (!outputs || !outputs.zipfile) {
return {
success: false,
message: "Flow did not return a ZIP attachment."
};
}
var zipAttachment = outputs.zipfile();
//--------------------------------------------------
// Return clean object
//--------------------------------------------------
return {
success: true,
sys_id: zipAttachment.getUniqueValue(),
file_name: zipAttachment.getDisplayValue("file_name"),
attachment_count: count,
batch_size: batch.ids.length,
runtime_seconds: duration
};
},
type: "HRAttachmentZipExecutor"
};
Batch Builder Script include -
Batch Builder Script include -
var HRAttachmentBatchBuilder = Class.create();
HRAttachmentBatchBuilder.prototype = {
initialize: function () {
this.TABLE_NAME = gs.getProperty("x_hr_export.table_name");
this.BATCH_SIZE = parseInt(
gs.getProperty("x_hr_export.batch_size", "1000"),
10
);
this._caseCache = {};
this._batchNumber = 0;
this._gr = new GlideRecord("sys_attachment");
this._gr.addQuery("table_name", this.TABLE_NAME);
this._gr.orderBy("sys_created_on");
this._gr.orderBy("sys_id");
this._gr.query();
},
nextBatch: function () {
var ids = [];
var originalNames = {};
var count = 0;
while (this._gr.next()) {
var attachmentId = this._gr.getUniqueValue();
var tableSysId = this._gr.getValue("table_sys_id");
if (!this._caseCache[tableSysId]) {
var hrCase = new GlideRecord(this.TABLE_NAME);
if (!hrCase.get(tableSysId)) {
continue;
}
this._caseCache[tableSysId] = hrCase.getValue("number");
}
var caseNumber = this._caseCache[tableSysId];
var originalName = this._gr.getValue("file_name");
originalNames[attachmentId] = originalName;
// ======================================================
// TEMPORARILY DISABLED
// We are checking if update() is affecting the cursor.
// ======================================================
/*
if (originalName.indexOf(caseNumber + "_") !== 0) {
this._gr.setValue(
"file_name",
caseNumber + "_" + originalName
);
this._gr.update();
}
*/
ids.push(attachmentId);
count++;
if (count >= this.BATCH_SIZE)
break;
}
if (ids.length === 0) {
return null;
}
this._batchNumber++;
return {
ids: ids,
originalNames: originalNames,
batch_number: this._batchNumber
};
},
type: "HRAttachmentBatchBuilder"
};
Custom action screenshots -
Custom action screenshots -
my major concern here is wthere architecuture wise this approach is going to hold or not and if not what are the possible approach i can take for such large ammount of data
