CyberNerd Posted June 29, 2006 Posted June 29, 2006 ' 2006-06-27 ' This script copies user details from MSSQL to Active directory for STUDENTS only. ' It has been written for exporting data from the CMIS MSSQL structure into ' AD that is specific to our site. It will require modification for your ' site and comes without warranty. ' ' This program is free software; you can redistribute it and/or modify ' it under the terms of the GNU General Public License as published by ' the Free Software Foundation; either version 2 of the License, or ' (at your option) any later version. ' ' This program is distributed in the hope that it will be useful, ' but WITHOUT ANY WARRANTY; without even the implied warranty of ' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the ' GNU General Public License for more details. ' Perform some connection setup. Set objADConnection = CreateObject("ADODB.Connection") objADConnection.Open "Provider=ADsDSOObject;" Set objADCommand = CreateObject("ADODB.Command") objADCommand.ActiveConnection = objADConnection Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Const adBookMarkFirst = 1 Set objCMISConnection = CreateObject("ADODB.Connection") Set objCMISRecordset = CreateObject("ADODB.Recordset") objCMISConnection.Open "DSN=ADSYNC;", "adsync", "password" objCMISRecordset.CursorLocation = adUseClient ' Read in a name or username (or part thereof) that is know to CMIS. WScript.StdOut.Write "Enter a name or username that is known to CMIS: " qUser = WScript.StdIn.ReadLine WScript.Echo If Len(qUser) = 0 Then WScript.Quit End If ' Query the CMIS ADSync view. sql = "SELECT * FROM ADSync " & _ " WHERE (displayName LIKE '%" & qUser & "%'" & _ " OR sAMAccountName LIKE '%" & qUser & "%') " & _ " AND leftSchool = 'N' "' & _ ' " AND employeeType = 'STUDENT'" ' Query CMIS... objCMISRecordset.Open sql, objCMISConnection, adOpenStatic, adLockOptimistic ' If more than one record was returned, present the user with a list to choose from. If objCMISRecordset.RecordCount > 1 Then idNum = 1 While Not objCMISRecordset.EOF WScript.Echo " " & idNum & ". " & objCMISRecordSet("displayName") & "; " & objCMISRecordset("sAMAccountName") & "; " & objCMISRecordset("employeeId") & "; " & objCMISRecordSet("department") & "; " & objCMISRecordset("dateOfBirth") objCMISRecordset.MoveNext idNum = idNum + 1 Wend WScript.Echo WScript.StdOut.Write "Choose user (1-" & (idNum-1) & ") or 'q' to quit: " row = WScript.StdIn.ReadLine If Left(row, 1) = "q" Then WScript.Quit End If row = CInt(row) If row > (idNum - 1) Or row < 1 Then WScript.Quit ' Move to the selected record (counting from the first record). objCMISRecordSet.Move (row - 1), adBookMarkFirst WScript.Echo End If If Not objCMISRecordset.EOF Then dateOfBirth = Left(Trim(objCMISRecordset("dateOfBirth")), 2) & Mid(Trim(objCMISRecordset("dateOfBirth")), 4, 2) & Right(Trim(objCMISRecordset("dateOfBirth")), 2) employeeId = Trim(objCMISRecordset("employeeId")) employeeType = Trim(objCMISRecordset("employeeType")) lastName = Trim(objCMISRecordset("sn")) firstName = Trim(objCMISRecordset("givenName")) middleName = Trim(objCMISRecordset("middleName")) initials = Left(Trim(objCMISRecordset("middleName")), 1) calledName = Trim(objCMISRecordset("calledName")) If Len(calledName) = 0 Then displayName = firstName & " " & lastName Else displayName = calledName & " " & lastName End If department = Trim(objCMISRecordset("department")) studentId = Trim(objCMISRecordSet("secondId")) If Len(objCMISRecordSet("sAMAccountName")) > 0 Then adUsername = objCMISRecordSet("sAMAccountName") Else adUsername = "" End If yearOfEntry = "" if objCMISRecordset("yearGroup") = "7" then yearOfEntry = "05" if objCMISRecordset("yearGroup") = "8" then yearOfEntry = "04" if objCMISRecordset("yearGroup") = "9" then yearOfEntry = "03" if objCMISRecordset("yearGroup") = "10" then yearOfEntry = "02" if objCMISRecordset("yearGroup") = "11" then yearOfEntry = "01" if objCMISRecordset("yearGroup") = "12" then yearOfEntry = "00" if objCMISRecordset("yearGroup") = "13" then yearOfEntry = "99" If employeeType = "STUDENT" Then description = "Student " & yearOfEntry & " / " & dateOfBirth Else description = "Staff_mail" End If WScript.Echo "Found '" & objCMISRecordSet("displayName") & "' (ID: " & employeeId & "; ADUsername: " & adUsername & ")" Dim objUser QueryAD employeeId, objUser WScript.Echo "" WScript.Echo "+==========================================================+" WScript.Echo "| Field | CMIS/Source Value | AD Value |" WScript.Echo "+==========================================================+" WScript.Echo "| AD Username | " & pad(adUsername, 20) & "< " & pad(getADValue(objUser, "sAMAccountName"), 20) & "|" WScript.Echo "| First Name | " & pad(firstName, 20) & "> " & pad(getADValue(objUser, "givenName"), 20) & "|" WScript.Echo "| Middle Name | " & pad(middleName, 20) & "> " & pad(GetADValue(objUser, "middleName"), 20) & "|" WScript.Echo "| Initials | " & pad(initials, 20) & "> " & pad(GetADValue(objUser, "initials"), 20) & "|" WScript.Echo "| Last Name | " & pad(lastName, 20) & "> " & pad(GetADValue(objUser, "sn"), 20) & "|" WScript.Echo "| Display Name | " & pad(displayName, 20) & "> " & pad(GetADValue(objUser, "displayName"), 20) & "|" WScript.Echo "| Description | " & pad(description, 20) & "> " & pad(GetADValue(objUser, "description"), 20) & "|" WScript.Echo "| Department | " & pad(department, 20) & "> " & pad(GetADValue(objUser, "department"), 20) & "|" WScript.Echo "| Employee ID | " & pad(employeeId, 20) & "> " & pad(GetADValue(objUser, "employeeId"), 20) & "|" WScript.Echo "+==========================================================+" WScript.Echo "" WScript.StdOut.Write "Copy user data from CMIS/Source to Active Directory? (y/n) " yesNo = WScript.StdIn.Read(1) If yesNo = "Y" Or yesNo = "y" Then If Len(firstName) > 0 Then objUser.Put "givenName", firstName If Len(middleName) > 0 Then objUser.Put "middleName", middleName If Len(initials) > 0 Then objUser.Put "initials", initials If Len(lastName) > 0 Then objUser.Put "sn", lastName If Len(displayName) > 0 Then objUser.Put "displayName", displayName If Len(description) > 0 Then objUser.Put "description", description If Len(department) > 0 Then objUser.Put "department", department objUser.SetInfo WScript.Echo WScript.Echo "Active Directory account updated!" If employeeType = "STUDENT" Then If Len(adUsername) > 0 Then updateSQL = "UPDATE STUD_ADMIN.NSTUPERSONAL SET ADUsername = '" & Replace(objUser.Get("sAMAccountName"), "'", "''") & "' WHERE StudentId = '" & studentId & "'" objCMISConnection.Execute updateSQL WScript.Echo "Updated CMIS ADUsername field!" Else updateSQL = "UPDATE STUD_ADMIN.NSTUPERSONAL SET ADUsername = '" & Replace(objUser.Get("sAMAccountName"), "'", "''") & "', ADCreationDate = GETDATE() WHERE StudentId = '" & studentId & "'" objCMISConnection.Execute updateSQL WScript.Echo "Updated CMIS ADUsername and ADCreationDate fields!" End If Else If Len(adUsername) > 0 Then updateSQL = "UPDATE STUD_ADMIN.LECTDETS SET ADUsername = '" & Replace(objUser.Get("sAMAccountName"), "'", "''") & "' WHERE LecturerId = '" & studentId & "'" objCMISConnection.Execute updateSQL WScript.Echo "Updated CMIS ADUsername field!" Else updateSQL = "UPDATE STUD_ADMIN.LECTDETS SET ADUsername = '" & Replace(objUser.Get("sAMAccountName"), "'", "''") & "', ADCreationDate = GETDATE() WHERE LecturerId = '" & studentId & "'" objCMISConnection.Execute updateSQL WScript.Echo "Updated CMIS ADUsername and ADCreationDate fields!" End If End If End If Set objUser = Nothing End If objCMISRecordset.Close objCMISConnection.Close objADConnection.Close Set objADCommand = Nothing Set objADConnection = Nothing ' Query AD for the specified employeeId, putting the AD user object in the supplied variable. Sub QueryAD(employeeId, objUser) WScript.Echo "Querying Active Directory for '" & employeeId & "'" objADCommand.CommandText = ";" & _ "(&(employeeId=" & employeeId & "));" & _ "ADsPath,distinguishedName;subtree" Set objADRecordSet = objADCommand.Execute If Not objADRecordset.EOF Then strADsPath = objADRecordset.Fields("ADsPath") Set objUser = GetObject(strADsPath) Else WScript.Echo "Could not find AD user with employeeID '" & employeeId & "'" End If objADRecordSet.Close Set objADRecordSet = Nothing End Sub ' Pad a string with spaces to the specified length. Function pad(strToPad, length) tmp = strToPad If Len(tmp) > length Then pad = Left(tmp, length) Else Do While Len(tmp) < length tmp = tmp & " " Loop pad = tmp End If End Function ' Get an value from the AD user object, ignoring errors. Function GetADValue(objUser, strValue) On Error Resume Next GetADValue = objUser.Get(strValue) If Len(GetADValue) = 0 Then GetADValue = "" End If End Function Not my code. will need modification for your site. Please use, distribute and update any fixes - works for us though.
CyberNerd Posted June 29, 2006 Author Posted June 29, 2006 I'm not too sure about SIMS but assuming its a MSSQL database, you'd just need to know the table structure.
MkII Posted June 29, 2006 Posted June 29, 2006 Thanks CN - just saw the other thread. This would be so useful for schools.
Geoff Posted June 29, 2006 Posted June 29, 2006 It's against the SIMs EULA though. You can't access the data directly. You can only access the data via the SIMs API. You can only use the SIMs API if you have a developer license from Capita (read expensive).
CyberNerd Posted June 29, 2006 Author Posted June 29, 2006 It's against the SIMs EULA though. You can't access the data directly. You can only access the data via the SIMs API. You can only use the SIMs API if you have a developer license from Capita (read expensive). I can see the headlines now: "MultiMillion Pound IT firm sells hard-up schools shoddy software then sues them for improving it" -I never had a future in media (or law) -I've disributed this as proof of concept, nothing more.
Geoff Posted June 29, 2006 Posted June 29, 2006 They wouldn't sue the school directly. It's bad publicity and there's no money in it. They'd sue the LEA instead.
CyberNerd Posted June 29, 2006 Author Posted June 29, 2006 Thats fine, we've not listened to the LEA in long time, they use SIMS ! it would more be difficult to sue for something they dont use
sahmeepee Posted June 30, 2006 Posted June 30, 2006 It's against the SIMs EULA though. You can't access the data directly. You can only access the data via the SIMs API. You can only use the SIMs API if you have a developer license from Capita (read expensive). Not quite right Geoff! Although you can't read from the database directly, you can read from the SIMS database via their reporting SDK gratis. They have fairly good reasons for this: (From Capita's Dan Clark on the SIMS forums) We do not support direct access to the SIMS SQL Database for a variety of reasons - the 3 main ones being Validation, Security and Change. In the case of validation, there is a huge set of business rules that get applied to the data both before it is read and written - to manipulate the type and format of the data returned; therefore meaning that direct queries will not always return the correct data (just identifying students on roll is not as easy as it actually seems - fields in the SIMS database are not actually "as they seem"). In security terms, you actually have to either change the database or server configuration to add physical SQL Users, or you have to use a dbo equivillent user to access the database - both of which result in security issues.Lastly, the Database Schema constantly changes and therefore there will be no way to keep up with the changes. So whats the solution? Actually - there is a 3rd party SDK in SIMS that allows you to extract the data , by automatically running reports and returning data via XML or CSV. Email me for the documentation, or, if this does not suite, we can look at other solutions. I've got the docs for this recently from Dan Clark (the tools are already in your SIMS install! The executables start with "command"). It's not perfect, but at least a csv/xml output could be imported into a database without human intervention given a little effort. The bad news - because it works via the reporting mechanism it's read-only. For the purposes of this thread that shouldn't be a problem. I'm somewhat surprised they can't find room in their 250MB SIMS install for this 50KB SDK manual. I will check with Dan Clark to see if I can post the file on here. You might also like to consider this post from Phil Neal on the moodle forums on Monday 26th June: Capita got together with Microsoft, RM and Granada over 2 years ago and proposed an open standard to Becta/DfES for moving data from an MIS to a VLE. The link is based on IMS. Becta has not taken up this proposal. We decided that in spite of not getting traction we should progress the proposal and the link has now been built into SIMS. It will be included in our software in the autumn. The Moodle community can develop a link if they would like to. Details will be made available on our web site. Phil Neal Director SIMS Apologies for the long post, but I think some of these developments could really help people in here. (BTW Thanks to Karl for finding the 2nd quote )
TechMonkey Posted July 12, 2006 Posted July 12, 2006 Did you get any reply about post the SDK documentation? Just been looking at the very post you quoted, which lead me here from google, and can't find the SDK in our SIMS install. Been looking at doing this for an age!
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now