Skip to main content

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
  • Contacts
  • Tasks
  • Attachments
note

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_ or receive_ 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 and MSGRAPH_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

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

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

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

This procedure will add a row in the msgraph_send_event_queue table. For more information, see Event properties.

The following parameters are available:

ParameterExamplesRequired
user_email_address'user1@website.com'x
allow_new_time_proposals0 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 timex
event_end_timezoneUTC (default). See timezones for other values. For e.g. West Europe use W. Europe Standard Time
has_attachments0 (default) or 1
hide_attendees0 (default) or 1
importanceempty (default), low, normal or high
is_all_day0 (default) or 1
is_online_meeting0 (default) or 1
is_reminder_on0 (default) or 1
locations'Meeting Room 4, Second Floor Room 2' (comma separated)
online_meeting_providerunknown (default), teamsForBusiness, skypeForBusiness, skypeForConsumer
organizer'user1@website.com'
recurrence_pattern_typeThe recurrence pattern type: daily, weekly, absoluteMonthly, relativeMonthly, absoluteYearly, relativeYearly
recurrence_intervalThe number of units between occurrences, where units can be in days, weeks, months, or years, depending on the type.
recurrence_monthThe month in which the event occurs. This is a number from 1 to 12
recurrence_day_of_monthThe day of the month on which the event occurs. Required if type is absoluteMonthly or absoluteYearly
recurrence_days_of_week_csvA collection of the days of the week on which the event occurs, e.g. [ "monday", "tuesday", "sunday" ]
recurrence_first_day_of_weekThe 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_indexSpecifies 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_typeThe recurrence range. The possible values are: endDate, noEnd, numbered
recurrence_start_dateThe date to start applying the recurrence pattern. Must be the same value as the start property of the recurring eventx
recurrence_end_dateThe date to stop applying the recurrence pattern. Required if type is endDate
recurrence_time_zoneTime zone for the startDate and endDate properties. If not specified, the time zone of the event is used
recurrence_number_of_occurrencesThe number of times to repeat the event. Required and must be positive if type is numbered
reminder_minutes_before_start15
response_requested0 (default) or 1
sensitivityempty (default), normal, personal, private or confidential
show_asempty (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 timex
event_start_timezoneUTC (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:

ParameterExamplesRequired
user_email_address'user1@website.com'x
event_idThe event_id that was saved when this event was added and msgraph_send_event_request_completed was calledx
attendees_required'user2@website.com, user3@website.com' (comma separated)
attendees_optional'user4@website.com, user5@website.com' (comma separated)
reset_attendees0 (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_body0 (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_categories0 (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_timezoneUTC (default). See timezones for other values. For example, for Western Europe, use W. Europe Standard Time
hide_attendees0 (default) or 1
importanceempty (default), low, normal or high
is_all_day0 (default) or 1
is_online_meeting0 (default) or 1
is_reminder_on0 (default) or 1
locations'Meeting Room 3, Third Floor Room 1' (comma separated)
reset_locations0 (default) or 1. Empties the locations when 1, overrules the locations parameter
online_meeting_providerunknown (default), teamsForBusiness, skypeForBusiness, skypeForConsumer
recurrence_pattern_typeThe recurrence pattern type: daily, weekly, absoluteMonthly, relativeMonthly, absoluteYearly, relativeYearly
recurrence_intervalThe number of units between occurrences, where units can be in days, weeks, months, or years.
recurrence_monthThe month in which the event occurs. This is a number from 1 to 12
recurrence_day_of_monthThe day of the month on which the event occurs. Required if type is absoluteMonthly or absoluteYearly
recurrence_days_of_week_csvA collection of the days of the week on which the event occurs, for example, [ "monday", "tuesday", "sunday" ]
recurrence_first_day_of_weekThe 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_indexSpecifies 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_typeThe recurrence range. The possible values are: endDate, noEnd, numbered
recurrence_start_dateThe date to start applying the recurrence pattern. Must be the same value as the start property of the recurring event
recurrence_end_dateThe date to stop applying the recurrence pattern. Required if type is endDate
recurrence_time_zoneTime zone for the startDate and endDate properties. If not specified, the time zone of the event is used
recurrence_number_of_occurrencesThe number of times to repeat the event. Required and must be positive if type is numbered
reset_recurrence0 (default) or 1. Empties the entire recurrence when 1, overrules all recurrence parameters
reminder_minutes_before_start15
response_requested0 (default) or 1
sensitivityempty (default), normal, personal, private or confidential
show_asempty (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 timex
event_start_timezoneUTC (default). See timezones for other values. For example, for Western Europe use W. Europe Standard Time
subject'Less important brainstrom meeting changed'
reset_subject0 (default) or 1. Empty the subject if 1, will overrule the subject parameter
correlation_idRepresents the external call to Microsoft Graph. After calling this procedure the id should be saved as shown in the example codex

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:

ParameterExamplesRequired
user_email_address'user1@website.com'x
event_idThe event_id that was saved when this event was added and msgraph_send_event_request_completed was calledx
correlation_idRepresents the external call to Microsoft Graph. After calling this procedure the id should be saved as shown in the example codex

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:

  1. In the subject msgraph_user_mail_folder, execute the task sync_mailbox to trigger the process flow msgraph_get_email_folder.
  2. Once the folders are retrieved, select the folders from which emails should be read
  3. 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 the onlineMeetingProvider 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.

Was this article helpful?