Urgent SIMS advice needed - Poss gross misconduct

[enjay]

We run transactional backups of SIMS; if you have those too, I'm guessing those would say which user deleted the specific record. Not sure if it is possible to read them in that way or not.

[dhicks]

We run transactional backups of SIMS

 

Reading this thread, I'm starting to think that's a good idea... How much data does SIMS produce daily, do you know? What sort of size backup facility would we be looking at?

 

--

David Hicks

[enjay]

That rather depends on the size of your SIMS database and how many transactions happen, also how long you want to keep them. We see very little activity in SIMS, but the only changes we make our contact details, we don't use it for attendance or assessment.

[dhicks]

We see very little activity in SIMS, but the only changes we make our contact details, we don't use it for attendance or assessment.

 

We're probably the same - I shouldn't imagine we produce much data. I'll look in to that, thanks for the idea.

 

--

David Hicks

[MattMitchell]

As far as the SQL goes, and assuming that you're working on a copy of your SIMS database, not the real thing, the query for finding how many behaviour events a student has this year (in a very basic form) looks like this:

 

USE 
GO
SELECT SP.person_id, COUNT(*) AS entries
FROM
sims.stud_behaviour B JOIN sims.stud_behaviour_student_link BSTU
ON B.behaviour_id = BSTU.behaviour_id
JOIN sims.sims_person SP
ON BSTU.student_id = SP.person_id
WHERE SP.forename = N'Enter Forename Here'
AND SP.surname = N'Enter Surname Here'
AND B.event_date >= 2009-09-02
GROUP BY SP.person_id

 

However, as has been noted, there is not an audit trail in the database for behaviour logs. This isn't really a surprise - the kind of logging people are suggesting can be very expensive in terms of processor time, developer time, RAM, disk space, etc, etc. Basically, every time a record is inserted, updated, or deleted, the previous record (for updates/deletes) needs to be stamped with the user carrying out the action, and a timestamp. Then, a new record has to be inserted (for inserts/updates) with the new data. Oh, and then every time you retrieve anything from the database, you have to check for the newest copy of everything, before you can start to do the work of the normal query processing.

 

We have 1500 on roll, and six periods per day, so session and lesson marks combined make 12000 records per day; about 1 million assessment results per year (OK, it's a lot, but that's another story and a lot of them are calculated rather than entered) so on average 5000 per day; throw in a hundred for achievement and behaviour incidents, and you get something in the region of 13,000 to 20,000 new records created every day - and that's without counting the deletions and changes that people are talking about recording!

 

It's just too much storage and processing for most people, and although it's easy to say "SIMS should have an audit trail", and I do share that opinion up to a point, it's not really practical or commercially viable. Schools would have to upgrade their SQL servers massively in terms of RAM, CPUs and disk space, and probably add a second server just to record the audit data (which is generally good practice in an ultra-secure tracking environment).

 

It may be that there's a way of querying the transaction logs on the server, but my gut feeling is that you can't get the info you're after. There is a way to track changes to data in tables built in to MS-SQL from 2008 on (Using the "Change tracking" functions/statements), but 1) it won't track who did it or when (just in terms of in what order) 2) it's turned off by default, so you'd have to turn it on BEFORE you can start tracking changes 3) you have to change database and table properties, which probably comes under the heading of "things that are unsupported and chargeable to fix by Capita" 4) it won't tell you who did it anyway!

 

The DBCC LOG('', ) command will give you the contents of the transaction logs, but on its own it won't help (try it in SQL Management Studio and see!). You can probably buy some software to let you view the logs, but it's probably not cheap.

 

Alternatively, you could run a command line report every hour that outputs behaviour events, and linked students, so that you could then look at deletions over time. If you're doing that, the best way is to create a user with 3rd party reporting rights in System Manager, and make sure you have the behaviour_id field in the output (this is a primary key to the table of events). The report option will avoid all those nasty licensing and database corruption risk issues, and will basically give you the same information (albeit needing a little more work).

 

Is disabling the staff member's access to behaviour management an option? It may be that it's not appropriate to their role to be entering these events, which might be a way of doing things. I agree entirely that deleting data entered by other staff would qualify as gross misconduct, but I don't think you could prove it through the data on the system.

 

Stepping away from the technical side of it all, though, what evidence do you have that the staff member has been deleting the records? You may not be able to show it through SIMS, but if you have written evidence from several members of staff, on several occasions, that they entered behaviour codes in and those for a particular student vanished, you'd probably have cause to investigate further, maybe using some kind of logging/monitoring software on the relevant computer. As it stands, the event sounds equivalent to a "no witnesses" scenario.

[powdarrmonkey]

