Export RITMs with variables into Excel

[georgeprad]

I have a requirement to export all the active RITMs to excel. I want to get the variables and values for each RITM and also want to get the SCTASKS numbers associated with them and the comments and worknotes from both the RITM and all the SCTASKS of that RITM. I could not find anything that gave me this export without creating custom tables.

[georgeprad · Accepted solution]

I have wrttent the following code that can be used as a background script to help achieve this. I am only pulling active RITMs with atleast one open task. You may change the filters as per your need. You may also need to check the tables the variables reference as this could bvary between instances. I am attaching this to the user record from where it can be downloaded. I have also put a limit on each file as I ran into issues downloading this data without putting a limit.

(function() {
var MAX_ATTACHMENT_BYTES = 14 * 1024 * 1024; // 14 MB
var userSysId = gs.getUserID();
var userTable = 'sys_user'; // Attach to current user

// Helper: Check if a value looks like a sys_id
function isSysID(val) {
return typeof val === 'string' && /^[0-9a-fA-F]{32}$/.test(val);
}

// Sys ID cache for performance
var sysIdCache = {};

function resolveSysID(sysID) {
if (!sysID || !isSysID(sysID)) return sysID;
if (sysIdCache[sysID]) return sysIdCache[sysID];

var tablesToCheck = [
'sys_user', 'cmn_location', 'sc_cat_item', 'sc_req_item',
'incident', 'change_request', 'cmdb_ci_service',
'sys_user_group', 'cmdb_ci_computer', 'cmn_cost_center',
'cmn_department', 'core_company', 'u_services'
];

for (var i = 0; i < tablesToCheck.length; i++) {
var gr = new GlideRecord(tablesToCheck[i]);
if (gr.isValid() && gr.get(sysID)) {
var display = gr.getDisplayValue();
sysIdCache[sysID] = display;
return display;
}
}

sysIdCache[sysID] = sysID; // fallback
return sysID;
}

// Get all comments for a RITM
function getComments(ritmSysID) {
var entries = [];
var grJournal = new GlideRecord('sys_journal_field');
grJournal.addQuery('element_id', ritmSysID);
grJournal.addQuery('element', 'IN', 'comments,work_notes');
grJournal.orderBy('sys_created_on');
grJournal.query();

while (grJournal.next()) {
var val = grJournal.value.toString().replace(/(\r\n|\n|\r)/g, ' ');
entries.push(val);
}

if (entries.length === 0) return '';
var combined = entries.join(' | ');
combined = '"' + combined.replace(/"/g, '""') + '"';
return combined;
}

// Get all worknotes from SCTASKs for a RITM

function getTaskWorknotes(ritmSysID) {
var entries = [];
var taskGR = new GlideRecord('sc_task');
taskGR.addQuery('request_item', ritmSysID);
taskGR.query();

while (taskGR.next()) {
var journalGR = new GlideRecord('sys_journal_field');
journalGR.addQuery('element_id', taskGR.sys_id);
journalGR.addQuery('element', 'work_notes');
journalGR.orderBy('sys_created_on');
journalGR.query();
while (journalGR.next()) {
var val = journalGR.value.toString().replace(/(\r\n|\n|\r)/g, ' ');
entries.push(val);
}
}

if (entries.length === 0) return '';
var combined = entries.join(' | ');
combined = '"' + combined.replace(/"/g, '""') + '"';
return combined;
}

// Get SCTASK numbers and sys_ids for a RITM

function getTaskNumbersAndSysIds(ritmSysID) {
var pairs = [];
var taskGR = new GlideRecord('sc_task');
taskGR.addQuery('request_item', ritmSysID);
taskGR.query();

while (taskGR.next()) {
pairs.push(taskGR.number.toString() + ' - ' + taskGR.sys_id.toString());
}

if (pairs.length === 0) return '';
var combined = pairs.join(' | ');
combined = '"' + combined.replace(/"/g, '""') + '"';
return combined;
}

var ritmGR = new GlideRecord('sc_req_item');
ritmGR.addQuery('active', true);
ritmGR.orderByDesc('sys_created_on');
ritmGR.query();

var csvHeader = 'RITM Number,RITM Sys ID,REQ Number,SCTASKs (Number - Sys ID),RITM Item Name,Requested For,Opened By,Opened At,Assignment Group,Assigned To,Comments,Worknotes from Tasks,Variable Name,Variable Value\n';
var csvLines = [csvHeader];
var attachmentIndex = 1;

while (ritmGR.next()) {
// Only include RITMs with active SCTASKs
var sctaskGR = new GlideRecord('sc_task');
sctaskGR.addQuery('request_item', ritmGR.sys_id);
sctaskGR.addQuery('active', true);
sctaskGR.query();
if (!sctaskGR.hasNext()) continue;

var ritmNumber = ritmGR.number.toString();
var reqNumber = ritmGR.request.number.toString();
var taskNumbersSysIds = getTaskNumbersAndSysIds(ritmGR.sys_id);
var itemName = ritmGR.cat_item ? ritmGR.cat_item.getDisplayValue() : '';
var requestedFor = ritmGR.request.requested_for.getDisplayValue();
var openedBy = ritmGR.opened_by ? ritmGR.opened_by.getDisplayValue() : '';
var openedAt = ritmGR.opened_at ? ritmGR.opened_at.getDisplayValue() : '';
var assignmentGroup = ritmGR.assignment_group ? ritmGR.assignment_group.getDisplayValue() : '';
var assignedTo = ritmGR.assigned_to ? ritmGR.assigned_to.getDisplayValue() : '';
var comments = getComments(ritmGR.sys_id);
var taskWorknotes = getTaskWorknotes(ritmGR.sys_id);

// Variables
var m2m = new GlideRecord('sc_item_option_mtom');
m2m.addQuery('request_item', ritmGR.sys_id);
m2m.query();

if (!m2m.hasNext()) {
csvLines.push([ritmNumber, ritmGR.sys_id, reqNumber, taskNumbersSysIds, itemName, requestedFor, openedBy, openedAt, assignmentGroup, assignedTo, comments, taskWorknotes, '', ''].join(','));
} else {
while (m2m.next()) {
var opt = m2m.sc_item_option.getRefRecord();
var varName = opt.item_option_new.getDisplayValue();
var varValue = opt.value ? opt.value.toString().replace(/(\r\n|\n|\r|,)/g, ' ') : '';
varValue = resolveSysID(varValue);

csvLines.push([ritmNumber, ritmGR.sys_id, reqNumber, taskNumbersSysIds, itemName, requestedFor, openedBy, openedAt, assignmentGroup, assignedTo, comments, taskWorknotes, varName, varValue].join(','));
}
}

// Check attachment size
var csvContent = csvLines.join('\n');
if (csvContent.length >= MAX_ATTACHMENT_BYTES) {
var grUser = new GlideRecord(userTable);
if (grUser.get(userSysId)) {
var attachment = new GlideSysAttachment();
attachment.write(grUser, 'RITM_Export_Part_' + attachmentIndex + '.csv', 'text/csv', csvContent);
gs.info('✅ Created attachment RITM_Export_Part_' + attachmentIndex + '.csv with ' + csvLines.length + ' lines');
}
attachmentIndex++;
csvLines = [csvHeader]; // reset for next batch
}
}

// Write final attachment if remaining lines
if (csvLines.length > 1) {
var grUser = new GlideRecord(userTable);
if (grUser.get(userSysId)) {
var attachment = new GlideSysAttachment();
attachment.write(grUser, 'RITM_Export_Part_' + attachmentIndex + '.csv', 'text/csv', csvLines.join('\n'));
gs.info('✅ Created final attachment RITM_Export_Part_' + attachmentIndex + '.csv');
}
}

gs.info('✅ Export complete. Total RITMs processed: ' + ritmGR.getRowCount());
})();

[Connectmustaq]

Hi @georgeprad ,

 

To export all active RITMs along with their variables, associated SCTASK numbers, comments, and worknotes from both RITMs and their SCTASKs, here are the practical options and approaches, since ServiceNow does not provide an out-of-the-box report combining all these details into a single export:

Key Points and Approaches

1. Variables Export
   Variables for each RITM are stored in a related table sc_item_option or sc_item_option_mtom. Exporting variables directly with RITM records via report or list export is limited in ServiceNow.

   • One workaround is to script a background export that queries variables per RITM and flattens them into CSV columns or key-value strings.

   • Alternatively, create a scripted report or use an integration (like data export via REST API) to gather variables and add them as concatenated text fields.

2. SCTASK association
   SCTASKs referencing RITMs are related via the request_item field on sc_task. Export SCTASK numbers via join queries or GlideRecord script, linking SCTASK numbers back to parent RITMs.

3. Comments and Worknotes
   These are journal fields stored on both RITM (sc_req_item) and SCTASK (sc_task).

   • Exporting journal fields (comments/worknotes) requires special handling as they don't export well in standard reports.

   • Use the Table API with sysparm_display_value=true to retrieve journal field contents or query the sys_journal_field table for entries linked to RITMs and SCTASKs.

4. No Single OOB Report
   There's no prebuilt report or UI export combining all this data simultaneously without custom tables or scripting.

Recommended Steps

• Build a background script or REST API script that:

  • Queries all active RITMs.

  • For each RITM, queries related sc_item_option for variables and flattens data.

  • Queries SCTASKs (sc_task) linked to that RITM, capturing their numbers and comments/worknotes.

  • Retrieves RITM comments and worknotes.

  • Structures all into CSV format and writes as an attachment or outputs for Excel.

• Reference scripts in the community such as copying RITM variables into SCTASK worknotes can be adapted to export variables similarly.​

• For comments/worknotes export, refer to official KB [KB0860915] which explains extracting journal fields via REST API or scripts.​

Summary

The export you want is feasible with one-time scripting or integration work that consolidates:

• RITM fields + variables (via sc_item_option).

• SCTASK numbers linked by request_item.

• Comments and worknotes from RITM and each SCTASK (via journal field queries).

This results in a rich Excel-friendly CSV export without creating custom tables but requires scripting or API use beyond standard reporting.

If it is helpful, please hit the thumbs button and accept the correct solution by referring to this solution in the future it will be helpful to them.

Thanks & Regards,

Mohammed Mustaq Shaik