Adding a Connection to SendGrid

To add a connection to SendGrid:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the SendGrid icon is not available, click the Add More icon to download and install the SendGrid connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

You must use an API key to authenticate through the SendGrid Sync App.

Obtaining an API Key

To obtain an API key, follow the steps below:

1. Log into SendGrid.
2. Click Settings.
3. Click API keys.
4. Click Create API Key.
5. Choose the Permissions the API Key should have, either Full Access or Restricted Access. The API key is then displayed.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

This section details a selection of advanced features of the SendGrid Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to SendGrid and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The Sync App models SendGrid APIs as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files.

However, the Recipients table is dynamic: The Sync App connects to SendGrid and gets the metadata for this table by calling the appropriate Web services. Any changes you make to the custom filters you define for Recipients are available when you connect; you can use these custom filters as columns in the WHERE clause. These custom filters are surfaced in the CustomFields table.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

New Marketing Campaigns Data Model

See New Marketing Campaigns Data Model for the available entities in the New Marketing Campaigns API.

Legacy Data Model

See Legacy Data Model for the available entities in the Legacy API.

The CData Sync App models the New Marketing Campaigns API as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

To use the New Marketing Campaigns Data Model, simply set Schema to NMC.

Tables

Tables describes the available tables that are easy to query and update.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to Sendgrid. They can be used to search, update, and modify information.

The Sync App models the data in SendGrid as a list of tables in a relational database that can be queried using standard SQL statements.

SendGrid Connector for CData Sync Tables

Name	Description
Alerts	Create, update, delete, and query Alerts in SendGrid.
ApiKeys	Query, Create, Update and Delete the available ApiKeys in SendGrid.
Blocks	Query and Delete the available Blocks in SendGrid.
Bounces	Query and Delete the available Bounces in SendGrid.
CancelScheduledSends	Update, delete, and query Cancel Scheduled Sends in SendGrid.
GlobalUnsubscribes	Query Global Unsubscribes in SendGrid.
InvalidEmails	Query and Delete invalid emails in SendGrid.
NMCCustomFields	Create, delete, and query New Marketing Campaign Custom Fields in SendGrid.
NMCLists	Create, update, delete, and query the available New Marketing Campaign Lists in SendGrid.
NMCSegments	Create, update, delete, and query New Marketing Campaign Segments in SendGrid.
NMCSenders	Create, update, delete, and query New Marketing Campaign Senders in SendGrid.
NMCSingleSends	Create, update, delete, and query the available New Marketing Campaign Single Sends in SendGrid.
Spam	Query and delete Spam reports in SendGrid.
Teammates	Query Teammates in SendGrid.
UnsubscribeGroups	Create, update, delete, and query Unsubscribe Groups in SendGrid.
Users	Query the available Users in SendGrid.

Create, update, delete, and query Alerts in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM Alerts
SELECT * FROM Alerts WHERE Id = '148825'

Insert

The following columns are required in an insert for Stats Notification: Type, EmailTo, Frequency.

INSERT INTO Alerts (Type, EmailTo, Frequency) VALUES ('stats_notification', '[email protected]', 'daily')

The following columns are required in an insert for Usage Limit: Type, EmailTo, Percentage.

INSERT INTO Alerts (Type, EmailTo, Percentage) VALUES ('usage_limit', '[email protected]', 20)

Update

You can only update EmailTo by specifying an Id.

UPDATE Alerts SET EmailTo = '[email protected]' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM Alerts WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The ID of the alert.
CreatedAt	Datetime	True		Date when the alert was created.
UpdatedAt	Datetime	True		Date when the alert was updated.
EmailTo	String	False		The email address that the alert will be sent to.
Percentage	Integer	False		The email address that the alert will be sent to.
Type	String	False		The type of alert. Allowed values (usage_limit, stats_notification).
Frequency	String	False		If the alert is of type stats_notification, this indicates how frequently the stats notifications will be sent.(daily, weekly, monthly).

Query, Create, Update and Delete the available ApiKeys in SendGrid.

Table-Specific Information

Select

To get all the Api Keys

SELECT * FROM ApiKeys

To get information about a specific Api Key

SELECT * FROM Apikeys WHERE ApiKeyId = 'Dqdvdvdvdv4yLeuVQqO3rFxN5ktUxA'

Insert

The following columns are required in an insert: Name. VALUES for Scopes are: alerts.create, alerts.read, alerts.update, alerts.delete, asm.groups.create, asm.groups.read, asm.groups.update, asm.groups.delete, ips.pools.ips.read, mail.send, mail_settings.bcc.read, mail_settings.bcc.update, mail_settings.address_whitelist.read, mail_settings.address_whitelist.update, mail_settings.footer.read, mail_settings.footer.update, mail_settings.forward_spam.read, mail_settings.forward_spam.update, mail_settings.plain_content.read, mail_settings.plain_content.update, mail_settings.spam_check.read, mail_settings.spam_check.update, mail_settings.bounce_purge.read, mail_settings.bounce_purge.update, mail_settings.forward_bounce.read, mail_settings.forward_bounce.update, partner_settings.new_relic.read, partner_settings.new_relic.update, partner_settings.sendwithus.read, partner_settings.sendwithus.update, tracking_settings.click.read, tracking_settings.click.update, tracking_settings.subscription.read, tracking_settings.subscription.update, tracking_settings.open.read, tracking_settings.open.update, tracking_settings.google_analytics.read, tracking_settings.google_analytics.update, user.webhooks.event.settings.read, user.webhooks.event.settings.update, user.webhooks.event.test.create, user.webhooks.event.test.read, user.webhooks.event.test.update, user.webhooks.parse.settings.create, user.webhooks.parse.settings.read, user.webhooks.parse.settings.update, user.webhooks.parse.settings.delete, stats.read, stats.global.read, categories.stats.read, categories.stats.sums.read, devices.stats.read, clients.stats.read, clients.phone.stats.read, clients.tablet.stats.read, clients.webmail.stats.read, clients.desktop.stats.read, geo.stats.read, mailbox_providers.stats.read, browsers.stats.read, user.webhooks.parse.stats.read, templates.create, templates.read, templates.update, templates.delete, templates.versions.create, templates.versions.read, templates.versions.update, templates.versions.delete, templates.versions.activate.create, user.timezone.read, user.timezone.update, user.settings.enforced_tls.read, user.settings.enforced_tls.update, api_keys.create, api_keys.read, api_keys.update, api_keys.delete, email_activity.read, categories.create, categories.read, categories.update, categories.delete, mail_settings.template.read, mail_settings.template.update, marketing_campaigns.create, marketing_campaigns.read, marketing_campaigns.update, marketing_campaigns.delete, mail.batch.create, mail.batch.read, mail.batch.update, mail.batch.delete, user.scheduled_sends.create, user.scheduled_sends.read, user.scheduled_sends.update, user.scheduled_sends.delete, access_settings.whitelist.create, access_settings.whitelist.read, access_settings.whitelist.update, access_settings.whitelist.delete, access_settings.activity.read, whitelabel.create, whitelabel.read, whitelabel.update, whitelabel.delete, suppression.create, suppression.read, suppression.update, suppression.delete, messages.read

INSERT INTO Apikeys (Name) VALUES ('apiname')

INSERT INTO Apikeys (Name, Scopes, Sample) VALUES ('apiname', 'alerts.create,alerts.read', 'test')

Update

You can update ApiKeys by specifying an ApiKeyid.You need to specify both the name and the scopes during the UPDATE even if you don't want to update it.

UPDATE Apikeys SET Name = 'apiname2', Scopes = 'alerts.create,alerts.read' WHERE apikeyid = 'Dqdvdvdvdv4yLeuVQqO3rFxN5kscsctUxA-P_Q'

Delete

You can Delete ApiKeys by specifying an ApiKeyid.

DELETE FROM ApiKeys WHERE apikeyid = 'CMp4C8klscscscsQ0GxqvxtK2-P_Q'

Columns

Name	Type	ReadOnly	References	Description
ApiKeyId [KEY]	String	False		The ID of your API Key.
Name	String	False		The name of your API Key.
Scopes	String	False		The permissions this API Key has access to. This is populated only when Id is specified.

Query and Delete the available Blocks in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Blocks WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Blocks by specifying an Email.

DELETE FROM Blocks WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the block was created.
Reason	String	False		The reason for the block.
Email [KEY]	String	False		The email that blocked.
Status	String	False		The enhanced SMTP block response.

Query and Delete the available Bounces in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Bounces WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Bounces by specifying an Email.

DELETE FROM Bounces WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the bounce was created.
Reason	String	False		The reason for the bounce.
Email [KEY]	String	False		The email that bounced.
Status	String	False		The enhanced SMTP bounce response.

Update, delete, and query Cancel Scheduled Sends in SendGrid.

Table-Specific Information

