Hi all i have a requirement to create a scheduled job followed by Notification email script to ,email to the each manager which contains pending incident and Change request counts of his respective team members. I created that part and that is working fine as shown below.
Now i need to update this like , those counts should be able to click, which i click it should redirect to respective incidents list view. Thanks in advance. Can anyone help.
Below is the notification script.
(function runMailScript(current, template, email, email_action, event) {
// Function to get incidents aging data for users under a specific manager
function getIncidentsAging(users) {
var agingData = {};
users.forEach(function(user) {
agingData[user.name] = {
'0-5 Days': 0,
'6-10 Days': 0,
'11-15 Days': 0,
'16-30 Days': 0,
'31-60 Days': 0,
'>60 Days': 0
};
// Query incidents
var gr = new GlideRecord('incident');
gr.addQuery('assigned_to', user.sys_id);
gr.addQuery('assignment_group', '!=', ''); // Assignment group is not empty
gr.addQuery('assigned_to', '!=', ''); // Assigned to is not empty
gr.addQuery('state', 'IN', '2,3'); // Incident state is 'In Progress' or 'Open'
gr.query();
var incidentCount = 0;
while (gr.next()) {
incidentCount++;
var assignedDate;
// Use the last_assigned_date directly
if (gr.u_last_assigned_on) {
assignedDate = new GlideDateTime(gr.u_last_assigned_on);
} else {
// Fallback if no last_assigned_date is found, using created date as a last resort
assignedDate = new GlideDateTime(gr.getValue('sys_created_on'));
}
var now = new GlideDateTime();
var daysOld = Math.floor((now.getNumericValue() - assignedDate.getNumericValue()) / (1000 * 60 * 60 * 24));
if (daysOld <= 5) {
agingData[user.name]['0-5 Days']++;
} else if (daysOld <= 10) {
agingData[user.name]['6-10 Days']++;
} else if (daysOld <= 15) {
agingData[user.name]['11-15 Days']++;
} else if (daysOld <= 30) {
agingData[user.name]['16-30 Days']++;
} else if (daysOld <= 60) {
agingData[user.name]['31-60 Days']++;
} else {
agingData[user.name]['>60 Days']++;
}
}
gs.info('Incident count for user ' + user.name + ': ' + incidentCount);
gs.info('Aging data for user ' + user.name + ': ' + JSON.stringify(agingData[user.name]));
});
return agingData;
}
// Function to get change request aging data for users under a specific manager
function getChangeRequestsAging(users) {
var agingData = {};
users.forEach(function(user) {
agingData[user.name] = {
'0-5 Days': 0,
'6-10 Days': 0,
'11-15 Days': 0,
'16-30 Days': 0,
'31-60 Days': 0,
'>60 Days': 0
};
// Query change requests
var gr = new GlideRecord('change_request');
gr.addQuery('assigned_to', user.sys_id);
gr.addQuery('assignment_group', '!=', ''); // Assignment group is not empty
gr.addQuery('assigned_to', '!=', ''); // Assigned to is not empty
gr.addQuery('state', '-5'); // Change request state is 'Open'
gr.query();
var changeRequestCount = 0;
while (gr.next()) {
changeRequestCount++;
var assignedDate;
if (gr.u_last_assigned_on) {
assignedDate = new GlideDateTime(gr.u_last_assigned_on); // Use the date when the assignment happened
} else {
// Fallback if no audit record is found, using created date as a last resort
assignedDate = new GlideDateTime(gr.getValue('sys_created_on'));
}
var now = new GlideDateTime();
var daysOld = Math.floor((now.getNumericValue() - assignedDate.getNumericValue()) / (1000 * 60 * 60 * 24));
if (daysOld <= 5) {
agingData[user.name]['0-5 Days']++;
} else if (daysOld <= 10) {
agingData[user.name]['6-10 Days']++;
} else if (daysOld <= 15) {
agingData[user.name]['11-15 Days']++;
} else if (daysOld <= 30) {
agingData[user.name]['16-30 Days']++;
} else if (daysOld <= 60) {
agingData[user.name]['31-60 Days']++;
} else {
agingData[user.name]['>60 Days']++;
}
}
gs.info('Change request count for user ' + user.name + ': ' + changeRequestCount);
gs.info('Aging data for user ' + user.name + ': ' + JSON.stringify(agingData[user.name]));
});
return agingData;
}
// Function to get users under a manager
function getUsersUnderManager(managerSysId) {
var users = [];
var gr = new GlideRecord('sys_user');
gr.addQuery('manager', managerSysId); // Querying users who report to this manager
gr.query();
while (gr.next()) {
users.push({
'sys_id': gr.sys_id.toString(),
'name': gr.getDisplayValue()
});
}
gs.info('Users under manager ' + managerSysId + ': ' + JSON.stringify(users));
return users;
}
// Function to check if any category has a count greater than zero
function hasNonZeroCount(data) {
for (var key in data) {
if (data[key] > 0) {
return true;
}
}
return false;
}
// Function to format and send email content
function formatAndSendEmail(managerSysId, managerName) {
var users = getUsersUnderManager(managerSysId);
var incidentData = getIncidentsAging(users);
var changeRequestData = getChangeRequestsAging(users);
gs.info('Formatting email content for manager: ' + managerName);
template.print('<p>Dear ' + managerName + ',</p>');
template.print('<p>Below is the count of incidents and change requests assigned to your team members categorized by their age in days.</p>');
// Incident Table
template.print('<h3>Incidents</h3>');
template.print('<table style="border-collapse: collapse; width: 100%;">');
template.print('<tr style="background-color: #f2f2f2;">');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">Name</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">0-5 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">6-10 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">11-15 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">16-30 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">31-60 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">>60 Days</th>');
template.print('</tr>');
for (var user in incidentData) {
var data = incidentData[user];
if (hasNonZeroCount(data)) { // Check if any category has a non-zero count
template.print('<tr style="background-color: #ffffff;">');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + user + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['0-5 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['6-10 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['11-15 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['16-30 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['31-60 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['>60 Days'] + '</td>');
template.print('</tr>');
}
}
template.print('</table>');
var hasChangeRequestData = false;
for (var user in changeRequestData) {
if (hasNonZeroCount(changeRequestData[user])) {
hasChangeRequestData = true;
break; // Exit loop if at least one user has non-zero counts
}
}
if (hasChangeRequestData) {
// Only print table if there are non-zero counts
template.print('<h3>Change Requests</h3>');
template.print('<table style="border-collapse: collapse; width: 100%;">');
template.print('<tr style="background-color: #f2f2f2;">');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">Name</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">0-5 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">6-10 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">11-15 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">16-30 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">31-60 Days</th>');
template.print('<th style="border: 2px solid #dddddd; padding: 10px; text-align: left;">>60 Days</th>');
template.print('</tr>');
for (var user in changeRequestData) {
var data = changeRequestData[user];
if (hasNonZeroCount(data)) { // Check if any category has a non-zero count
template.print('<tr style="background-color: #ffffff;">');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + user + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['0-5 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['6-10 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['11-15 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['16-30 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['31-60 Days'] + '</td>');
template.print('<td style="border: 2px solid #dddddd; padding: 10px; text-align: left;">' + data['>60 Days'] + '</td>');
template.print('</tr>');
}
}
template.print('</table>');
}
}
// Run the function to format and send email
var managerSysId = current.manager; // Get manager Sys ID from current record
var managerName = current.manager.getDisplayValue();
formatAndSendEmail(managerSysId, managerName);
})(current, template, email, email_action, event);
