+ Post New Thread
Page 3 of 5 FirstFirst 12345 LastLast
Results 31 to 45 of 66
MIS Systems Thread, Urgent SIMS advice needed - Poss gross misconduct in Technical; Originally Posted by MattMitchell However, as has been noted, there is not an audit trail in the database for behaviour ...
  1. #31

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    Quote Originally Posted by MattMitchell View Post
    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.

  2. #32

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    3,898
    Thank Post
    1,182
    Thanked 1,053 Times in 748 Posts
    Rep Power
    327
    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

  3. #33

    dhicks's Avatar
    Join Date
    Aug 2005
    Location
    Knightsbridge
    Posts
    5,609
    Thank Post
    1,223
    Thanked 772 Times in 670 Posts
    Rep Power
    234
    Quote Originally Posted by MattMitchell View Post
    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

  4. #34

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    242
    Thank Post
    21
    Thanked 35 Times in 26 Posts
    Rep Power
    20
    Quote Originally Posted by powdarrmonkey View Post
    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.

    Quote Originally Posted by powdarrmonkey View Post
    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.

    Quote Originally Posted by powdarrmonkey View Post
    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.

  5. #35

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,644
    Thank Post
    858
    Thanked 645 Times in 428 Posts
    Rep Power
    498
    Quote Originally Posted by bossman View Post
    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?

  6. #36

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    3,898
    Thank Post
    1,182
    Thanked 1,053 Times in 748 Posts
    Rep Power
    327
    @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.

  7. #37


    Join Date
    Dec 2005
    Location
    In the server room, with the lead pipe.
    Posts
    4,618
    Thank Post
    275
    Thanked 777 Times in 604 Posts
    Rep Power
    223
    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".

  8. #38

    Gatt's Avatar
    Join Date
    Jan 2006
    Posts
    6,644
    Thank Post
    858
    Thanked 645 Times in 428 Posts
    Rep Power
    498
    Quote Originally Posted by bossman View Post
    @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!

  9. #39

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,836
    Thank Post
    671
    Thanked 1,380 Times in 1,142 Posts
    Rep Power
    348
    Quote Originally Posted by Gatt View Post
    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!!

    Quote Originally Posted by FN-GM View Post
    what happens if you just use view all? That will probably stop them.
    Quote Originally Posted by creese View Post
    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).

    Quote Originally Posted by dhicks View Post
    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.

    Quote Originally Posted by MattMitchell View Post
    ...
    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.

    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.

    Quote Originally Posted by bossman View Post
    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
    Quote Originally Posted by bossman View Post
    @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.

  10. #40

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,836
    Thank Post
    671
    Thanked 1,380 Times in 1,142 Posts
    Rep Power
    348
    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
    Last edited by vikpaw; 22nd June 2010 at 11:15 AM. Reason: add details

  11. #41
    enjay's Avatar
    Join Date
    Apr 2007
    Location
    Reading, Berkshire, UK
    Posts
    4,485
    Thank Post
    282
    Thanked 196 Times in 167 Posts
    Rep Power
    75
    You got a link to where we go to vote for that?

  12. #42

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    242
    Thank Post
    21
    Thanked 35 Times in 26 Posts
    Rep Power
    20
    Capita Supportnet - once you've logged in, click on "Support" then "Change Requests" and search by that number

  13. #43

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,836
    Thank Post
    671
    Thanked 1,380 Times in 1,142 Posts
    Rep Power
    348
    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/Change...geRequest.aspx

    this is the url of the page, but i fear it wont work, give it a go:
    http://support.capitaes.co.uk/Change...a7%c3%9b%c3%98

  14. Thanks to vikpaw from:

    enjay (22nd June 2010)

  15. #44

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    242
    Thank Post
    21
    Thanked 35 Times in 26 Posts
    Rep Power
    20
    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.

  16. #45

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28 31' 48.89", +28 25' 37.42" ... if only.
    Posts
    3,180
    Thank Post
    173
    Thanked 370 Times in 300 Posts
    Rep Power
    172
    Quote Originally Posted by MattMitchell View Post
    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.

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

Similar Threads

  1. Urgent advice Please
    By jsnetman in forum Hardware
    Replies: 1
    Last Post: 23rd April 2009, 11:44 AM
  2. Urgent Help needed!
    By The_GuRu in forum Network and Classroom Management
    Replies: 27
    Last Post: 20th February 2008, 09:20 AM
  3. Replies: 12
    Last Post: 22nd October 2007, 07:15 PM
  4. advice needed on weather a new server is needed
    By projector1 in forum Hardware
    Replies: 3
    Last Post: 24th February 2006, 09: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
  •