Select

This table only supports filtering by BatchId. The following are the only possible SELECT queries:

SELECT * FROM CancelScheduledSends
SELECT * FROM CancelScheduledSends WHERE BatchId = 148825

Update

You can update CancelScheduledSends by specifying an BatchId.

Allowed Values : cancel, pause

UPDATE CancelScheduledSends SET Status = 'cancel'  WHERE BatchId = 1310789

Delete

You can only delete from CancelScheduledSends by specifying an BatchId.

DELETE FROM CancelScheduledSends WHERE BatchId = 1310789

Columns

Name	Type	ReadOnly	References	Description
BatchId [KEY]	String	False		The Id of the batch.
Status	String	False		The status of the scheduled send.Allowed Values: cancel, pause

Query Global Unsubscribes in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. However this filter is optional. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalUnsubscribes WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

INSERT

Performing an Insert operation in this table you need to specify an email which will be added to the GlobalUnsubscribes list. For example:

INSERT INTO GlobalUnsubscribes (Email) VALUES ('[email protected]')

DELETE

Performing a Delete operation in this table you need to specify an email which will be removed from the GlobalUnsubscribes list. For example:

DELETE FROM GlobalUnsubscribes WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
Email [KEY]	String	False		Email of the unsubscriber.
CreatedAt	Datetime	True		The time the unsubscriber was created.

Query and Delete invalid emails in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM InvalidEmails
SELECT * FROM InvalidEmails WHERE Email = '[email protected]'

Delete

You can only delete from InvalidEmails by specifying an Email.

DELETE FROM InvalidEmails WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
Created	Datetime	True		The time the when the email address was added to the invalid emails list.
Reason	String	False		A reason explaining why the email address was marked as invalid..
Email [KEY]	String	False		The email address that was marked as invalid.

Create, delete, and query New Marketing Campaign Custom Fields in SendGrid.

Table Specific Information

New Marketing Campaign Custom fields are filters for a Contact that are defined by the user. NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the NMCCustomFields or filter them by Id:

SELECT * FROM NMCCustomFields WHERE Id = '1'

Use quotations to filter on NMC Custom Fields regardless of type.

Insert

To insert on NMCCustomFields specify the Name of the field and its Type. The Type can be text, date, or number.

INSERT INTO NMCCustomFields (name, type) VALUES ('age', 'number') 

Update

UPDATE NMCCustomFields SET name = 'updated name' WHERE Id = '123' 

Delete

You can only delete from NMCCustomFields by specifying an Id:

DELETE FROM NMCCustomFields WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the custom field.
Name	String	False		The name of the custom field.
Type	String	False		The type of the custom field. Allowed values are number, text, or date.

Create, update, delete, and query the available New Marketing Campaign Lists in SendGrid.

Table-Specific Information

NMC prefix stands for New Marketing Campaign.

Select

The NMCLists table can only filtered by its Id.

SELECT * FROM NMCLists WHERE Id = '123'

Insert

Only the Name for the NMCLists is required to insert into this table.

INSERT INTO NMCLists (Name) VALUES ('TestList')

Update

You can only update from NMCLists by specifying an Id.

UPDATE NMCLists SET Name = 'New Name' WHERE Id = '123'

Delete

You can only delete from NMCLists by specifying an Id.

DELETE FROM NMCLists WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the list.
Name	String	False		The name of the list.
ContactCount	Integer	True		The recipient count of the list.

Create, update, delete, and query New Marketing Campaign Segments in SendGrid.

Table-Specific Information

NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the segments or filter them by Id.

SELECT * FROM NMCSegments WHERE Id = '1'

Insert

A Segment divides NMCContacts by certain criteria. To specify these criteria use the QueryDSL filter.

INSERT INTO NMCSegments (Name, QueryDSL) VALUES ("New Segment", "Segment Cdata", "first_name LIKE '%test%'"))

The statement above creates NMCSegments containing NMCContacts with FirstName containing the value "test".

Update

You can update the name or the conditions. Since the whole set will be overwritten, specify a full set.

UPDATE NMCSegments SET Name = "Name Updated", QueryDSL = "first_name LIKE '%test update%'" WHERE Id = '123'

Delete

You can only delete NMCSegments by Id:

DELETE FROM NMCSegments WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the segment.
Name	String	False		The name of the segment.
ParentListId	String	False		The Id of the list associated with the segment.
ContactsCount	Integer	True		The recipient count of the segment.
CreatedAt	Datetime	True		Field name for the first condition.
SampleUpdatedAt	Datetime	False		Field name for the first condition.
UpdatedAt	Datetime	True		Field name for the first condition.
QueryDSL	String	False		Field name for the first condition.
NextSampleUpdate	Datetime	True		The last updated samples date.

Create, update, delete, and query New Marketing Campaign Senders in SendGrid.

Table-Specific Information

NMC stands for New Marketing Campaign.

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM NMCSenders
SELECT * FROM NMCSenders WHERE Id = '148825'

Insert

The following columns are required in an insert: Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country.

INSERT INTO NMCSenders (Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country) VALUES ('Nickname', 'FromEmail', 'FromName', 'ReplytoEmail', 'Address', 'City', 'Country')

Update

You can update NMCSenders by specifying an Id.

UPDATE NMCSenders SET Nickname = 'Nickname', FromEmail = 'FromEmail', FromName = 'FromName', ReplytoEmail = 'ReplytoEmail', Address = 'Address', City = 'City', Country = 'Country'  WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM NMCSenders WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The unique identifier of the sender identity.
Nickname	String	False		A nickname for the sender identity. Not used for sending..
FromEmail	String	False		This is where the email will appear to originate from for your recipient.
FromName	String	False		This is the name appended to the from email field. IE - Your name or company name.
ReplytoEmail	String	False		This is the email that your recipient will reply to.
ReplytoName	String	False		This is the name appended to the reply to email field. IE - Your name or company name.
Address	String	False		The physical address of the sender identity.
Address2	String	False		Additional sender identity address information.
City	String	False		The city of the sender identity.
State	String	False		The state of the sender identity.
Zip	String	False		The zipcode of the sender identity.
Locked	String	False		Indicator if the sender is locked.
VerifiedStatus	String	False		Verified status of the sender.
VerifiedReason	String	False		Verified reason of the sender.
Country	String	False		The country of the sender identity.
CreatedAt	Datetime	True		The end of the time range when the bounce was created.
UpdatedAt	Datetime	True		The end of the time range when the bounce was created.

Create, update, delete, and query the available New Marketing Campaign Single Sends in SendGrid.

Table-Specific Information

NMC stands for New Marketing Campaign.

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM NMCSingleSends
SELECT * FROM NMCSingleSends WHERE Id = '148825'

Insert

The following columns are required in an insert: Name.

INSERT INTO NMCSingleSends (Name) VALUES ('SingleSend Name')

Update

You can update NMCSingleSends by specifying an Id.

UPDATE NMCSingleSends SET Name = 'SingleSend Updated' WHERE Id = 1310789

Delete

You can only delete from NMCSingleSends by specifying an Id.

DELETE FROM NMCSingleSends WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the single send.
Name	String	False		The name of the single send.
Categories	String	False		The name of the single send.
Status	String	False		The status of the single send.
CreatedAt	String	True		The created timestamp of the single send.
UpdatedAt	String	True		The updated timestamp of the single send.
IsABTest	Boolean	False		Indicator showing if the single send is an AB Test.
SendAt	Datetime	False		The ISO 8601 time at which to send the single send.
EmailConfigCustomUnsubscribeUrl	String	False		The URL allowing recipients to unsubscribe.
EmailConfigEditor	String	False		The editor, design or code, used to modify the Single Send's design in the Marketing Campaigns application user interface. This is populated only when Id is specified.
EmailConfigGeneratePlainContent	Boolean	False		If this property is set to true, plain_content is always generated from html_content. If it's set to false, plain_content is not altered. This is populated only when Id is specified.
EmailConfigHtmlContent	String	False		The HTML content of the Single Send. This property is not used when a design_id value is set. This is populated only when Id is specified.
EmailConfigIpPool	String	False		The name of the IP Pool from which the Single Send emails are sent. This is populated only when Id is specified.
EmailConfigPlainContent	String	False		The plain text content of the Single Send. This is populated only when Id is specified.
EmailConfigSenderId	Integer	False		The ID of the verified sender from whom the Single Send will be sent. This is populated only when Id is specified.
EmailConfigSubject	String	False		The subject line of the Single Send. This is populated only when Id is specified.
EmailConfigSuppressionGroupId	Integer	False		The ID of the Suppression Group to allow recipients to unsubscribe. This is populated only when Id is specified.
SendToListIds	String	False		The IDs of each contact list to which the Single Send will be sent. This is populated only when Id is specified.
SendToSegmentIds	String	False		The IDs of each segment to which the Single Send will be sent. This is populated only when Id is specified.
SendToAll	Boolean	False		If this property is set to true, the Single Send will be sent to all of your contacts. This is populated only when Id is specified.

