CSM Notification

[Kri]

I have two Custom tables in CSM which receive a Cases for the Customers. I need to monitor the cases on the daily basis for each customer and if there is an drop of 40% then a notification need to be trigger. How I can achieve this?

[Rajesh Chopade1]

here is the sample script, do needful changes as per your requirement:

var table1 = 'u_custom_table_1'; // Replace with your custom table name
var table2 = 'u_custom_table_2'; // Replace with your second custom table name
var customerField = 'u_customer'; // Field that stores customer info

// calculate percentage change
function calculatePercentageChange(oldValue, newValue) {
    if (oldValue === 0) {
        return newValue === 0 ? 0 : 100;
    }
    return ((newValue - oldValue) / oldValue) * 100;
}

// Function to get case counts for a given date
function getCaseCountsByDate(date) {
    var caseCounts = {};

    // Query first custom table
    var gr1 = new GlideRecord(table1);
    gr1.addQuery('sys_created_on', '>=', date + ' 00:00:00');
    gr1.addQuery('sys_created_on', '<=', date + ' 23:59:59');
    gr1.query();
    while (gr1.next()) {
        var customer = gr1.getValue(customerField);
        caseCounts[customer] = (caseCounts[customer] || 0) + 1;
    }

    // Query second custom table
    var gr2 = new GlideRecord(table2);
    gr2.addQuery('sys_created_on', '>=', date + ' 00:00:00');
    gr2.addQuery('sys_created_on', '<=', date + ' 23:59:59');
    gr2.query();
    while (gr2.next()) {
        var customer = gr2.getValue(customerField);
        caseCounts[customer] = (caseCounts[customer] || 0) + 1;
    }

    return caseCounts;
}

// Calculate date strings for today and yesterday
var today = new GlideDateTime();
var yesterday = new GlideDateTime();
yesterday.addDaysUTC(-1);

var todayDateStr = today.getLocalDate().toString();
var yesterdayDateStr = yesterday.getLocalDate().toString();

// Get case counts for today and yesterday
var todayCounts = getCaseCountsByDate(todayDateStr);
var yesterdayCounts = getCaseCountsByDate(yesterdayDateStr);

// Compare and notify if drop is 40% or more
for (var customer in yesterdayCounts) {
    var yesterdayCount = yesterdayCounts[customer];
    var todayCount = todayCounts[customer] || 0;
    var percentageDrop = calculatePercentageChange(yesterdayCount, todayCount);

    if (percentageDrop <= -40) {
        // Construct notification message
        var message = 'Significant drop in cases for customer ' + customer + ': ' +
                      'Yesterday: ' + yesterdayCount + ', Today: ' + todayCount + 
                      ' (' + Math.abs(percentageDrop.toFixed(2)) + '% drop)';

        // Trigger notification (use an event or direct email as per your setup)
        gs.eventQueue('custom.case_drop_notification', null, message, '');
    }
}