What No Audit trail ?! - Safeguarding

[PotNoodleTech]

Hi Phil. I think if you sent an email out to school SLT asking if they would like you to implement full auditing/logging of staff access in SIMS 99% would say yes.. It would be invaluable in any possible disciplinary situation. You may find that LEA's have been submitting these kind of questions/requests, not schools directly as often schools will pass problems/improvements up to LEAS support as they are supposed to and then the LEA (hopefully) pass it on to you guys.

[FN-GM]

Or the change request system indicates that there is a strong desire for it.

 

I think you will find its not a true reflection of what people actually want.

[PhilNeal]

Support Units have never had the ability to delete change requests. Change requests are regularly reviewed and form the basis of our future developments - we take them very seriously.

[Barcrest]

The change request system is, quite frankly, flawed.

Let's put it this way - a search for "marksheet", "discover", or "msi", using the last 5 years turns up zero responses. I *highly* doubt that there's not been a single change request with those 3 terms in 5 years!!

 

This might be because the current system is broken. Do a blank search and you will see all the requests however searching on a term doesn't work. I was told this is because I shouldn't have access to see the requests or vote on them however with a blank search and using date ranges you can find requests and vote on them. Maybe this is why you got zero responses.

 

These 2 are duplicates and rank towards the highest in votes

Employee Merge Routine 1411-1345590 75 Vote Score

Routine to merge staff 1301-1161020 39 Vote Score

 

Here is one for an audit trail,

Audit Trail 1301-1165698 21 Vote Score

Page 14 when ranked from highest votes down.

Edited February 3, 2016 by Barcrest

[woodham]

ive just looked for the 3 times ive filed a request 1 for an MSI installer and twice for Audit trails , the audit trail ones are not there .......

[SwedishChef]

@SwedishChef & others thanks for sharing your views on this.

 

SIMS does not audit access views. The approach we have taken is to provide access controls to data areas which are then available to schools to assign. Since schools vet their staff, and we are rarely asked to provide anything further, we have considered this to be adequate but we regularly review this position. Schools frequently tell us about the functionality they want in SIMS and audit trails have not often been a priority.

 

New data protection laws are being drafted that will be available for viewing shortly for implementation in early 2018. Data access auditability may well become a requirement, and if so, we will update SIMS accordingly.

 

Hi @PhilNeal thanks for replying, I won't go into the views on the change request system, however I would address your "approach" to think capita don't need to audit staff access due to the fact that staff are "vetted". I think this approach is naive.

The DBS check to vet staff will only flag up certain criteria and wont find "first time offenders" or ones who are breaking school policy but not the law. Neither is is a proactive approach (neither does it support a reactive approach!)

 

I think the point regarding a canvas of senior leaders and that nearly 99% would think an audit trial on staff access is a good yes is very true..... btw these are the leaders who review the schools MIS, when looking for alternatives.

[elsiegee40]

SIMS does not audit access views. The approach we have taken is to provide access controls to data areas which are then available to schools to assign. Since schools vet their staff, and we are rarely asked to provide anything further, we have considered this to be adequate but we regularly review this position. Schools frequently tell us about the functionality they want in SIMS and audit trails have not often been a priority.

 

New data protection laws are being drafted that will be available for viewing shortly for implementation in early 2018. Data access auditability may well become a requirement, and if so, we will update SIMS accordingly.

Woah there @PhilNeal

 

Have you done any Safeguarding training?

 

A DBS checks a member of staff for convictions at a single point in time. It does not test what someone is doing but hasn't been detected and it doesn't test what someone might decide to do after they have been employed.

 

Requirements are already in place for schools to be able to investigate Safeguarding concerns and that may mean monitoring use of the MIS. We are talking about protecting young people from harm here in case you have forgotten.

 

The job is difficult enough with remote and encrypted devices. Any system that is avavilable in school that has this built in/made easier would be welcomed with open arms.

 

Instead of taking the blockers approach "We'll wait until we forced to", why not take the marketing initiative "Here it is. Capita helping schools to protect the youngsters in their care"?

Edited February 5, 2016 by elsiegee40

[pete]

^ That.

 

Being unable to audit who accessed and modified pupil and staff records, medical data, employment information etc is a Safeguarding issue now.

[psydii]

Given that CMIS has an audit trail, there shouldn't be any risk commercially to capita to offer one in SIMS. I understand that sometimes capita can't do things for fear of being seen to pro-actively dominiate the market, but in this case an audit trail is something driven by DPA best practice and Safe Guarding requirements.

 

And as for asking capita for it: I know the question has been asked internally here, and of our LA support team. The response from the MIS Manager and the borough is 'sims can't do that'. So we stop asking the question, because for us at the moment moving from sims is not an option. As we do not have a direct relationship with capita how can we raise it with you?

[PhilNeal]

@psydii anyone that is a SIMS user can have a "MyAccount" account - the change request system is there as are forums that are monitored and responded to by our product management team.