Query and delete Spam reports in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM Spam
SELECT * FROM Spam WHERE Email = '[email protected]'

Delete

Delete from Spam 

You can delete from Spam by specifying an Email.

DELETE FROM Spam WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time when the spam report was made.
Email [KEY]	String	False		The email address of the recipient who marked your message as spam.
Ip	String	False		The IP address that the message was sent on.

Query Teammates in SendGrid.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

• Username supports the '=,IN' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Teammates WHERE Username = 'test'

SELECT * FROM Teammates WHERE Username IN ('test', 'test2')

Insert

The following columns are required in an insert: Email and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

The IsAdmin is optional and by default is accepted as false from the API.

INSERT INTO Teammates (Email, Scopes) VALUES ('[email protected]', 'user.profile.read,user.profile.update')

INSERT INTO Teammates (Email, Scopes, IsAdmin) VALUES ('[email protected]', 'user.profile.read,user.profile.update', 'true')

Update

The following columns are required in an update: Username and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

UPDATE Teammates SET Scopes = 'user.profile.read' WHERE Username = 'testuser'

Delete

Only the username column is required to delete a record from Teammates table. For example:

DELETE FROM Teammates WHERE Username = 'testuser'

Columns

Name	Type	ReadOnly	References	Description
Username [KEY]	String	False		The username of the teammate.
Email	String	False		Email of the teammate.
FirstName	String	False		First name of the teammate.
LastName	String	False		Last name of the teammate.
UserType	String	False		User type of the teammate.
IsAdmin	Boolean	False		Indicator if the teammate is admin.
Phone	String	False		Phone of the teammate.
Website	String	False		Website of the teammate.
Company	String	False		Company of the teammate.
Address	String	False		Address of the teammate.
Address2	String	False		Address 2 of the teammate.
City	String	False		City of the teammate.
State	String	False		State of the teammate.
Country	String	False		Country of the teammate.
Zip	String	False		Zip of the teammate.
Scopes	String	False		Scope of the permissions for the new teammate. Multiple values are allowed and should be split by a comma symbol. Possible values are: user.account.read,user.credits.read,user.email.create,user.email.read,user.email.update,user.email.delete,user.profile.create,user.profile.read,user.profile.update,user.profile.delete

Create, update, delete, and query Unsubscribe Groups in SendGrid.

Table-Specific Information

Select

UnsubscribeGroups can only be filtered by Id.

SELECT * FROM UnsubscribeGroups WHERE Id = '1'

Insert

Provide a Name and a Description to insert into UnsubscribeGroups:

INSERT INTO UnsubscribeGroups (Name, Description, IsDefault) VALUES ('Unsubscribe1', 'General Unsubscribe', true)

Update

You can only update UnsubscribeGroups by specifying an Id.

UPDATE UnsubscribeGroups SET Name = 'New Name', Description = 'New Description' WHERE Id = '1'

Delete

You can only delete from UnsubscribeGroups by specifying an Id.

DELETE FROM UnsubscribeGroups WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the unsubscribe group.
Name	String	False		The name of the unsubscribe group.
Description	String	False		The description of the unsubscribe group.
LastEmailSentAt	Datetime	True		The last time an email was sent to the unsubscribe group.
IsDefault	Boolean	False		If the unsubscribe group is the default group or not.
Unsubscribes	Integer	True		The number of unsubscribes from this group.

Query the available Users in SendGrid.

View-Specific Information

Select

Query this table to retrieve your current profile details.

SELECT * FROM Users 

Update

You can update the all the columns marked as readonly false.

UPDATE Users Set Address='Milpitas', Address2='North Carolina' WHERE id='38713966'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the user.
Address	String	False		The address of the user.
Address2	String	False		The secondary address field for the user.
City	String	False		The city of the user.
Company	String	False		The company of the user.
Country	String	False		The country of the user.
FirstName	String	False		The first name of the user.
LastName	String	False		The last name of the user.
Phone	String	False		The phone number of the user.
State	String	False		The state the user is located in.
Website	String	False		The Web site URL of the user.
Zip	String	False		The ZIP code of the user.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

SendGrid Connector for CData Sync Views

Name	Description
AutomationStats	Get All Automation Stats
Categories	Query the available Categories in SendGrid.
CategoryStats	Query the available Category Stats in SendGrid.
Designs	List Designs
GlobalStats	Query the available Global Stats in SendGrid.
GroupSuppressions	Query group suppressions in SendGrid.
IPAccessManagement	Query Global Unsubscribes in SendGrid.
MailSettings	Query all the mail settings in SendGrid.
MessageEvents	List of events related to email message in SendGrid.
Messages	Query Email Activities in SendGrid.
NMCContacts	Query New Marketing Campaign Contacts in SendGrid.
NMCReservedFields	Query the available New Marketing Campaign Reserved Fields in SendGrid.
PartnerSettings	Query all the partner settings in SendGrid.
SingleSendClickTrackingStats	Get Single Send Click Tracking Stats by ID
SingleSendsStats	Get All Single Sends Stats
StatsByBrowser	Query Browser Statistics in SendGrid.
StatsByClient	Query Client Statistics in SendGrid.
StatsByDevice	Query Device Statistics in SendGrid.
StatsByLocation	Query Location Statistics in SendGrid.
StatsByMailboxProvider	Query Mailbox Provider Statistics in SendGrid.
SubuserStats	Query Subuser Statistics in SendGrid.
Templates	Retrieve paged transactional templates

Get All Automation Stats

View-Specific Information

Select

The following is an example to query the AutomationStats view:

 
SELECT * FROM AutomationStats

Columns

Name	Type	References	Description
Id	String		This is the ID of the Automation you are requesting stats for.
Aggregation	String		This describes the time unit to which the stat is rolled up. It is based on the aggregated_by parameter included in the request. It can be 'total' or the date (in YYYY-MM-DD format) the stats are for.
StepId	String		This is the ID of the step if the stats were requested to be grouped by step_id.
BounceDrops	Integer		BounceDrops
Bounces	Integer		Bounces
Clicks	Integer		Clicks
Delivered	Integer		Delivered
InvalidEmails	Integer		InvalidEmails
Opens	Integer		Opens
Requests	Integer		Requests
SpamReportDrops	Integer		SpamReportDrops
SpamReports	Integer		SpamReports
UniqueClicks	Integer		UniqueClicks
UniqueOpens	Integer		UniqueOpens
Unsubscribes	Integer		Unsubscribes

Query the available Categories in SendGrid.

View-Specific Information

Select

Query this table to get available categories in SendGrid.

SELECT * FROM Categories

Columns

Name	Type	References	Description
Name	String		The name of the categories.

Query the available Category Stats in SendGrid.

View-Specific Information

Select

The following is an example to query the CategoryStats view:

 
SELECT * FROM CategoryStats WHERE Name = 'news'

You can also filter by Date. If you do not specify one, a default span of the past 1 year is used. The operators =, >=, <=, <, and > are supported for this column.

SELECT * FROM CategoryStats WHERE Name = 'news' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the category.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails in this category dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

List Designs

View-Specific Information

Select

The following is an example to query the Designs view:

 
SELECT * FROM Designs

Columns

Name	Type	References	Description
Id	String		ID of the Design.
UpdatedAt	String		Datetime that Design was last updated.
CreatedAt	String		Datetime that Design was created.
ThumbnailURL	String		A Thumbnail preview of the template's html content.
Name	String		The name of the new design.
Editor	String		The editor used in the UI.

Query the available Global Stats in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalStats 

SELECT * FROM GlobalStats WHERE Date > '2016-01-01' AND Date < '2016-03-30'

Columns

Name	Type	References	Description
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
BounceDrops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails globally dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

Query group suppressions in SendGrid.

Table-Specific Information

Select

The following are examples to query the GroupSuppressions table:

SELECT * FROM GroupSuppressions WHERE GroupId = 391

SELECT * FROM GroupSuppressions WHERE GroupId IN (391, 392, 393)

Columns

Name	Type	References	Description
GroupId [KEY]	Integer		Email of the unsubscriber.
Emails	String		The time the unsubscriber was created.

Query Global Unsubscribes in SendGrid.

View-Specific Information

Select

The Sync App will use the SendGrid API to retrieve information about the IP addresses which have accessed the account. However there is no filtering supported server-side for this view.

Only the following query is processed server side:

SQL

SELECT * FROM IPAccessManagement

Columns

Name	Type	References	Description
IP	String		IP Address.
Allowed	Boolean		Indicator if this IP is allowed for access.
AuthMethod	String		Authentication method.
FirstAt	Timestamp		First time when IP has accessed the account.
LastAt	Timestamp		Last time when IP has accessed the account.
Location	String		Location of the IP.

