Skip to main content

Automate Securly Guardians upload from SIMS

Securly has recently allowed automated uploads of guardian contact details. This requires getting your guardians details into a csv file in a specific format. This is easy to do manually - but takes a little work to do automatically. This is the procedure that I use to extract the data from Capita SIMS and sync it to Securly on a daily basis. You can see the basic Securly guide here - but don't believe the bit about the columns being in any order - I've found they have to be in the order specified!

SIMS Report

The field I use are:
Note - you have to edit the names of the fields to the ones Securly want.
I run this on a daily schedule and output the result to a csv file on a virtual machine that I use for lots of automated tasks.
"C:\Program Files\SIMS\SIMS .net\commandreporter" /user:xxxxxxx /password:xxxxxxx /report:"securlyreport" /OUTPUT:\\Win10man\gam\parents.csv /QUIET

Modifying the output to meet the Securly requirements 

SIMS outputs csv files with double quotes around each entry. Securly cannot cope with this and you also need to columns in the right order. So after the report runs a PowerShell script runs to reorder the columns and remove the quotes:
$Path = "c:\gam\parents.csv"
(Import-CSV -Path $Path) | Select-Object -Property 'Parent First Name', 'Parent Last Name', 'Parent Email', 'Student First Name', 'Student Last Name', 'Student Email'| Export-CSV -Path $Path -notype
(gc C:\GAM\parents.csv) | % {$_ -replace '"', ""} | out-file C:\GAM\parents.csv -Fo -En ascii

Now we are ready to upload the file. To do this I use WinSCP. Securly provides credentials for their ftp upload server specific to you and the setup procedure is detailed here. WinSCP provides and easy scripting tool that will generate a script you can run as a scheduled task. Check in your Securly admin console that the upload has been successful.
That's it - no more manual uploads.


Popular posts from this blog

Delete a specific email using GAM

If a user send an inappropriate email to a loads of people or get stung by some sort of email exploit you can quickly delete the email from all of the recipients using a GAM command.
Step 1 - get the email header Go into Google Vault and search for the offending user or someone known to have got the message.
Click show details and grab the email ID. This will be a long string of characters followed by @mail.gmail.com
Step 2 - find out who has the email Go into Google Vault and find the original message sent by the offending user. Look at the details to see who got it. Copy the list and dump it into a spreadsheet. Clean up to just a list of emails with a column header 'mail'. Save as a csv file.
Step 3 - delete messages with GAM Put your CSV file in your GAM folder - this e.g. assumes its called mail.csv
Run:
gam csv mail.csv gam user ~mail delete messages query rfc822msgid:MESSAGEIDHERE doit

The alternative nuke option is:
gam all users delete messages query rfc822msgid:MESSAGEI…

My favorite GAM commands - well a few of them at least!

Where would be without GAM? Paying for expensive syncing tools or doing tedious manual tasks in the admin console. GAM can automate most things you might want to do in G Suite. So these are a few of my favourite commands - one I use either as part of a batch file - or just standalone. There are loads more - but these are ones that are used daily.
Classroom Create a spreadsheet of all your domains classes - gam print courses todrive

Create a spreadsheet of a teacher's classes: gam print courses teacher fred@mydomain.com todrive

Bulk create classes:
gam csv classes.csv gam create course alias ~alias name ~alias section ~subject teacher ~teacher status ACTIVE

where classes.csv is a list of classes you want to make.

Add teachers:
gam csv teachers.csv gam course ~alias add teacher ~teacher

Add students:
gam csv students.csv gam course ~alias add teacher ~student

Sync Students (in this example to a group - but could be an ou/csv file)
gam csv groups.csv gam course ~groupmail sync students g…

How to beat ChromeOS EOL and carry on getting updates

ChromeOS devices are great in loads of ways, but they have built-in obsolescence. Google will stop providing updates at a predetermined time according to the schedule you can find here. So the best you will achieve is 6.5 year if you buy the device on the launch day. In reality, it will be generally much less. It's something to watch as a good deal might not be such a good deal if the device only has two years left.

Once a device reaches its "due" date, you get a red pop up telling you its time to update every time you log in. The update section tells you there are no more updates. Now the device will work fine - for a while. You might get another 6 months use out of it before core services like Gmail stop working. However, if you are prepared to do a little work, you can install the OS of your choice onto the device and carry on using the device and not accept this. If you want ChromeOS, then you can install Cloudready from Neverware. I'll outline the basic steps be…