Addressing Inconsistencies in Email Count Monitoring

[Harinder]

I'm trying to switch my SMTP account after a certain threshold because of the limitations of the O365 SMTP account. Currently, I'm monitoring the number of sent emails from the Sys_email table using a GlideAggregate (count) script. Most of the time, it works perfectly within the business rules. However, occasionally, it produces unexpected counts. For example, the current count is usually in the range of 4000 or more, but suddenly, it drops to 0, 900, or 300. This inconsistency is causing unexpected switches between SMTP accounts.

I've raised this issue to investigate why I'm experiencing this behavior and to get guidance on how to handle this task more efficiently.

Here's the script snippet I'm using:

var emailCount = new GlideAggregate('sys_email');
emailCount.addAggregate('COUNT');
emailCount.addQuery('sys_created_on', '>=', startTimePst);
emailCount.addQuery('sys_created_on', '<=', currentPst);
emailCount.addQuery('type', 'sent');
emailCount.addQuery('notification_type', 'SMTP');
emailCount.query();

[Tony Chatfield1]

Hi, unfortunately the partial code you have posted does not make your configuration clear, or help the community understand your issue. When\how are you running this 'count' ?
If you are counting messages sent in last 24 hours? then a single encoded query might be easier to manage

mailbox=sent^sys_created_onRELATIVEGT@hour@ago@24^notification_type=SMTP

[Harinder]

Hi Tony, Thanks for taking time to respond. I used below mentioned code for having more control over the start time to ensure email sent count is matching with my screen under logs and with the time of SMTP account. Which is working fine, I even tried with encoded query as you suggested that also works but the issue is inconsistency of counts (Example given above).

Also, I wanted to monitor total number of emails sent in a day not in last 24 hours (Relative) because O365 SMTP account has a limit to send email in 24 hours which reset at mid night.

Here is the code for the time zone adjustment:

// Define the Pacific Time Zone (PST) offset in minutes (PST is UTC-8)
var pstOffsetMinutes = -1 * 60;

// Calculate the current time in PST
var currentPst = new GlideDateTime();
currentPst.addMinutes(pstOffsetMinutes);

// Define the start time for the time range (e.g., 00:00 PST)
var startTimePst = new GlideDateTime(currentPst);
startTimePst.setDisplayValue(currentPst.getDisplayValue().split(" ")[0] + " 02:00:00");

emailCount.addQuery('sys_created_on', '>=', startTimePst);
emailCount.addQuery('sys_created_on', '<=', currentPst);