Query all the mail settings in SendGrid.

Table-Specific Information

Select

You can retrieve all the mail settings.

SELECT * FROM MailSettings

Columns

Name	Type	References	Description
Title	String		The title of the mail setting.
Enabled	String		Indicates if this mail setting is currently enabled.
Name	String		The name of the mail setting.
Description	String		A description of the mail setting.

List of events related to email message in SendGrid.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

• MessageId supports the '=' comparisons.

SQL

SELECT * FROM MessageEvents WHERE MessageId = '1234'

Columns

Name	Type	References	Description
MessageId	String		The Id of the message.
EventName	String		Name of event. The allowed values are bounced, opened, clicked, processed, dropped, delivered, deferred, spam_report, unsubscribe, group_unsubscribe, group_resubscribe.
Processed	Datetime		The date when the event was processed.
Reason	String		Explanation of what caused the message to be 'bounced', 'deferred', or 'blocked'.
AttemptNum	Integer		Used with 'deferred' events to indicate the attempt number out of 10.
Url	String		Used with 'clicked' event to indicate which url the user clicked.
BounceType	String		Use to distinguish between types of bounces. The allowed values are soft, hard.
HttpUserAgent	String		Client recipient used to click or open message.
MXServer	String		The MX server that received the email.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
Categories	String		Categories users associated to the message.
UniqueArgs	String		JSON hash of key-value pairs associated with the message.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.

Query Email Activities in SendGrid.

NOTE: If you want access to Email activities, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Note: The API will return only 1000 records. Please choose from the available filters to restrict the search.

• Id supports the '=,IN' comparisons.
• Status supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Subject supports the '=,!=,LIKE,NOT LIKE' comparisons.
• FromEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
• ToEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Clicks supports the '>,<,<=,>=,=,!=' comparisons.
• LastEventTime supports the '>=, <=, BETWEEN' comparisons.
• ApiKeyId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• AsmGroupId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• MarketingCampaignId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• MarketingCampaignName supports the '=,!=,LIKE,NOT LIKE' comparisons.
• OriginatingIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
• OutboundIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Teammate supports the '=,!=,LIKE,NOT LIKE' comparisons.
• TemplateId supports the '=,!=,LIKE,NOT LIKE' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Messages WHERE Id = '1234'

SELECT * FROM Messages WHERE Id IN ('1234', '4567')

SELECT * FROM Messages WHERE Status = 'not_delivered'

SELECT * FROM Messages WHERE LastEventTime BETWEEN '2020-01-01' AND '2020-01-15'

Columns

Name	Type	References	Description
Id [KEY]	String		The Id of the message.
Status	String		Status of the email.
Subject	String		Subject of the email.
FromEmail	String		The email of the sender.
ToEmail	String		The email of the receiver.
ClicksCount	Integer		The number of clicks.
OpensCount	Integer		The number of times that the email was opened.
LastEventTime	Datetime		The datetime of the event.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
MarketingCampaignId	String		The id of the marketing campaign.
MarketingCampaignName	String		The name of the marketing campaign.
MarketingCampaignSplitId	String		Split id of the campaign.
MarketingCampaignVersion	String		Version of the campaign.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.
Events	String		List of events related to email message in SendGrid.

Query New Marketing Campaign Contacts in SendGrid.

View-Specific Information

New Marketing Campaign contacts are recepients that are managed by the user. NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the NMCContacts or filter them by Id, ListId or SegmentId:

SELECT * FROM NMCCustomFields WHERE Id = '1'
SELECT * FROM NMCCustomFields WHERE Id IN ('1', '2', '3')
SELECT * FROM NMCCustomFields WHERE ListId = '1'
SELECT * FROM NMCCustomFields WHERE ListId IN ('1', '2', '3')
SELECT * FROM NMCCustomFields WHERE SegmentId = '1'
SELECT * FROM NMCCustomFields WHERE SegmentId IN ('1', '2', '3')

Columns

Name	Type	References	Description
Id [KEY]	String		This column is dynamic and maps to the corresponding field in the API.
AddressLine1	String		This column is dynamic and maps to the corresponding field in the API.
AddressLine2	String		This column is dynamic and maps to the corresponding field in the API.
City	String		This column is dynamic and maps to the corresponding field in the API.
Country	String		This column is dynamic and maps to the corresponding field in the API.
PostalCode	String		This column is dynamic and maps to the corresponding field in the API.
StateProvinceRegion	String		This column is dynamic and maps to the corresponding field in the API.
Line	String		This column is dynamic and maps to the corresponding field in the API.
Facebook	String		This column is dynamic and maps to the corresponding field in the API.
PhoneNumber	String		This column is dynamic and maps to the corresponding field in the API.
UniqueName	String		This column is dynamic and maps to the corresponding field in the API.
WhatsApp	String		This column is dynamic and maps to the corresponding field in the API.
Email	String		This column is dynamic and maps to the corresponding field in the API.
FirstName	String		This column is dynamic and maps to the corresponding field in the API.
LastName	String		This column is dynamic and maps to the corresponding field in the API.
CreatedAt	Datetime		This column is dynamic and maps to the corresponding field in the API.
UpdatedAt	Datetime		This column is dynamic and maps to the corresponding field in the API.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
SegmentId	String		This column is dynamic and maps to the corresponding field in the API.
ListId	String		This column is dynamic and maps to the corresponding field in the API.

Query the available New Marketing Campaign Reserved Fields in SendGrid.

View-Specific Information

Select

This table does not support filtering. NMC prefix stands for New Marketing Campaign.

SELECT * FROM NMCReservedFields

Columns

Name	Type	References	Description
Id	String		The Id of the reserved field.
Name	String		The name of the reserved field.
Type	String		The type of the reserved field.

Query all the partner settings in SendGrid.

Table-Specific Information

Select

You can retrieve all the partner settings.

SELECT * FROM PartnerSettings

Columns

Name	Type	References	Description
Title	String		The title of the partner.
Enabled	String		Indicates if this partner setting has been enabled.
Name	String		The name of the partner setting.
Description	String		A description of this partner setting.

Get Single Send Click Tracking Stats by ID

View-Specific Information

Select

Get Single Send Click Tracking Stats. To access the view, you’ll need to provide the Single Send ID.

 
SELECT * FROM SingleSendClickTrackingStats where SingleSendId = '74550809-66a2-11ef-ad7f-460f539623db'

Columns

Name	Type	References	Description
SingleSendId	String	NMCSingleSends.Id	The ID of Single Send for which you want to retrieve Click Tracking stats.
Url	String		This is the URL of the link clicked. If {{custom_fields}} are part of the URL, they will be included.
UrlLocation	Integer		This is the location of the link clicked in each Single Send A/B variation, or in the Single Send itself if there are no variations. Links are numbered from the top down; the topmost link is index 0.
ABvariation	String		This is the A/B variation of the Single Send stat returned. It is set to 'all' if the ab_variation query parameter was not set in the request and group_by doesn't contain ab_variation.
ABphase	String		This is the A/B phase of the Single Send stat returned. If the ab_phase query parameter was not provided, it will return 'all'.
Clicks	Integer		The number of clicks on this particular link.

Get All Single Sends Stats

View-Specific Information

Select

The Sync App uses the SendGrid API to process search criteria that refer to the Id column, while other filters are processed client side within the Sync App.

Get All Single Sends Stats

SELECT * FROM SingleSendsStats 

Get Single Send Stats by ID

SELECT * FROM SingleSendsStats where id = '25723e10-669e-11ef-b50f-0e4b6974e9ad'

Columns

Name	Type	References	Description
Id [KEY]	String		This is the ID of the Single Send you require stats for.
ABvariation	String		This is the A/B variation of the Single Send stat returned. If the group_by parameter doesn't include ab_variation in the request, then the value is 'all'.
ABphase	String		This is the A/B phase of the Single Send stat returned. If the group_by parameter doesn't include ab_phase in the request, then the value is 'all'.
Aggregation	String		This describes the time unit to which the stat is rolled up. It is based on the aggregated_by parameter included in the request. It can be 'total' or the date (in YYYY-MM-DD format) the stats are for.
BounceDrops	Integer		BounceDrops
Bounces	Integer		Bounces
Clicks	Integer		Clicks
Delivered	Integer		Delivered
InvalidEmails	Integer		InvalidEmails
Opens	Integer		Opens
Requests	Integer		Requests
SpamReportDrops	Integer		SpamReportDrops
SpamReports	Integer		SpamReports
UniqueClicks	Integer		UniqueClicks
UniqueOpens	Integer		UniqueOpens
Unsubscribes	Integer		Unsubscribes

Query Browser Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByBrowser WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Browser Statistics by name.

