Extracting the file path of Linked Documents and Student ID from SIMS

[ddevey]

Does anyone know how to extract the document path of any linked documents from SIMS? We are in the process of transitioning to ISAMS and this is the last major data set we have to extract. Capita have been no use whatsoever.

 

Ideally I need to be able to pull the student ID and the corresponding path to the files in the doc server.

 

Looking at the doc server directory structure is no help whatsoever.

 

Any help would be greatly appreciated.

[GREED]

Can probably help Groupcall provide this for our apps and partner apps too. Let me ask one of my developers later this morning.

[Banjo]

I have a feeling that you will need to manually download them and then upload them onto your new system if you want to keep them.

[hiphopamus]

Cohesion - Examples

 

This software might help..

[GREED]

I have just spoken with my developers. The link to documents in the doc store is encrypted and only Capita can do anything around that. However, you can extract the documents. We are working on something around this to extend what partners have right now to make easy for schools to use, however for the right now I would suggest you take a look at Cohesion mentioned above as they have a tool ready to go for you (albeit you have to pay for it).

[jinnantonnixx]

As @GREED says, the documents are encrypted. The first 8 bytes of every encrypted document are the same, and the document name is partially mangled. The first two characters of this mangled filename determine which subfolder they're stored in (\0c, \af, \3b, etc). A simple hashing to balance the folder structure.

 

The view [rpt_vix_Document_710] shows you where the files are stored, it has a flag called 'document_attached'. This will give you the location of the files stored on the document server, but as they're encrypted they are of no use.

 

You'll either have to ask Capita to provide a decryption tool or figure out if it's possible to decrypt them yourself. I haven't tried so I can't give you any further information.

Edited February 10, 2017 by jinnantonnixx

[ddevey]

Thanks for all the help. Very useful indeed. Does anyone know what the output from Cohesion software looks like? I've contacted them, but haven't had a response yet. Can you set it to retain the same folder structure as the Doc server? I've managed to extract all the file paths through SQL, but I need to retain the folder structure to ensure everything links up.

 

Why does Capita encrypt the linked docs anyway? I know there is the "data protection" argument but if that were true, they would encrypt the whole database. There's all sorts of personal information that would fall under the DPA that I can extract with a few SQL commands on the server.

[vikpaw]

Salamander Software offered to do this for me at my last school.

Very helpful bunch.

Our plan was to export everything to a folder labelled as the student ID, then the receiving system would work out how to import that per student.

[jinnantonnixx]

Thanks for all the help. Very useful indeed. Does anyone know what the output from Cohesion software looks like? I've contacted them, but haven't had a response yet. Can you set it to retain the same folder structure as the Doc server? I've managed to extract all the file paths through SQL, but I need to retain the folder structure to ensure everything links up.

 

Why does Capita encrypt the linked docs anyway? I know there is the "data protection" argument but if that were true, they would encrypt the whole database. There's all sorts of personal information that would fall under the DPA that I can extract with a few SQL commands on the server.

 

The documents are encrypted and decrypted by the SIMS client software, so they are sent, stored and retrieved in an encrypted form to/from the document server. The document only exists in plain text on the SIMS workstation - everywhere else it's encrypted. There could be all sorts of very sensitive data in these documents so that is quite a reasonable thing to do.

 

As for tables, you can raise the bar by employing various strategies of encryption, with differing overheads, but ultimately the system administrator will always be able to see the data, so you've gained nothing practical. The solution is in your system security and the programming (application roles vs user permissions, etc).

Edited February 13, 2017 by jinnantonnixx

[Banjo]

Cohesion - Examples

 

This software might help..

 

They are a technical partner of Capita. RECORDLINKwriter | Capita SIMS

 

They are also offering a free trial Cohesion - Trial. If this is the full fat version for 10 days - then problem solved.

 

Free 10 Day Trial of RECORDLINKwriter

 

Try RECORDLINKwriter free of charge for 10 days. To order your trial call the Capita SIMS team on 0845 4603042 or fill in the form below. You will receive an email with your trial Product Key and instructions on how to get started.

[Cache]

I thought Groupcall could do this already actually, maybe was a custom module or just restricted to certain types of documents though. I only have a vague recollection of it from what CLEO were going to provide as part of the subscription for the learning platform/parental reporting. I forget the name of it now but still have my pack of stuff on a shelf somewhere.

 

Edit: remembered - it was MIDAS

Edited February 14, 2017 by Cache

[GREED]

I thought Groupcall could do this already actually, maybe was a custom module or just restricted to certain types of documents though. I only have a vague recollection of it from what CLEO were going to provide as part of the subscription for the learning platform/parental reporting. I forget the name of it now but still have my pack of stuff on a shelf somewhere.

 

Edit: remembered - it was MIDAS

 

Groupcall do provide this for our partners via Xporter - it needs one to be a developer to write against Xporter and then interpret the feed to produce the documents - we do not currently have an 'off the shelf' tool outside our products (or partner products) that can do this, and not one to do in bulk as is being requested here.