Crow Canyon Data Sync Service for Intune
Applies to: SharePoint Online
Note: This service requires an additional purchase.
Description
Crow Canyon DataSync Service for SharePoint is a Windows Service that
synchronizes Intune data to corresponding SharePoint list(s). This service also
synchronizes any ODBC-compliant database table(s) data to corresponding
SharePoint list(s) and vice versa. We can use this as one way sync as well based
on the use case. This service is installed in the customer’s intranet and is
configurable in terms of which records to sync, mapping from Intune to
SharePoint list columns and the sync frequency. It can sync new records by
creating new list items in Intune and sync existing records by updating list items.
Pre-requisites
1. Intune is setup and devices are synced in Intune. If not, please refer below
article to setup Intune:
Article:
https://learn.microsoft.com/en-us/mem/intune/fundamentals/deployment-plan-
setup
2. Intune and SharePoint should be accessible from the server where the
service is being installed.
3. Microsoft .NET Framework 4.7.2 or above should be installed on the server.
4. Local Administrator account on the server to install the service.
Installation Package
1. Configuration.xml
2. CrowCanyon.DBSyncService.exe
3. InstallUtil.exe
4. Dependency Dlls (Microsoft.SharePoint.Client.dll,
Microsoft.SharePoint.Client.Runtime.dll, Microsoft.Identity.Client.dll,
microsoft.identitymodel.dll, Microsoft.IdentityModel.Extensions.dll)
5. List Templates (O365/On-Premises)
Installation Steps
1. Prepare the install location
2. Prepare the SharePoint Environment
3. Setup graph API to fetch managed devices from Intune
4. Prepare the Configuration file
5. Install Crow Canyon data sync Service
Prepare the install location
Download package “CrowCanyon.DBSyncService.zip”, right click,
properties and unblock the file if it is blocked.
Open Command Prompt (Run as Administrator) navigate to the Package
location enter the below command to verify MD5 signature for the
package.
In this example, package download link is like
“https://…/MD5_44dd766b53664330143b8a49cecd7362/CrowCanyon.DBS
yncService.zip” and the command output is matching with GUID in the
download link as expected. Please contact
sharepointsupport@crowcanyon.com if this GUID does not match.
After MD5 signature is validated, right click on zip file and extract the
required package files.
Paste the installation package at proper location as this location will be the
installation location and the logs will generated in this location and service
assemblies (*.dll) will be present in the same location
Place all the below files in required folder and the service will be installed
in this location. Right click on each file in the package, properties, unblock
if the file is blocked.
o Configuration.xml
o CrowCanyon.DBSyncService.exe
o InstallUtil.exe
o Dependency assemblies
Note:
Package contains “Configuration_LANSweeper.xml”,
“Configuration_SCCM.xml” files, and Configuration_Intune based on the
application that is being configured (LANSweeper, SCCM or Intune),
rename the corresponding configuration file to configuration.xml.
If we are using this for custom database sync other than Lansweeper and
SCCM, take any configuration file as starting point and update it with
required changes.
Prepare the SharePoint Environment
Create two lists in the target SharePoint site using the list templates available in
the package.
1. Create a list for Sync Process Log using "CCSSyncLog.stp" List Template
from the package. List name should be "CCSSyncLog".
a. Index "Created" column, update the default view with filter like
"Created" is greater than or equal to "[Today]-7".
2. Create a list for Sync Time using "CCSSyncTime.stp" List Template from
the package. List name should be "CCSSyncTime".
a. Recreate "SyncLog" Lookup column (Lookup List: CCSSyncLog)
Note:
For SharePoint Online, use the list templates from "O365" folder in the package.
Create the required list schema in the SharePoint Target list to map the columns
from the intune to SharePoint columns. These will be used at a later stage while
defining the field mappings from Intune to SharePoint list fields.
Setup graph API to fetch managed devices from Intune
Configure graph API to read the devices from Intune.
1. Create an Azure Active Directory application using Azure portal
a. Log in to portal.azure.com open the open the “Microsoft Entra ID”
blade
b. Select the “App registrations” blade under “Manage” and
select ‘New registration’
c. Fill in the following information:
i. Name - Crow Canyon Graph API
ii. Supported account types
iii. Select Register
d. Select the Overview blade and copy the Application ID.
Note that this Application Id will be used in configurations later
in the setup process.
e. In the Certificates & secrets blade, select New client
secret
f. Enter a description, expiration (select Never), and select Add
g. Copy the key value (i.e., App credentials)
h. Copy the Application ID from overview page.
“Application ID” and “App credentials” will be used in the Crow Canyon Data
Sync service configuration .
2. Configure Application permissions for the application registration.
a. In the API permissions blade, select Add a permission.
b. Select Microsoft APIs. and select Microsoft Graph.
c. In Application permissions, select following Permission
DeviceManagementManagedDevices.PrivilegedOperations.All
DeviceManagementManagedDevices.ReadWrite.All
DeviceManagementManagedDevices.Read.All
DeviceManagementRBAC.ReadWrite.All
DeviceManagementRBAC.Read.All
DeviceManagementApps.ReadWrite.All
DeviceManagementApps.Read.All
DeviceManagementConfiguration.ReadWrite.All
DeviceManagementConfiguration.Read.All
DeviceManagementServiceConfig.ReadWrite.All
DeviceManagementServiceConfig.Read.All
d. click Add permissions
3. Grant admin consent from the Azure portal
a. Sign in to the Azure portal as a Global Administrator, an Application
Administrator, or a Cloud Application Administrator.
b. Select Azure Active Directory then Enterprise applications.
c. Select Crow Canyon Graph API, select ‘Permissions’ and then
click Grant admin consent
Note: Below screenshot is showing Email API, in our case this will be
“Crow Canyon Graph API”
Prepare the Configuration file
Setup Configuration.xml file.
It is possible to configure sync settings for multiple Intune sync using our Data
Sync service. For every Intune sync we can add one Sync configuration node.
Every Sync Configuration contains mainly below set of nodes
SharePoint Information
Database Information
Mappings
Signature Information
Sync Time Information
Process Information
Log Policy
SyncConfig Node
Every Intune needs to have its own Sync configuration node. Every sync
configuration node need to have its own Sync Configuration Id.
SyncConfigID Attribute
o Unique ID provided for a sync configuration. For example,
CCSIntune1, CCSIntune2, etc.
SPInfo Node
We need to provide SharePoint List connection information in this node.
SPType Attribute
Type of SharePoint environment Possible values : (On Prem, On Prem CSOM ,
Office 365)
For SharePoint Online environment, use “Office 365”
SiteURL Node
Enter SharePoint site URL in which the target list exists that we need to use for
this sync
Domain Node
If we are configuring for On-premises, this node is required otherwise we can
leave this node blank
UserName Node
Specify a user name that has "create and update" item permissions on the
SharePoint Sync List
Password Node
Enter user password for the user mentioned in UserName Node
Generally password may have special character so we need to use below syntax
for password.
<Password IsSecure=”false”><![CDATA[password]]></Password>
App Client Node
This is required only for O365 Version where the tenant uses Modern
Authentication. In this case, we need to register an app for the site collection and
grant permissions for the Modern app to access the site.
To create Modern app for the site collection, please use below URL and click on
‘Generate’ for Client ID and Client Secret, specify a domain name as needed.
https://<site collection url>/_layouts/15/AppRegNew.aspx
Fill the following details
Title: Crow Canyon DB Service
App Domain: www.contoso.com
Redirect URI: https://www.contoso.com/default.aspx
Please specify the domain of the organization and Redirect URI with required
organization page URL. Above used addresses are a sample addresses we have
considered for configurations.
Save the above details to a notepad file.
To grant permissions on the site for the modern app, please use below URL and
grant permissions as shown below
https://<site collection url>/_layouts/15/appinv.aspx
Enter Application Client ID obtained above and click on Lookup to auto fetch
details as shown below.
Enter below lines in Permission Request XML and click on Ok.
<AppPermissionRequests AllowAppOnlyPolicy="true" >
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection"
Right="FullControl" />
</AppPermissionRequests>
Update Configuration.xml with above App Client ID and App Client Secret as
shown below.
ListName Node
Specify SharePoint target list title that we need to sync (create/update items) for
this sync
UpdateCAML Node
Enter CAML Query that could check if the Intune entry is already created in
SharePoint list. If the service finds the SharePoint list item with this CAML query
then it updates the item otherwise it creates the item in SharePoint list so it
eliminates the duplicate entries.
This CAML Query accepts the placeholders. We can use the SharePoint
column internal name in "Name" attribute of "FieldRef" tag and we can use
Intune result set column name in "Value" tag text. This way every time
before the service creates a new entry for every item in result data set, it
checks if the entry is already created in SharePoint.
Placeholder Syntax: {ValueExp: "[DBColumn Name]", RegEx=""}
Please note that in ValueExp, more than one column name and static text
can be specified. e.g., we could give "[DBFirstName] [DBLastName]" (note
the static space between Db column name expressions)
A regular expression can also be specified that will extract the matching
string from the ValueExp (after replacing DB column values) and then that
will be used in the query. e.g. if we want to extract the domain name from
SharePoint site URL then you can use something like https://(.+?)/
DelayExecution Node
When querying SharePoint to check if the item is already created in SharePoint
list using above UpdateCML setting, sometimes SharePoint may throw throttling
error (http status codes 429, 406, 503 and 504). This generally happens when
syncing large data and for try avoiding this conflict, we can add delay between
each operation using this node. In the below sample, we are adding 100 milli
seconds delay between each SharePoint operation.
<DelayExecution>100</DelayExecution>
RetryCountAfterThrottle Node
Event after using delay execution setting mentioned above, if we are still
receiving the throttle error from SharePoint then we can use this node to retry.
Service uses RetryIntervalAfterThrottle node mentioned below for waiting (in
seconds) before next retry. See example below, it will retry two times.
<RetryCountAfterThrottle>2</RetryCountAfterThrottle>
RetryIntervalAfterThrottle Node
Event after using delay execution setting mentioned above, if we are still
receiving the throttle error from SharePoint then we can use this node along with
RetryCountAfterThrottle setting described above. See example below, it will retry
after 2 seconds. Generally SharePoint will provide the number of seconds to be
waited before retry and this property will be used only in case SharePoint does
not give the required wait interval information.
<RetryIntervalAfterThrottle>2</RetryIntervalAfterThrottle>
DBInfo Node:
Provide Intune Connection Information
DBType Attribute:
Provide database type as "MSIntune". Also supports “SQLServer”.
Specify Tenant, Client Id and Client Secrete of graph API setup in above step
<Tenant>contoso.com</Tenant>
<AppClientID>##Client ID##</AppClientID>
<AppClientSecret>##Client Secrete##</AppClientSecret>
Mappings Node:
This section provides ability to map the Intune column values to SharePoint list
columns.
Map Node:
This defines the mapping detail of SharePoint Column.
SPField Attribute:
This defines the SharePoint List column internal name
ValueExp Attribute:
This define the SharePoint Column Value Expression.
Value Expression can have Intune Recordset Column Name enclosed in square
bracket([]). For example: [AssetName]
We can define multiple placeholders in one Expression to Concatenate the Two
Recordset Column value.
RegEx Attribute:
Parse the Value expression value using Regular expression. For Example get the
Domain name from Web Url
SPLookupListColName Attribute:
Resolve the lookup column based on specified column internal name in this
property instead of resolving value based on display column setting in lookup
column setting of the column mentioned in SPField attribute above.
SignatureInfo Node:
SignatureFieldName Attribute: SharePoint List Column Name (Internal
Name of the Column)
SignatureFieldValue Attribute: Unique Value for this Sync like
CCSLansweeper. This helps us to identify the items created by our Data
Sync Service.
SyncTimeInfo Node:
ListName Attribute: This define the CCSSyncTime SharePoint list name
Process Node:
Recurr Attributrte:
If True, then Sync Process has to run event n number of minutes (based not
Recurring interval)
If False, then process run daily once based on ScanTime
ScanTime Node:
In this node, we can set the Hour at which this sync should start happen every
day. Possible Value (0-23) hour of day. If recurrence is disabled then only sync
happens daily at the time mentioned in this node.
RecurInterval Node:
This defines the recurrence interval in minutes for the sync. This setting will be
used only if recurrence is enabled.
LogPolicy Node
LogLevel Node:
Information Log will be generated if the value is set to 1. For Error log, set this
attribute to 0.
LogFileName node:
The log file name can be given in this attribute
SyncLogInfo Node:
SyncLogListName Attribute: CCSSyncLog SharePoint list Name. We can refer
this list to see the logs to investigate possible unknown errors.
ServiceSettings:
This tag has the settings SyncInterval node for the service which are common to
all the Sync configurations. Based on the time interval set in the SyncInterval
Node, service starts after every n minutes.
RetainLogFilesForHours node can be used to automatically delete the service
logs. Enter required number of hours to retain the log files in Logs folder.
Example: <RetainLogFilesForHours>10</RetainLogFilesForHours>
Install Crow Canyon Data Sync Service
Open administrator command prompt (Run as administrator)
Change the location to setup files location
o Cd <<setup file path>>
Run below command:
o InstallUtil.exe -i "CrowCanyon.DBSyncService.exe"
In prompts to enter the service account, fill the details and save (Local
Administrator account)
After this step, “Crow Canyon Database Sync Service” starts appearing in
service in the services.msc
