Microsoft Graph solutions
Introduction
Microsoft Graph is a RESTful web API that enables you to access Microsoft Cloud service resources. After you register your app and get authentication tokens you can make requests to the Microsoft Graph API.
More info about the API can be found here.
The currently supported resources are:
- Events (appointments)
EmailsContactsTasksAttachments
More resources will be supported later.
To enable our end products to receive and send these resources we make use of process flows which will eventually call HTTP Connectors. For each resource that we support a different solution is available in the Thinkstore.
With this solution scheduled process flows are used to process a queue table which enables the Thinkwise Application to synchronize supported resources between the Microsoft Cloud. In this queue tables rows can be added that contain data for the different supported resources. The process flows will make an external call to the MS Graph API (using an Http Connector process action) that can add, update, delete and retrieve resources for multiple users.
This product is a replacement for the original Thinkwise Exchange Connector, although it cannot simply be upgraded. The new product is designed differently and is now fully maintainable within the Software Factory. No stand-alone service is required any more.
Configuration
When one or more of the solutions are installed in the Thinkstore, an OAuth configuration is needed for the different process flows to work. The OAuth configuration msgraph_oauth is delivered with the solution and needs to be filled in once installation has completed. This can be done in the Software Factory, but it can also be overridden once it’s synchronized to IAM.
The process flows included in the solutions are used to synchronize the resources between the Thinkwise end product adn the MS Graph API, they are all prefixed with msgraph_send_ or msgraph_receive_.
To keep all resources data up to date the process flows should be scheduled so the queued resources are synchronized regularly.
A schedule for this flows can be added in IAM which will determine how often the rows in the queue tables are processed.
The msgraph_user table needs to be manually filled by the developer.
For every unique user that needs to synchronize resources, a row should be added in this table.
For every MS Graph solution that is installed an extra boolean column is added which determines which recources are synchronized for this user, e.g. receive_events.
Shared principles across MS Graph solutions
All tables, procedures, functions and data types are prefixed with msgraph_ to make sure they are properly ordered on the database. After the first part there is a send_ or receive_ prefix to indicate in which direction the synchronization flows. After that the names will be more specific for the resource they belong to, e.g. msgrap_receive_event_request_completed. The procedures prefixed with msgraph_ are meant to be enhanced by the developer with code templates to process the sent and received synchronized data. Procedures prefixed with prc_msgraph are process procedures and should not be manipulated by the developer.
For each resource a receive and send solution is available, e.g. MSGRAPH_RECEIVE_EVENT and MSGRAPH_SEND_EVENT. This way the developer can choose specifically what he wants to synchronize between the end product and the MS Graph API.
In each MS Graph solution the following two tables are always present:
- msgraph_user
- msgraph_log
msgraph_user
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 holds columns for authenticating the user against the MS Graph API. Authentication can be done with app-only permissions, more info can be found here.
Later, support for delegated permissions will be added.
The following columns are available:
- Email address
- Access token
Additional boolean columns are added that can indicate whether a user is allowed to synchronize certain resources, e.g. receive_events and send_events. Each column will be automatically added once the solution is installed from the Thinkstore.
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.
msgraph_log
Errors that occur will be logged in this table. Once a call to MS Graph fails the status code and response body will be inserted and the developer can use this to troubleshoot the problem.
Resources
Receiving events
To enable receiving events please install the MSGRAPH_RECEIVE_EVENT solution from the Thinkstore.
Receiving events for the first time requires a queued item in the msgraph_receive_event_queue table. This can be accomplished by calling the msgraph_receive_event_add procedure, which will add a queued item to this table which will later synchronize events for the given user between a certain date and time range using the process flow msgraph_receive_event.
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)
After the range is synchronized the returned delta token can be used to synchronize the same range from a certain point in time, to prevent redundant data and to prevent the entire range from being synchronized every time.
This can be done with the msgraph_receive_event_add_delta procedure, which will also 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 build in process flow will check this queue table every x minutes and will process all the rows present. It will call the MS Graph API for each row and will call the following procedures:
- All received events are separated and iterated over so for every event the
msgraph_receive_event_process_eventprocedure can be called. All properties of an event are parsed as parameters and the developer can weave a template to process this. - Every deleted event can be processed in the
msgraph_receive_event_process_deleted_eventprocedure with a woven template. - A final call to
msgraph_receive_event_request_completedwill take place to allow the developer 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 every procedure that processes data the developer needs to weave a template. In these code examples a simple example is provided 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_d
- range_start_datetime
- range_end_datetime
The correlation id (uniqueidentifier) is an output parameter and needs to be saved somewhere (e.g. msgraph_correlations) so it can be used to synchronize the same date time range with the received delta token in the future.
The task could look like this:
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 in the queue. The parameters can be filled with the same correlation id and delta token that were saved with msgraph_receive_event_request_completed.
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.
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.
-- 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.
update msgraph_correlations
set delta_token = @delta_token
where user_email_address = @user_email_address,
and correlation_id = @correlation_id
Sending events
To enable sending events please install the MSGRAPH_SEND_EVENT solution from the Thinkstore.
Sending events requires a queued item in the msgraph_send_event_queue table. This can be accomplished by calling the msgraph_send_event_add, msgraph_send_event_update or msgraph_send_event_delete procedure, which will add a queued item to this table which will later synchronize events for the given user using the process flow msgraph_send_event. A schedule for this flow can be added in IAM which will determine how often the rows in the queue table are processed.
Code examples
For every procedure that processes data the developer needs to weave a template. In these code examples a simple example is provided 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:
| 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 on MS side | |
| 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 MS Graph. After calling this procedure the id should be saved as shown in the example code |
This an example of how to call this procedure:
-- 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. Empty the attendees when 1, will overrule 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. Empty the body_content when 1, will overrule the body_content parameter | |
| categories | [ "Red Category", "Green Category" ]. Does not work at the moment, known issue on MS side | |
| reset_categories | 0 (default) or 1. Empty the categories when 1, will overrule the categories parameter | |
| event_end | '2022-10-20 09:00:00.000' (UTC datetime). Change event_end_timezone if this should be local time | |
| event_end_timezone | UTC (default). See timezones for other values. For e.g. West 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. Empty the locations when 1, will overrule 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, 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 | |
| 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. Empty the entire recurrence when 1, will overrule 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 e.g. West 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 MS 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.
This an example of how to call this procedure:
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 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,
@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 MS Graph. After calling this procedure the id should be saved as shown in the example code | x |
This an example of how to call this procedure:
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 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,
@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 MS Graph is finished and the correlation that was saved is now done. For newly added events the event_id will be filled and this can now be processed together with the correlation_id. A template can be woven to this Control Procedure which can look like this:
update msgraph_correlations
set event_id = @event_id,
finished = 1
where user_email_address = @user_email_address,
and correlation_id = @correlation_id
Known issues
- Adding, updating and deleting
categoriesfor 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. Microsoft Documentation states that
isOnlineMeetinghas to be set to true and theonlineMeetingProviderhas to be set. This should initialize an online meeting but it does nothing.