However, as has been noted, there is not an audit trail in the database for behaviour logs. This isn't really a surprise - the kind of logging people are suggesting can be very expensive in terms of processor time, developer time, RAM, disk space, etc, etc. Basically, every time a record is inserted, updated, or deleted, the previous record (for updates/deletes) needs to be stamped with the user carrying out the action, and a timestamp. Then, a new record has to be inserted (for inserts/updates) with the new data. Oh, and then every time you retrieve anything from the database, you have to check for the newest copy of everything, before you can start to do the work of the normal query processing.

 

We have 1500 on roll, and six periods per day, so session and lesson marks combined make 12000 records per day; about 1 million assessment results per year (OK, it's a lot, but that's another story and a lot of them are calculated rather than entered) so on average 5000 per day; throw in a hundred for achievement and behaviour incidents, and you get something in the region of 13,000 to 20,000 new records created every day - and that's without counting the deletions and changes that people are talking about recording!

 

If Serco Facility can do it (and it does, we use it) I don't see why SIMS shouldn't be able to.

 

It's just too much storage and processing for most people, and although it's easy to say "SIMS should have an audit trail", and I do share that opinion up to a point, it's not really practical or commercially viable.

 

Actually I think that's an indefensible position. Other major customer-focussed software manages to keep an audit trail perfectly happily - it's essential if there is any foul play, as in this case.

 

Schools would have to upgrade their SQL servers massively in terms of RAM, CPUs and disk space, and probably add a second server just to record the audit data (which is generally good practice in an ultra-secure tracking environment).

 

You can't make a sweeping generalisation like that without some research.

[bossman]

Access logs works for me gives me the record of the person who has edited behaviour record but not exactly which one but also gives date, time and workstation name.

Pretty quick also

[dhicks]

It's just too much storage and processing for most people

 

Okay, but how's about if you just stored the binary diffs of the database files every night, as mentioned? You could do the processing overnight, probably on a snapshot copy of the database files. Is 20,000 records a day actually all that much, on a daily average, in terms of megabytes? A couple of terrabytes of storage is pretty cheap these days, even if you bought a dedicated server just to store SIMS backups - maybe £500?

 

--

David Hicks

[MattMitchell]

If Serco Facility can do it (and it does, we use it) I don't see why SIMS shouldn't be able to.

Anything can do it - you could even rewrite all the tables in the SIMS db as temporal tables with the relevant triggers, and create views over the top of these to expose the tables to the Capita stuff (obviously, not a recommendation before someone goes and does it!). But how much information is available - do you get a "before and after" view of any record that has been changed, added or deleted? Do you just log which columns have changed, but not the data?

 

There are lots of applications that store partial or summary audit logs (e.g. failed login attempts, "record changed" info, etc), and it's not difficult at all, no. But a full, historical database isn't quite as common in software.

 

You can't make a sweeping generalisation like that without some research.

Normally, I'd agree, but considering that we're in an industry where people get upset because they've just upgraded to five-year-old version of MSSQL, and will now have to upgrade to a two-year-old version, is it likely that customers will need to upgrade their hardware if the server part of the application is more resource-hungry? There are often complaints about SIMS's being slow, implementing something like this would slow it down more.

 

Actually I think that's an indefensible position. Other major customer-focussed software manages to keep an audit trail perfectly happily - it's essential if there is any foul play, as in this case.

Having an audit trail is essential in order to prove or disprove) foul play. But, since we're talking about having enough concrete, conclusive evidence to justify dismissing a staff member on the basis of the data alone, you would need a full change record of all the data. You'd need to prove

• What data was there previously, including all the field values
  
• Who entered the data, and why
  
• What data was deleted or changed by the staff member involved
  
• What data is deleted by other staff, and how often
  
• What data is changed by other staff, and how long after the original event is recorded
  

 

Does Facility log information to that kind of level? There are applications that do it, but I'd be surprised if a school MIS did.

[Gatt]

Access logs works for me gives me the record of the person who has edited behaviour record but not exactly which one but also gives date, time and workstation name.

Pretty quick also

 

Ok probably a daft question - but how do you get it to show the logs for an individual user?

[bossman]

@Gatt:

 

Through the system manager> tools> access logs then set the dates which you want to run it from and too (works more effective if dates are closer together) then choose the advanced facility drill down into what you want i.e. Staff and then what the data set is which will be student>behaviour and then tick the box for edit access and then run.

It will bring back all the required data in between those dates specified and give the times and the person who edited and from which workstation.

 

I admit that it is not brilliant but does give you some information which could be beneficial. Maybe not as good as Serco and has been mentioned should be a standard requirement of all database systems which carry confidential data.