SELECT * FROM StatsByBrowser WHERE Name = 'Firefox'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.

Query Client Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByClient WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Client Statistics by name.

SELECT * FROM StatsByClient WHERE Name = 'Other'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Query Device Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByDevice WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Device Statistics by name.

SELECT * FROM StatsByDevice WHERE Name = 'Phone'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Query Location Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByLocation WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Location Statistics by name.

SELECT * FROM StatsByLocation WHERE Name = 'US'

To query Location Statistics by type.

SELECT * FROM StatsByLocation WHERE Type = 'country'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Query Mailbox Provider Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByMailboxProvider WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Mailbox Provider Statistics by name.

SELECT * FROM StatsByMailboxProvider WHERE Name = 'Microsoft Outlook Live'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Query Subuser Statistics in SendGrid.

View-Specific Information

Select

You can filter by SubuserName to retrieve stats of a specific subuser.

SELECT * FROM SubuserStats WHERE SubuserName = 'Example'

You can also filter by Date. Operators =, >=, <=, <, and > are supported for this column. If you do not specify one, a default span of the past 1 year is used.

SELECT * FROM SubuserStats WHERE SubuserName = 'News' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns

Name	Type	References	Description
Date	Datetime		The time the subuser statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links that were clicked in the emails from this subuser.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails from this subsuer that were confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times emails from this subuser were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid by this subuser.
Requests	Integer		The number of emails that you requested to send via SendGrid from this subuser.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails from this subuser that were marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked on links in your emails from this subuser.
UniqueOpens	Integer		The number of unique recipients who opened your emails from this subuser.
UnsubscribeDrops	Integer		The number of emails from this subuser dropped by SendGrid because the recipients unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails from this subuser.
SubuserName	String		The name of the statistic.
Type	String		The type of the statistic.

Retrieve paged transactional templates

View-Specific Information

Select

The following is an example to query the Templates view:

 
SELECT * FROM Templates

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator.

• Generation supports the '='.

 
SELECT * FROM Templates WHERE Generation = 'dynamic'

Columns

Name	Type	References	Description
ID [KEY]	String		The ID of the transactional template.
Generation	String		Defines the generation of the template. By default, we will get data for both dynamic and legacy. The allowed values are dynamic, legacy.
Name	String		The name for the transactional template.
UpdatedAt	String		The date and time that this transactional template version was updated.
Versions	String		The different versions of this transactional template.

The CData Sync App models the Legacy API as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files.

However, the Recipients table is dynamic: The Sync App connects to SendGrid and gets the metadata for this table by calling the appropriate Web services. Any changes you make to the custom filters you define for Recipients are available when you connect; you can use these custom filters as columns in the WHERE clause. These custom filters are surfaced in the CustomFields table.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

To use the Legacy Data Model, simply set Schema to Legacy.

Tables

Tables describes the available tables that are easy to query and update.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to Sendgrid. They can be used to search, update, and modify information.

The Sync App models the data in SendGrid as a list of tables in a relational database that can be queried using standard SQL statements.

SendGrid Connector for CData Sync Tables

Name	Description
Alerts	Create, update, delete, and query Alerts in SendGrid.
ApiKeys	Query, Create, Update and Delete the available ApiKeys in SendGrid.
Blocks	Query and Delete the available Blocks in SendGrid.
Bounces	Query and Delete the available Bounces in SendGrid.
CancelScheduledSends	Update, delete, and query Cancel Scheduled Sends in SendGrid.
CustomFields	Create, delete, and query Custom Fields in SendGrid.
GlobalUnsubscribes	Query Global Unsubscribes in SendGrid.
InvalidEmails	Query and Delete invalid emails in SendGrid.
Lists	Create, update, delete, and query the available Lists in SendGrid.
MarketingCampaigns	Create, update, delete, and query Marketing Campaigns in SendGrid.
Recipients	The SendGrid table Recipients.
Schedules	Create, update, delete, and query Campaign Schedules in SendGrid.
Segments	Create, update, delete, and query Segments in SendGrid.
SenderIdentities	Create, update, delete, and query Sender Identities in SendGrid.
Spam	Query and delete Spam reports in SendGrid.
Teammates	Query Teammates in SendGrid.
UnsubscribeGroups	Create, update, delete, and query Unsubscribe Groups in SendGrid.
Users	Query the available Users in SendGrid.

Create, update, delete, and query Alerts in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM Alerts WHERE Id = '148825'

Insert

The following columns are required in an insert for Stats Notification: Type, EmailTo, Frequency.

INSERT INTO Alerts (Type, EmailTo, Frequency) VALUES ('stats_notification', '[email protected]', 'daily')

The following columns are required in an insert for Usage Limit: Type, EmailTo, Percentage.

INSERT INTO Alerts (Type, EmailTo, Percentage) VALUES ('usage_limit', '[email protected]', 20)

Update

You can only update EmailTo by specifying an Id.

UPDATE Alerts SET EmailTo = '[email protected]' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM Alerts WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The ID of the alert.
CreatedAt	Datetime	True		Date when the alert was created.
UpdatedAt	Datetime	True		Date when the alert was updated.
EmailTo	String	False		The email address that the alert will be sent to.
Percentage	Integer	False		The email address that the alert will be sent to.
Type	String	False		The type of alert. Allowed values (usage_limit, stats_notification).
Frequency	String	False		If the alert is of type stats_notification, this indicates how frequently the stats notifications will be sent.(daily, weekly, monthly).

Query, Create, Update and Delete the available ApiKeys in SendGrid.

Table-Specific Information

Select

To get all the Api Keys

SELECT * FROM ApiKeys

To get information about a specific Api Key

SELECT * FROM Apikeys WHERE ApiKeyId = 'Dqdvdvdvdv4yLeuVQqO3rFxN5ktUxA'

Insert

The following columns are required in an insert: Name. VALUES for Scopes are: alerts.create, alerts.read, alerts.update, alerts.delete, asm.groups.create, asm.groups.read, asm.groups.update, asm.groups.delete, ips.pools.ips.read, mail.send, mail_settings.bcc.read, mail_settings.bcc.update, mail_settings.address_whitelist.read, mail_settings.address_whitelist.update, mail_settings.footer.read, mail_settings.footer.update, mail_settings.forward_spam.read, mail_settings.forward_spam.update, mail_settings.plain_content.read, mail_settings.plain_content.update, mail_settings.spam_check.read, mail_settings.spam_check.update, mail_settings.bounce_purge.read, mail_settings.bounce_purge.update, mail_settings.forward_bounce.read, mail_settings.forward_bounce.update, partner_settings.new_relic.read, partner_settings.new_relic.update, partner_settings.sendwithus.read, partner_settings.sendwithus.update, tracking_settings.click.read, tracking_settings.click.update, tracking_settings.subscription.read, tracking_settings.subscription.update, tracking_settings.open.read, tracking_settings.open.update, tracking_settings.google_analytics.read, tracking_settings.google_analytics.update, user.webhooks.event.settings.read, user.webhooks.event.settings.update, user.webhooks.event.test.create, user.webhooks.event.test.read, user.webhooks.event.test.update, user.webhooks.parse.settings.create, user.webhooks.parse.settings.read, user.webhooks.parse.settings.update, user.webhooks.parse.settings.delete, stats.read, stats.global.read, categories.stats.read, categories.stats.sums.read, devices.stats.read, clients.stats.read, clients.phone.stats.read, clients.tablet.stats.read, clients.webmail.stats.read, clients.desktop.stats.read, geo.stats.read, mailbox_providers.stats.read, browsers.stats.read, user.webhooks.parse.stats.read, templates.create, templates.read, templates.update, templates.delete, templates.versions.create, templates.versions.read, templates.versions.update, templates.versions.delete, templates.versions.activate.create, user.timezone.read, user.timezone.update, user.settings.enforced_tls.read, user.settings.enforced_tls.update, api_keys.create, api_keys.read, api_keys.update, api_keys.delete, email_activity.read, categories.create, categories.read, categories.update, categories.delete, mail_settings.template.read, mail_settings.template.update, marketing_campaigns.create, marketing_campaigns.read, marketing_campaigns.update, marketing_campaigns.delete, mail.batch.create, mail.batch.read, mail.batch.update, mail.batch.delete, user.scheduled_sends.create, user.scheduled_sends.read, user.scheduled_sends.update, user.scheduled_sends.delete, access_settings.whitelist.create, access_settings.whitelist.read, access_settings.whitelist.update, access_settings.whitelist.delete, access_settings.activity.read, whitelabel.create, whitelabel.read, whitelabel.update, whitelabel.delete, suppression.create, suppression.read, suppression.update, suppression.delete, messages.read

INSERT INTO Apikeys (Name) VALUES ('apiname')

INSERT INTO Apikeys (Name, Scopes, Sample) VALUES ('apiname', 'alerts.create,alerts.read', 'test')

