Urgent SIMS advice needed - Poss gross misconduct

[Gatt]

Help!

 

my Line manager has aasked me to investigate a possible case of GM involving SIMS Behaviour/Conduct Logs

 

A member of staff is suspected of deleting the logs of their own child..

 

Is there anyway to find out when a log was removed and by whom?

[ICT_GUY]

I would contact you higher support as any interfering with the system could be prejudicial to legal action further on. County level support will be able to advise on how to proceed.

[DMcCoy]

http://support.capitaes.co.uk/websupport/RenderXSL.aspx?KB=KB50832.xmlFalse

 

Apparently not.

 

System manager is also broken when you try and open the logs, using all the memory and crashing. Not that proper auditing is essential for when you want to enable remote access to sims etc, no, why ever would we need something as silly as that!

[vikpaw]

Take a backup now. system manager can be done whilst system is live. now you can work with that. Also dump current server event logs.

 

As far as i know, no there isn't. If you have daily backups you can maybe narrow down what day it occured, but only if it was done the day after the backup.

 

System Manager will give you some logging, but it usually crashes my machine. worth a try though.

 

SupportNet KB50832 - Is there anyway of finding out who made changes in SIMS .net?

: only login sessions (along with the rights granted to a user at the time) are audited in the SQL Server database.

Look at the event logs on SIMS server, i quite often see password failures, not sure if it logs success too. Again, may help you track it down.

You'll need a lot of luck, in terms of the time of day, and who else might have been on the system at the time.

Best thing to do is contact Capita or your support for the official advise, but also let your boss / the executive team know how hard it is to find such info.

[Gatt]

So capita Fail again by not including a proper audit trail!

oh wonderful.. thanks so much Capita!

 

I have rung our LA supoprt and waiting on a call back from them to see if there is anyway it can be traced..

[vikpaw]

pipped me

[creese]

Not many logs are kept by SIMS. At least they are not accessible. Sending the data to Capita may get a result but it is too easy to delete behaviour entries.

 

This should be a disiplinary matter but would be difficult at present to prove.

[dhicks]

As far as i know, no there isn't. If you have daily backups you can maybe narrow down what day it occured, but only if it was done the day after the backup.

 

SIMS runs on top of MS SQL Server. When you "delete" a record in an SQL database, is it actually deleted straight away or simply marked as deleted and left for a clean-up process to deal with? Depending on when, exactly, the data was deleted, and how often any clean-up process runs, would it be possible to recover "deleted" records from an SQL database file?

 

--

David Hicks

[Gatt]

:frusty:

 

Capita will not speeak to me becuase the LEA are our supoprt contact

 

That would be fine if the LEA actaully helped me

 

so according to Capita Drone i need to

 

Log call with LEA (done)

LEA investigate my issue

if LEA cannot find solution the LEA have to raise issue with Capita

Capita then investigate and then pass back to LEA

LEA then carry out whatever capita have advised them to do

 

So its Me -> LEA -> CAPITA -> LEA -> ME

 

/me not a happy chappy at this ridiculous process....

[localzuk]

If there are no simple logs, how about pulling up a backup of the SIMS database from a while back, and comparing the tables? Laborious work I know, but its a possibility?

[Gatt]

SIMS runs on top of MS SQL Server. When you "delete" a record in an SQL database, is it actually deleted straight away or simply marked as deleted and left for a clean-up process to deal with? Depending on when, exactly, the data was deleted, and how often any clean-up process runs, would it be possible to recover "deleted" records from an SQL database file?

 

--

David Hicks

 

 

just been through the SQL DB - there is a table called sims.stud_behaviour - but either only a dozen people have recorded a behiouirs log this year or something is very wrong with this table as it goes back to 2001!

[TechMonkey]

Would hassling the LEA & demanding an escalation be worth it? This would be massively time sensitive, potentially a DPA issue. Wave that at them may be a big enough stick.

 

We have had the same problem with the lea>capita and back again route. Even had the lea tell us to phone capita direct to get something and then get pushed back to the LEA from them!

[localzuk]

just been through the SQL DB - there is a table called sims.stud_behaviour - but either only a dozen people have recorded a behiouirs log this year or something is very wrong with this table as it goes back to 2001!

 

I'm not sure on the table structure, but have a look in the views. There is more than likely a relevant one in there.

[vikpaw]

SIMS runs on top of MS SQL Server. When you "delete" a record in an SQL database, is it actually deleted straight away or simply marked as deleted and left for a clean-up process to deal with? Depending on when, exactly, the data was deleted, and how often any clean-up process runs, would it be possible to recover "deleted" records from an SQL database file?

 

--

David Hicks

 

You're far more knowledgable than me on the subject. I got the impression, deletions were final, and Capita state there is no logging. i've found similar when trying to track changes to assessment marks. the only logs they have to keep are for attendance marks.

 

I've never done it, but i believe it is possible, to use backups of the transaction logs and play these back onto a full backup. This would still only narrow down the timeframe at best, as i don't know if it would record the person doing it. We set 5 transaction log backups to occur through the day via SQL management studio. After a few weeks i delete them.

 

