SharePoint E-Mail Confirmations and Alerts (under the hood)

After spending way too much time looking for documentation explaining exactly how SharePoint e-mail worked under the hood I decided to bring some of my findings together and associate the many tags I used in hopes that it will save others time. 

Now I'm not just talking about how to configure Outgoing or Incoming e-mail in SharePoint. I'm talking about how an email is triggered, generated and then sent to a recipient(s) when someone clicks on (e.g) Follow when visiting a users profile or blog. E-mail alerts is one thing...there are timer jobs and SQL tables and drop folders involved to make e-mail alerts work. The simpler confirmation emails were what was troubling me. After much digging it appeared to be much simpler than I imaged.

First off...a lot of what you will read below was gathered from TechNet articles and other MS resources after much digging.

This TechNet article helped better explain the Alert portion of e-mail generation quite well.     https://msdn.microsoft.com/en-us/library/office/hh161512(v=office.14).aspx

Confirmation Emails

These emails are actually sent by the SharePoint Web Front End server that is serving up the SharePoint page at the time it is generated. These emails are sent under the identity that the IIS worker process is using (w3wp.exe)

CONFIRMATION EMAIL PROCESSING
When outgoing email is enabled through the Central Admin dashboard the ability to set confirmation emails for web applications becomes available. All that this configuration does is provide the needed properties for generating an .eml file. The process could almost be compared to clicking an email link in a web page and being taken to your mail client where it automatically populates the address of the recipient. Only in this case the contents of the Outgoing Email configuration are used as a reference and then whatever profile you trigger during email generation provides the needed field for the rest of the SMTP packet. Namely the Work email property in your user properties for UPA configuration. As long as the Work email is linked to a valid AD account and the property configured contains an email address then it will send out a packet to whatever SMTP relay you setup . <- And this is all I really learned about confirmation e-mail generation. I'm sure there are steps I'm missing but this was the gist of it and it helped resolve an ongoing issue for me.

Alert Emails

Require the existence of a Timer Job <Immediate Alerts> This Timer Job runs under the credentials of an account that needs to be a member of the SharePoint Administrators Group and that it also has access to the Content Database in SQL. When an alert is triggered the email is not immediately sent (as one might expect) but rather is queue for delivery when the Time Job runs (typically every 5 minutes) and is sent under the credentials of the job account

ALERT EMAIL GENERATION
The following is the procedure for processing alerts:

1. The timer job calls the SharePoint Foundation alert handler every five minutes.

2. The SharePoint Foundation alert handler scans the SchedSubscriptions table to identify which alerts to process.

3. The SharePoint Foundation alert handler creates a query object from the information and calls the Execute() method on the SharePoint Server Query object model.

4. The alerts handler returns the results from the query to the SharePoint Foundation alert handler, which sends an alert with the results to the user.

These scheduled subscriptions will fire and execute the next time the SharePoint Foundation Timer service (owstime.exe) processes the default and custom timer jobs.
This Timer service executes at a predetermined time interval. Timer jobs are the SharePoint-specific way to perform a specific task on a scheduled basis.
Windows SharePoint Services enables support for users to create alerts for other users. The user with these privileges can customize the trigger for an alert, and customize the alert notification. This functionality is exposed in the SharePoint Foundation object model, enabling programmatic manipulation of alerts.

The SharePoint Foundation alert framework has multiple tables for storing information. The alert framework manipulates the following key database tables:

· EventCache
· EventLog · ImmedSubscriptions
· SchedSubscriptions · EventsSubsMatches

A timer job [inappropriately named] Job- immediate-alerts runs (because it processes both immediate and scheduled subscriptions) at intervals of every five minutes (the default). All events that occurred after the last run are picked up (in batches of 10,000 events), and both immediate and scheduled subscriptions are picked up.

Each event is compared with each subscription to find a match (same siteID, same listID and, if itemID is specified, same itemID). If there is a match, the subscription filter is run on the event data to find a match if there are any filters specified on the fields such as "tasks assigned to me filter". If there is a match, security trimming is done for the user who is the owner of the alert. For example, if the user did not have permissions to read the list items, he will not get the alert. If the match between event a nd subscription passes security trimming, what happens next depends on whether it matched to an immediate subscription or to a scheduled subscription.

If the event matched an immediate subscription, the event and subscription are added to an in - memory junction list consisting of subID -eventID, which is processed later to build the alert email or message and send it out. Otherwise, if the event matched a scheduled subscription, the event and subscription it matched is added to EventsSubMatches table to be picked up again when that subscription is actually due.
After all the events are processed, the matches between events and immediate subscriptions are built into alert messages and sent. The matches between events and scheduled subscriptions are added to the EventsSubMatches table. Then, the scheduled subscriptions that are due are picked up with event-subscription junction entries for each subscription that is due. All the events for each subscription are rolled up and sent as an alert.

