[Development] An Open Source Parent Gateway

[pcstru]

Just because it might be useful to someone :

 

I've been using Intelligent Converters MsSQL to MySQL product to extract our CMIS data and dump it into a MySQL database which I can then use in whatever way I want without any danger of causing abnormal loading the MsSQL server. I do the extract overnight but I could pull data more regularly if I wanted (after registration etc). Seems to be reasonably efficient and it's cheap.

 

Don't see why that wouldn't work with SIMS although depending on what you are doing, it may not be any advantage over the command line reporter. Personally I like to have full and unrestricted access to the data from SQL - but that's not for everyone.

[CyberNerd]

sorry maybe i worded this thread wrong but im not looking for full vle. just parental access to simple things like a report on their child or possibly a small attendance summary.

 

I was thinking along the lines that the moodle platform is a good php environment; by writing a module you would benefit from all the things moodle has to offer, security updates, passwords, single sign on, email, calendar which you wouldn't need to re-implement. You don't need to use the fully functional VLE aspect if you didn't want to, but it would help with adoption and others could more easily participate with the code.

 

 

Just because it might be useful to someone :

 

I've been using Intelligent Converters MsSQL to MySQL product to extract our CMIS data and dump it into a MySQL database which I can then use in whatever way I want without any danger of causing abnormal loading the MsSQL server. I do the extract overnight but I could pull data more regularly if I wanted (after registration etc). Seems to be reasonably efficient and it's cheap.

 

Don't see why that wouldn't work with SIMS although depending on what you are doing, it may not be any advantage over the command line reporter. Personally I like to have full and unrestricted access to the data from SQL - but that's not for everyone.

 

This works up to the point that Capita/Serco decide to redesign their database, or you want to extend things by writing back to the MIS (online assessment for example).

SIF/groupcall is probably the way forward.

[pcstru]

This works up to the point that Capita/Serco decide to redesign their database,

Something they seem to do every :

 

a) month

b) 10+ years

 

They have much much more to lose by re-design. They struggle to get their own developers not breaking the database while making enhancements or fixes - any change they make at that level has a cost and the more impact that change might have, the larger the cost.

 

or you want to extend things by writing back to the MIS (online assessment for example).

 

Certainly use their import functions and API's at that point. Not when it costs you more to use them than you reasonably need to pay (in terms of time and effort). Of course, it depends what you want to achieve. If you want to get information from data to people who can use it to make a difference and you want to do that quickly, then IMO you don't dick about with API's - you go straight to the data. The pace of change in your working environment will wash away your sins thrice before MIS suppliers catch up.

[CyberNerd]

^ don't get me wrong, I applaud what you've done. I just believe that something like SIF or an API supported by the vendor is a better way of achieving it in the long term.

[matt40k]

IMO you don't dick about with API's - you go straight to the data.

 

So why do we have audit trail? So idiot can bypass it and skip over the policies that are in place to protect you. Way to go giving technician a bad name and why? Because you can't be bothered to follow the procedure and ask for help. One for your CV.

 

Sorry if it sounds extreme, just fed up of repeating myself. Maybe someone needs to publicly publish details of someone who's gone down the path and has f00ked up the database or raised data protection issue and is now jobless.

[pcstru]

So why do we have audit trail? So idiot can bypass it and skip over the policies that are in place to protect you. Way to go giving technician a bad name and why? Because you can't be bothered to follow the procedure and ask for help. One for your CV.

 

You have an audit trail for accesss to the data? CMIS certainly doesn't - if you are a user with access to reporting you can see anything. SIMS - not that I'm aware of, you have a permissions system that may or may not be granular enough but it does not keep a record of the fact that you selected particular data. CMIS' audit is pretty useless even for tracking down data writes and I've seen enough comments about SIMS audit trails that I don't believe they are entirely effective for that, let alone read.

 

Data protection is an issue - but have a look at the plethora of 3rd party performance analysis packages out there or the number of schools which make data available to teachers on spreadsheets or PDF's of reports. You can be very secure and very compliant with DP - perfectly safe, because no one can get effective access to what they want. DP is as much about staff knowledge and training as it is about technical measures.

 

[ETA - Another way of looking at this : if I am ignorant enough of the issues to get myself fired writing SQL to access a database, what makes you think I would think of setting up appropriate permissions in an MIS system in the first place.]

Edited March 23, 2012 by pcstru

[GREED]

Back to the original points boys

 

Just to add my 2 pence here: SIF does not get the recognition it really deserves, and to answer the original questions: Everything should be SIF enabled. It is easy and cost effective for everyone meaning you don't need to directly partner with all the companies or all the MIS suppliers, you don't need to pay, in some cases, extortionate prices to be technical partners or whatever, and most importantly of all, if gives schools the freedom to pick and choose the products that is right for their school, not just ones the MIS they have is working with (which is a frequent occurrence!)

 

