2SQL - Demo for Google Password Changes

In this demo, I show how to use 2SQL to reset all of my student accounts to an initial password that they can figure out.

Here is the final Query

/* Reset Google Passwords */

/*

Create a GAM script to reset all student passwords to fl.6digitDOB

first initial - last initial - DOT - 6 digit DOB

Sample GAM Script is: GAM Update User 11110011@student.district.edu Password "ab.010216" Changepassword On

*/

Select

GoogleExport.primaryEmail

, SisExport.GivenName

, SisExport.SurName

, SisExport.BirthDate

, lower(substr(SisExport.GivenName,1,1)) AS 'f Init'

, lower(substr(SisExport.SurName,1,1)) AS 'l Init'

, '.' AS 'Dot'

, substr( Replace(SisExport.BirthDate,'/','') ,1,4) AS 'MoDay'

, substr( Replace(SisExport.BirthDate,'/','') ,7,2) AS 'Year'

, 'GAM Update User ' || GoogleExport.primaryEmail || ' Password "' || lower(substr(SisExport.GivenName,1,1)) || '' || lower(substr(SisExport.SurName,1,1)) || '.' || substr( Replace(SisExport.BirthDate,'/','') ,1,4) || '' || substr( Replace(SisExport.BirthDate,'/','') ,7,2) || '" Changepassword On' AS 'GAM Script'

From GoogleExport

Left Join SisExport on GoogleExport.primaryEmail = SisExport.SisId || '@student.district.edu'

Note that the sample data for this is included with the download.

Leave a comment