Lightspeed

SIS Integration – Lightspeed CSV Method

Importing data from your student information system (SIS) is the most practical and advantageous way to promote adoption of the classroom tools in Mobile Manager, Web Filter, and Classroom Orchestrator.

Before we start, here’s why integrating data from your student information system is so helpful.

  • 1. User accounts are imported and updated automatically.
  • 2. Classroom groups are created and populated with users and devices–making them ready for teachers to manage.
  • 3. Class and school transfers for users are updated automatically when SIS data changes. Administrators and Teachers save time by not having to update records in more than one application.
  • 4. New term- and school-year information can be propagated automatically, preserving or recreating user records, groups, and device assignments
  • 5. Devices can be commissioned and decommissioned, associated with asset tags, and assigned to schools, groups, and students.

While this course describes how to integrate your SIS data using properly formatted CSV files, Lightspeed Systems supports several other methods including Apple Classroom integration, Enhanced SIS Integration with Clever (requires separate licensing), and SIMS integration for UK users.

Locate the Data in Your SIS

The first step to importing your student information system (SIS) data is associating the fields in your SIS database with the corresponding fields in the Lightspeed Systems import files–then exporting the data in the correct sequence.

To get started, locate the “custom reports” interface in your SIS. That’s where you can specify the database fields to export. For the exported report file format, you’ll need a CSV with comma-separated values or “Excel” designated. Some SIS applications will let you click to select fields, while others may require you to write an SQL query.

Sample SIS Interfaces

Note: The CSV import file does not necessarily have to be generated by your student information system. You can export data from other applications as long as the export file is in the correct format. You can also create the file manually in a spreadsheet and save it as a CSV file.

CSV Specifications

Here’s the fine print.

  • Plain text CSV files
  • Encoded as “UTF-8 without BOM”
  • No header row in the output file
  • One line per record
  • Each field separated by commas
  • All fields must be declared, even optional fields you choose not to populate, except the last field. There must be only one comma at the end of each row.
  • No commas or numerals in first or last name fields. If necessary, substitute Roman numerals, e.g., John Doe 3rd should be changed to John Doe III.
  • Before importing, you can open the file in a text editor such as Notepad to verify that the correct fields are present and in the correct order.

SIS import error logs are accessible for troubleshooting inside Lightspeed Systems products.

Create three files

You’ll produce three files that map SIS data to your Lightspeed Systems products:

  • a users csv file to create users with corresponding school, email, SIS ID, type (staff or student), username
  • a groups csv file to create class groups with corresponding school, SIS ID, and title
  • a memberships csv file to populate class groups with users so that all users within a group can be managed at once

Let’s get started.

Map user data

A user data file will add or update user accounts. This file MUST be named either users.csv or users-v2.csv. Files with any other name, case, or extension will be ignored. There is no functional difference between these two files. However, if both files are present, only users-v2.csv will be processed.

Note: users-v2.csv files must contain headers with the specific column Field Names below.

Create the users.csv or users-v2.csv file

Column Field Name Description Note
A unique_sis_user_id Unique ID of the user in your SIS. Must be unique within the entire district required
B username The user’s login name. For users authenticating with an auth source on your Rocket, this should be their network username and must be unique to the district. required
C first_name First name of the user required
D last_name Last name of the user required
E unique_sis_school_id The SIS ID for each school, which must be unique within your district. required
F grade Grade level is optional unless you need to filter Campus Library items by grade level, or set restrictions policies in Mobile Manager by grade level. United States Student grade level or year (1 through 12) Use 0 (zero) for Kindergarten, and -1 (minus one) for Pre-Kindergarten. United Kingdom – Student year (1 through 12). Use -1 (minus one) for Reception, 0 for Year 1, 1 for Year 2, etc.For example:Reception- -1
Year 1- 0
Year 2- 1
Year 3- 2…
Year 13- 12
optional
G email Email address where the user can receive notifications, messages, and alerts. optional
H user_type student: 1staff: 2 If left blank, the default user type student is used.
I password Only needed for users not authenticating with your Directory service or GAFE domain Optional*
J authentication 0 = Directory Service authentication, 1 = Local authentication Recommended*

*Password option: When authenticating user logins without a Rocket server (option “1” in Column J), you must enter a password in Column I.

Format

Column A Column B Column C Column D Column E Col. F Col. G Col. H Col. I Col. J
unique_sis_user_id Username first_name last_name unique_sis_school_id Grade Email user_type Password authentication

