Email in Queue > 100

Carl Hawes · ‎10-08-2017

Querying (by a Web service) the [sys_email] table can help to get the item "Email in Queue > 100" only.

Looking for Table/Field name to query that value

vab_13 · ‎10-08-2017

You might want to consider Scripted SOAP Web Services.

Query the count in the Scripted Web Service and return the relevant value depending on the length of the returned object.

var grObject=new GlideRecord('sys_email');

grObject.addQuery(<Add your query here/>);

//grObject.addQuery(<Add your AND query here/>);

grObject.query();

if(grObject.getValue('sys_id').toString().length >=100){

         "Add your logic here and return the relevant value to do the required action at 3rd Party";

}

Mwatkins · ‎10-09-2017

You will want to be very careful with querying the sys_email table by web service. It is a very large table and can bring down a whole instance if not carefully accessed. For example, if you send multiple web requests that trigger inefficient queries on the sys_email table it can exceed available resources on even the largest database, causing system wide performance degradation.

I recommend always including a time boxed date filter in your queries and using a limit on the number of results returned. Time boxing the query is when you tell the system to give you only records where DATE > X AND DATE < Y. The reason I say this is that the sys_email table is set up with something called Table Extension. Table Extension is a background data model that breaks a single "logical" table into multiple separate "physical" tables on the database. When someone asks ServiceNow for the "sys_email" table, the data access layer of ServiceNow knows that actually it needs to query multiple separate physical tables.

Logical table: sys_email

Physical tables:

sys_email0000

sys_email0001

sys_email0002

sys_email0003

sys_email0004

...

Each shard covers a different date range with a start and end date. If someone asks ServiceNow for any email created in the last 3 days, ServiceNow's Data Access Layer is smart enough to know that the last 3 days of email information are stored in shard #22, so it only queries shard #22. However, if someone asks ServiceNow for any email in the "outbound" queue, in the "ready" state, then the DAL does not know what shard to look in and it has to do a UNION ALL query that joins all 23 shards (we know there are 23 shards since 22 is the most recent shard and the counting starts at shard #0)!! This can get very expensive.

The next recommendation is to include a limit on the number of results that can be returned in a single query to 1,000 or fewer results. If you query a certain time frame and 1,000 records are returned then you know there are more records in that time frame. In order to get the rest of the records in that time frame you can have the results sorted by the time field you are using for the time box. This way, the next query you make will move the time box based on the time value returned by the last record in the previous query. For example:

1st query: returns 1000 records, sorted ascending on sys_created_on

SELECT ... FROM X WHERE sys_created_on > 2017-10-10 09:21:00 AND sys_created_on < 2017-10-10 09:31:00 ORDER BY sys_created_on ASC LIMIT 1000

1 - sys_created_on: 2017-10-10 09:21:01

2 - sys_created_on: 2017-10-10 09:21:23

...

1000 - sys_created_on: 2017-10-10 09:25:13

2nd query: moves the time box based on the last value returned

SELECT ... FROM X WHERE sys_created_on > 2017-10-10 09:25:13 AND sys_created_on < 2017-10-10 09:35:13 ORDER BY sys_created_on ASC LIMIT 1000

1 - sys_created_on: 2017-10-10 09:25:15

2 - sys_created_on: 2017-10-10 09:25:33

...

1000 - sys_created_on: 2017-10-10 09:28:13

My guess is that you are trying to set up some kind of monitoring against your instance to determine if the email queue is falling behind. If so, then I would definitely recommend putting a date limitation on whatever query you end up using. You could use the query that the out-of-box Outbox folder uses (see the screenshot below). By using the copy query option when right+clicking the breadcrumb you can get the encoded query and use it in the GlideRecord query that Vab suggested. As you can see, the "Outbox" module queries for "Created on Today"