The On Premise engine has an exporter type for SQL Server. This will write emails, meetings and other data out to SQL Server when it runs instead of the normal iPAAS endpoint. Some customers who want to use our API parsing but don't want to build their own email processing pipeline use our one premise engine for this purpose.
Configuration
In the appsettings.json file:
{
"exporter": "SqlServerV1",
"sqlserverv1_connectionstring": "Server=localhost:1433;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;",
"sqlserverv1_tables": "Email,Meeting",
"sqlserverv1_table_emails_column_subject": true,
"sqlserverv1_table_emails_column_attachments": true,
"sqlserverv1_table_meetings_column_subject": true,
...
}Configuration Options
exporter - By changing the "exporter" property we're telling SigParser to send the results to a SQL Server database. The exporter will create some tables automatically in the database server.
sqlserverv1_connectionstring - A connection string to the SQL Server database where the emails will be stored.
sqlserver1_tables - Which type of output tables to you want to send data to. This should be a comma separated list of options.
Options: Email, Meeting
Other options may be added later.
sqlserverv1_table_emails_column_subject - Determines whether to write the subject value to the table. Some companies don't want the subject field populated.
sqlserverv1_table_emails_column_attachments - Determines whether to write the attachments value to the table. Some companies don't want the attachments field populated.
Tables Created
SigParser will write emails to this table for processing by your internal system. The same email might be written multiple times to the table. This table is designed for you to process the data out of in ascending order by EmailID. Do not keep data in this table forever otherwise the table will grow forever. Once you process the email you should delete it from the table.
EmailID - bigint - PRIMARY KEY - Auto incrementing field. When you're querying this table for emails you should use this to sync all the records out of the table.
CreatedDate - DateTimeOffset - Date the email was written to the table.
MailboxID - ID of the mailbox the email originated from. Sometimes this is an ugly number.
MailboxEmailAddress - The email address of the mailbox if available. Useful if MailboxID is ugly.
InternetMessageID - This is the MIME header "Message-ID" which is generally globally unique to this email message. This is an important field to use to deduplicate emails across mailboxes.
FolderName - Name of the folder this email was found in. The FolderName can change over time as the folder is renamed or the email is moved between folders.
FolderID - The ID the email was found in. The FolderID changes over time if a user is moving it between folders.
FromAddress - Email address of the sender of the email
FromDisplayName - Display name of the sender which isn't always set.
ToAddresses - A JSON array of objects for recipients of the email.
Example: [{"displayname":"John Smith", "email":"john@example.com"}, ...]
Subject - The subject line for the email.
CcAddresses - A JSON array of objects for the recipients of the email.
Example: [{"displayname":"John Smith", "email":"john@example.com"}, ...]
EmailDate - Date the email was sent.
ReplyTo - The MIME header "Reply-To" which is set when an email is in reply to another email. This would be the MIME "Message-ID" of that original message which we store at "InternetMessageID"
MessageID - The ID of the message in the mailbox.
ReferencesIDs - The MIME header "References" which refers to the "Message-ID"s of all the other messages in the reply chain up to this email.
ParsedSignature - A JSON object representing the parsed signature details. The JSON structure matches the API response for the /api/Parse/Email/Contact/JSON response.
FailedRecipients - A JSON array of email addresses who this email failed to send to. This email isn't a real email and the email addresses in the collection should be treated as dead email addresses. There is almost always just one email address.
Example: ["john@example.com", "mark@example.com"]
Attachments - A JSON array of the attachment names that were on this email.
Example: [{"filename": "...", "isinline": false, "size": 49494, "id": "..."}, ...]
Categories - A JSON array of categories on the email.
Meeting
SigParser will create a Meeting table in the SQL server instance which will store the meeting records if this feature is enabled.
Cancelled Meetings: If a meeting is cancelled it's IsCancelled field will be set to true and the MailboxMeetingID will be set. You'll need to use the MailboxMeetingID to find the associated meeting in your database and set it to cancelled.
MeetingID - Unique ID on the table for the meeting. Use this to process the rows. Auto increments.
MailboxMeetingID - ID of the meeting in the mailbox. Very important to track this if you need to keep track of meeting cancellations.
CreatedDate - Date the row was created in the SQL Server table. Not the date the meeting was actually created.
MailboxID - Mailbox ID the meeting came from.
MailboxEmailAddress - Email address for the mailbox. The email address may change over time as the company changes domains.
ICalUID - A globally unique identifier for the meeting. Use this field to deduplicate meetings across mailboxes.
Recipients - JSON array of receipient objects. Each objects has the properties DisplayName, EmailAddress, ResponseStatus
Categories - JSON array of category names
Subject - Subject of the meeting.
CalendarID - ID of the calendar.
CalendarName - Friendly name for the calendar
StartTime - When does the event start?
EndTime - When does the event end?
MeetingCreatedDate - When was the meeting created in the mailbox?
IsCancelled - Is the meeting cancelled?
OrganizerEmail - Who organized the meeting?
OrganizerName - Who organized the meeting?
Recurring - Is this a recurring meeting?
Sensitivity
IsAllDay
ShowAs
LastModified