I understand the idea of building this, but do it for your school or local school... you won't sell it. If you do, be prepared to put a hell of a lot of time and money into you new company just to keep up with changes to the MISs and changes in customer requirements.

[zag]

Sorry if it sounds extreme, just fed up of repeating myself. Maybe someone needs to publicly publish details of someone who's gone down the path and has f00ked up the database or raised data protection issue and is now jobless.

 

I wont be using a live database for this Its all on a test SQL instance for now.

 

Is there any official documentation for these API's? How do I get hold of them?

[matt40k]

@pcstru

 

If you use the SIMS API, you use a SIMS user account, this uses the standard SIMS security groups which schools have spreadsheet details what permissions are, there is an audit trail that details when users are added or removed. This limits it in an approved, standard, controlled way who has access to what.

 

Creating a sql user with read-only access would ignore this and give you full access over the database. Using the SA or similar with read-write would be completely stupid and would get you fired.

 

If a school (head, SMT, governors, parents, staff) decide to use a open-source product to access their MIS system, which is fine, they have to access the risk and the damage that it could cause if a security hole is exploted. If you purchase a product off the shelf, the company would have hired a 3rd party to regularly security check there software and would have insurance to cover any costs that might occur if the worse happens.

 

Clearly posting on a public forum that you're ignoring the approved methods of data access is silly. If you've actually done it, your leaving your school vulunable and unsupported is professional gross misconduct. I hope for anyone sake that ignores the approve methods that they have covered there backs (in writing) and for the schools sake, they don't end up getting hacked.

[matt40k]

I wont be using a live database for this Its all on a test SQL instance for now.

 

Is there any official documentation for these API's? How do I get hold of them?

 

If it's got real pupil\staff data in, it does matter and you MUST follow the same rules. If you're after the documentation, contact who you get support from for SIMS to see if it covers it. It's not easy to support for LAs, but they might be happy if they let you (proxy via them) to Capita. Documentation is pretty basic, they give you enough to get you started, but Capita are happy enough to give you examples of doing bits and tip bits. They won't give you final code, but it's enough. Once you've got the basic sorted you can work it out, personally, I'd rather ask them for an example then waste hours of my time working it out.

Edited March 23, 2012 by matt40k
Muppet got my does and doesn't the wrong way round - doh!

[CyberNerd]

 

Is there any official documentation for these API's? How do I get hold of them?

 

I've been using the commandreporter tool, part of sims. I create a report in SIMS and have a scheduled task publish it on a secure internal web service that our secure data connector picks up and exports to google docs. I've not taken it any further than that, and it was really just an experiment and to get around a problem where staff without MSOffice can use SIMS data.

[zag]

We are supported directly so I gave capita education services a ring (0844 893 8000)

 

1st Lady - She has never heard of the "Sims API". Put me onto her manager, he asked why I wanted them which I told him I just wanted access to the reports in an automated way. He transferred me to the "correct department".

2nd chap - He had also never heard of the Sims API either, Spoke to his manager. They didn't know so logged a call....

 

Searched Support net for API, no relevant results.

 

Am I looking in the wrong places?

[zag]

I've been using the commandreporter tool, part of sims. I create a report in SIMS and have a scheduled task publish it on a secure internal web service that our secure data connector picks up and exports to google docs. I've not taken it any further than that, and it was really just an experiment and to get around a problem where staff without MSOffice can use SIMS data.

 

Cool this is probably exactly what I want.

 

Could you write up how you did it?

[glennda]

The best way to do it would be to batch script command reported output to csv and then at the end execute a php page which sucks up the csv's and then trims etc imports into mysql

 

thats what i do atm with out sites. If licensing for Xporter was so much we would use that!

 

Similar to @CyberNerd method this is the one I did

[CyberNerd]

 

Could you write up how you did it?

 

essentially:

