Mass importing User Photos into Security Expert using Data Sync Service

[Adam_Steele]

Warning

Potential for Data Loss: The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure or unfamiliar with any complex steps detailed in this article, please contact Product Support for assistance.

Issue

Need a method for mass importing User photos for creating / updating the Users for a Security Expert site.

Product Line

Security Expert

Environment

Security Expert

Security Expert Data Sync Service (SX-DB-SYNC)

Cause

Data Sync Service is typically used to import data from the third party system.  In this instance, a CSV file containing the data to be imported into the Security Expert database is used. The Data Sync Service is configured to monitor the folder where this CSV file is located and automatically (if configured) import the relevant data into Security Expert, creating records (including users, access levels, areas, doors, schedules, and so on) for each matched entity.

Resolution

The Data Sync Service can be used to map specific fields from a CSV file to fields within Security Expert. One of those fields can be the User photos. Below is a guide on how to implement this.

1. Directory Setup

Before opening the Security Expert Data Sync Service, it is necessary to setup a CSV file and directory for the Data Sync Service to work with. The only requirement for now is that each image must have a unique name and that the folder housing the images must be called "Photos". Figure 1 shows an example folder structure:

Figure 1

Note: The names of the folder, CSV file and image names are not critical. As long as the image names match what is in the CSV file.

2. CSV File

A CSV file is a comma separated file with each row being a separate record and columns (fields) separated by comma's. An example CSV is shown below:

UniqueID,FirstName,LastName,AccessLevel,PIN
101,First,User,Visitor,1284
102,Second,User,Visitor,5618

Note: The UniqueID field will need to be the file name of the Users image. For the below example CSV, two images must be present in /Photos/ with the names 101.jpg and 102.jpg.

3. Security Expert Operator

It is recommended that a new Security Expert Operator is added for importing from the Data Sync Service. This ensures that records that have been added / modified / deleted by the Data Sync Service can easily be identified. To proceed past the Data Target section the Show PIN numbers for Users option must be enabled in the Operator object.

4. Data Target

Now that an Operator, directory and CSV have been set up, open Security Expert Data Sync Service and follow these steps:

1. Select Security Expert from the Target System dropdown.
2. Confirm the SOAP Server Address. If the defaults were accepted during the installation of the SOAP Service, enter http://localhost:8030/SecurityExpertSOAPService/service.svc.
3. Enter the Username of an Operator.
4. Enter the Password for that Operator.
5. From the Site dropdown, select the Site that the records are to be imported to. All Sites that have been added in Security Expert will be available in the dropdown.
6. Overwrite the Site ID during the field mapping process if the records are imported to different Sites within a single import.

Figure 2

Note: The rest of the fields on the Configuration window are disabled until a valid Server Address, Username and Password have been entered.

5. Sync Options

1. Set the Resynchronize Every time to a time interval over which the service should resync data.
2. Tick the Enable Error Log checkbox to populate the Event Viewer with events that will make error solving simpler. 

Figure 3

6. Data Mapping

 Configure the records and import file with the following steps.

1. Select Users from the Record to Sync drop-down. 
2. In the File Directory field, enter the created folder.
3. If importing a single file, enter the name of the file in the Import File field. If the checkbox is left unticked, all files in the File Directory will be imported.
4. From the File Format drop-down, select Delimited.
5. If the import file is a CSV, select {CR}{LF} as the Row and {Comma} as Column delimiters.
6. Enable the option to Start Import At Row 2 to skip the header. 

Figure 4

7. Field Mapping

The data mapping phase allows CSV columns (Source Column) to be mapped to a record type in the Security Expert Database (Target Field). To import the example CSV, the field mapping must be configured as below. Link a Target Field and Source Column, simply select both in the interface, complete the following:

1. Link the following [Target Field : Source Column]:
   1. ImageID : 1,
   2. First Name : 2,
   3. Last Name : 3,
   4. UserAccessLevelGroupDataData > UserAccessLevel : 4
   5. PINNumber : 5,
2. Select ImageID (Column 1) as the Unique Field.
3. For UserAccesLevel, open the Advanced Data Configuration by clicking the [ … ] button.
   1. Ensure that Use This Field As The Group Data Identifier is set and Grouping Type is Singular Data.
   2. Ensure Target Field Record Type is ticked and set to Access Levels.
4. Click Save and then Start to begin the syncing of Users.

Figure 5

8. Troubleshooting

If troubleshooting is required, examine the following logs:

1. All Events - In Security Expert under Monitoring > Status Page View, open the All Events view to view Data Sync Service events.
2. Event Viewer - Open the Windows Event Viewer and navigate to Event Viewer > Applications and Services Logs > Schneider ElectricSecurityExpert event log.