[FN-GM]

Instead of taking the blockers approach "We'll wait until we forced to", why not take the marketing initiative "Here it is. Capita helping schools to protect the youngsters in their care"?

 

Because Capita is not here to help us, they are just try to make as much money as possible.

[CAM]

Out of interest, what auditing processes do the other MIS systems have? Step forwards please MIS reps.

[pcstru]

Instead of taking the blockers approach "We'll wait until we forced to", why not take the marketing initiative "Here it is. Capita helping schools to protect the youngsters in their care"?

Because cost? Seriously, building a usable audit of user access would be complex and high cost. Granular user permissions are a pretty standard way to delegate access based on trust and if you have trust issues, then an access log is just evidence after the fact.

 

Audit of write access on the other hand (who changed this data) should be relatively cheap and such logging was certainly in the architecture of Capita's LEA product a while back (ISTR it was in SIMS DBase too - but I might be misremembering). An audit could probably be done anyway from the DB logs directly although it would need to be mashed up with other logged data to track a change down to a user and it would take someone with extremely good MSSql DB skills to do it.

[pcstru]

Because Capita is not here to help us, they are just try to make as much money as possible.

I'm not a big fan of the huge corporate beast that is Capita, but such statements are disingenuous to the many people that work hard to try to make SIMS a good product and offer a good quality service. Capita's profit margins is not what actually motivates them day in day out.

[CyberNerd]

Because cost? Seriously, building a usable audit of user access would be complex and high cost. Granular user permissions are a pretty standard way to delegate access based on trust and if you have trust issues, then an access log is just evidence after the fact.

 

Audit of write access on the other hand (who changed this data) should be relatively cheap and such logging was certainly in the architecture of Capita's LEA product a while back (ISTR it was in SIMS DBase too - but I might be misremembering). An audit could probably be done anyway from the DB logs directly although it would need to be mashed up with other logged data to track a change down to a user and it would take someone with extremely good MSSql DB skills to do it.

 

That's ridiculous. I can track every change, view or edit in any document back 180 days, mail even further and I can see every website visited by 1800 students back 3 weeks. This is searching through *multiple terrabytes* of info and I can get an answer back pretty quickly.

Don't be all "it's too difficult" on a single database with only a few hundred users. This is clearly a commercial decision.

[pcstru]

That's ridiculous. I can track every change, view or edit in any document back 180 days, mail even further and I can see every website visited by 1800 students back 3 weeks. This is searching through *multiple terrabytes* of info and I can get an answer back pretty quickly.

Don't be all "it's too difficult" on a single database with only a few hundred users. This is clearly a commercial decision.

Unless you can re-write the history of SIMS .net development and build it in from the start, then it would be complex and expensive to engineer it into a mature, stable product. Apart from anything else, it would involve a massive QA effort since it has to affect every single access to every single bit of data via every single means. If you can do it as easily as you think you can, then do it as a 3rd party product and you will, according to then demand here, be mining a rich vein of pure gold.

 

Technically, I can see how you might put something in the middle as a proxy that would potentially capture the data you would need, the complex part then would be making sense of it and presenting it in a usable, digestible, robust enough to use in a disciplinary context, form. If you can actually do that, @PhilNeal would probably want to arrange for you a very well paid job, although he might find himself in a bidding war with every other MIS (or any other large scare specialist corporate information system) supplier out there.

[Geoff]

Hmm. Lets see. I have this SQL script for bolting on an audit trail to a MS SQL database. That any use? (I completely forget where it came from, lost in the mists of time).

 

USE MYAWESOMEDATABASE
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1), 
TableName varchar(128), 
PrimaryKeyField varchar(1000), 
PrimaryKeyValue varchar(1000), 
FieldName varchar(128), 
OldValue varchar(1000), 
NewValue varchar(1000), 
UpdateDate datetime DEFAULT (GetDate()), 
UserNamevarchar(128)
)
GO

DECLARE @sql varchar(8000), @TABLE_NAMEsysname
SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME) 
FROM INFORMATION_SCHEMA.Tables 
WHERE 
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'