Examples

users.csv

Sample Users CSV Files

users-v2

Note: The import files must be named correctly or they won’t be imported.
Note: CSV files must be encoded as “UTF-8 without BOM”. There can be no spaces between each line.

Map group data

A groups data file will add or update your groups. You can use the groups.csv import file or groups-v2.csv import file to add or update groups in Mobile Manager and Classroom Orchestrator. Additionally, groups-v2.csv contains the option to enable and disable Apple Classroom in groups. Our system will initially search for the newer groups-v2.csv format, which accommodates associating subgroups to parent groups, and will then search for the older groups.csv format if it cannot locate a groups-v2.csv file. Files with any other name, case, or extension will be ignored.

Create the groups.csv file

Column Field Name Description Notes
A unique_sis_group_id Unique ID in your SIS for each virtual classroom group to create. Must be unique within the entire district required
B group_name Name of the group/class required
C unique_sis_user_id The Group Owner. Unique SIS ID of the group owner (normally a teacher or other staff level user) required
D unique_sis_school_id Each Group is associated with either a school (typical), or a district. Enter the SIS ID for the school or district that is “home” for this group required

Create the groups-v2.csv file

Column Field Name Description Notes
A unique_sis_group_id Unique ID in your SIS for each virtual classroom group to create. Must be unique within the entire district required
B group_name Name of the group/class required
C unique_sis_user_id The Group Owner. Unique SIS ID of the group owner (normally a teacher or other staff level user) required
D unique_sis_school_id Each Group is associated with either a school (typical), or a district. Enter the SIS ID for the school or district that is “home” for this group required
E sis_parent_group_id If the group is nested inside another group, i.e., a parent group, use this field to indicate that parent group’s SIS ID. optional
F apple_classroom Every group can have Apple Classroom enabled. Enter a value of ‘1’ in this field to enable it or a value of ‘0’ to disable it. Leaving this field blank will leave the status of Apple Classroom unchanged. optional

Format (groups.csv)

Column A Column B Column C Column D Column E
unique_sis_group_id group_name unique_sis_user_id unique_sis_school_id Not used

Format (groups-v2.csv)

Column A Column B Column C Column D Column E Column F
unique_sis_group_id group_name unique_sis_user_id unique_sis_school_id sis_parent_group_id apple_classroom

Note: The Groups-v2.csv file MUST  include a header row with the following information:

unique_sis_group_id,group_name,unique_sis_user_id,unique_sis_school_id,sis_parent_group_id,apple_classroom

Examples

groups-v2

groups

Note: The import files must be named correctly or they won’t be imported.
Note: CSV files must be encoded as “UTF-8 without BOM”. There can be no spaces between each line.

.

Map group members data

Use the import memberships.csv or memberships-v2.csv file to add or update group members and to add group admins in the Lightspeed Dashboard. This file MUST be named either memberships.csv or memberships-v2.csv. Files with any other name, case, or extension will be ignored.If both files are present on the server, only memberships-v2.csv will be processed.

Note: There is a functional difference in the way these two files are processed with regards to updating/removing memberships. We recommend that new clients use the memberships-v2.csv file format, as it allows for associating both subgroups and parent groups.

In memberships.csv, only groups are evaluated. All users who are listed in the file, but are not members of a group, the users will have that membership removed. If said group is not listed anywhere in the file, memberships in that group is unaffected.

In memberships-v2.csv, both users and groups are evaluated. Users are removed from groups that are not listed in the file, the same as memberships.csv. However, users will be removed from groups, even if the group is not listed anywhere in the file, as long as these conditions are present:

  • The user is listed somewhere in the file
  • The group has a SIS ID
  • The user was not added to the group manually

Note: memberships-v2.csv files must contain headers with the specific column Field Names below.

Create the memberships.csv or memberships-v2.csv file

Column Field Name Description Notes
A unique_sis_group_id Unique ID in your SIS for each virtual classroom group to populate. Must be unique within the entire district required
B unique_sis_user_id Unique ID for each user in your SIS who is a member of this group/class required
C unique_sis_school_id Each Group is associated with either a school (typical), or a district. Enter the SIS ID for the school or district that is “home” for this groups required
D mm_admin A blank value or a value of “0” will only add or modify user membership. A value of “1” will add the user as an admin of the group optional

Format

Column A Column B Column C Column D
unique_sis_group_id unique_sis_user_id unique_sis_school_id mm_admin

