Using IC KY Data Extract Utility and VB Scripts to automate student account management.

1. Using IC KY Data Extract Utility and VB Scripts to automate student account management. Jimmy Pack Chief Information Officer Franklin County Public Schools Time: 1:15-2:15 Session #4Date: 3/8/2012Session Room: Beckham

2. Resources • http://franklin.kyschools.us/KYSTE12.aspx • http://franklin.kyschools.us • http://blogs.fcpschools.us/jimmypack • http://twitter.com/jimmypack • jimmy.pack@franklin.kyschools.us • Twitter - #kyste2012

3. Overview • Borrow code from someone • Cory Goode, Barren County Schools • Setup Data Extract in IC • Most already using this for Food Services, Transportation or Library management software • Determine what to create or modify • Modify code • Debug – Debug – Debug • VbsEdit –trial version, never expires, is editor

4. Key Points - 1 • Using extensionattribute4 in AD properties for user unique identifier (key) • If starting today with this, all existing users must have this in their attributes, otherwise you get all new userids created. • A script has been created to just update that field for existing users • Currently using for students only

5. Key Points - 2 • Student userids = fmlname • John L Doe = jldoe • Johnny L Doe = jldoe1 • Default passwords can be unique for different OUs • Email can be enabled or disabled • Accounts can be enabled or disabled • Inactive students in IC can have accounts disabled automatically

6. Key Points - 3 • Can specify grade levels to update • Can update only, not create new, or vice-versa • Logfileis written for reporting purposes • Approximately 20 minutes to run for our approximately 4,000 student accounts, for updates and new students during the year • A 1 second pause is required at times to allow proper credentials and creation of items

7. Output file from IC • 0 = Student ID • 1 = Social Security # • 2 = State ID • 3 = Status • 4 = School # • 5 = District ID • 6 = NHR • 7 = ? • 8 = ? • 9 = Last Name • 10 = First Name • 11 = Middle Name • 12 = Birthday • 13 = Sex • 14 =Grade

8. Output file from IC Too many fields for this script, just using fields 0-14. Fields separated by ~ The only fields referenced in the script are: Student ID, Status, School #, Last, First and Middle names and Grade File format is: “StudentID””~””SSN” ~””StateID” ~””Status” ~””School#” ~””DistrictID” ~””NHR?” ~””?” ~””?” ~””Lastname” ~””Firstname” ~””Middlename” ~””Birthday” ~””Sex” ~”Grade”… (15 other fields) Sample format to use with testing can be shorter: "9999999999"~""~"9999999999"~"1"~"040"~"181"~“NHR"~""~""~“Mouse"~“Mickey"~“A"~""~""~"12" "8888888888"~""~"8888888888"~"1"~"040"~"181"~""~""~""~“Mouse"~“Minnie"~“A"~""~""~"11"

9. Logfile ICAD - Log 2/13/2012 1:46:51 PM ------------------------------------------------------------------------------------------------------------- Action,Studentid,Username,Fullname,Comments Updated school group, 1234567890, DWSmith, SmithDavidW, LDAP://cn=EM Students,OU=_Groups,OU=Students,DC=franklin,DC=ketsds,DC=net Existing account already disabled, 1234567890, SSmith, Smith-Sarahi, Withdrawn in IC Updated school group, 1234567891, ANSmith, Smith- Amber N, LDAP://cn=FC Students,OU=_Groups,OU=Students,DC=franklin,DC=ketsds,DC=net Updated school group, 1234567892, JLSmith, Smith- Jamyah L, LDAP://cn=EM Students,OU=_Groups,OU=Students,DC=franklin,DC=ketsds,DC=net Updated school group, 1234567893, DDSmith, Smith- Daimon D, LDAP://cn=ED Students,OU=_Groups,OU=Students,DC=franklin,DC=ketsds,DC=net Updated graduation year, 1234567894, LBSmith, Smith- Laura B, 2012 Updated home folder, 1234567894, LBSmith, Smith- Laura B, \\e181000s1\students\2012\lbsmith Finished

10. Script Overview • Read in IC export file line by line • Break apart fields and store in variables • Look for existing userid (match on extensionattribute4) • If no match, create new user • If match, look for changes that need updating

11. Adding New User • Check for existing userid • Check student and then staff userids • If exists, add digit to end until no match found • Create userid and set • Names • Password • Home directory and actual folder with permissions • Graduation year in Department • Extensionattribute4 • Enable account and email if desired • Add to correct school groups and internet access groups

12. Updating Existing User • Look for updates to • Grade • School • School group memberships • Home directory and Home drive • Account active status

13. Common Issues • Errors with manual account creations • Forget to add extensionattribute4 • Copying account and not updating extensionattribute4 • Typo for extensionattribute4 • Students repeating a grade and thus changing graduation year, home folder location • Inaccurate IC entry • Impatient STCs if script is not run periodically

14. Customization • School OU and group data • Sub SetSchoolVars • LDAP values should reflect your environment • LDAP://OU=Students,DC=franklin • Logfilelocation • IC text file location • student_username customization • Default password – student_pw • Home directory and home drive

15. Other Scripts • Fix_student_groups – used to reset school OU groups once userid has changed OUs • icad_fix_fullname2.0 – working on this to fix what is displayed in ADUC (instead of userid) • icad_update_ssids – updates existing userids with extensionattribute4 • Not perfect but will help

16. Questions?