Skip to main content

ServiceNow - Custom scheduled reports - pulling in data from different tables using HTML

(Courtesy of Mohammad Nassar)



scheduled report:



event:



example notification



email script:

(function runMailScript(current, template, email, email_action, event) { email.setSubject('IM Summary ' + gs.nowDateTime()); email.addAddress('cc', 'dl_imsummary_xxx@xxxx.net', 'dl_imsummary_xxx@xxxx.net'); var REPORT_EMAIL = new _Report_Emails(); var output = ''; var table_name, encoded_query, field_names; output += '<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">'; output += '<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>'; output += '<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>'; // Active Major Incidents (Non-Dom1) output += '\n<h4>Active Major Incidents (Non-Dom1):</h4>\n'; table_name = 'incident'; encoded_query = 'active=true^major_incident_state=accepted^service_offering.name!=Dom1'; field_names = 'number, priority, state, short_description, service_offering, reassignment_count, sys_updated_on, description, u_service_portfolio'; output += REPORT_EMAIL.queryDataAndPresentInHTML(table_name, encoded_query, field_names); // Active Major Incidents (Dom1) output += '\n\n<h4>Active Major Incidents (Dom1):</h4>\n'; table_name = 'incident'; encoded_query = 'active=true^major_incident_state=accepted^service_offering.name=Dom1'; field_names = 'number, priority, state, short_description, service_offering, reassignment_count, sys_updated_on, description, u_service_portfolio'; output += REPORT_EMAIL.queryDataAndPresentInHTML(table_name, encoded_query, field_names); output += '\n<h4>NOTE: See MIM Comms for latest updates.</h4>'; // Scheduled Ongoing Changes output += '\n\n<h4>Scheduled Ongoing Changes:</h4>'; var today = GlideDate(); var next_day = REPORT_EMAIL.getNextDayDate(); table_name = 'change_request'; encoded_query = 'state!=3^start_dateSTARTSWITH' + today + '^ORstart_dateSTARTSWITH' + next_day; field_names = 'number, priority, state, short_description, service_offering, reassignment_count, sys_updated_on, description, u_service_portfolio'; output += REPORT_EMAIL.queryDataAndPresentInHTML(table_name, encoded_query, field_names); // MIM OOH Rota Cover - 12AM to 8AM var users_covering_rota_at_morning = REPORT_EMAIL.getRotaCoverageUsers(new GlideDate() + ' 01:00:00'); output += '\n\n<h4>MIM OOH Rota Cover - 12AM to 8AM:</h4>'; output += REPORT_EMAIL.queryDataAndPresentInHTML('sys_user', 'sys_idIN' + users_covering_rota_at_morning, 'name, email'); // MIM OOH Rota Cover - 06PM to 12AM var users_covering_rota_at_evening = REPORT_EMAIL.getRotaCoverageUsers(new GlideDate() + ' 20:00:00'); output += '\n\n<h4>MIM OOH Rota Cover - 06PM to 12AM:</h4>'; output += REPORT_EMAIL.queryDataAndPresentInHTML('sys_user', 'sys_idIN' + users_covering_rota_at_evening, 'name, email'); template.print(output); })(current, template, email, email_action, event);






















script include:


var _Report_Emails = Class.create(); _Report_Emails.prototype = Object.extendsObject(AbstractAjaxProcessor, { sendReportEmailForMIM: function() { if (!this.isTodayWorkingDay()) return; gs.eventQueue('.mim.daily_mim_report_email', '', 'DTSIncidentManagement@.NET', 'DTSIncidentManagement@.NET'); }, sendReportEmailForSMGT: function() { if (!this.isTodayWorkingDay()) return; gs.eventQueue('.mim.daily_smgt_report_email', '', 'dl_smtg_eod_to@.net', 'dl_smtg_eod_to@.net'); }, getRotaCoverageUsers: function(date_time) { var group_sys_id = gs.getProperty('.ooh.incident.managers'); var rota_coverage_users = new OnCallRotationSNC().whoIsOnCall(group_sys_id, '', '', date_time); var rota_users = []; for (var i=0; i<rota_coverage_users.length; i++) rota_users.push(rota_coverage_users[i].userId); return rota_users; }, queryDataAndPresentInHTML: function(table_name, encoded_query, field_names) { var records = this.queryData(table_name, encoded_query); var presentable_data = this.representDataInHTML(records, field_names); return presentable_data; }, queryData: function(table_name, encoded_query) { var rec = new GlideRecord(table_name); rec.addEncodedQuery(encoded_query); rec.query(); return rec; }, representDataInHTML: function(rec, field_names) { var data = ''; field_names = field_names.split(','); if (rec.hasNext()) { data += '<style> table {width: 100%;} table, th, td {border: 1px solid lightgrey; border-collapse: collapse;} th, td {padding: 5px;} th {font-weight: bold;} </style>'; data += '<table class="table table-striped table-bordered table-hover">'; } var while_loop_accessed = false; while (rec.next() && field_names.length != 0) { if (while_loop_accessed == false) { while_loop_accessed = true; for (var i = 0; i < field_names.length; i++) { if (i == 0) data += '<thead><tr>'; var field_label = rec.getElement(field_names[i].trim()).getLabel(); data += '<th>' + field_label + '</th>'; if (i == field_names.length -1) data += '</tr></thead>'; } } for (var i = 0; i < field_names.length; i++) { if (i == 0) data += '<tbody><tr>'; var field_label = rec.getElement(field_names[i].trim()).getLabel(); var field_value = rec.getElement(field_names[i].trim()).getDisplayValue(); var style = this.getStyle(rec, field_label, field_value); if (field_label == 'Number') field_value = this.getURI(rec, field_value); data += '<td' + style + '>' + field_value + '</td>'; if (i == field_names.length -1) data += '</tr></tbody>'; } } if (data != '') data += '</table>'; else data = 'No data available'; return data; }, getURI: function(record, field_value) { var uri = 'https://' + gs.getProperty('instance_name') + '.service-now.com/' + record.getTableName() + '.do?sys_id=' + record.sys_id; return '<a href="' + uri + '">' + field_value + '</a>'; }, getStyle: function(record, field_label, field_value) { if (record == null || record == undefined) return ''; field_label = field_label.toLowerCase(); field_value = field_value.toLowerCase(); var table_name = record.getTableName(); var bg_colour = ' style="background-color: '; if (table_name == 'incident' && field_label == 'number') return bg_colour + this.getAlternativeColour(record.u_rag_status) + ';"'; if (table_name == 'change_request' && field_label == 'state') { if (field_value == 'new') // If state is 'New' return bg_colour + '#5e95ed' + ';"'; if (field_value == 'access') // If state is 'Assess' return bg_colour + 'yellow' + ';"'; if (field_value == 'implement') // If state is 'Implement' return bg_colour + '#24ba0e' + ';"'; } return ''; }, getAlternativeColour: function(colour) { colour = colour.toLowerCase(); if (colour == 'green') return 'LimeGreen'; if (colour == 'amber') return 'orange'; if (colour == 'red') return 'tomato'; return colour; }, getNextDayDate: function() { var next_day = GlideDate(); if (this.isTodayFriday()) next_day.addDays(3); else next_day.addDays(1); return next_day; }, isTodayFriday: function() { var today = GlideDate(); var week_day = today.getDayOfWeekLocalTime(); var next_day = GlideDate(); return (week_day == 5); }, isTodayWorkingDay: function() { var today = GlideDate(); var week_day = today.getDayOfWeekLocalTime(); var next_day = GlideDate(); return (week_day != 6 || week_day != 7); }, type: '_Report_Emails' });

Comments

Popular posts from this blog

URL link in addInfoMessage

var ga=new GlideAjax('gld_HR_ajax'); ga.addParam('sysparm_name', 'checkEmployeeNumber_hrProfile'); ga.addParam('sysparm_hrprofilenumber', g_form.getValue('number')); ga.addParam('sysparm_employeenumber', newValue); ga.getXMLAnswer(function(answer) { if (answer!='undefined' && answer!=''){ var navURL="<a style='text-decoration:underline;color:blue' href=hr_profile.do?sysparm_query=number=" + answer + ">" + answer + "</a><img width='3' src='images/s.gif'/>"; var sMsg='The employee number entered already exists on another HR Profile ' + navURL; //alert(sMsg); g_form.showErrorBox('employee_number', 'error - please check'); g_form.addInfoMessage(sMsg); } });

GlideRecord setValue

setValue(String name, Object value) Sets the specified field to the specified value. Normally a script would do a direct assignment, for example,  gr.category = value . However, if in a script the element name is a variable, then  gr.setValue(elementName, value)  can be used. When setting a value, ensure the data type of the field matches the data type of the value you enter. This method cannot be used on journal fields. If the value parameter is null, the record is not updated, and an error is not thrown https://developer.servicenow.com/app.do#!/api_doc?v=madrid&id=r_GlideRecord-setValue_String_Object