Three Steps of SharePoint Alert Subscription Processing

The processing of changes to send out alerts occurs mainly in three steps:

1. Perform filtering and security trimming.

2. Process immediate subscriptions.

3. Process scheduled subscriptions.

Filtering and Security Trimming for SharePoint Alerts

The proc_GetEventDataAndSubscriptionFilters procedure is first called to get the latest top 10,000 events that have not been processed yet, that are visible to alerts (EventType & 0x0FFF != 0), and that have at least one subscription with the same siteId and listId in the subscription tables. This is an event batch.

The proc_GetEventDataAndSubscriptionFilters procedure also selects all the subscriptions from both the Immedsubscriptions and Scheduledsubscriptions tables that have at least one event with the same SiteId and ListId. Both the event batch and the list of subscriptions are sorted by SiteId and ListId.

The procedure also marks the ID and event time of the last event so that it can start from there the next time(EventBatch table). Finally, it moves all the events that were picked up in this batch to the EventLog table and sets the EventData and Acl columns of the events that were picked up to NULL in the EventCache table. This is done for performance reasons because the EventCache table is highly used. The entries in the EventLog table will be used in the processing of scheduled subscriptions.

For each subscription in the subscriptions list, all the matching events are found. Matching implies having the same site ID and list ID. Because the list of subscriptions and the list of events are sorted by site ID, list ID, the matching consists of just wa lking over the ordered lists.
When there is a match, further checking is performed to determine whether itemID is the same (if itemID is not NULL in the subscription, that is, it is an item - level alert). If it is, the filter as specified in the binaryfilter column (which is a binary form of filter column) is run on the event data. If the event passes the filter, security trimming is performed on the user of that alert.

NOTE
If it is a dynamic alert, the user to whom the alert will be sent will be picked up from the field specified by DynamicRecipient property in the alerts property bag. If the alert is assigned to a SharePoint group, the group is expanded for the individual u sers and security trimming is performed for each user.

After security trimming is performed, all the matches between events and scheduled subscription are entered in the EventsSubMatches table as junction entries by using the proc_InsertEventSubscriptionJunctionEntries procedure. Each junction entry consists of an eventid, a subscription ID and lookupfieldpermissionresults, which are the results of security trimming on lookup fields for the event.

Processing SharePoint Alerts That Are Scheduled for Immediate Execution

The proc_GetEventDataAndSubscriptionFilters procedure does not get all the columns in the subscriptions tables. As a result, now proc_GetMatchingSubscriptionsData is called in batches of 256 subscriptions for all subscriptions in the memory junction of event -subscription. This gets all the data for each subscription. Then, for each subscription, the alert email or message is built by using the template that is specified by the AlertTemplateName column.

Processing Scheduled SharePoint Alert Subscriptions

Scheduled subscriptions can be either daily or weekly regular alerts or daily or weekly always- notify alerts. Always notify alerts are alerts that fire without any matching event.
Each of the following steps happen serially for each type of scheduled subscription. Processing of scheduled subscriptions starts by getting a list of site collections that have at least one daily or weekly alert.

Scheduled SharePoint Alert Subscription Process

1. The proc_EnumSubscribedSites procedure is called to get a list of all site collections that have at least one daily regular alert in the schedsubscriptions table; actually, all site collections that have at least one daily regular alert and at least one event in the event log table, to eliminate sites that have a very low volume of events.

2. The proc_MatchSchedSubscriptions procedure is called for each SiteId in the list of site collections. It gets the list of daily alerts for that site collection, all the events-subscriptions junction entries for that site collection, and all the events from the EventLog table. It also checks the NotifyTime and gets only those subscriptions that are due, meaning that NotifyTime is earlier than the current time.

3. For each subscription, all the events are rolled up into one digest and the alert is sent out. The lookup permissions results are useful in building the alert because those lookup fields that the user is not authorized to see should not be rendered in the alert.

4. The proc_DeleteEventLog procedure is called to delete all the entries in EventLog that are older than one week because they will not be used in any subscription.

The previous steps are performed for each type of scheduled subscription: daily or weekly regular alerts and daily or weekly always notify alerts. Most of the time, always notify alerts do not have to have an email built and sent. Next, the proc_DeleteSubscriptionJunctionEntries procedure is called to delete all the junction entries for each subscription that was processed. This is done in batches of 256 subscriptions so that the database server is not overloaded. The proc_UpdateSchedSubscriptionTimes procedure is then called to update all the subscriptions whose NotifyTimeUtc is earlier than the current time. It adds a day to all the daily subscriptions and a week to all the scheduled subscriptions.

I hope that the above gathered information provided some help to those looking for resources on this topic!