This article is a supplement to the ServiceNow documentation. For full documentation please refer ServiceNow official website
Checkout our NEW Video Channel you can like and subscribe too!

Use case

Customer wants an excel as a attachment in the mail with the list of all users in SNOW who are using the below Dashboards at what time.

  • Test-DB
  • Test-Backup

scheduledjob190120216.JPG

Solution

To achive this we will create a scheduled job

  1. Navigate to System Defination > scheduled job scheduledjob190120211.JPG

  2. Click on New Button scheduledjob190120212.JPG

  3. Select “Automatically run a script your choosing

scheduledjob190120213.JPG

  1. Fillup fileds as required. scheduledjob190120214.JPG

scheduledjob190120215.JPG

 var SUBJECT = "Dashboards Reports";
var RECIPENTS = ["c.mukherjee88@gmail.com"];
//var EMAIL_BODY = "Please note that this mail is for users information in SNOW who are all using the below Dahsboards :Test-DB ; Test-Backup";
var REPORT_COLUMNS = ["Dashboard Name", "Viewer", "Time(UTC)"];
var CSV_NAME = "Dashboard Details.csv";
//var DASHBOARD_VIEW = "CMDB Dashboard - CMDB View";
var DASHBOARD_VIEW = ["Test-DB", "Test- Backup"];
var EMAIL_BODY = "Please find Dashboard Usage information for " + DASHBOARD_VIEW.toString();

//Inserting record in sys_email table
var mailGr = new GlideRecord('sys_email');
mailGr.initialize();
mailGr.type = 'send-ignored';
mailGr.notification_type = 'SMTP';
mailGr.subject = SUBJECT;
mailGr.recipients = RECIPENTS.toString();
mailGr.body = '<html><head></head><body>' + EMAIL_BODY + '<div>&nbsp;</div><div style="display:inline">PFA the csv.</div></body></html>';
mailGr.content_type = 'multipart/mixed';
mailGr.insert();

var csvData = REPORT_COLUMNS.toString() + "\r\n";
var content = '';

//Extracting data from Navigator history table
var gr = new GlideRecord('sys_ui_navigator_history');
gr.addEncodedQuery("descriptionIN" + DASHBOARD_VIEW.toString() + "^sys_created_onONLast 60 days@javascript:gs.beginningOfLast60Days()@javascript:gs.endOfLast60Days()^title=Dashboard");
gr.query();
while (gr.next()) {
    content = '';
    content = content + gr.description + ",";
    content = content + gr.user.getDisplayValue() + ",";
    content = content + gr.sys_created_on + "\r\n";
    gs.print(content);
    csvData = csvData + content;
}

//Inserting in sys_attachement table of mailgr record.
var sa = new GlideSysAttachment();
sa.write(mailGr, CSV_NAME, 'application/csv', csvData);

var attachmentget = new GlideSysAttachment();
var agr = attachmentget.getAttachments('sys_email', mailGr.sys_id);
if (agr.next()) {
    var sysEmailAttachment = new GlideRecord('sys_email_attachment');
    sysEmailAttachment.initialize();
    sysEmailAttachment.attachment = agr.sys_id;
    sysEmailAttachment.file_name = agr.file_name.toString();
    sysEmailAttachment.source = 'notification';
    sysEmailAttachment.content_disposition = 'attachment';
    sysEmailAttachment.email = mailGr.sys_id;
    sysEmailAttachment.insert();
}

mailGr.type = 'send-ready';
mailGr.update();

Then RECIPENTS(c.mukherjee88@gmail.com) will receive a mail with an excel with below details:

scheduledjob190120216.JPG

    Content