Dynamic cases for sending out an email

[Utkarsha]

Hello All,

I need to write a scheduled job script to send email  when is there is no Insert/update of records in the list of tables.

table,operations, User to notify and Number of days should be accepted dynamically from a property.

Email should contain a table with the list of tables where there is no interaction in last n number of days.

Script should be dynamic enough to handle below scenarios

Example cases

Case 1

Table : Company
No of days : 2
Users to be notified :user 5

Case 2

Table :Exception
No of days 5
users to be notified : user 1, user2

Case 3

Table 3

Table : User
No of days : 1
users to be notified : user 3 , user 4
I have created a single system property for this in json format

{
"table": "sys_user","core_company",
"operations": "Insert","update",
"user to notify": "",
"number of days": "7",
}
How can I handle the given scenarios from this single property using email script, notification and scheduled script?

Any thoughts if anyone can share?

Thank you in advance!

[Ruhee Gupta]

Hi Utkarsha,

Can you try with below code -

// Define the list of tables to monitor
var tablesToMonitor = ['table1', 'table2', 'table3'];

// Get the number of days from a property (assuming the property name is 'sys_property_name')
var numberOfDays = gs.getProperty('sys_property_name');

// Get the current date and calculate the date n days ago
var currentDate = new GlideDateTime();
var nDaysAgo = new GlideDateTime();
nDaysAgo.subtractDaysUTC(numberOfDays);

// Initialize an empty array to store tables with no interactions
var tablesWithNoInteractions = [];

// Loop through each table to check for interactions
tablesToMonitor.forEach(function(table) {
var gr = new GlideAggregate(table);
gr.addAggregate('COUNT');
gr.addQuery('sys_updated_on', '>=', nDaysAgo);
gr.query();

if (!gr.next() || gr.getAggregate('COUNT') == 0) {
tablesWithNoInteractions.push(table);
}
});

// If there are tables with no interactions, send an email
if (tablesWithNoInteractions.length > 0) {
var emailBody = "Tables with no interactions in the last " + numberOfDays + " days:<br><br>";
emailBody += "<table border='1'><tr><th>Table Name</th></tr>";

tablesWithNoInteractions.forEach(function(table) {
emailBody += "<tr><td>" + table + "</td></tr>";
});

emailBody += "</table>";

// Get the user to notify (assuming the user is stored in a property called 'user_to_notify')
var userToNotify = gs.getProperty('user_to_notify');

// Send the email
var email = new GlideEmailOutbound();
email.setSubject('Tables with no interactions in the last ' + numberOfDays + ' days');
email.setBody(emailBody);
email.addRecipient(userToNotify);
email.send();
}

Please mark my answer Helpful if worked for you.

[Muhammed Medni]

Hi @Utkarsha try this script

var NotificationHandler = Class.create();
NotificationHandler.prototype = {
    initialize: function() {},

    notifyUsers: function(configJSON) {
        var config = JSON.parse(configJSON);
        var tables = config.tables.split(',');
        var operations = config.operations.split(',');
        var usersToNotify = config.users_to_notify.split(',');
        var numberOfDays = parseInt(config.number_of_days);

        for (var i = 0; i < tables.length; i++) {
            var tableName = tables[i];
            var operation = operations[i];

            var gr = new GlideRecord(tableName);
            gr.addQuery('sys_created_on', 'ON', gs.daysAgo(numberOfDays));
            gr.query();
            while (gr.next()) {
                if (operation == 'insert' || operation == 'update') {
                    gs.eventQueue('eventname', gr, usersToNotify);
                }
            }
        }
    },

    type: 'NotificationHandler'
};


property
Name: notification.config
Value:

    {
        "tables": "sys_user,core_company",
        "operations": "insert,update",
        "users_to_notify": "user3,user4,user5",
        "number_of_days": "1"
    }

Scheduled Script
var configJSON = gs.getProperty('notification.config');
if (configJSON) {
    var notificationHandler = new NotificationHandler();
    notificationHandler.notifyUsers(configJSON);
}

[Utkarsha]

hello @Muhammed Medni ,

Thank you so much for your inputs on this 😇....I'm so glad to tell you that the logic which you shared is working and triggering emails..can you please guide me on how to display the content using email script in an email
I kind of wanted to display a table in email in the shared format-

Thank you,

Utkarsha