Automatically create Incident if the count of a report is higher than 50

MT247 · ‎03-02-2020

Hi,

today we had an issue with our platform, where the mails in the outbox queue have not been processed for 4 hours. I have created a dashboard to monitor the queue, but it would be great, if i could send out a mail, or even better create an incident for the case that the count of tickets in the outbox is above 50.

The dashboard is using a simple report on the sys_email table counting the mails in the outbox, so the number is displayed on the dashboard.

Anyone any idea on how to implement something like this?

Cheers

Michael

Prateek kumar · ‎03-02-2020

Try something with a before insert BR on Email table:

var str = "mailbox=outbox^sys_created_onONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)"; //this checks outbox emails created today
var gr = new GlideRecord('sys_email');
gr.addEncodedQuery(str);
gr.query();
var count = gr.getRowCount();
if(count>50){
gs.eventQueue(''); //Trigger notification as required
var inc = new GlideRecord('incident');
inc.initialize();
inc.your_fields = '';
inc.insert();
}

https://developer.servicenow.com/app.do#!/api_doc?v=newyork&id=r_GS-eventQueue_S_O_S_S_S

https://developer.servicenow.com/app.do#!/api_doc?v=newyork&id=r_GlideRecord-initialize

Please mark my response as correct and helpful if it helped solved your question.
-Thanks

View solution in original post

Swadesh Saraf2 · ‎03-02-2020

I have done exactly what you need here, just that I am checking failed emails only:

   var grSysEmail = new GlideRecord('sys_email');
    grSysEmail.addEncodedQuery("sys_created_onRELATIVEGE@hour@ago@1^type=send-failed^recipients!=admin@example.com^ORrecipients=NULL");
    grSysEmail.query();
    var count = grSysEmail.getRowCount();
    if (count > 20) {

<trigger your email from here by using an event>
}

What I am checking if there are more than 20 failed emails in last 1 hour, I will be getting an email and if it continues, I will keep getting email for any count above 20.

Hope this helps.

Adam Stout · ‎03-02-2020

I would be cautious about this. You are adding extra overhead to EVERY email you send. Depending on volume, this could be an issue. I would look at a job that runs every 15 minutes or something like that.

sethivarun · ‎03-02-2020

you can create a insert business rule on the sys_email table that runs on an email insert and check for the count of the emails that are in the outbox.

If it is more than 50 then trigger an event which in turn would generate a notification

Swadesh Saraf2 · ‎03-02-2020

This maybe a good option, but considering a live production scenario and how ServiceNow processed the emails, if there are more than 50 outbound emails generated in a minute, it will trigger a false alert.