Update

You can update ApiKeys by specifying an ApiKeyid.You need to specify both the name and the scopes during the UPDATE even if you don't want to update it.

UPDATE Apikeys SET Name = 'apiname2', Scopes = 'alerts.create,alerts.read' WHERE apikeyid = 'Dqdvdvdvdv4yLeuVQqO3rFxN5kscsctUxA-P_Q'

Delete

You can Delete ApiKeys by specifying an ApiKeyid.

DELETE FROM ApiKeys WHERE apikeyid = 'CMp4C8klscscscsQ0GxqvxtK2-P_Q'

Columns

Name	Type	ReadOnly	References	Description
ApiKeyId [KEY]	String	False		The ID of your API Key.
Name	String	False		The name of your API Key.
Scopes	String	False		The permissions this API Key has access to. This is populated only when Id is specified.

Query and Delete the available Blocks in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Blocks WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Blocks by specifying an Email.

DELETE FROM Blocks WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the block was created.
Reason	String	False		The reason for the block.
Email [KEY]	String	False		The email that blocked.
Status	String	False		The enhanced SMTP block response.

Query and Delete the available Bounces in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Bounces WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Bounces by specifying an Email.

DELETE FROM Bounces WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the bounce was created.
Reason	String	False		The reason for the bounce.
Email [KEY]	String	False		The email that bounced.
Status	String	False		The enhanced SMTP bounce response.

Update, delete, and query Cancel Scheduled Sends in SendGrid.

Table-Specific Information

Select

This table only supports filtering by BatchId. The following are the only possible SELECT queries:

SELECT * FROM CancelScheduledSends
SELECT * FROM CancelScheduledSends WHERE BatchId = 148825

Update

You can update CancelScheduledSends by specifying an BatchId.

Allowed Values : cancel, pause

UPDATE CancelScheduledSends SET Status = 'cancel' WHERE BatchId = 1310789

Delete

You can only delete from CancelScheduledSends by specifying an BatchId.

DELETE FROM CancelScheduledSends WHERE BatchId = 1310789

Columns

Name	Type	ReadOnly	References	Description
BatchId [KEY]	String	False		The Id of the batch.
Status	String	False		The status of the scheduled send.Allowed Values: cancel, pause

Create, delete, and query Custom Fields in SendGrid.

Table Specific Information

Custom fields are filters for a Recipient that are defined by the user.

Select

You can retrieve all the CustomFields or filter them by Id:

SELECT * FROM CustomFields WHERE Id = '1'

Use quotations to filter on Custom Fields regardless of type.

Insert

To insert on CustomFields specify the Name of the field and its Type. The Type can be text, date, or number.

INSERT INTO CustomFields (name, type) VALUES ('age', 'number') 

Update

This table does not support updating.

Delete

You can only delete from CustomFields by specifying an Id:

DELETE FROM CustomFields WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The Id of the custom field.
Name	String	False		The name of the custom field.
Type	String	False		The type of the custom field. Allowed values are number, text, or date.

Query Global Unsubscribes in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. However this filter is optional. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalUnsubscribes WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

INSERT

Performing an Insert operation in this table you need to specify an email which will be added to the GlobalUnsubscribes list. For example:

INSERT INTO GlobalUnsubscribes (Email) VALUES ('[email protected]')

DELETE

Performing a Delete operation in this table you need to specify an email which will be removed from the GlobalUnsubscribes list. For example:

DELETE FROM GlobalUnsubscribes WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
Email [KEY]	String	False		Email of the unsubscriber.
CreatedAt	Datetime	True		The time the unsubscriber was created.

Query and Delete invalid emails in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM InvalidEmails
SELECT * FROM InvalidEmails WHERE Email = '[email protected]'

Delete

You can only delete from InvalidEmails by specifying an Email.

DELETE FROM InvalidEmails WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
Created	Datetime	True		The time the when the email address was added to the invalid emails list.
Reason	String	False		A reason explaining why the email address was marked as invalid..
Email [KEY]	String	False		The email address that was marked as invalid.

Create, update, delete, and query the available Lists in SendGrid.

Table-Specific Information

Select

The Lists table can only filtered by its Id.

SELECT * FROM Lists WHERE Id = '123'

Insert

Only the Name for the List is required to insert into this table.

INSERT INTO Lists (Name) VALUES ('TestList')

Update

You can only update from Lists by specifying an Id.

UPDATE Lists SET Name = 'New Name' WHERE Id = '123'

Delete

You can only delete from Lists by specifying an Id.

DELETE FROM Lists WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of the list.
Name	String	False		The name of the list.
RecipientCount	Integer	True		The recipient count of the list.

Create, update, delete, and query Marketing Campaigns in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM MarketingCampaigns
SELECT * FROM MarketingCampaigns WHERE Id = '1'

Insert

The following columns are required in an insert: Title, Subject, SenderId, ListIds, SegmentIds, Categories, SuppressionGroupId, HtmlContent, and PlainContent.

Note that a CustomUnsubscribeUrl can be used in place of a SuppressionGroupId. However, SendGrid highly recommends to use SuppressionGroupId.

Use the check points below to find the SenderId and SuppressionGroupId inputs:

• To find the SenderId, navigate to Marketing -> MarketingCampaigns -> Senders. Create a new sender if you do not have one already. Then click the gear on the Action column and select Edit. The Id is displayed on the navigation bar after /senders/id.
• To find the SuppressionGroupId, insert into the UnsubscribeGroups table; the created Id is the SuppressionGroupId.

INSERT INTO MarketingCampaigns (Title, Subject, SenderId, ListIds, Categories, SuppressionGroupId, HtmlContent, PlainContent) VALUES ('Test Campaign', 'March Newsletter', '123', '1,2', 'fruits, health', '12', '<b>Eat Healthy</b>', 'Eat healthy')

Update

You can only update MarketingCampaigns by specifying an Id.

UPDATE MarketingCampaigns SET PlainContent = 'Eat healthier' WHERE Id = '123'

Delete

You can only delete from MarketingCampaigns by specifying an Id.

DELETE FROM MarketingCampaigns WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the marketing campaign.
Title	String	False		The title of the marketing campaign.
Subject	String	False		The subject of the marketing campaign.
SenderId	Integer	False		The sender Ids of the marketing campaign, separated by a comma.
ListIds	String	False		The list of list Ids of the marketing campaign, separated by a comma.
SegmentIds	String	False		The list of segment Ids of the marketing campaign, separated by a comma.
Categories	String	False		The categories of the marketing campaign.
SuppressionGroupId	Integer	False		The suppression group Id of the marketing campaign.
CustomUnsubscribeUrl	String	False		The custom unsubscribe URL of the marketing campaign.
IpPool	String	False		The IP pool of the marketing campaign.
HtmlContent	String	False		The HTML content of the marketing campaign.
PlainContent	String	False		The content of the marketing campaign.
Status	String	True		The status of the marketing campaign.
Editor	String	False		The editor used in the UI. The allowed values are code, design.

The SendGrid table Recipients.

Table Specific Information

SendGrid has the ability to categorize Recipients with filters defined by the user. Accordingly, you can execute statements to the Recipients table to search by and insert these filters. However, these filters are reported as attributes of the Custom Fields table.

Select

The following query retrieves only the default fields of the Recipients table, not the Custom Fields that you have created. To retrieve the values of the Custom Fields of a Recipient, query the Custom Fields table.

You can also retrieve all Recipients inside a certain List or a Segment, but it is not possible to filter by both. Use multiple queries instead:

SELECT * FROM Recipients WHERE SegmentId = '1'
SELECT * FROM Recipients WHERE ListId = '2'

You can build criteria depending on a Custom Field. When querying use quotations regardless of type.This kind of filtering is done client side. In this case Age is a CustomField created by the user.

SELECT * FROM Recipients WHERE Age = '30'

Insert

To insert into Recipients Email is required.

You can insert Custom Fields into the Recipients table. See the CustomFields section for more information about how to create custom fields.

INSERT INTO Recipients (Email, FirstName, LastName, Age, Department ... Other Custom Fields as necessary) VALUES ('[email protected]', 'name', 'surname', '30', 'Education' ... Other Custom Field values)

Update

You can only update from this table by specifying an Id. Additionally, the Email column cannot be updated.

UPDATE Recipients SET Age = '12' WHERE Id = '1'

Delete

You can only delete from this table by specifying an Id.

