Over your storage limits? sys_email is loud (and full of junk)

Janel
Kilo Sage

‎07-26-2023 05:48 AM - edited ‎08-30-2023 05:24 AM

This is a continuation of the mini-series "Over your storage limit? Reducing our storage footprint; our lessons learned."

https://www.servicenow.com/community/developer-forum/over-your-storage-limit-reducing-our-storage-fo...

 

This post is about how we learned that not taking care of sys_email can put you in a bad spot.

 

Disclaimer:

This post is about the deletion of records to reduce the storage footprint size at our company.  This is what worked for us and it may not work for you.  Know your policies and retention periods and as always, review and make sure you test everything in a sub-prod instance first.

 

 

sys_email is loud (and full of junk)

Unfortunately, sys_email is a rotated table and we could not use table cleaner for it.  We discovered millions of junk records on this table; bound-back emails, people BCCing and CCing the instance, spam, people randomly emailing the instance, the instance emailing itself, etc. 

Clearly we hadn't been taking care of sys_email and it made a big target for cleanup.

https://docs.servicenow.com/bundle/utah-platform-administration/page/administer/reference-pages/conc...

 

We implemented 2 3 things to help maintain this table.

  1. (We just started working on this one) Create a catch-all inbound rule (it couldn't be a flow because all flows will run before inbound email rules) that responds to the sender if the email did not match any other inbound Flow/inbound rule.
    1. If an email comes in and doesn't match any other inbound conditions, we reply back to the person saying we got it, not sure what it is for, provide instructions to matching common rules (like putting the task in the subject or making sure to include a watermark), and then delete the email. 
    2. We're hoping this clears the way so the second thing we did can be relaxed.
  2. Put in a daily scheduled job that deleted records daily/weekly/monthly that met our retention conditions that we knew we didn't need.
  3. We did end up putting in a second scheduled job to troll around sys_email and delete orphaned emails. 
    1. This was needed because emails are linked through a document_id field.  document_id fields do not perform cascade deletes like a reference field.  So if the parent record (like an HR Task) is deleted, it doesn't always take the related emails with it.
    2. Archiving is the primary reason we had to do this.  We were able to prove that archive rules (using the related records options to delete) are still orphaning things.
    3. I'm not going to post that one here since it is just a copy of what we did here: https://www.servicenow.com/community/developer-forum/finding-and-deleting-orphaned-attachments/m-p/2...

 

Warning!  Deleting a sys_email record will remove it from any activity filters/streams!  Consider if seeing this in activity streams is important to your users.

 

I made a script to delete the unwanted emails by putting the individual queries into an array to execute the deletions in chunks (instead of having a huge long running job, a huge encoded query, or multiple jobs).

 

Here is a partial script containing our company's non-retention related things to maybe help get you started.  There are a few things in here that I never thought about.  Like those attachments for large list exports?  They sit around here forever, with the attachment!  If users use the email client then abandon the message, the sys_email record is still created and sits around forever.  We probably reclaimed nearly 500-700GB just cleaning up sys_email records.

Make sure you review it and updated it before executing in any of your instances!

 

/* Because sys_email is a rotated table that is shared across many processes we need to manually delete things from it at certian intervals.
* Add the encoded query into the array for the emails you want to delete from sys_email.  Please sort it based on the time in your query.  Shortest durations at the top, longest at the bottom.
* Keep the query to something reasonable (via a date range) and if needed, back up into it by dates if possible to avoid a long running job.
Make sure your encoded query contains the DATE that you want to go back.  We can't use setLimit with deleteMultiple.  And sort it into the array based on time. */


var destruction = [
    //Delete all emails daily where we sent to ourselves and we received-ignored it.  These are already on the ignored SMTP sender list. 
    'sys_created_onRELATIVELT@dayofweek@ago@1^user={your_instance}@service-now.com^type=received-ignored',
    //https://{your_instance}.service-now.com/sys_email_list.do?sysparm_query=user%3D{your_instance}%40service-now.com%5Etype%3Dreceived-ignored%5Esys_created_onRELATIVELT%40dayofweek%40ago%401&sysparm_view=


    //Delete emails daily for large list exports and the undeliverable copies.  E.G. "Attached is the XML file you requested".
    'sys_created_onRELATIVELT@dayofweek@ago@1^subjectSTARTSWITHAttached is the^ORsubjectSTARTSWITHUndeliverable: Attached is the^subjectLIKEfile you requested^ORsubjectLIKEspreadsheet file (.xlsx) you requested',
    //https://{your_instance}.service-now.com/sys_email_list.do?sysparm_query=sys_created_onRELATIVELT%40dayofweek%40ago%401%5EsubjectSTARTSWITHAttached%20is%20the%5EORsubjectSTARTSWITHUndeliverable%3A%20Attached%20is%20the%5EsubjectLIKEfile%20you%20requested%5EORsubjectLIKEspreadsheet%20file%20(.xlsx)%20you%20requested&sysparm_view=


    //At 7 days, delete emails that were abandoned by the email client.  If you start creating an email and don't send it, it stays on sys_email.  If you add an attachment, the attachment will stay too.
    'sys_created_onRELATIVELT@dayofweek@ago@7^error_string=User did not press the Send button in Email Client^type=send-ignored',
    //https://{your_instance}.service-now.com/sys_email_list.do?sysparm_query=sys_created_onRELATIVELT%40dayofweek%40ago%407%5Eerror_string%3DUser%20did%20not%20press%20the%20Send%20button%20in%20Email%20Client%5Etype%3Dsend-ignored&sysparm_view=


    //At 14 days, delete all scheduled report emails.  We only keep this long for troubleshooting.
    'sys_created_onRELATIVELT@dayofweek@ago@14^target_table=sysauto_report^type=sent',
    //https://{your_instance}.service-now.com/sys_email_list.do?sysparm_query=sys_created_onRELATIVELT%40dayofweek%40ago%4014%5Etarget_table%3Dsysauto_report%5Etype%3Dsent&sysparm_view=


    //At 21 days, delete all >>sent<< emails that aren't associated to a record.  These will be from workflows/flows and scripts that called an event without a record.  These are mostly SLA warnings/breaches or an undeliverable response to an auto-reply.
    'sys_created_onRELATIVELT@dayofweek@ago@35^target_table=^ORtarget_table=sys_email^typeINsend-failed,send-ignored,send-ready,sent',
    //https://{your_instance}.service-now.com/sys_email_list.do?sysparm_query=sys_created_onRELATIVELT%40dayofweek%40ago%4028%5Etarget_table%3D%5EORtarget_table%3Dsys_email%5EtypeINsend-failed%2Csend-ignored%2Csend-ready%2Csent&sysparm_view=


    //At 35 days, delete all undeliverable/returned >>received<< mail that could not be processed.  These are bouncebacks from other systems.  We only keep them this long for possible troubleshooting purposes.
    'sys_created_onRELATIVELT@dayofweek@ago@35^userSTARTSWITHpostmaster@^ORuserSTARTSWITHMAILER-DAEMON^typeINreceived,received-ignored',
    //https://{your_instance}.service-now.com/sys_email_list.do?sysparm_query=userSTARTSWITHpostmaster%40%5EORuserSTARTSWITHMAILER-DAEMON%5Esys_created_onRELATIVELT%40dayofweek%40ago%4035&sysparm_view=


    //At 42 days, delete all >>received<< emails where there is no table/record and they couldn't be/weren't processed by an inbound email rule.  This is a catch-all for email that we got and don't have a record for it  No way to trace it to anything.
    'sys_created_onRELATIVELT@dayofweek@ago@42^target_table=^ORtarget_table=sys_email^typeINreceived,received-ignored'
    //https://{your_instance}.service-now.com/sys_email_list.do?sysparm_query=sys_created_onRELATIVELT%40dayofweek%40ago%4042%5Etarget_table%3D%5EORtarget_table%3Dsys_email%5EtypeINreceived%2Creceived-ignored&sysparm_view=
];


for (var k = 0; k < destruction.length; k++) {
    var des = new GlideRecord('sys_email');
    des.addEncodedQuery(destruction[k]);
    des.orderByDesc('sys_created_on'); //We go descending so if they end up overlapping they always start with the month specified in the query.
    des.query(); //What's the latest argument on whether this is needed for deleteMultiple?
    des.deleteMultiple();
}

 

If you're running a script like the above for the first time, make sure you back into the date ranges and delete in chunks before settling on your final numbers.

 

I hope this post gives you ideas on how you can get your sys_email table under control.

  • 2,683 Views
6 REPLIES 6

pespinar
Tera Contributor
In response to Janel

‎09-21-2023 05:27 AM

I do not understand very well this issue. We are currently deleting records from the sys_email table, and the related attachments are being automatically (cascading) deleted. I can post some screenshots proving it.

 

Un saludo,

Pablo

Pablo Espinar
ServiceNow Technical Lead at Holcim
0 Helpfuls

Janel
Kilo Sage
In response to pespinar

‎10-02-2023 05:04 AM

Yeah, I'm seeing the same thing too if we delete an email, usually any attachments on the email are deleted as well.  If I delete the record that the email is for, that record doesn't delete the email.

 

Archiving is also a strange case and does not always delete related records correctly and leaves things behind.

0 Helpfuls