Install apache on SIMS server (it doesn't need to be on sims server, could be any windows server with sims installed)

configure to listen on https only (non-standard port)

use a self-signed certificate

configure firewall and apache to only allow connection from one host (in my case the SDC).

Write a sims report

create a batch file to use command reporter to pick up the report and output it as a csv > to the web servers directory

schedule the batch file to run every X

install secure data connector, configure connection direct to google (no proxies, firewall everything else) - this is all documented here:

https://developers.google.com/secure-data-connector/

write an app that uses appengine to somehow give parents secure access

inform ICO that you share data outside of EU

???

profit

 

I've not actually done the last bits - and probably won't get around to it.

Edited March 23, 2012 by CyberNerd
cannt spel

[zag]

essentially:

Write a sims report

create a batch file to use command reporter to pick up the report and output it as a csv > to the web servers directory

schedule the batch file to run every X

 

Sorry yeh I know all the 3rd party bits

 

But how do you use the command reporter? I basically want to convert 500 reports to something readable.

 

SIMS.net Command Line Report Exporting Utility 7.142.28.0
Parameters :
/USER:ABCD         - ABCD is the SIMS user name 
                  (not the SQL Server user name)
/PASSWORD:ABCD     - ABCD is the SIMS password 
                  (not the SQL Server password)
/SERVERNAME:ABCD   - The name of the SQL Server to connect to.
/DATABASENAME:ABCD - The name of the SIMS database to run the report against.
/TRUSTED           - Use the current Windows user to connect 
			   to the SIMS system.
			   (not to be used with /USER or /PASSWORD)
/REPORT:"ABCD"   - ABCD (quotes optional) is the name of the report 
                  seen in this users My Reports screen in SIMS.net
/PARAMS:"ABCD"   - ABCD (quotes optional) is a string containing xml 
                  which defintes the parameters for the report. 
                  See the Third Party Access to SIMS.net SDK for details on 
                  the format of this xml string.
/OUTPUT:"ABCD"   - ABCD (quotes optional) is the name of the output file 
                  to write the report contents or parameter definitions to.
                  The OUTPUT Parameter is optional, if left out the system 
                  will output the results in standard xml to the console.
/PARAMDEF          - Output the parameter list for the specified report. 
                  If OUTPUT is not provided the results will be written in 
                  standard xml to the console. See the Third Party Access 
                  to SIMS.net SDK for details on the format of this xml 
                  string.
/PARAMFILE:"ABCD" - ABCD (quotes optional) is the filename of an xml file
                     which contains the parameters for the report. 
                     See the Third Party Access to SIMS.net SDK for details on 
                     the format of this xml string.
/QUIET	         - Switch to quiet mode, this mode will not write any log 
                  messages to the console.
         
If the system is not run in QUIET mode and no OUTPUT is specified then 
the result data will be proceeded with  and followed by .

/HELP or /? - Display this help information

Edited March 23, 2012 by zag

[penfold_99]

How is it? Work ok?

Yup seems to be - we struggled initially but that was down to the amount of data in sims which we where trying to extract using the groupcall exporter Sif agents. I'm currently trying to setup user accounts for parents.

 

It was a massive amount of data, its the most amount of behaviour events i have seen. You can see rack them up when doing positive behaviour and merit point schemes.

[CyberNerd]

Sorry yeh I know all the 3rd party bits

 

But how do you use the command reporter? I want to convert 500 reports to something readable.

 

]

 

cd C:\Program Files\SIMS\SIMS .net
commandreporter.exe /USER:XXXX /PASSWORD:XXXXXXX /REPORT:"student list by year group" /OUTPUT:"C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\yeargroup.csv"  

[glennda]

It was a massive amount of data, its the most amount of behaviour events i have seen. You can see rack them up when doing positive behaviour and merit point schemes.

 

The worst thing about it is that its supposed to be a good school!

[CyberNerd]

you'd need to do each report individually commandreporter method.

 

If you are serious, look into SIF.

[pcstru]

@pcstru

 

If you use the SIMS API, you use a SIMS user account, this uses the standard SIMS security groups which schools have spreadsheet details what permissions are, there is an audit trail that details when users are added or removed. This limits it in an approved, standard, controlled way who has access to what.

Ok, so you meant permissions rather than auditing. But people are talking about API's that make data available to 3rd party systems. They are likely to be configured to use elevated privileges and for use of command reporter pulling out great swathes of data, that is now sitting in a text file somewhere. So what exactly protects that?

Creating a sql user with read-only access would ignore this and give you full access over the database. Using the SA or similar with read-write would be completely stupid and would get you fired.

I'm not and have never been talking about writing to a live database. I can understand why companies do not want people to do that and having had to pick apart a database contaminated with ignorance, they have my sympathies. Technically, I am simply taking a copy of the database and putting it on a different platform. The target database is as well protected as the MS SQL database and the utility used can transfer at a socket level without ever dumping a text file some where inconvenient. Access to the data is then controlled through the 'application' that I provide. I access the copy via SQL because the application 'platform' I'm working with (LAMPS - but perl rather than PHP) makes it very cost effective to do so. There is no reason why this is necessarily any less secure than access via an API.

If a school (head, SMT, governors, parents, staff) decide to use a open-source product to access their MIS system, which is fine, they have to access the risk and the damage that it could cause if a security hole is exploted. If you purchase a product off the shelf, the company would have hired a 3rd party to regularly security check there software and would have insurance to cover any costs that might occur if the worse happens.