just been through the SQL DB - there is a table called sims.stud_behaviour - but either only a dozen people have recorded a behiouirs log this year or something is very wrong with this table as it goes back to 2001!

 

I'm not sure on the table structure, but have a look in the views. There is more than likely a relevant one in there.

 

I don't think looking at the tables will show anything, as it will only show data that is there, or on a backup that was there. I don't believe there is explicit logging aside from the SQL processes @dhicks; mentioned which presumably get deleted after.

 

All you'll achieve is to work out when it was done, so unless it was after hours, it will be quite hard to track. You'll need to combine the soft evidence with facts about who was in school and logged into a computer at the time...

[dhicks]

just been through the SQL DB - there is a table called sims.stud_behaviour - but either only a dozen people have recorded a behiouirs log this year or something is very wrong with this table as it goes back to 2001!

 

The problem is, I assume any standard SQL tool built to browse through an SQL database is going to respect any "deleted" flags on records, hence it isn't going to show you tha data you want - you're going to need a forensics package of some sort that reads the SQL database directly without going through the SQL engine.

 

--

David Hicks

[dhicks]

You're far more knowledgable than me on the subject.

 

No, I'm simply guessing here, based on how I remember being taught about how SQL databases work. Records are fixed size so you can easily predict their location on disk, allowing you to seek straight to a particular record when you want it, no searching through chains of linked records. This means you can't simply delete a record, though - you have to re-write the whole database file at some point if you want to do that. It's a question of how often the MS SQL database engine does that tidy-up routine - once a day, overnight, or just when the number of deleted records hits a given threshold? Either way, that might have already been done and the data might be gone, but if not then a forensics tool of some sort might be able to restore the data. I don't know anything about SQL forensics tools, or if the above guess-work is really valid in any way, it's just a starting point from which to trundle off to Google and investigate SQL forensics tools.

 

--

David Hicks

[vikpaw]

interesting, so perhaps, the truncation of logs is part of this process... where's @jinnantonnix; - there's an SQL expert lurking there for sure. Edited June 17, 2010 by vikpaw
typo

[Sarconia]

There really does need to be some kind of audit trail, especially when it comes to behaviour records. Numerous schools who I have dealt with have had this exact issue and it's very difficult to prove anything.

 

I don't really know that much about SQL or how it works as I'm just a basic "dabble when you need to" person but if the files aren't deleted automatically and are only flagged for deletion at a later tidy up point then it might be possible to retrieve them both with the knowledge of where they are stored and before the tidy up happens. I'm going to have to see if it's possible to test this as I'm intrigued now... find where behaviour incidents are stored, add a new one in the front end of SIMS, check the table(s), delete it from SIMS and check the table(s) again to see if it's gone.

[pete]

Do you know a) when the record was ok and b) when it was changed?

 

Do you have backups of the transaction logs? You may be able to whittle it down a bit (opportunity, not proof).

 

If you look in the Capita forums you'll see a thread here: http://support.capitaes.co.uk/newforum/main.asp?params=%95%9E%A1%9B%A0%A6%C8%A0%B0%A2%A4%B4%A9%8E%F1%98%94%90%9C%A8%AD%DC%97%9B%F3%95%CF%DB

 

Doesn't help now, but there's Tools > Setups > Data Change Management. From the above thread (I suspect the link won't work search the forum for "Data Change Management"). It's also not exposed to the end user in terms of being able to report on changes, last time I checked.

[dhicks]

interesting, so perhaps, the truncation of logs is part of this process...

 

Good point, that would make sense - a tidy-up job that runs nightly to compact the database, sort out logs, etc. Would rather imply that you're not going to get much out of that database, but like you say, at this point we really need an expert.

 

--

David Hicks

[dhicks]

I don't really know that much about SQL or how it works as I'm just a basic "dabble when you need to" person but if the files aren't deleted automatically and are only flagged for deletion at a later tidy up point then it might be possible to retrieve them both with the knowledge of where they are stored and before the tidy up happens. I'm going to have to see if it's possible to test this as I'm intrigued now... find where behaviour incidents are stored, add a new one in the front end of SIMS, check the table(s), delete it from SIMS and check the table(s) again to see if it's gone.

 

If I'm correct, and my basic database theory is still up-to-date (which it very possibly isn't), then new records would simply be appended to the end of a database file. Something might come along at some point and re-organise that file, I don't know how often that happens. I don't know anything about the internal binary format of an MS SQL database file, and it's not the sort of thing that's going to be published (you're meant to access them via an API, obviously), so it'll probably take a lot of time and/or an expensive tool of some sort to analyse that file.

 

--

David Hicks

[Gatt]

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!!

 

[FN-GM]

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

[creese]

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

 

No, they can still delete.

[Gatt]

I cannot believe that an MIS such as SIMS has so many glaring security holes in it!

We all know how reliable staff are at locking their PCs and that kids head straight to SIMS (again usually left open on the aforementioned unlocked PC) and make some changes such as deleting that conduct log tha could prevent them going oin a school trip, etc...

 

So why the hell did Capita think it was OK to omit a security setting that would only allow such things to be deleted by the likes of SMT/SLT and to not include some way of auditing what has been done in the system!