[pete]

There's also the schools DP obligations to provide an auditing and logging infrastructure for all systems and applications that hold/access or handle data classed as IL2-protect or above. (See Data Handling Procedures in Government report - Becta's guidance is from Sep 2008).

 

That's relatively easy to set up (and it makes my life easier as a sysadmin tool) and doesn't require great expense using Ossec and Splunk. I had both our domains dumping relevant info with a couple of hours and the more annoying apps within a week.

 

The area it's noticably absent and horrendously annoying to audit is SIMS. I can audit the Document Server, but the database doesn't lend itself to a blow-by-blow "who did what".

[Gatt]

@Gatt:

 

Through the system manager> tools> access logs then set the dates which you want to run it from and too (works more effective if dates are closer together) then choose the advanced facility drill down into what you want i.e. Staff and then what the data set is which will be student>behaviour and then tick the box for edit access and then run.

It will bring back all the required data in between those dates specified and give the times and the person who edited and from which workstation.

 

I admit that it is not brilliant but does give you some information which could be beneficial. Maybe not as good as Serco and has been mentioned should be a standard requirement of all database systems which carry confidential data.

 

Argh!! Out of Memory error

 

Damn you Capita!

[vikpaw]

Ok so I am basically waiting for the LEA to get back to me about this..

In the mean time I have been looking at seeing if there was a way to prevent conduct logs being deleted by normal staff

 

Capita FAIL #2 - you can only set 3 permissions for conduct logs:

View (All)

View (Own)

Edit (All)

 

Thats right, you guesssed it - there is no way to deny a user from deleting conduct logs!! they can either be allowed to see all of them, see only the logs they entered or - and this is a cracker - all them to change any conduct log entered by any member of staff - so they can change the information or delete the entire log, created by any member of staff, for any pupil!!

 

 

what happens if you just use view all? That will probably stop them.

 

No, they can still delete.

 

I have come across this issue many times, have logged with local support through to capita before. it's also been mentioned on supportnet a number of times as well as on EG, but Capita don't seem to want to act on this one.

 

It's simply the case that, the permissions aren't arranged such that you can create but not edit. view (own) is pointless, as in order to create you need an edit permission, which by default is edit (all).

 

Reading this thread, I'm starting to think that's a good idea... How much data does SIMS produce daily, do you know? What sort of size backup facility would we be looking at?

 

I have transaction logs every few hours and they range from 600kB to 47MB, nothing really. After this thread, i think i am going to change mine to be hourly, though i don't think the frequency matters, as you can play them back onto a full back up, which we do nightly. It comes in at around 3GB and compresses to a 1GB rar file. I usually delete transactions after a week or so.

 

...

It may be that there's a way of querying the transaction logs on the server, but my gut feeling is that you can't get the info you're after. There is a way to track changes to data in tables built in to MS-SQL from 2008 on (Using the "Change tracking" functions/statements), but 1) it won't track who did it or when (just in terms of in what order) 2) it's turned off by default, so you'd have to turn it on BEFORE you can start tracking changes 3) you have to change database and table properties, which probably comes under the heading of "things that are unsupported and chargeable to fix by Capita" 4) it won't tell you who did it anyway!

 

The DBCC LOG('', ) command will give you the contents of the transaction logs, but on its own it won't help (try it in SQL Management Studio and see!). You can probably buy some software to let you view the logs, but it's probably not cheap.

 

Is disabling the staff member's access to behaviour management an option? It may be that it's not appropriate to their role to be entering these events, which might be a way of doing things. I agree entirely that deleting data entered by other staff would qualify as gross misconduct, but I don't think you could prove it through the data on the system.

 

Stepping away from the technical side of it all, though, what evidence do you have that the staff member has been deleting the records? You may not be able to show it through SIMS, but if you have written evidence from several members of staff, on several occasions, that they entered behaviour codes in and those for a particular student vanished, you'd probably have cause to investigate further, maybe using some kind of logging/monitoring software on the relevant computer. As it stands, the event sounds equivalent to a "no witnesses" scenario.

 

I think it's easy to record the details if we want them and it's clear that we do. The issue of audit is one thing, and we do need it, however, aside from that, we need to have an edit (own) or create only permission. I've been told that as any teacher may need to access the behaviour incidents and make amendments they have edit (all) but that's silly. there should be some protection and/or logging. It should still be a teacher's task to record behaviour, and for that matter achievements.

 

I've had to deal with suspected issues like this, and it's a pain. The worst thing is one set of teachers made it their business to inform all else that this was a flaw in the software. I now train people to enter behaviour via the register, and so they don't by default have the edit view. Luckily for us, it's 'more clicks' to access the edit view, so they dont bother, and so can't make accidental changes.

 

