Email Digest

PratikshaK53479 · 3 weeks ago

I have a requirement to implement a weekly email digest.

We have a custom table ABC that stores audit records whenever an Incident is created or updated. A separate record is created in the ABC table for each field update. The table contains the following information:

Field Name
Reference Record (Incident)
Updated By
Date/Time
Old Value
New Value

The Incident table contains a Category field (e.g., Network, Hardware).

Requirement:

A weekly email digest should be sent every Friday.
One email should be generated per Category.
The email should contain all ABC records created during the week for incidents belonging to that category.

Example:

For Network category, if 5 incidents are updated resulting in 35 records in the ABC table, a single email should be sent containing all 35 records.
For Hardware category, if 2 incidents are updated resulting in 5 records in the ABC table, a separate email should be sent containing those 5 records.

Has anyone implemented a similar requirement using Email Digest? I am looking for guidance on how to group records by Category and send separate emails for each category. Any help would be greatly appreciated.
Thanks in advance.

RaghavendraGund · 2 weeks ago

Hello @PratikshaK53479

I replicated this scenario in my PDI to check, and a scheduled job with one email per category works. Email DIgest alone can't meet this requirement.

Why Email Digest doesn't meet this requirement
--> "Allow Digest" field on the email Notification combines notifications by target record(single or multiple). There is no option to group by a field value like incident category.(Refer Screenshot1)

What I did on my PDI:
--> I created a "u_abc_audit" table that stores audit records whenever an Incident is created or updated, i created this table to match with your requirement(Refer Screenshot2)
-->I created a Business rule to write audit rows to "u_abc_audit" table just to meet your requirement
Now, the important part

I created a Scheduled Job which runs on Friday and counts ABC records from the last 7 days, grouped by the incident’s Category (GlideAggregate can group on a dot-walked field).(Refer Screenshot3)
For each category, the job fires one event, with parm1 = category and parm2 = start of the window.(Refer Screenshot4)
One notification on the ABC table listens for that event. (Refer Screenshot5)
A mail script pulls all ABC rows for that category and builds the table in the email body. It also sets the subject with the category name.(Refer Screenshot6)

Scheduled Job script:

var start = new GlideDateTime();
start.addDaysUTC(-7);
var since = start.getValue();

var ga = new GlideAggregate('u_abc_audit');
ga.addQuery('sys_created_on', '>=', since);
ga.addAggregate('COUNT');
ga.groupBy('u_incident.category');
ga.query();

while (ga.next()) {
    var cat = ga.getValue('u_incident.category');
    if (!cat) continue;

    var rec = new GlideRecord('u_abc_audit');
    rec.addQuery('u_incident.category', cat);
    rec.addQuery('sys_created_on', '>=', since);
    rec.setLimit(1);
    rec.query();
    if (rec.next()) {
        gs.eventQueue('abc.weekly.digest', rec, cat, since);
    }
}

Mail script (called in the notification body as ${mail_script:abc_weekly_digest}):

(function runMailScript(current, template, email, email_action, event) {
    var category = event.parm1;
    var since = event.parm2;

    email.setSubject('Weekly Incident Audit Digest - ' +
                     current.u_incident.category.getDisplayValue());

    var gr = new GlideRecord('u_abc_audit');
    gr.addQuery('u_incident.category', category);
    gr.addQuery('sys_created_on', '>=', since);
    gr.orderBy('u_incident.number');
    gr.orderBy('sys_created_on');
    gr.query();

    template.print('<p>' + gr.getRowCount() + ' changes recorded this week.</p>');
    template.print('<table border="1" cellpadding="4" style="border-collapse:collapse;">' +
        '<tr><th>Incident</th><th>Field</th><th>Old Value</th><th>New Value</th>' +
        '<th>Updated By</th><th>Date/Time</th></tr>');
    while (gr.next()) {
        template.print('<tr><td>' + gr.u_incident.number + '</td><td>' + gr.u_field_name +
            '</td><td>' + gr.u_old_value + '</td><td>' + gr.u_new_value +
            '</td><td>' + gr.sys_created_by + '</td><td>' +
            gr.sys_created_on.getDisplayValue() + '</td></tr>');
    }
    template.print('</table>');
})(current, template, email, email_action, event);

Change the table and field names to match your ABC table. In my test, u_incident is the reference to Incident.

Digest timing comes from Digest Interval records (1 Hour, 4 Hours, 1 Day, 7 Days). These are durations only, with no option to pick a weekday, so “every Friday” can’t be set.(Screenshot8)

Result: with changes on incidents in 4 categories, one run produced 4 emails, one per category, each listing only that category’s records.(Screenshot7)

If this helps, please mark it Helpful or Accept as Solution.

Regards,
Raghavendra G