A new field has been added in order to allow you to add admins to a group: mm_admin. Using this column to set an admin role will NOT make the user a member of the group. An additional line, without the 4th column, will need to be added to the CSV file.

The behavior of the 4th column is as follows:

  • If using a header line, this column should be named mm_admin.
  • Set the 4th column to “1” to make the user on that line an admin of the group.
  • Set the 4th column to “0” or leave it empty to add/modify group memberships.
  • If the user should be both an Admin and a Member of the group, then simply add a copy of the line without the 4th column.

Examples:

 

memberships-v2

memberships.csv

Note: The import files must be named correctly or they won’t be imported.
Note: CSV files must be encoded as “UTF-8 without BOM”. There can be no spaces between each line.

Once you have exported SIS data into a properly formatted CSV file, you’ll need to share it in a place Lightspeed Systems can access it. You can do that in one of two ways: on a Lightspeed Rocket appliance or on a web server.

Share your CSV file on a Rocket

The process differs slightly depending on Rocket version.

You must place the export file in your Lightspeed Rocket’s Network Share/sis_import folder.

Rocket version 2.x

To find the details for the Network Share, log into your Rocket console, click Administration, select the root tier, and then scroll way down to Network Share at the bottom. You should see something like

\\<IP address>\tier1

as shown in the screenshot below.

r3-00224

  • Inside that share are two folders: backup and sis_import, for backups and the SIS imports, respectively. Please note that the root tier share is the only one that has the backup All other tiers will only have the sis_import folder.

Rocket version 3.x

To find the details for the Network Share, log into your Rocket appliance, click Settings, then select Tier Management. Select the root tier, and then scroll way down to Network Share at the bottom. You should see something like

\\<IP address>\tier1

as shown in the screenshot below.

r3-00223

  • Inside that share are two folders: backup and sis_import for backups and the SIS imports, respectively. Please note that the root tier share is the only one that has the backup. All other tiers will only have the sis_import folder.

Share your CSV file on a web server

Commercial web hosting service

Most commercial web hosting providers allow their subscribers to create subdomains, and will update the necessary DNS records for you automatically. All you need to do is log into the control panel for your web hosting account, then navigate to the “subdomains” page. You can learn more about subdomains in this article at Webmasters-Central.com.

Folder creation is even simpler and does not require you to update your DNS record, especially if your web hosting provider offers a Web FTP interface. Log in via FTP to your website’s root directory.

  • If you’re using a graphical FTP client, right-click on the root folder and select “New Folder”. Give the folder a name, and you’re done.
  • If you’re using a command-line FTP client, use the command “mkdir name” where name is the name of the folder to create.

On-site web server

To add a subdomain to an on-site web server, you’ll need to follow the instructions in your web server documentation to set up a “virtual host.” You’ll also need to create a CNAME record in your DNS settings to resolve the subdomain to the correct location on the web server.

To add a folder, follow the “folder creation” instructions in the first section of this article.

Note: A detailed discussion on web server and DNS configuration is outside the scope of this document. Please refer to the documentation for your web server for more information.

Make sure server is accessible on Port 80

Before you add the Custom URL to Launch, make sure it’s accessible on Port 80.

Note: Encrypted connections (port 443) and alternate port numbers (e.g., 8080, 8000) are not currently supported.

To verify that the Custom URL is correct, open a web browser and navigate to the website address for your CSV imports.

  • This message indicates that the site is not reachable because of a DNS error. Either the DNS update has not propagated yet (allow up to 24 hours), or the DNS record was not updated correctly (contact your web host’s support department).

r3-01022

  • This message shows that the subdomain or folder is reachable. For security reasons, you should update your web server settings to disable directory browsing, or add a blank index.html page to the folder.

r3-0102-24

If sharing files on a Rocket, enable Port 80 access

Many Lightspeed Systems functions require external access to the Lightspeed Server’s management NIC. Such access will be denied if a couple things aren’t in place: 1) the appropriate DNS “NAT” entries identifying the Lightspeed Systems Rocket publicly and privately and 2) required firewall rules.

Note: This document specifically refers to Port 80, yet you can use the same instructions to configure any port.
Note: These instructions also work for setting up your proxy port.

