Microsoft Graph solutions
Introduction
Microsoft Graph is a powerful RESTful API that lets you access and interact with data across Microsoft 365 services, including Outlook, OneDrive and Teams. By registering your application and obtaining authentication tokens, you can interact with Microsoft Graph to retrieve, modify, and manage data across these services. For more information on using the Microsoft Graph API, see Use the Microsoft Graph API.
The following resources are currently supported by the Thinkwise Platform:
- Events
- emails
ContactsTasksAttachments
This product replaces the Thinkwise Exchange Connector. The MS Graph solution can be fully maintained in the Software Factory, which removes the need for a stand-alone service.
Database structure
In each Microsoft Graph solution the following two tables are always present:
msgraph_user
msgraph_log
msgraph_user
The msgraph_user
table must be completed manually.
You should add a row for each unique user that needs to synchronize resources.
For every installed Microsoft Graph solution, an additional Boolean column is added to the table.
These columns determine which resources are synchronized for each user (for example, receive_events
).
The following columns are available:
- Email address
- Access token
All synchronized data is based on a user’s unique email address. For this reason, the email address can only exist once in this table. The table includes columns for authenticating the user against the Microsoft Graph API. Authentication can be performed with app-only permissions. For more information, see Authentication and authorization basics.
Additional Boolean columns are added that can indicate whether a user is allowed to synchronize certain resources, such as receive_events
and send_events
.
Each column will be automatically added once the solution is installed from the Thinkstore.
msgraph_log
Errors that occur will be logged in this table. Once a call to Microsoft Graph fails, the status code and response body will be inserted. You can use this to troubleshoot the problem.
Naming conventions
All tables, procedures, functions and data types follow a structured naming convention to ensure they are systematically ordered in the database.
- Each name is prefixed with
msgraph_
to maintain consistent ordering. - The next part of the name includes a prefix
send_
orreceive_
to indicate the direction of the synchronization. - Finally, a resource-specific name is added, for example,
msgrap_receive_event_request_completed
.
The following guidelines clarify the purpose of different types of procedures:
- Procedures prefixed with
msgraph_
can be customized with code templates to process the sent and received synchronized data. - Procedures
prc_msgraph
are process procedures and should not be changed. - Each resource has both a send and receive solution, for example
MSGRAPH_RECEIVE_EVENT
andMSGRAPH_SEND_EVENT
. This allows you to select what specific resources to synchronize between the end product and the Microsoft Graph API.
OAuth configuration
When one or more of the solutions are installed from the Thinkstore,
an OAuth configuration is required for the process flows to work.
Each solution includes the OAuth configuration msgraph_oauth
, which must be completed after installation.
This can be done in the Software Factory, but it can also be overridden once it is synchronized to IAM.
For more information on how to connect Microsoft Graph webservices, see Introduction to Microsoft Graph Authentication.
The process flows in the solutions are used to synchronize the resources between your application and the Microsoft Graph API, they are all prefixed with msgraph_send_
or msgraph_receive_
.
To keep all resource data up to date, the process flows should be scheduled so the queued resources are synchronized regularly.
You can add a schedule for each process flow in IAM to determine how often the rows in the queue tables are processed.
Set up permissions in Microsoft Entra ID
To ensure that web connections work correctly, it is important to set up a complete application registration in Microsoft Entra ID and grant the necessary permissions.
Microsoft Entra ID has two permission types:
- Delegated permissions - These permissions are used when the application needs access to the API as the signed-in user. For example, the application may need to read the user's profile or send mail as the user. Delegated permissions are applicable when a signed-in user is present and the application is acting on their behalf.
- Application permissions - These permissions are used when an application needs to access an API without a signed-in user, such as a background service. Only an administrator can grant consent for application permissions.
Setting up permissions
Thinkstore solutions
You can use process flows that interact with HTTP connectors to enable your applications to send and receive these resources. A dedicated solution is available in the Thinkstore for each supported resource.
For example, a solution can use scheduled process flow to process a queue table, allowing your application to synchronize supported resources with the Microsoft Cloud. The process flow will make an external call to the Microsoft Graph API using a HTTP connector to add, update, delete and retrieve resources for multiple users.
If one of the process flows fail with a 401 (unauthorized)
status code the process flow will try and update the user’s access token and tries the same call again.
If it still fails then something else is wrong. An error will be logged with the specific reason.
The following solutions are available in the Thinkstore for the following resources:
Receiving events
Set up receiving events
The following parameters are required for the msgraph_receive_event_add
procedure:
- Email address (
user_email_address
) - Correlation ID (
correlation_id, output
) - Range start date and time (
range_start_datetime
) - Range end date and time (
range_end_datetime
)
Before you can receive events, you must add a queued item to the msgraph_receive_event_queue
table by calling the msgraph_receive_event_add
procedure.
This will insert a queued item into the table.
The process flow msgraph_receive_event
will then use this queued item to synchronize events for the given user between a specific date and time range.
Synchronizing events
After a range is synchronized a delta token is returned which can be used to update the same range from a specific point in time. This prevents that redundant data is retrieved and avoids that the entire range is repeatedly synchronized.
To do this, you must call the msgraph_receive_event_add_delta
procedure, which will queue an item in the msgraph_receive_event_queue
table.
The following parameters are required for the msgraph_receive_event_add_delta
procedure:
- Email address (
user_email_address
) - Correlation ID (
correlation_id, output
) - Delta token (
delta_token
)
The built-in process flow will check the msgraph_receive_event_queue
table every X amount of minutes and will process all available rows.
For each row, it calls the Microsoft Graph API and executes the following procedures:
- Processing received events - All received events are separated and iterated over so for every event the
msgraph_receive_event_process_event
procedure can be called. All properties of an event are parsed as parameters, allowing you to apply a woven template for processing. - Handling deleted events - Every deleted event can be processed in the
msgraph_receive_event_process_deleted_event
procedure with a woven template. - Storing the delta token - After processing, a final call to
msgraph_receive_event_request_completed
is made. This allows you to save the delta token that was returned by the external call. A template can be woven to store this received delta token.
Code examples
For each procedure that processes data, you must weave a template. The following code examples provide a simple implementation for each procedure.
- msgraph_receive_event_add
- msgraph_receive_event_add_delta
- msgraph_receive_event_process_event
- msgraph_receive_event_process_deleted_event
- msgraph_receive_event_request_completed
msgraph_receive_event_add
A task can be created to call this procedure which will add an item to the queue table msgraph_receive_event_queue
.
The procedure has the following parameters:
user_email_address
correlation_id
range_start_datetime
range_end_datetime
The correlation ID (unique identifier) is an output parameter and needs to be saved somewhere (for example, as msgraph_correlations
) so it can be used to synchronize the same date time range with the received delta token in the future.
Example
set @correlation_id = null
exec msgraph_receive_event_add
@user_email_address,
@correlation_id,
@range_start_datetime,
@range_end_datetime
-- Save correlation_id
insert
into msgraph_correlations
@user_email_address,
@range_start_datetime,
@range_end_datetime
@correlation_id,
null –- delta_token
msgraph_receive_event_add_delta
A task can be created to call this procedure which will add a delta item to the queue.
The parameters can be filled with the same correlation id and delta token that were saved with msgraph_receive_event_request_completed
.
Example
exec msgraph_receive_event_add_delta
@user_email_address,
@correlation_id,
@delta_token
msgraph_receive_event_process_event
This procedure processes one event at a time. If multiple events are received from the external call this procedure will be called multiple times.
Example
insert into msgraph_event_temp
(
correlation_id,
odata_etag,
event_id,
created_date_time,
last_modified_date_time,
change_key,
categories,
transaction_id,
original_start_time_zone,
original_end_time_zone,
ical_u_id,
reminder_minutes_before_start,
is_reminder_on,
has_attachments,
subject,
body_preview,
importance,
sensitivity,
is_all_day,
is_cancelled,
is_organizer,
response_requested,
series_master_id,
show_as,
type,
web_link,
online_meeting_url,
is_online_meeting,
online_meeting_provider,
allow_new_time_proposals,
occurrence_id,
is_draft,
hide_attendees,
response_status,
body,
event_start,
event_end,
location,
locations,
recurrence,
attendees,
organizer,
online_meeting,
calendar_odata_association_link,
calendar_odata_navigation_link
)
values
(
@correlation_id,
@odata_etag,
@event_id,
@created_date_time,
@last_modified_date_time,
@change_key,
@categories,
@transaction_id,
@original_start_time_zone,
@original_end_time_zone,
@ical_u_id,
@reminder_minutes_before_start,
@is_reminder_on,
@has_attachments,
@subject,
@body_preview,
@importance,
@sensitivity,
@is_all_day,
@is_cancelled,
@is_organizer,
@response_requested,
@series_master_id,
@show_as,
@type,
@web_link,
@online_meeting_url,
@is_online_meeting,
@online_meeting_provider,
@allow_new_time_proposals,
@occurrence_id,
@is_draft,
@hide_attendees,
@response_status,
@body,
@event_start,
@event_end,
@location,
@locations,
@recurrence,
@attendees,
@organizer,
@online_meeting,
@calendar_odata_association_link,
@calendar_odata_navigation_link
)
msgraph_receive_event_process_deleted_event
This procedure is called for every event that is deleted within the original datetime range.
Example
-- This will delete the original item and all of its recurrences
delete
from msgraph_event_temp
where event_id = @removed_event_id
or series_master_id = @removed_event_id
msgraph_receive_event_request_completed
After receiving events for a queued item, a delta token is returned which indicates a timestamp.
With this delta token the next synchronization knows where to start.
This delta token can be saved in the same place the correlation id is saved when msgraph_receive_event_add
was called.
Example
update msgraph_correlations
set delta_token = @delta_token
where user_email_address = @user_email_address,
and correlation_id = @correlation_id
Sending events
Install the MSGRAPH_SEND_EVENT
solution from the Thinkstore to enable sending events.
Set up sending events
To send events, a queued item must be added to the msgraph_send_event_queue
table.
This can be done by calling one of the following procedures:
msgraph_send_event_add
– Adds a new event to the queue.msgraph_send_event_update
– Updates an existing event in the queue.msgraph_send_event_delete
– Deletes an event from the queue.
After adding an event to the queue, the process flow msgraph_send_event
will synchronize the events for the specific user.
A schedule for this flow can be configured in IAM, which determines how often the queued rows are processed.
Code examples
For each procedure that processes data, you must weave a template. The following code examples provide a simple implementation for each procedure.
- msgraph_send_event_add
- msgraph_send_event_update
- msgraph_send_event_delete
- msgraph_send_event_request_completed
msgraph_send_event_add
This procedure will add a row in the msgraph_send_event_queue
table. For more information, see Event properties.
The following parameters are available:
Parameter | Examples | Required |
---|---|---|
user_email_address | 'user1@website.com' | x |
allow_new_time_proposals | 0 or 1 (default) | |
attendees_required | 'user2@website.com, user3@website.com' (comma separated) | |
attendees_optional | 'user4@website.com, user5@website.com' (comma separated) | |
body_content_type | 'html' (default) or 'text' | |
body_content | 'This is the content of the body' | x |
categories | [ "Red Category", "Green Category" ] . Does not work at the moment, known issue at Microsoft. | |
event_end | '2022-10-20 08:00:00.000' (UTC datetime). Change event_end_timezone if this should be local time | x |
event_end_timezone | UTC (default). See timezones for other values. For e.g. West Europe use W. Europe Standard Time | |
has_attachments | 0 (default) or 1 | |
hide_attendees | 0 (default) or 1 | |
importance | empty (default), low , normal or high | |
is_all_day | 0 (default) or 1 | |
is_online_meeting | 0 (default) or 1 | |
is_reminder_on | 0 (default) or 1 | |
locations | 'Meeting Room 4, Second Floor Room 2' (comma separated) | |
online_meeting_provider | unknown (default), teamsForBusiness , skypeForBusiness , skypeForConsumer | |
organizer | 'user1@website.com' | |
recurrence_pattern_type | The recurrence pattern type: daily , weekly , absoluteMonthly , relativeMonthly , absoluteYearly , relativeYearly | |
recurrence_interval | The number of units between occurrences, where units can be in days, weeks, months, or years, depending on the type. | |
recurrence_month | The month in which the event occurs. This is a number from 1 to 12 | |
recurrence_day_of_month | The day of the month on which the event occurs. Required if type is absoluteMonthly or absoluteYearly | |
recurrence_days_of_week_csv | A collection of the days of the week on which the event occurs, e.g. [ "monday", "tuesday", "sunday" ] | |
recurrence_first_day_of_week | The first day of the week. The possible values are: sunday , monday , tuesday , wednesday , thursday , friday , saturday . Default is sunday . Required if type is weekly | |
recurrence_index | Specifies on which instance of the allowed days specified in daysOfWeek the event occurs, counted from the first instance in the month. The possible values are: first , second , third , fourth , last . Default is first . Used if type is relativeMonthly or relativeYearly | |
recurrence_range_type | The recurrence range. The possible values are: endDate , noEnd , numbered | |
recurrence_start_date | The date to start applying the recurrence pattern. Must be the same value as the start property of the recurring event | x |
recurrence_end_date | The date to stop applying the recurrence pattern. Required if type is endDate | |
recurrence_time_zone | Time zone for the startDate and endDate properties. If not specified, the time zone of the event is used | |
recurrence_number_of_occurrences | The number of times to repeat the event. Required and must be positive if type is numbered | |
reminder_minutes_before_start | 15 | |
response_requested | 0 (default) or 1 | |
sensitivity | empty (default), normal , personal , private or confidential | |
show_as | empty (default), free , tentative , busy , oof , workingElsewhere , unknown | |
event_start | '2022-10-20 06:00:00.000' (UTC datetime). Change event_start_timezone if this should be local time | x |
event_start_timezone | UTC (default). See timezones for other values. For e.g. West Europe use W. Europe Standard Time | |
subject | 'Important brainstorm meeting' | x |
correlation_id (output) | Represents the external call to Microsoft Graph. After calling this procedure the id should be saved as shown in the example code |
Example
-- The msgraph_send_event_add procedure will fill the correlation_id
set @correlation_id = null
exec msgraph_send_event_add
@email_address, -- user_email_address
0, -- allow_new_time_proposals
@attendees, -- attendees
null, -- attendees_optional
'html', -- body_content_type
@body, -- body
0, -- reset_body, body will become empty and @body will be ignored
null, -- categories
@event_end, -- event_end, e.g. dateadd(hour, 2, getutcdate())
null, -- cal_item_end_timezone
0, -- has_attachments
0, -- hide_attendees
null, -- importance, e.g. "normal"
0, -- is_all_day
0, -- is_draft
0, -- is_online_meeting
0, -- is_reminder_on
@locations, -- locations, eg 'Meeting Room 4.2, Ocean, Beach'
null, -- online_meeting
null, -- online_meeting_provider
null, -- organizer, e.g. 'user1@website.com
null, -- recurrence_pattern_type
null, -- recurrence_interval
null, -- recurrence_month
null, -- recurrence_day_of_month
null, -- recurrence_days_of_week_csv
null, -- recurrence_first_day_of_week
null, -- recurrence_index
null, -- recurrence_range_type
null, -- recurrence_start_date
null, -- recurrence_end_date
null, -- recurrence_time_zone
null, -- recurrence_number_of_occurrences
null, -- reminder_minutes_before_start
null, -- response_requested
null, -- sensitivity
null, -- show_as
@event_start, -- event_start, e.g. getutcdate()
null, -- cal_item_start_timezone
@subject, -- subject, e.g. 'Important meeting'
@correlation_id -- correlation_id
Save correlation_id so this external call can be traced back to once msgraph_send_event_request_completed
is called, so the developer knows the call is done.
insert
into msgraph_correlations
@user_email_address,
@correlation_id
The procedure will put every property in a correct JSON format before it is added to the queue table. This way it can be processed by the process flow correctly.
msgraph_send_event_update
This procedure will also add a row in the msgraph_send_event_queue
table.
Since not all properties can be edited the parameter list slidely differs from the add procedure.
The following parameters are available:
Parameter | Examples | Required |
---|---|---|
user_email_address | 'user1@website.com' | x |
event_id | The event_id that was saved when this event was added and msgraph_send_event_request_completed was called | x |
attendees_required | 'user2@website.com, user3@website.com' (comma separated) | |
attendees_optional | 'user4@website.com, user5@website.com' (comma separated) | |
reset_attendees | 0 (default) or 1. Empties the attendees when 1, overrules the attendees_required and attendees_optional parameters | |
body_content_type | 'html' (default) or 'text' | |
body_content | 'This is some different body content than it was originally' | |
reset_body | 0 (default) or 1. Empties the body_content when 1, overrules the body_content parameter | |
categories | [ "Red Category", "Green Category" ] . Does not work at the moment, known issue at Microsoft | |
reset_categories | 0 (default) or 1. Empties the categories when 1, overrules the categories parameter | |
event_end | '2022-10-20 09:00:00.000' (UTC datetime). Changes event_end_timezone if this should be local time. | |
event_end_timezone | UTC (default). See timezones for other values. For example, for Western Europe, use W. Europe Standard Time | |
hide_attendees | 0 (default) or 1 | |
importance | empty (default), low , normal or high | |
is_all_day | 0 (default) or 1 | |
is_online_meeting | 0 (default) or 1 | |
is_reminder_on | 0 (default) or 1 | |
locations | 'Meeting Room 3, Third Floor Room 1' (comma separated) | |
reset_locations | 0 (default) or 1. Empties the locations when 1, overrules the locations parameter | |
online_meeting_provider | unknown (default), teamsForBusiness , skypeForBusiness , skypeForConsumer | |
recurrence_pattern_type | The recurrence pattern type: daily , weekly , absoluteMonthly , relativeMonthly , absoluteYearly , relativeYearly | |
recurrence_interval | The number of units between occurrences, where units can be in days, weeks, months, or years. | |
recurrence_month | The month in which the event occurs. This is a number from 1 to 12 | |
recurrence_day_of_month | The day of the month on which the event occurs. Required if type is absoluteMonthly or absoluteYearly | |
recurrence_days_of_week_csv | A collection of the days of the week on which the event occurs, for example, [ "monday", "tuesday", "sunday" ] | |
recurrence_first_day_of_week | The first day of the week. The possible values are: sunday , monday , tuesday , wednesday , thursday , friday , saturday . Default is sunday . Required if type is weekly | |
recurrence_index | Specifies on which instance of the allowed days specified in daysOfWeek the event occurs, counted from the first instance in the month. The possible values are: first , second , third , fourth , last . Default is first . Used if type is relativeMonthly or relativeYearly | |
recurrence_range_type | The recurrence range. The possible values are: endDate , noEnd , numbered | |
recurrence_start_date | The date to start applying the recurrence pattern. Must be the same value as the start property of the recurring event | |
recurrence_end_date | The date to stop applying the recurrence pattern. Required if type is endDate | |
recurrence_time_zone | Time zone for the startDate and endDate properties. If not specified, the time zone of the event is used | |
recurrence_number_of_occurrences | The number of times to repeat the event. Required and must be positive if type is numbered | |
reset_recurrence | 0 (default) or 1. Empties the entire recurrence when 1, overrules all recurrence parameters | |
reminder_minutes_before_start | 15 | |
response_requested | 0 (default) or 1 | |
sensitivity | empty (default), normal , personal , private or confidential | |
show_as | empty (default), free , tentative , busy , oof , workingElsewhere , unknown | |
event_start | '2022-10-20 07:00:00.000' (UTC datetime). Change event_start_timezone if this should be local time | x |
event_start_timezone | UTC (default). See timezones for other values. For example, for Western Europe use W. Europe Standard Time | |
subject | 'Less important brainstrom meeting changed' | |
reset_subject | 0 (default) or 1. Empty the subject if 1, will overrule the subject parameter | |
correlation_id | Represents the external call to Microsoft Graph. After calling this procedure the id should be saved as shown in the example code | x |
Because some properties cannot be set to null
an extra boolean is added to force a reset on them, e.g. reset_recurrence
.
For all other properties setting null
means the value is ignored for this update.
Example
set @correlation_id = null
exec msgraph_send_event_update
@email_address, -- user_email_address
@event_id, -- event_id
null, -- attendees_required
null, -- attendees_optional
0, -- reset_attendees
null, -- body_content_type
null, -- body_content
0, -- reset_body
null, -- categories
0, -- reset_categories
@event_end, -- event_end
null, -- cal_item_end_timezone
null, -- hide_attendees
null, -- importance
null, -- is_all_day
null, -- is_online_meeting
null, -- is_reminder_on
null, -- locations
0, -- reset_locations
null, -- online_meeting_provider
null, -- recurrence_pattern_type
null, -- recurrence_interval
null, -- recurrence_month
null, -- recurrence_day_of_month
null, -- recurrence_days_of_week_csv
null, -- recurrence_first_day_of_week
null, -- recurrence_index
null, -- recurrence_range_type
null, -- recurrence_start_date
null, -- recurrence_end_date
null, -- recurrence_time_zone
null, -- recurrence_number_of_occurrences
0, -- reset_recurrence
null, -- reminder_minutes_before_start
null, -- response_requested
null, -- sensitivity
null, -- show_as
null, -- event_start
null, -- event_start_timezone
@subject, -- subject
0, -- reset_subject
@correlation_id -- correlation_id
Save correlation_id
so this external call can be traced once msgraph_send_event_request_completed
is called, so you know the call is done.
insert
into msgraph_correlations
@user_email_address,
@event_id,
@correlation_id
The procedure will put every property in a correct JSON format before it is added to the queue table. This way it can be processed by the process flow correctly.
msgraph_send_event_delete
This procedure will add a row in the msgraph_send_event_queue
table.
The following parameters are available:
Parameter | Examples | Required |
---|---|---|
user_email_address | 'user1@website.com' | x |
event_id | The event_id that was saved when this event was added and msgraph_send_event_request_completed was called | x |
correlation_id | Represents the external call to Microsoft Graph. After calling this procedure the id should be saved as shown in the example code | x |
Example
set @correlation_id = null
exec msgraph_send_event_delete
@email_address,
@event_id,
@correlation_id
Save correlation_id
so this external call can be traced once msgraph_send_event_request_completed
is called, so you know the call is done.
insert
into msgraph_correlations
@user_email_address,
@event_id,
@correlation_id
msgraph_send_event_request_completed
After sending events for a queued item, this procedure is called to confirm the external call to Microsoft Graph is finished and the correlation that was saved is now done.
For newly added events, the event_id
will be filled so it can be processed with the correlation_id
.
Example
update msgraph_correlations
set event_id = @event_id,
finished = 1
where user_email_address = @user_email_address,
and correlation_id = @correlation_id
Receiving emails
Install the MSGRAPH_RECEIVE_EMAIL
solution from the Thinkstore to enable receiving emails.
The following delegated permissions and scopes are required for this solution:
Mail.Read
User.Read
offline_access
Set up receiving emails
Before you can receive emails, you must first fetch all the folders in your inbox and select which folders the application is allowed to read:
- In the subject
msgraph_user_mail_folder
, execute the tasksync_mailbox
to trigger the process flowmsgraph_get_email_folder
. - Once the folders are retrieved, select the folders from which emails should be read
- Execute the task
enable_mail_folder_sync
to enable the email synchronization from these folders.
Retrieving emails
Once this setup is complete, the scheduled process flow msgraph_receive_email
is ready to retrieve emails from the folder.
This process flow loops through all the configured folders and retrieves emails from the folders within a certain date range.
Emails are stored as follows:
- Emails are stored in the table
msgraph_email
. - Attachments are stored in the table
msgraph_email_attachment
. - Embedded images are not part of the email body but are referenced by a Content-ID (CID).
A business logic process replaces these IDs with the actual data, so the
body_preview
displays the images correctly.
You can use the msgraph_email
table directly in your application's user interface.
Alternatively, you can implement additional logic to filter and transfer only relevant emails, such as those related to customer and supplier interactions.
You can store these emails in a dedicated email table.
By doing this, you can link emails to other entities, providing a 360-degree overview of the interactions within your business.
Receiving emails in a Thinkwise application
Known issues
- Adding, updating and deleting
categories
for an event does not work. - Multiline text for the event body that contain enters do not work.
Use
REPLACE(@body,CHAR(13)+CHAR(10),'</br>')
before adding the value to the queue table. - Adding an online meeting to an event does not work.
The Microsoft documentation states that
isOnlineMeeting
has to be set to true and theonlineMeetingProvider
has to be set. This should initialize an online meeting, but it does nothing. - The import and export functionality of the Thinkwise Software Factory contains security handlers that exclude sensitive information from the export. This also changes the web connection authentication, replacing the web connection parameter with a random bearer token. You should correct this.
- If the OAuth provider indicates that the redirect URL is not supported, the correct redirect URL to Indicium has not been added yet in Microsoft Entra ID.
The correct redirect URL should be
https://[myserver]/indicium/oauth-callback
.