Moodle, SIMS and Windows

[alan-d]

I'm looking to pick the brains of anyone running a setup like ours. Currently we have moodle 1.9 running from Apache, php and MySQL on a Windows 2003 server.

Moodle user accounts are created and authenticated via LDAP, courses are created and populated manually.

We now want to move a stage further and pull user information, courses, classes and timetables from SIMs into moodle automatically. Possibly even append the sims calendar to the moodle one.

During my search on various forums I see it is possible to some degree, on *nix systems but I've yet to find anywhere running the same setup as us.

Any help or pointers would be greatly appreciated

[penfold_99]

Hi,

we current have SIMS, Active directory and Moodle integrated.

Here al little bit about how they are integrated.

SIMS -> AD - we have a script in place that will auto create a student account over night after they have been placed on roll. We currently working on a way to allow heads of year to disable user account through SIMS.net.

AD -> Moodle - we currently have moodle authenticate against active directory.

Moddle <-> SIMS - we have moodle pulling student course information from SIMS.net. We have just created a plugin for moodle to display the students time table.

[CyberNerd]

So first you need to tie up your SIMS with the active directory.
I suggest you populate an ldap field in AD with the users SIMS UPN. edit: sorry UPN
We map the 'employeeID' field.
Once you've done that you then have a reference point, so that moodle/AD and SIMS can all talk together.

You'll need to setup a 'view' of the MSSQL database on the sims server. the view needs to contain the UPN, and course details.

This is our SIMS view

USE [sims]

GO
/****** Object:  View [dbo].[mdl_enrolments]    Script Date: 12/05/2007 17:16:34 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[mdl_enrolments]
AS
SELECT DISTINCT class_name AS course, staff_initials AS person, 'editingteacher' AS role
FROM         sims.curr_via_classperiods AS p
UNION
SELECT DISTINCT p.class_name AS course, m.unique_pupil_no AS person, 'student' AS role
FROM         sims.curr_via_classperiods AS p INNER JOIN
                      sims.stud_via_group_members AS m ON m.base_group_id = p.base_group_id

Once that is setup you can then integrate the courses in moodle:



Maybe I can help penfold write some documentation

[penfold_99]

I will start to document the whole process from end to end.

With our script the is a prerequisite on the structure of AD regarding year groups etc.

[alan-d]

Our user names are year of entry- Form Letter - last 4 of UPN. (eg 09A1234)

Do your login names just use firstname/surname or do you have a different method?

[penfold_99]

Our user names are year of entry- Form Letter - last 4 of UPN. (eg 09A1234)

Do your login names just use firstname/surname or do you have a different method?

our script creates usernames

[Intake Year][Inital Firstname][Middle Inital][Surname]

the OU structure needs to be

Student [this can be named anything but a base OU]
-- Year 00 [These need to be in the format Year XX]
-- Year 01
-- Year 02
etc

what happen if your students move form?
if their letter is part of the username?

do you store the UPN in the employee id in active directory?

[klawd]

So first you need to tie up your SIMS with the active directory.
I suggest you populate an ldap field in AD with the users SIMS UPN. edit: sorry UPN
We map the 'employeeID' field.
Once you've done that you then have a reference point, so that moodle/AD and SIMS can all talk together.

You'll need to setup a 'view' of the MSSQL database on the sims server. the view needs to contain the UPN, and course details.

This is our SIMS view

USE [sims]

GO
/****** Object:  View [dbo].[mdl_enrolments]    Script Date: 12/05/2007 17:16:34 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[mdl_enrolments]
AS
SELECT DISTINCT class_name AS course, staff_initials AS person, 'editingteacher' AS role
FROM         sims.curr_via_classperiods AS p
UNION
SELECT DISTINCT p.class_name AS course, m.unique_pupil_no AS person, 'student' AS role
FROM         sims.curr_via_classperiods AS p INNER JOIN
                      sims.stud_via_group_members AS m ON m.base_group_id = p.base_group_id

Once that is setup you can then integrate the courses in moodle:



Maybe I can help penfold write some documentation

Out of interest, Does this violate SIMS EULA by accessing the tables directly?
I assume it does since they are very touchy about this. Not criticizing your approach, I am wondering if you have suffered any backlash from doing it this way yet (legal threats/suspension of support etc)


Can I ask if SIMS support for your school is supported by LA or directly by Capita?

I'm curious as we get our support for SIMS via our LA and they are close to hopeless when discussing this kind of thing.

[apeo]

As i understand it, capita will not allow you to write to the sims database but they shouldnt have an issue with viewing it. I cant see any reason why they would disallow reading the sims database directly but capita confirmation on this would be good.

[CyberNerd]

Out of interest, Does this violate SIMS EULA by accessing the tables directly?

We believe that are not violating the EULA because the data is ours and it is our SQL server. We are not accessing the table directly, we are accessing a view of the database.
We phoned Capita and they said it was fine, it is our data.
The API capita provide is only available on windows, so LAMP cannot use it.
Capita have been sued before (by bromcom) for stifling competition by preventing access.
We may write information back at some point, but haven't decided how we'll do this yet.

[CyberNerd]

I found the complaint that the OFT upheld against capita, it suggests to me that if Capita tried to prevent direct read and write access to SQL server it would be uncompetitive:

Title: Capita Business Services Limited and Bromcom Computers p

Complainant: Bromcom Computers plc

Complainee : Capita Business Services Limited (trading as 'Capita Education
Services')
Case closed: 2 May 2003

Issue: Whether the technical method of access and erms by which Capita Business Services Limited offered interface information to access data on its Microsoft SQL Server infringed the Competition Act 1998

Relevant provision: Chapter II of the Competition Act 1998

In March 2002, the Office of Fair Trading (OFT) opened an investigation into the terms by which Capita Business Services Limited (Capita) offered interface information to third parties to provide access to data on Capita's Microsoft SQL Server and the technical method adopted for such access.
Capita's Microsoft SQL Server forms a key part of its school information management system (branded as 'SIMS') and contains various data relating to staff and pupils (such as the grades and attendance of pupils), room allocation/timetables, as well as data necessary that schools are required to keep for legal and policy reasons.
School information management systems are installed in the majority of primary and secondary schools throughout the UK, of which Capita's SIMS is by far the most prevalent. Under Capita's business model, schools license a number of 'modules', which effectively sit on top of Capita's Microsoft SQL Server, enabling schools to extract and input data which they view on a fixed network of computers within the school. For example, with Capita's attendance module, teachers can enter lesson attendance marks in their classroom, and can see the day's registration marks and previous lesson attendance, on screen by accessing attendance related data on Capita's Microsoft SQL Server. Schools using SIMS can also purchase a number of third party products which may offer improved or complementary functionality to Capita's modules.
Bromcom Computers plc (Bromcom) developed a wireless attendance product which required interface information to access the attendance related data on Capita's Microsoft SQL Server. Until Capita's decision to migrate to Microsoft SQL Server, the Bromcom had the necessary interface information, enabling it to compete with Capita's own attendance module.
Given Capita's stable and high market share in the schools information management system sector generally, the presence of entry barriers such as high switching costs for schools, the generally risk averse and financially constrained nature of schools as purchasers and the low market shares of competitors, the OFT decided it had reasonable grounds for suspecting Capita was dominant. Given that Capita was able to control or prevent access to such data, it could potentially limit innovation and choice by excluding competition between Bromcom's products and Capita's own modules. Bromcom alleged that Capita was abusing its dominant position by constructively refusing to supply interface information to enable access to the attendance related data by offering an interface at an unreasonable price and on inadequate terms (namely that the technical method of access was inappropriate for Bromcom's product). Bromcom further alleged that Capita sought to tie the provision of such interface information with the supply of an interface written and charged for by Capita. Bromcom predominantly focussed on interface information which accessed attendance related data on the Microsoft SQL Server. Although Bromcom had also sought relief in relation to interface information accessing such data on Capita's Microsoft SQL Server as the Complainant 'may reasonably require', Bromcom only subsequently confirmed this by widening its complaint to cover interface information which accessed the entire range of data on Capita's Microsoft SQL Server at a reasonable price and on adequate terms.
The OFT considered the appropriateness of the technical method of accessing Capita's Microsoft SQL Server used by Capita and the nature of the interface offered to Bromcom to access Capita's Microsoft SQL Server.
The OFT, assisted by an independent IT Expert, initially concluded that, whilst Capita used an appropriate technical method of access, there were competition concerns about making the availability of the necessary interface information to use this method of access conditional on the supply of an interface written and charged for by Capita.
When informed of the OFT's initial conclusions, Capita offered voluntary assurances covering the disclosure to Bromcom of interface information to enable access to data on Capita's Microsoft SQL Server, subject to the agreement of commercial terms2.
Following receipt of the Voluntary Assurances, the Office believes its competition concerns have been allayed and has subsequently closed its investigation.

OFT's action: Case closed
Case officer: Edward.Anderson@oft.gsi.gov.uk
Case reference: CP/01476-01

http://www.oft.gov.uk/shared_oft/ca9...sures/2003.pdf

[alan-d]

o

what happen if your students move form?
if their letter is part of the username?

do you store the UPN in the employee id in active directory?

It's rare for students to move form but if they do they keep the same username. This is one of the legacy policies we need to have another look at.

We currently don't populate AD directly from SIMs, we use a CSV file to create the accounts once we have the correct rolls.

Has anyone looked at or used the Salamander software ? Comments on this would be useful.

Thanks for the input so far.

[dhicks]

The API capita provide is only available on windows, so LAMP cannot use it.

It can if it comes here :-) I wrote a basic web server that simply passes requests on to the SIMS CommandReporter tool to run queries and then return the XML produced. I've then got some utility scripts that do stuff on various client machines (e.g. update AD). I figure this way there's no need to argue with SIMS as to whether we're breaking their EULA or not.

--
David Hicks

[CyberNerd]

It can if it comes here :-) I wrote a basic web server that simply passes requests on to the SIMS CommandReporter tool to run queries and then return the XML produced. I've then got some utility scripts that do stuff on various client machines (e.g. update AD). I figure this way there's no need to argue with SIMS as to whether we're breaking their EULA or not.

That sounds pretty cool, are you going to publish any of it?
There isn't an argument about EULA, because we asked capita and they said it was fine, our LEA didn't have a problem either and it seems nor do the OFT.

[penfold_99]

Has anyone looked at or used the Salamander software ? Comments on this would be useful.

I looked at the information about salamander but i think this is a scheduled update process with an ldap link.

Our process is real time.

[apeo]

penfold, what language are you scripts written in and can we have a look at scripts & the plugin you've created?