Count of how many times a particular email notification has been sent in defined time interval

[kushan1]

We need to know the count of how many times a particular email notification has been sent in defined time interval. For example: We have 300 notification configured in sysevent_email_action table and the fact we want to know is which out of the 300 is mostly commonly and widely used. So we need the count of how many time each notification has been sent/triggered in past 6 months etc. It would be great if you can let us know on how to derive it. Is there any report or kind of background script which can fetch this details?

[Ankur Bawiskar]

Hi Kushan,

I think you can have report on email logs table.

Regards

Ankur

[Kushan Kukkunoo]

We don't have notification name in the logs and subject cannot be used to represent a particular notification. Can you suggest how from the email logs we can link an email sent to particular notification.

[Soumita3]

Hi Kushan,

Please find the below code which will suffice your requirement:

This can be written wither in a background script/ Fix script-

var gr = new GlideRecord('sysevent_email_action');
gr.addEncodedQuery('active=true');
gr.query();
while(gr.next())
{
var count = 0;
// gs.print(gr.name);
var gr1 = new GlideRecord('sys_email_log');
gr1.addEncodedQuery('sys_created_onONLast 6 months@javascript:gs.beginningOfLast6Months()@javascript:gs.endOfLast6Months()');
gr1.query();
while(gr1.next())
{
//gs.print("gr: " +gr.sys_id);
//gs.print("gr1 :"+gr1.sys_id);

if(gr.sys_id == gr1.notification)
{
count++;
}
}
if(count!=0)
gs.print("Notification name: "+ gr.name + " Count: "+ count);

}

Please mark the answer as correct if it suffice your query.

Thanks.