When users should no longer have access to the system, we recommend that you Archive them to save space. Archived users do not count against your total number of users in CourseMill. The advantage to archiving rather than deleting is twofold: You can still pull transcripts and completions for archived users, and you can restore archived users.
Background on Archiving:
Student Data for CourseMill is kept in a variety of student tables. When a Student is archived, their student data including account info, demographics, organizations, and course completion data gets moved to the archive tables. This means the Student's data is preserved, but they can no longer log in until they are released from the archive. When a Student gets released from the archive, their student data gets moved back over to the corresponding student tables. There is currently no way to unarchive a student through CourseMill directly, so instead we have to do a Batch User Import with the appropriate data to change.
Process Overview:
In order to release a Student from the Archive, we need to confirm these 4 fields of information in the archivestudent table in the database:
OrgID, StudentID, FirstName, LastName.
First, we will use the Custom Reporting feature to build Custom Reports with SQL queries to confirm the user is in the archive and to get the info we need.
Then we will create a CSV file to use for importing the student data via the Bulk User Data import feature and the 'Bulk Restore Users' option. We will then check to confirm the user was released from the archive.
Full Instructions:
- Login as a global admin and go under the Reports sidebar menu and click on the General option.
- Click on the Add Custom Report option.
- Change the Category to Administrator, and the Type to Database Query, then in the Description text field type a title for your report. This report will essentially be your template to use to run SQL queries for the archive.
- Copy and paste the following SQL query into the Database Query textbox. Make sure to put a space at the end of each line:
SELECT OrgID, StudentID, FirstName, LastName FROM archivestudent
WHERE LastName = 'Student's Last Name'
Replace the 'Student's Last Name' with the last name of the requested Student. So, for our example this would be Targaryen. So that second line would read
WHERE LastName = 'Targaryen' - Click on Save to save the report, then find your newly created Custom Report in the list, or use the Title box in the top menu and click on Search to find your report.
- Click on the Run Report icon to the left of the Category of the report to run it.
- If you can find the matching record for the requested Student, proceed to Step 10.
- Otherwise, refresh the page to clear the report results and find your newly created report again. Right-click on the report and then click View/Edit Details.
- Repeat Steps 4 through 6 trying different combinations of searches to locate the requested Student.
Sometimes the Student's name has changed since the last time they were active with your organization, so you may need to do a wildcard search to find the Student. The easiest way to do this is to use the LIKE operator and the wildcard % percent sign. This indicates 'all characters before or after the sign' For instance:
WHERE LastName LIKE 'Targ%' -- this finds every record with a LastName that starts with 'Targ'
WHERE LastName LIKE '%ryen' -- this finds every record with a LastName that ends with 'ryen'
WHERE LastName LIKE 'Ta%en' -- this finds every record with a LastName that starts with 'Ta', has any number of characters in the middle, and ends with 'en'
You can also try searching by the FirstName field or EmailID field, and you can combine field searches. For instance:
WHERE LastName LIKE 'Targ%' AND EmailID LIKE 'atarg%' -- this finds every record that has a LastName that starts with 'Targ' AND has an E-mail address that starts with 'atarg'.
Once you are able to find the requested Student using the custom report: - If there are more results than just the student(s) you are looking for, you can change the WHERE statement to filter by that Student's StudentID.
In the example above, we used WHERE LastName LIKE 'Targ%' and ended up getting results for multiple Students:
Since, Aegon Targaryen is our requested Student, we take note of his StudentID and can narrow down our report to show only his information by changing our WHERE clause to: WHERE StudentID = 'ATarg1' This narrows our results to only show our target Student. - Once you have the report results showing only the student(s) want to release from the archive, click on the Download Generated Report button at the top nav bar. Depending on your browser settings, the CSV file it creates will be automatically saved or you will be prompted to choose where to save. Make sure you note the file location.
- Go under the Users sidebar menu and click on Manage Users.
- Click on Batch User Data at the top menu
- Under the Batch Operation dropdown, select Bulk Restore Users. Click on Choose File and find your CSV file that you just created when you downloaded your report results.
- Click on the Process button and confirm that there are no errors and the number of records restored matches what you have in your import file.
If there are errors, you can click on the error report link to download a CSV file showing you what the errors are. Open your CSV file in a plain text editor and correct the errors.
The CSV file needs to have the first line with each header separated by a comma, and then each successive line should have data for each of those headers also separated by a comma.
NOTE: DO NOT open the CSV file in Excel. Doing so may cause Excel to incorrectly apply automatic formatting and changes to fields with dates, symbols, leading zeroes, etc.
Once the errors are corrected, go back to step 12.
If there are no errors, proceed to the next steps: - Under the Process Data dropdown, select Process Data. Click on Choose File and find your CSV file with your report results again. Click the Process button and confirm the records were successfully restored.
- Close the Batch User Data window and then check the Include Inactive checkbox at the top nav bar. In the User ID text field, type in the StudentID of the restored student and confirm they now appear in the user list.
If you are not able to find the Student, make sure the OrgID in the search box matches the one associated with the Student in your CSV file.