You have a very rosy view of software development within the private sector. I spent 30 years doing database development for private companies on products that were sold to customers and I can only say that putting your trust in them blindly is a bit of a mistake. At least with an in-house app, I know exactly what I am dealing with and the only person I need to trust is myself and others in the team.

Clearly posting on a public forum that you're ignoring the approved methods of data access is silly. If you've actually done it, your leaving your school vulunable and unsupported is professional gross misconduct. I hope for anyone sake that ignores the approve methods that they have covered there backs (in writing) and for the schools sake, they don't end up getting hacked.

 

I am entirely comfortable with what I am doing, how I am doing it and perhaps most importantly why I am doing it. I don't believe there is any danger of me being fired for doing it, but thank you for your concern. I'm a strong believer that we own our own data and have the rights to access it in whatever way suits us best. I'm not encouraging others to follow my 'lead' just trying to put information where others who might find it useful, might find it.

 

ETA - Apologies to all for the diversion, I'll shut me gob now.

Edited March 23, 2012 by pcstru

[penfold_99]

How is it? Work ok?

 

We are supported directly so I gave capita education services a ring (0844 893 8000)

 

1st Lady - She has never heard of the "Sims API". Put me onto her manager, he asked why I wanted them which I told him I just wanted access to the reports in an automated way. He transferred me to the "correct department".

2nd chap - He had also never heard of the Sims API either, Spoke to his manager. They didn't know so logged a call....

 

Searched Support net for API, no relevant results.

 

Am I looking in the wrong places?

 

You need to ask for SIMS Business Objects documentation.

[matt40k]

I am entirely comfortable with what I am doing, how I am doing it and perhaps most importantly why I am doing it. I don't believe there is any danger of me being fired for doing it, but thank you for your concern. I'm a strong believer that we own our own data and have the rights to access it in whatever way suits us best. I'm not encouraging others to follow my 'lead' just trying to put information where others who might find it useful, might find it.

 

My concern is that you help someone who isn't an expert who then does get into trouble and I'm one of the people who could end up picking up the pieces. If you do something that isn't using the approved method, include a very bold - this is not supported, check with your support provider as this may invaild your support agreement.

 

My other concern is that you spend £££ of tax payers money developing a system, then you leave and the school just ditches it or replaces it with something else. I'm aware of this happening for real in a school. Lovely system too from what I gather.

[pcstru]

@matt. I'm afraid that by default, I like to treat people as responsible adults who are quite capable of making informed decisions off their own backs and taking responsibility for their own actions. I need some evidence before I start to think, or treat them as idiots. So I won't be including any disclaimers for example when I supplied examples of SQL and stored procedures to ease access to assessment data in CMIS or when I supplied some example spreadsheets which could be configured to directly access MIS databases. I assume if people have the means to provide the credentials to access a database at that level, then they have that means for a reason.

 

Naturally, I'm delighted that you like me, hate to see tax payers money wasted. I do believe we (by which I mean the school I work at) are in the business of educating students, we are not a software development house. When we choose to do something a particular way, it should be because that maximises the benefits to the students. There is a balance to be had with staff skills. If you worry too much about what happens when people leave, then you will restrict what you can offer to the lowest common denominator that you can expect to recruit. If we always did that then our IT staff would be unhappy (because they would feel undervalued) and our students would be ill served (because we actually have some highly skilled staff that have a lot to offer). The risk needs to be identified and managed rather than ignored but such risks can be managed.

 

I'm sure there are many examples of failed systems and systems that are replaced when people do leave. 'Systems' do tend to have limited lifetimes anyway so change is a natural part of the cycle. I'm not sure I can think of any particularly successful systems that started out in Schools. SIMS, CMIS, Parentpay ... most of the performance monitoring stuff I've been looking at seems to have been cooked up in a school or in very close association (often by the immediate family of a teacher), but I'm sure none of them are good examples that anyone actually uses. We must naturally leave this kind of thing to the boys and girls at the likes of Capita and Serco, they after all are gifted with a talent that can only thrive in the profit motivated private sector and as the consummate professionals they are, we never witness them withdrawing a spring release or issuing an upgrade which is so ill thought out that the only option is to restore a database from backup after applying it.

 

Anyway, just sayin'. Have a splendidly sunny weekend everyone.

[PhilNeal]

I recommend that every iteration of what you implement is externally PEN tested by a recognised organisation - the fines on the school for exposing data to the wrong people are crippling. Make sure you deal properly with court orders; once a court order is issued the excluded parent must not be able to see any details of the child.