+ Post New Thread
Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 66
MIS Systems Thread, Urgent SIMS advice needed - Poss gross misconduct in Technical; Originally Posted by vikpaw You're far more knowledgable than me on the subject. No, I'm simply guessing here, based on ...
  1. #16

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,772
    Thank Post
    1,308
    Thanked 804 Times in 698 Posts
    Rep Power
    246
    Quote Originally Posted by vikpaw View Post
    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

  2. #17

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    interesting, so perhaps, the truncation of logs is part of this process... where's @jinnantonnix; - there's an SQL expert lurking there for sure.
    Last edited by vikpaw; 17th June 2010 at 11:14 AM. Reason: typo

  3. #18
    Sarconia's Avatar
    Join Date
    Mar 2010
    Location
    London
    Posts
    315
    Thank Post
    15
    Thanked 34 Times in 31 Posts
    Rep Power
    16
    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.

  4. #19


    Join Date
    Dec 2005
    Location
    In the server room, with the lead pipe.
    Posts
    4,715
    Thank Post
    288
    Thanked 789 Times in 616 Posts
    Rep Power
    226
    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/newfor...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.

  5. Thanks to pete from:

    timzim (7th July 2010)

  6. #20

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,772
    Thank Post
    1,308
    Thanked 804 Times in 698 Posts
    Rep Power
    246
    Quote Originally Posted by vikpaw View Post
    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

  7. #21

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,772
    Thank Post
    1,308
    Thanked 804 Times in 698 Posts
    Rep Power
    246
    Quote Originally Posted by Sarconia View Post
    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

  8. #22

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,820
    Thank Post
    883
    Thanked 685 Times in 454 Posts
    Rep Power
    505
    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!!


  9. #23

    FN-GM's Avatar
    Join Date
    Jun 2007
    Location
    UK
    Posts
    16,374
    Thank Post
    906
    Thanked 1,811 Times in 1,559 Posts
    Blog Entries
    12
    Rep Power
    468
    what happens if you just use view all? That will probably stop them.

  10. #24

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28° 31' 48.89", +28° 25' 37.42" ... if only.
    Posts
    3,261
    Thank Post
    182
    Thanked 375 Times in 304 Posts
    Rep Power
    183
    Quote Originally Posted by FN-GM View Post
    what happens if you just use view all? That will probably stop them.
    No, they can still delete.

  11. #25

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,820
    Thank Post
    883
    Thanked 685 Times in 454 Posts
    Rep Power
    505
    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!

  12. #26
    enjay's Avatar
    Join Date
    Apr 2007
    Location
    Reading, Berkshire, UK
    Posts
    4,497
    Thank Post
    282
    Thanked 196 Times in 167 Posts
    Rep Power
    76
    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.

  13. Thanks to enjay from:

    dhicks (17th June 2010)

  14. #27

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,772
    Thank Post
    1,308
    Thanked 804 Times in 698 Posts
    Rep Power
    246
    Quote Originally Posted by enjay View Post
    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

  15. #28
    enjay's Avatar
    Join Date
    Apr 2007
    Location
    Reading, Berkshire, UK
    Posts
    4,497
    Thank Post
    282
    Thanked 196 Times in 167 Posts
    Rep Power
    76
    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.

  16. #29

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,772
    Thank Post
    1,308
    Thanked 804 Times in 698 Posts
    Rep Power
    246
    Quote Originally Posted by enjay View Post
    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

  17. #30

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    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:

    Code:
    USE <Enter Your Restored Database Name>
    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('<tablename>', <detail level 1-4>) 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.

  18. Thanks to MattMitchell from:

    vikpaw (29th June 2010)



SHARE:
+ Post New Thread
Page 2 of 5 FirstFirst 12345 LastLast

Similar Threads

  1. Urgent advice Please
    By jsnetman in forum Hardware
    Replies: 1
    Last Post: 23rd April 2009, 12:44 PM
  2. Urgent Help needed!
    By The_GuRu in forum Network and Classroom Management
    Replies: 27
    Last Post: 20th February 2008, 10:20 AM
  3. Replies: 12
    Last Post: 22nd October 2007, 08:15 PM
  4. advice needed on weather a new server is needed
    By projector1 in forum Hardware
    Replies: 3
    Last Post: 24th February 2006, 10:20 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •