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

Edited October 21, 2008 by CyberNerd

[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: [email protected]

Case reference: CP/01476-01

 

http://www.oft.gov.uk/shared_oft/ca98_case_closures/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? Edited October 23, 2008 by apeo

[penfold_99]

penfold, what language are you scripts written in and can we have a look at em?

 

They are in vbscript, you can once i have attached a GPL licence to it.

I also need to remove one manual exception i had to put in.

 

is it possible to know the format of your username and how you ad OU are laid out? As the script is quick strict in the structure they it can work with.

[apeo]

I work with Alan so thats how our usernames are made up and as to our ad its:

 

Student

-

 

eg

 

Student

- 08

- 07

 

etc.

 

As its in vbscripts i should be able to make the required changes. Its the moodle plugin that im not sure about. Dont know enough of php or how moodle works.

[dhicks]

That sounds pretty cool, are you going to publish any of it?

 

Yes, it was actually that dodgy Python script that I posted a while back, the one that I could get to install as a Windows service but not uninstall - kind of a one-way installation choice. Still haven't figured that bit out...

 

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.

 

I figure if Capita don't have a proper API for writing back to SIMS then we'll concentrate on sorting out a better replacement.

 

--

David Hicks

[penfold_99]

I work with Alan so thats how our usernames are made up and as to our ad its:

 

Student

-

 

eg

 

Student

- 08

- 07

 

etc.

 

As its in vbscripts i should be able to make the required changes. Its the moodle plugin that im not sure about. Dont know enough of php or how moodle works.

 

Hi apeo,

 

What is the format of the username as so far the script will work without any changes to the logic

[apeo]

Username is made up of 3 elements, so for example if the student is this years year 7, in reg group A and adno is 3104 then it will be as follows:

 

08A3104

[penfold_99]

Username is made up of 3 elements, so for example if the student is this years year 7, in reg group A and adno is 3104 then it will be as follows:

 

08A3104

 

you have the same format as alan-d, this will be a simple change to implement.

 

Once i have finish removing our custom requirements i will edit a version for you, you can put it in reporting mode see if it could work.

[Tonyoung]

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:

 

 

CN - after reading this extract I came to a slightly different conclusion. That Capita can prevent direct read and write access to the SIMS database but have given a voluntary assurance to make the interfaces to allow access to be database to be made available under reasonable commercial terms. I think this is a significant difference ?

 

Also where does it say the complaint was upheld ? If it had been wouldn't Capita have been found guilty and fined ? Can't recall reading about that.

Edited October 24, 2008 by Tonyoung

[penfold_99]

CN - after reading this extract I came to a slightly different conclusion. That Capita can prevent direct read and write access to the SIMS database but have given a voluntary assurance to make the interfaces to allow access to be database to be made available under reasonable commercial terms. I think this is a significant difference ?

 

Also where does it say the complaint was upheld ? If it had been wouldn't Capita have been found guilty and fined ? Can't recall reading about that.

 

It would still be uncompetitive as capita forces a third party to use windows so it can interface with its API.

 

If the competing solution was based on linux, Capitas Api is useless.

 

If the project is Open Source, Capita's API costs are unreasonable in commercial terms as there are no funds avaliable to pay for it.

[CyberNerd]

CN - after reading this extract I came to a slightly different conclusion. That Capita can prevent direct read and write access to the SIMS database but have given a voluntary assurance to make the interfaces to allow access to be database to be made available under reasonable commercial terms. I think this is a significant difference ?

 

Also where does it say the complaint was upheld ? If it had been wouldn't Capita have been found guilty and fined ? Can't recall reading about that.

 

maybe you should re-read the last paragraph

 

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.

[mleighton]

My interpretation of the situation is that Capita cannot deny access to the data held, however their preferred method for third party users to access it is via SIMS reporting - generation of xml reports containg the data required and then subsequent processing of these data files. They provide an SDK which includes details on schduled batch programs.