Why enable external port access to the management NIC

  • Permit Launch (Lightspeed Dashboard), Mobile Manager, and Classroom Orchestrator integration with Lightspeed Systems Rocket
  • Remotely filter with Mobile Filter, including the Mobile Filter Browser for iPhone, iPod, and iPads
  • View reports off  site
  • Allow email summary links to work outside of the network
    –Can be also done over port 443 (SSL) if enabled, see SSL Certificate for the Lightspeed Systems Rocket

Note: Our server will first try to retrieve the file over SSL port 443 and if that fails, the server will then try over HTTP port 80. The file will be securely transferred as long as port 443 is enabled.

How to enable Port 80 and Proxy port access

The following information outlines the steps that should be taken to assure users both internal and external to the network can properly access your Lightspeed Systems Rocket.

  • Set Up an FQHN
    Using a Fully Qualified Host Name (FQHN) vs an external IP allows users to access the server directly while inside without having to loop outside the network and back in. This assumes you’ve created separate DNS settings for external and internal (described below).
  • Rocket Settings
    Log into the Rocket appliance and navigate to Administration > Network Interfaces.Find the IP Address setting under Management Interface and specify the FQHN for your Rocket. For example, ls.ourusd.k12.ca.us
  • Firewall Settings
    Create an access rule in your Firewall to translate (NAT) an external IP Address on port 80 to the internal IP Address of Lightspeed Systems Rocket. For example, Port 80: 209.16.17.2 = 10.16.80.2This rule directs the firewall to allow port 80 (HTTP) sessions between the external user and the Lightspeed Systems Rocket.Note: If you are enabling a proxy port (Port 8080, or your particular specified proxy port 80**), you will need to repeat the Firewall Settings step for your specific proxy port. 
  • DNS Setting
    Internal DNS
    Create an entry to translate the FQHN to the internal IP of the Rocket. For example, ls.ourusd.k12.ca.us = 10.16.80.2This will assure that users inside the network access the Rocket directly, rather than having their selected action(s) travel out of the network and then back in.External DNS
    Create an entry to translate the FQHN to the external IP of the Lightspeed Systems Rocket. For example, ls.ourusd.k12.ca.us = 209.16.17.2

With these addressing modifications in place, users should be able to access the Rocket from both inside and outside the enterprise network.

If sharing files on a Rocket, setup Rocket integration

Now we’ll jump into Launch Admin Tools to inform Lightspeed Systems where to find your shared files. To proceed, you’ll need to have district admin privileges designated in your account. You’ll know for sure when you login to Launch–as only district admins will see the Districts and SIS options under Admin Tools.

If you’re sharing files on a web server, you can skip ahead to “Define SIS Integration in Launch.”

To enable Lightspeed Systems products for your staff using Launch and to set up automatic student-data imports from a Lightspeed Rocket appliance, you’ll need to integrate your Rocket with your district and schools.

Follow these instructions or try the guided walkthrough.

  • 1. Navigate to https://launch.lightspeedsystems.com and log in as a District administrator.
  • 2. Click the Admin Tools tile and select Schools or District, then select by name the organization you want to configure.

r3-00227

  • 3. Under Lightspeed Integration, select Lightspeed Web Filter and enter the IP Address or FQHN that will connect directly to your Rocket over port 80.
  • 4. Click Refresh Tiers to query your Rocket’s authentication sources, then select the authentication sources to use for Educators (staff), Students, and Web Zones.
    • Enabling Web Zones and providing an authentication source here will allow your teachers to create and manage Web Zones for groups they own.
    • To enable Mobile Manager for staff in the Lightspeed Dashboard, select (check) the Mobile Device Management.
  • 5. Click Save to apply your settings.

 

The final configuration step in the Launch dashboard is to select your SIS Integration type.

  • Navigate to https://launch.lightspeedsystems.com and log in as a District administrator.
  • Click the Admin Tools tile and select SIS.
  • On the SIS page, open the Your SIS Integration dropdown list and select one of the following options: Lightspeed CSV, Clever, SIMS, Google Classroom, School Manager Passport.
  • Once SIMS is selected you will need to select your default school. You must select a school so that we know which school to add the users to.
  • No SIS – if you do not have a Lightspeed Rocket appliance or external web server to host the import files, choose this option. Then click Actions and select Import Users to upload your users.csv file.

 

Once you’ve created properly formatted files, made them accessible, and configured Launch to know where to find them, syncing data is hands off.

The first time out though, you should sync immediately to ensure there are no errors in your data files. Let’s review that now.

Sync Now

Scheduled CSV imports begin automatically around 6pm Pacific Time every day (1am GMT), and may take several hours to complete, depending on the number of records you are importing, and the number of other districts in the import queue. Further, the scheduled import runs only if there is a change to a .csv file since the last import. If there are no changes to the file, then the process will not run.

You can start a CSV import of users.csv, groups.csv, or memberships.csv before or after the scheduled import task, from the Admin Tools / SIS / Actions menu in Launch.

  • 1. Place the updated csv file(s) in your Lightspeed Rocket’s Network Share/sis_import folder or the Custom URL address on your web server.
  • 2. Navigate to https://launch.lightspeedsystems.com and log in as a District administrator.
  • 3. Click the Admin Tools tile and select SIS.
  • 4. Click the Actions button and select Validate SIS Files first, so you can verify that the csv import file is correctly formatted.

r3-00230

  • 5. Click Actions and then SIS Error Logs to check for any errors in the files and make corrections to the files as needed, upload any updated files and try validating again.
  • 6. Once all errors are resolved in the files, click the Actions button and select Sync Now to begin importing your updated CSV files from your Rocket appliance. Notice the information message indicating that the import is in progress.

r3-00231

  • 7. Check the SIS Error Logs again to ensure all went according to plan.

Note: If you are unable to export from a SIS, or you are having trouble exporting information to match the required structure, please contact your SIS vendor or administrator.

If your CSV imports are not working as expected, double check they’ve been formatted and imported properly using the information below.

CSV Specifications

Here’s the fine print again.

  • Plain text CSV files
  • Encoded as “UTF-8 without BOM”
  • Header row in the users-v2.csv output file; no header row in users.csv
  • One line per record
  • Each field separated by commas
  • All fields must be declared, even optional fields you choose not to populate, except the last field. There must be only one comma at the end of each row.
  • No commas or numerals in first or last name fields. If necessary, substitute Roman numerals, e.g., John Doe 3rd should be changed to John Doe III, 4th grade should be 4.
  • Before importing, you can open the file in a text editor such as Notepad to verify that the correct fields are present and in the correct order.
  • File names must be all lower case and named: users.csv or users-v2.csv, groups.csv or groups-v2.csv, and memberships.csv or memberships-v2.csv. Otherwise you will get a “404 not found” error.

Tips from the Lightspeed Pros

Here are tips straight from the Lightspeed Systems Support Team.

  • Make sure each school referenced in your files uses a school SIS ID that matches the school’s SIS ID in Launch.
  • If uploading to Rocket, make sure all three files are present to avoid an error that Rocket is unreachable.
  • Ensure files are in the correct folder on your Rocket or web server.
  • Check for correct local permissions: Local Everyone with Read and Read & Write.
  • Test by importing a sample file with just a few rows at first, then look at the SIS Error logs (described below) to find and correct any formatting errors in the CSV file.
  • Review for common users.csv errors
    -grade must be a numerical value, e.g., 4 NOT 4th and NOT fourth.
    -column J, Authentication, cannot be left blank
    -to edit a user, change one field at a time: SIS ID, username, email. This is where we get email or username “already in use” error a lot.
  • Review for common groups.csv and groups-v2.csv errors
    -group owner must exist in your users.csv file or already existing in the Launch dashboard
    -if using groups.csv, column E is no longer needed for the current product mix but is required to import. Recommendation: use 0 for Public.
  • Review for common memberships.csv errors
    -school ID must match SIS ID given in School profile under Launch > Admin Tools > Schools.

SIS Error Logs

Once you’ve imported the files, you can check the SIS Error Logs.

  • 1. Navigate to https://launch.lightspeedsystems.com and log in as a District administrator.
  • 2. Click the Admin Tools tile and select SIS.
  • 3. Click the Actions button and select SIS Error Logs.

r3-00230

View Error Logs for District

This will give you an indicator of the overall errors with the import and how many.

View Error Logs for Schools

To view error logs for a specific school within your district, click to open the Schools dropdown list, then select the school to view. The list will be populated automatically if there are error messages associated with the school.

r3-00241

Observe that you can click column headers to sort by column. If part of a column is hidden, you can drag the dividing line in the column header to expand the column. If the report consists of multiple pages, click the < and > buttons in the top right area of the page to go forward and back.

Search Error Logs

You can search the error logs for a specific string, to help you locate errors of a specific type, for example users, groups, or memberships. Type your query in the search box in the top area of the page, and the results will be populated automatically.

r3-00242