DELETE FROM Recipients WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		This column is dynamic and maps to the corresponding field in the API.
SegmentId	Int32	False		This column is dynamic and maps to the corresponding field in the API.
ListId	Int32	False		This column is dynamic and maps to the corresponding field in the API.
Email	String	False		This column is dynamic and maps to the corresponding field in the API.
FirstName	String	False		This column is dynamic and maps to the corresponding field in the API.
LastName	String	False		This column is dynamic and maps to the corresponding field in the API.
CreatedAt	Datetime	True		This column is dynamic and maps to the corresponding field in the API.
LastClicked	Datetime	True		This column is dynamic and maps to the corresponding field in the API.
LastEmailed	Datetime	False		This column is dynamic and maps to the corresponding field in the API.
LastOpened	Datetime	True		This column is dynamic and maps to the corresponding field in the API.
UpdatedAt	Datetime	True		This column is dynamic and maps to the corresponding field in the API.
Age	String	False		This column is dynamic and maps to the corresponding field in the API.
Department	String	False		This column is dynamic and maps to the corresponding field in the API.
University	String	False		This column is dynamic and maps to the corresponding field in the API.

Create, update, delete, and query Campaign Schedules in SendGrid.

Table-Specific Information

Select

The following is an example to query the Schedules table:

SELECT * FROM Schedules WHERE MarketingCampaignId = '123'

Insert

Provide MarketingCampaignId and SendAt to schedule a campaign at a specific time. You can simply specify SendAt to immediately send a campaign. SendGrid requires that both the html and plain text content have an [unsubscribe] tag in them for a campaign to be sent.

INSERT INTO Schedules (MarketingCampaignId) VALUES (3091692)

INSERT INTO Schedules (MarketingCampaignId, SendAt) VALUES (1, '11/11/2018')

Update

You can only update Schedules by specifying a MarketingCampaignId.

UPDATE Schedules SET SendAt = '01-01-2016' WHERE MarketingCampaignId = '123'

Delete

You can only delete Schedules by specifying a MarketingCampaignId.

DELETE FROM Schedules WHERE MarketingCampaignId = '123'

Columns

Name	Type	ReadOnly	References	Description
MarketingCampaignId [KEY]	Integer	False		Id of the campaign.
Status	String	True		Status of the campaign.
SendAt	Datetime	False		Date to start the campaign.

Create, update, delete, and query Segments in SendGrid.

Table-Specific Information

Select

You can retrieve all the segments or filter them by Id.

SELECT * FROM Segments WHERE Id = '1'

Insert

A Segment divides Recipients by certain criteria. To specify these criteria use the ConditionsField columns and the Id of the List to apply these criteria to.

The ConditionsAndOr columns connect the given Field with the previous one.

INSERT INTO Segments (Name, ListId, ConditionsField1, ConditionsValue1, ConditionsOperator1, ConditionsAndOr1, ConditionsField2, ConditionsValue2, ConditionsOperator2, ConditionsAndOr2) VALUES ("New Segment", 123, 'age', '20', '>', '', 'age', '30', '<', 'and')

The statement above creates Segments containing Recipients between age 20 and 30.

Update

You can update the name or the conditions. Since the whole set will be overwritten, specify a full set.

UPDATE Segments SET Name = 'Name Updated', ConditionsField1 = 'age', ConditionsOperator1 = '<=', ConditionsAndOr1 = '', ConditionsValue1 = '30' WHERE Id = '123'

Delete

You can only delete Segments by Id:

DELETE FROM Segments WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of the segment.
Name	String	False		The name of the segment.
ListId	Integer	False		The Id of the list associated with the segment.
RecipientCount	Integer	True		The recipient count of the segment.
ConditionsField1	String	False		Field name for the first condition.
ConditionsValue1	String	False		Field value for the first condition.
ConditionsOperator1	String	False		Condition operator for the first condition.
ConditionsAndOr1	String	False		How to connect to the previous condition. Allowed values are AND or OR.
ConditionsField2	String	False		Field name for the second condition.
ConditionsValue2	String	False		Field value for the second condition.
ConditionsOperator2	String	False		Condition operator for the second condition.
ConditionsAndOr2	String	False		How to connect to the previous condition. Allowed values are AND or OR.
ConditionsField3	String	False		Field name for the third condition.
ConditionsValue3	String	False		Field value for the third condition.
ConditionsOperator3	String	False		Condition operator for the third condition.
ConditionsAndOr3	String	False		How to connect to the previous condition. Allowed values are AND or OR.
ConditionsField4	String	False		Field name for the fourth condition.
ConditionsValue4	String	False		Field value for the fourth condition.
ConditionsOperator4	String	False		Condition operator for the fourth condition.
ConditionsAndOr4	String	False		How to connect to the previous condition. Allowed values are AND or OR.
ConditionsField5	String	False		Field name for the fifth condition.
ConditionsValue5	String	False		Field value for the fifth condition.
ConditionsOperator5	String	False		Condition operator for the fifth condition.
ConditionsAndOr5	String	False		How to connect to the previous condition. Allowed values are AND or OR.

Create, update, delete, and query Sender Identities in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM SenderIdentities
SELECT * FROM SenderIdentities WHERE Id = '148825'

Insert

The following columns are required in an insert: Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country.

INSERT INTO SenderIdentities (Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country) VALUES ('Nickname', 'FromEmail', 'FromName', 'ReplytoEmail', 'Address', 'City', 'Country')

Update

You can update SenderIdentities by specifying an Id.

UPDATE SenderIdentities SET Nickname = 'Nickname', FromEmail = 'FromEmail', FromName = 'FromName', ReplytoEmail = 'ReplytoEmail', Address = 'Address', City = 'City', Country = 'Country' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM SenderIdentities WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	False		The unique identifier of the sender identity.
Nickname	String	False		A nickname for the sender identity. Not used for sending..
FromEmail	String	False		This is where the email will appear to originate from for your recipient.
FromName	String	False		This is the name appended to the from email field. IE - Your name or company name.
ReplytoEmail	String	False		This is the email that your recipient will reply to.
ReplytoName	String	False		This is the name appended to the reply to email field. IE - Your name or company name.
Address	String	False		The physical address of the sender identity.
Address2	String	False		Additional sender identity address information.
City	String	False		The city of the sender identity.
State	String	False		The state of the sender identity.
Zip	String	False		The zipcode of the sender identity.
Country	String	False		The country of the sender identity.
CreatedAt	Datetime	True		The end of the time range when the bounce was created.
UpdatedAt	Datetime	True		The end of the time range when the bounce was created.
Locked	Boolean	True		A boolean flag that is true when the Sender is associated with a campaign in Draft, Scheduled, or In Progress status. You cannot update or delete a locked Sender.
VerifiedReason	String	True		The reason for verifying the sender
VerifiedStatus	Boolean	True		A boolean flag indicating whether the Sender is verified or not. Only verified Senders can be used to send email.

Query and delete Spam reports in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM Spam
SELECT * FROM Spam WHERE Email = '[email protected]'

Delete

Delete from Spam 

You can delete from Spam by specifying an Email.

DELETE FROM Spam WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time when the spam report was made.
Email [KEY]	String	False		The email address of the recipient who marked your message as spam.
Ip	String	False		The IP address that the message was sent on.

Query Teammates in SendGrid.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

• Username supports the '=,IN' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Teammates WHERE Username = 'test'

SELECT * FROM Teammates WHERE Username IN ('test', 'test2')

Insert

The following columns are required in an insert: Email and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

The IsAdmin is optional and by default is accepted as false from the API.

INSERT INTO Teammates (Email, Scopes) VALUES ('[email protected]', 'user.profile.read, user.profile.update')

INSERT INTO Teammates (Email, Scopes, IsAdmin) VALUES ('[email protected]', 'user.profile.read, user.profile.update', 'true')

Update

The following columns are required in an update: Username and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

UPDATE Teammates SET Scopes = 'user.profile.read' WHERE Username = 'testuser'

Delete

Only the username column is required to delete a record from Teammates table. For example:

DELETE FROM Teammates WHERE Username = 'testuser'

Columns

Name	Type	ReadOnly	References	Description
Username [KEY]	String	False		The username of the teammate.
Email	String	False		Email of the teammate.
FirstName	String	False		First name of the teammate.
LastName	String	False		Last name of the teammate.
UserType	String	False		User type of the teammate.
IsAdmin	Boolean	False		Indicator if the teammate is admin.
Phone	String	False		Phone of the teammate.
Website	String	False		Website of the teammate.
Company	String	False		Company of the teammate.
Address	String	False		Address of the teammate.
Address2	String	False		Address 2 of the teammate.
City	String	False		City of the teammate.
State	String	False		State of the teammate.
Country	String	False		Country of the teammate.
Zip	String	False		Zip of the teammate.
Scopes	String	False		Scope of the permissions for the new teammate. Multiple values are allowed and should be split by a comma symbol. Possible values are: user.account.read,user.credits.read,user.email.create,user.email.read,user.email.update,user.email.delete,user.profile.create,user.profile.read,user.profile.update,user.profile.delete

Create, update, delete, and query Unsubscribe Groups in SendGrid.

Table-Specific Information