Access logs works for me gives me the record of the person who has edited behaviour record but not exactly which one but also gives date, time and workstation name.

Pretty quick also

 

@Gatt:

 

Through the system manager> tools> access logs then set the dates which you want to run it from and too (works more effective if dates are closer together) then choose the advanced facility drill down into what you want i.e. Staff and then what the data set is which will be student>behaviour and then tick the box for edit access and then run.

It will bring back all the required data in between those dates specified and give the times and the person who edited and from which workstation.

 

You're bloomin' lucky then. I've only ever got it to work once, on a single day range, and for a user that hardly does anything. I didn't think it actually told you what they accessed, only what they had access to, so all teachers would come up with behaviour edit rights. I can't be sure as i've never got it to work since then, it just times out, and makes me !

 

On the server, the application log shows failure audit, so i'm thinking of turning on success audit, so can see logins that way. the security log, also shows access, but i think that's just from users mapping the S drive so would be useless. They kind of help to narrow down when the system was used though.

[vikpaw]

This change request was just brought to my attention, i'm sure there are others like it, but if you haven't already, i'd suggest voting on this one, since we all feel so strongly:

0903-496191

 

EDIT:

Details of CR No. 0903-496191

Establishment: Windsor Maidenhead Royal Borough of

Issue Create on: 26/03/2009

Published on: 26/03/2009

Voting Score: 46

Contact : Margaret Jinks

Condition: Open

Product: SIMS .net Setup & Permissions

Solution:

Description: Audit Trail - who has changed what and whenNotes: Now that the number of staff with access to records is increasing we are being repeatedly asked for a method of audit trailing changes to records.Status: Published for consultation

Edited June 22, 2010 by vikpaw
add details

[enjay]

You got a link to where we go to vote for that?

[MattMitchell]

Capita Supportnet - once you've logged in, click on "Support" then "Change Requests" and search by that number

[vikpaw]

sorry, links don't normally work, so i didn't bother.

the below might take you to the screen where you can search for a change request and enter the number:

http://support.capitaes.co.uk/ChangeRequest/BrowseChangeRequest.aspx

 

this is the url of the page, but i fear it wont work, give it a go:

http://support.capitaes.co.uk/ChangeRequest/DetailChangeRequest.aspx?id=%c3%98%c3%99%c3%92%c3%9c%c3%a8%c3%a5%c3%8c%c3%9e%c3%a7%c3%9b%c3%98

[MattMitchell]

Interestingly, this tool Apex SQL Log looks like it'll show the info you want. It does install custom procedures into your master database, so it's one to run past whoever supports your DB server, and also to check with Capita/your LA as far as whether this is acceptable to do on a live SIMS server.

[creese]

Interestingly, this tool Apex SQL Log looks like it'll show the info you want. It does install custom procedures into your master database, so it's one to run past whoever supports your DB server, and also to check with Capita/your LA as far as whether this is acceptable to do on a live SIMS server.

 

Anything like that would be better run on a copy of the database, attached to a test server.

[vikpaw]

Anything like that would be better run on a copy of the database, attached to a test server.

 

We should all add that caveat to our signatures

[ssiruuk2]

This is one of the many gripes I had with Sims when we moved from Facility / Serco years ago.. no audit trail. How Capita don't consider this a priority or a massive security problem I don't know, it cannot be anything from a technical standpoint surely as Serco have managed it just fine with their MIS running on SQL and that was nearly ten years ago when we were running it.

 

I had to prove something simliar to the issue raised in this thread and in the end I restored a copy of the entire server to a VM and manually checked the data that we suspected had been tampered with.

Edited June 22, 2010 by ssiruuk2
spelling..

[Shuriken1]

Have you considered anything in terms of workstation monitoring? Impero, Netranger, whatever the other ones are called. If you've got one of them in place, it might give you some record of what people have been up to. Just a long shot, which you probably would have considered already if you've got one in place but thought I'd mention it.

 

Shuriken.

[ssiruuk2]

Even if you could put such a monitoring system in place it would prove a nightmare to try and nail down exactly when a specific change was made, and thats assuming you have a system recording screen activity for all staff at all times!?! With the advent of SLG / web based write access to Sims for teaching staff (theres absolutely no way are we giving that access to staff) I can see the need for an audit trail on the server of some kind being even more important!

 

Sort it out Capita.

Edited June 22, 2010 by ssiruuk2
more spelling..

[enjay]

Have you considered anything in terms of workstation monitoring? Impero, Netranger, whatever the other ones are called.

 

All that would tell you is that the teacher in question opened SIMS, which one would expect them to do anyway.