Applies to: SharePoint On-Premises and SharePoint Online
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 SCCM database to SharePoint Assets list (one way sync).
- 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/On-Prem 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 SCCM.
- 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.
- s.ResourceID (Id)
- s.Netbios_Name0 (Computer Name)
- Operating_System_Name_and0 (OSName)
- CSYS.Manufacturer0 (CSYSManufacturer)
- CSYS.Model0 (CSYSModel)
- Processor.Name0 (ProcessorName)
- OPSYS.Name0 (OPSYSName)
- OPSYS.ServicePackMajorVersion0 (OPSYSVersion)
- MEM.TotalPhysicalMemory0 (MEMTotalRAM)
- LOGDisk.Size0 (TotalHDSpace)
- RS.lastLogon0 (LastLogon)
- RS.User_Domain0 (Domain)
- RS.User_Name0 (UserName)
- Rs.Last_Logon_Timestamp0 (LASTLogonTime)
- PCBios.SerialNumber0 (PCBiosSerialNumber)
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 SCCM database, then we can alter the standard stored procedure accordingly.