WHILE @TABLE_NAMEIS NOT NULL
BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql = 
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as
declare @bit int ,    @Field int ,    @maxfield int ,
@char int ,    @Fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000), 
@UpdateDate varchar(21) ,    @username varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = ''' + @TABLE_NAME+ '''
-- date and user
select    @username = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Get primary key columns for full outer join
select@PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = ''PRIMARY KEY''
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '''''''' 
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = ''PRIMARY KEY''
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
where  pk.TABLE_NAME = @TableName   
and CONSTRAINT_TYPE = ''PRIMARY KEY''   
and c.TABLE_NAME = pk.TABLE_NAME   
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
select    @Field = 0,    @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while    @Field <    @maxfield
begin
select    @Field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION >    @Field
select @bit =     @Field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = (    @Field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select    @Fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION =    @Field
select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' +    @Fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' +    @Fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' +    @Fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' +    @username + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' +    @Fieldname + '' <> d.'' +    @Fieldname 
select @sql = @sql + '' or (i.'' +    @Fieldname + '' is null and  d.'' +    @Fieldname + '' is not null)'' 
select @sql = @sql + '' or (i.'' +    @Fieldname + '' is not null and  d.'' +    @Fieldname + '' is null)'' 
exec (@sql)
end
end
'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables 
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
END

 

Standard disclaimers of course, I'm not responsible if you use this script and your SIMS database turns into a small squirrel.

Edited February 5, 2016 by Geoff

[pcstru]

Hmm. Lets see. I have this SQL script for bolting on an audit trail to a MS SQL database. That any use?

That may work if you want to know that a change was made by the DB user that everyone uses to make a connection to the SIMS DB. Actual SIMS users are handled by the application, not the database. Also, the request here as I understand it is for logging access, not changes.

[bobsmith]

Out of interest, what auditing processes do the other MIS systems have? Step forwards please MIS reps.

 

Facility CMIS logs *almost* every change made by a user, whether within the main application or by the web portal.

 

It does NOT audit views however. I'm not sure SQL would be able to record that easily? Or would it be able to because each record accessed would involve a SELECT statement being run?

Edited February 5, 2016 by bobsmith
i should really proof read

[Meldrew]

@psydii anyone that is a SIMS user can have a "MyAccount" account - the change request system is there as are forums that are monitored and responded to by our product management team.

 

As someone who has been asked multiple times by senior management to find out who made a specific change in SIMS, and now reading this thread, I went to myaccount to vote for an audit system. There are lots of duplicate entries for an audit system. Should we be voting against all of them that seem relevant and Capita will aggregate the totals somehow? How would the most popular be determined based upon some people voting to a single change request they found versus me for completeness voting them all up. Surely this needs something like the government petition system where duplicates are checked before a new entry is raised?

 

Meldrew

[pcstru]

Facility CMIS *almost* every change made by a user, whether within the main application or by the web portal.

Our experience with CMIS/ePortal was that any time we wanted to know who had made a change, the supposed audit trail was not of any use. So it was a bit of a chocolate teapot for us.

[bobsmith]

The audit trail within the Facility application was buggy as hell, mostly because it had trouble parsing the 1000's of records generated.

 

However as a test of our new found SQL skills, after the school sent us on the MS SQL courses, the NM and myself set up an audit trimming and backing up script which gave you a month at a time, this was found to enable us to track any significant change easily.

[FN-GM]

I'm not a big fan of the huge corporate beast that is Capita, but such statements are disingenuous to the many people that work hard to try to make SIMS a good product and offer a good quality service. Capita's profit margins is not what actually motivates them day in day out.

 

The people and the company are 2 separate things. However its the company that is directing the people. I am basically saying what you are saying. Its all down to cost at the end of the day although @PhilNeal will not admit it in the public forum.

Edited February 5, 2016 by FN-GM

[mthomas08]

This has come up before, and it's also something we've wondered about and wanted here too. Storage isn't an excuse and frankly hasn't been a viable excuse for the last decade. Let's face facts here - yes SIMS is large, but even with an audit trail available on every database change it wouldn't touch the logs of, as example, Impero for a typically sized secondary school which manages to log users, computers, websites, file tracking, usb tracking. They had to make a change in one of the major versions to go from text logging to database/SQL logging but that would have been natural development anyway. So yes, it is odd that it hasn't already been part of SIMS for ages.

 

Although I agree space shouldn't be an excuse, I'm sure we all know that simply expanding a server isn't a 5 second job. Luckily enough we limit our SIMs server to pretty much SIMs.net plus the software packages that export data overnight for other products. Problem is though, you also have to consider server performance. I don't think it would be a massive issue for us but I would certainly raise my eye brow at the thought of our SIMs Server doing much more. It's not like SIMs is the fastest package at the moment either.

[pcstru]

The audit trail within the Facility application was buggy as hell, mostly because it had trouble parsing the 1000's of records generated.

 

However as a test of our new found SQL skills, after the school sent us on the MS SQL courses, the NM and myself set up an audit trimming and backing up script which gave you a month at a time, this was found to enable us to track any significant change easily.

Not sure I understand that. You are saying you used their audit trail and just truncated it to the last months records?

 

Our experience was that the information was simply not recorded by the audit - it wasn't a problem with the number of records, the information just wasn't there (or if it was, Serco didn't understand how to make any sense of it). I did think of doing something similar to @Geoff's suggestion, but CMIS/ePortal take the same approach as SIMS - the actual application end user is not the DB user and it would have been a lot of effort to close that gap.

 

[ETA - there is also a significant risk that a blunt table trigger approach to audit will play cripple Mr Database at some point (most likely when you least expect it or need it)]

Edited February 5, 2016 by pcstru