Select

UnsubscribeGroups can only be filtered by Id.

SELECT * FROM UnsubscribeGroups WHERE Id = '1'

Insert

Provide a Name and a Description to insert into UnsubscribeGroups:

INSERT INTO UnsubscribeGroups (Name, Description, IsDefault) VALUES ('Unsubscribe1', 'General Unsubscribe', true)

Update

You can only update UnsubscribeGroups by specifying an Id.

UPDATE UnsubscribeGroups SET Name = 'New Name', Description = 'New Description' WHERE Id = '1'

Delete

You can only delete from UnsubscribeGroups by specifying an Id.

DELETE FROM UnsubscribeGroups WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the unsubscribe group.
Name	String	False		The name of the unsubscribe group.
Description	String	False		The description of the unsubscribe group.
LastEmailSentAt	Datetime	True		The last time an email was sent to the unsubscribe group.
IsDefault	Boolean	False		If the unsubscribe group is the default group or not.
Unsubscribes	Integer	True		The number of unsubscribes from this group.

Query the available Users in SendGrid.

View-Specific Information

Select

Query this table to retrieve your current profile details.

SELECT * FROM Users 

Update

You can update the all the columns marked as readonly false.

UPDATE Users Set Address='Milpitas', Address2='North Carolina' WHERE id='38713966'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the user.
Address	String	False		The address of the user.
Address2	String	False		The secondary address field for the user.
City	String	False		The city of the user.
Company	String	False		The company of the user.
Country	String	False		The country of the user.
FirstName	String	False		The first name of the user.
LastName	String	False		The last name of the user.
Phone	String	False		The phone number of the user.
State	String	False		The state the user is located in.
Website	String	False		The Web site URL of the user.
Zip	String	False		The ZIP code of the user.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

SendGrid Connector for CData Sync Views

Name	Description
Categories	Query the available Categories in SendGrid.
CategoryStats	Query the available Category Stats in SendGrid.
GlobalStats	Query the available Global Stats in SendGrid.
GroupSuppressions	Query group suppressions in SendGrid.
IPAccessManagement	Query Global Unsubscribes in SendGrid.
MailSettings	Query all the mail settings in SendGrid.
MessageEvents	List of events related to email message in SendGrid.
Messages	Query Email Activities in SendGrid.
PartnerSettings	Query all the partner settings in SendGrid.
ReservedFields	Query the available Reserved Fields in SendGrid.
StatsByBrowser	Query Browser Statistics in SendGrid.
StatsByClient	Query Client Statistics in SendGrid.
StatsByDevice	Query Device Statistics in SendGrid.
StatsByLocation	Query Location Statistics in SendGrid.
StatsByMailboxProvider	Query Mailbox Provider Statistics in SendGrid.
SubuserStats	Query Subuser Statistics in SendGrid.

Query the available Categories in SendGrid.

View-Specific Information

Select

Query this table to get available categories in SendGrid.

SELECT * FROM Categories

Columns

Name	Type	References	Description
Name	String		The name of the categories.

Query the available Category Stats in SendGrid.

View-Specific Information

Select

The following is an example to query the CategoryStats view:

SELECT * FROM CategoryStats WHERE Name = 'news'

You can also filter by Date. If you do not specify one, a default span of the past 1 year is used. The operators =, >=, <=, <, and > are supported for this column.

SELECT * FROM CategoryStats WHERE Name = 'news' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the category.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails in this category dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

Query the available Global Stats in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalStats 

SELECT * FROM GlobalStats WHERE Date > '2016-01-01' AND Date < '2016-03-30'

Columns

Name	Type	References	Description
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
BounceDrops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails globally dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

Query group suppressions in SendGrid.

Table-Specific Information

Select

The following are examples to query the GroupSuppressions table:

SELECT * FROM GroupSuppressions WHERE GroupId = 391

SELECT * FROM GroupSuppressions WHERE GroupId IN (391, 392, 393)

Columns

Name	Type	References	Description
GroupId [KEY]	Integer		Email of the unsubscriber.
Emails	String		The time the unsubscriber was created.

Query Global Unsubscribes in SendGrid.

View-Specific Information

Select

The Sync App will use the SendGrid API to retrieve information about the IP addresses which have accessed the account. However there is no filtering supported server-side for this view.

Only the following query is processed server side:

SQL

SELECT * FROM IPAccessManagement

Columns

Name	Type	References	Description
IP	String		IP Address.
Allowed	Boolean		Indicator if this IP is allowed for access.
AuthMethod	String		Authentication method.
FirstAt	Timestamp		First time when IP has accessed the account.
LastAt	Timestamp		Last time when IP has accessed the account.
Location	String		Location of the IP.

Query all the mail settings in SendGrid.

Table-Specific Information

Select

You can retrieve all the mail settings.

SELECT * FROM MailSettings

Columns

Name	Type	References	Description
Title	String		The title of the mail setting.
Enabled	String		Indicates if this mail setting is currently enabled.
Name	String		The name of the mail setting.
Description	String		A description of the mail setting.

List of events related to email message in SendGrid.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

• MessageId supports the '=' comparisons.

SQL

SELECT * FROM MessageEvents WHERE MessageId = '1234'

Columns

Name	Type	References	Description
MessageId	String		The Id of the message.
EventName	String		Name of event. The allowed values are bounced, opened, clicked, processed, dropped, delivered, deferred, spam_report, unsubscribe, group_unsubscribe, group_resubscribe.
Processed	Datetime		The date when the event was processed.
Reason	String		Explanation of what caused the message to be 'bounced', 'deferred', or 'blocked'.
AttemptNum	Integer		Used with 'deferred' events to indicate the attempt number out of 10.
Url	String		Used with 'clicked' event to indicate which url the user clicked.
BounceType	String		Use to distinguish between types of bounces. The allowed values are soft, hard.
HttpUserAgent	String		Client recipient used to click or open message.
MXServer	String		The MX server that received the email.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
Categories	String		Categories users associated to the message.
UniqueArgs	String		JSON hash of key-value pairs associated with the message.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.

Query Email Activities in SendGrid.

NOTE: If you want access to Email activities, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Note: The API will return only 1000 records. Please choose from the available filters to restrict the search.

• Id supports the '=,IN' comparisons.
• Status supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Subject supports the '=,!=,LIKE,NOT LIKE' comparisons.
• FromEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
• ToEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Clicks supports the '>,<,<=,>=,=,!=' comparisons.
• LastEventTime supports the '>=, <=, BETWEEN' comparisons.
• ApiKeyId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• AsmGroupId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• MarketingCampaignId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• MarketingCampaignName supports the '=,!=,LIKE,NOT LIKE' comparisons.
• OriginatingIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
• OutboundIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Teammate supports the '=,!=,LIKE,NOT LIKE' comparisons.
• TemplateId supports the '=,!=,LIKE,NOT LIKE' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Messages WHERE Id = '1234'

SELECT * FROM Messages WHERE Id IN ('1234', '4567')

SELECT * FROM Messages WHERE Status = 'not_delivered'

SELECT * FROM Messages WHERE LastEventTime BETWEEN '2020-01-01' AND '2020-01-15'

Columns

Name	Type	References	Description
Id [KEY]	String		The Id of the message.
Status	String		Status of the email.
Subject	String		Subject of the email.
FromEmail	String		The email of the sender.
ToEmail	String		The email of the receiver.
ClicksCount	Integer		The number of clicks.
OpensCount	Integer		The number of times that the email was opened.
LastEventTime	Datetime		The datetime of the event.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
MarketingCampaignId	String		The id of the marketing campaign.
MarketingCampaignName	String		The name of the marketing campaign.
MarketingCampaignSplitId	String		Split id of the campaign.
MarketingCampaignVersion	String		Version of the campaign.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.
Events	String		List of events related to email message in SendGrid.

Query all the partner settings in SendGrid.

Table-Specific Information

Select

You can retrieve all the partner settings.

SELECT * FROM PartnerSettings

Columns

Name	Type	References	Description
Title	String		The title of the partner.
Enabled	String		Indicates if this partner setting has been enabled.
Name	String		The name of the partner setting.
Description	String		A description of this partner setting.

Query the available Reserved Fields in SendGrid.

View-Specific Information

Select

This table does not support filtering.

SELECT * FROM ReservedFields

Columns

Name	Type	References	Description
Name	String		The name of the reserved field.
Type	String		The type of the reserved field.

Query Browser Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByBrowser WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Browser Statistics by name.

SELECT * FROM StatsByBrowser WHERE Name = 'Firefox'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.

Query Client Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByClient WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Client Statistics by name.

SELECT * FROM StatsByClient WHERE Name = 'Other'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Query Device Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM StatsByDevice WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Device Statistics by name.

SELECT * FROM StatsByDevice WHERE Name = 'Phone'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.