Applies to: SharePoint On-Premises and SharePoint Online

Description

Crow Canyon Data Sync windows service will be installed in one of the SharePoint on-premises servers. This service invokes a process in the regular intervals (configurable) and sync the data from LAN Sweeper database to SharePoint Assets list (one way sync).

Pre-Requisites

• An SQL Account that has read permissions on the database to connect to SQL Server and execute permissions on the stored procedure that we may create during the configurations so the service can communicate with SQL database and read the required information. This information will be used in the configurations.
• A SharePoint account that has at least add/edit item permissions on the required SharePoint site. This information will be used in the configurations so the service can communicate with SharePoint Online site.
• An SQL Admin Account to create Stored Procedure in the SQL database. This account will be used only to create the stored procedure and it will not be used in any configurations.
• Database and SharePoint should be accessible from the server where the service is installed.
• Local Administrator account on the server to install the service.

This sync service reads a configuration xml file that has three different configurable nodes as described below.

1. Database information

• This section takes the connection string so the service can communicate with the database and read information from LAN Sweeper.
• It takes the stored procedure name as input that returns the required information from database.
• We have our standard stored procedure that returns below information.
  • tblAssets.AssetID (Asset ID)
  • tblAssets.AssetName (Asset Name)
  • tblAssets.Description (Asset Description)
  • tblAssets.FQDN (FQDN)
  • tblAssets.IPAddress (IP Address)
  • tblAssets.Lasttried (Last Log on date)
  • tblOperatingsystem.Caption (Operating System)
  • tblOperatingsystem.ServicePackMajorVersion (OS Service Pack)
  • tblOperatingsystem.Version (OS Version)
  • tblADusers.Company (User Company)
  • tblADusers.Department (User Department)
  • tblADusers.email (User Email)
  • tblADusers.Fax (User Fax)
  • tblADusers.Mobile (User Mobile)
  • tblADusers.Telephone (User Telephone)
  • tblADusers.Zip (User Zip)
  • tblADusers.Userdomain (user Domain)
  • tblADusers.Username (User Name)
  • tblAssetCustom.Manufacturer (Make)
  • tblAssetCustom.Model (Model)
• Along with the above information, we also update “Asset Info Link” hyperlink column in SharePoint Asset with your corresponding LAN Sweeper web console URL so one can always click to see more details about the Asset.

2. SharePoint information

This section takes SharePoint site URL, user credentials so it can communicate with SharePoint and add/update the Assets.

3. Column Mappings

This section provides ability to map the database column values to SharePoint Assets list column.

Also, our sync service considers “Asset ID” as unique key and verifies the existence prior to creating the Asset in SharePoint, this way it eliminates duplicate items creation. Please note that if we would like to pull more information from LAN Sweeper database, then we can alter the standard stored procedure accordingly.