Solved

Need a solution to keep a version history of Excel files

Posted on 2013-11-11
8
339 Views
Last Modified: 2013-11-17
Hey guys,

I have a requirement where the Finance dept want people to save each revision of an Excel workbook to a folder but they shouldn't be allowed to delete or modify any existing files They should also be able to read them.

I have done a few hours of testing NTFS permissions to try and make this work but its seems to be a bit of catch 22 type situation to me. They can write new files but cant write existing files? There are such no granular NTFS permissions for that from what I can tell but I don't claim to be an expert by any stretch.

Is there a way to allow new files to be created but make them read only once they are created?

Do you guys have any recommendations on how to solve this problem? I am starting to look at things like TortoiseSNV and Git because I am beginning to doubt I can solve this with NTFS permissions alone. However I am very reluctant to proceed down this path as it appears that it would require a lot of training of already change resistant users in order for them to use such a solution correctly.

Is there an idiot proof solution to my problem?

Thanks in advance
0
Comment
Question by:defecta
  • 4
  • 3
8 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39640268
Using Excel's built-in tools, there are likely to be no solutions other than careful naming of the files:
    http://www.auditexcel.co.za/article/spreadsheet-version-control-using-only-excels-inbuilt-tools/#.UoFmn-LCmoo

Your ideas of using a version control system (VCS) is a great one, but does require additional training.  

Another possibility is to use a continuous data protection system that never deletes files and keeps a running history, but this is likely to only retain data for so long -- depending on the storage space available.  Depending on your hardware and server environment, this last oiption may take the form of a software component, or you could leverage a NAS solution like the Buffalo Terastation.  Remember, though -- once ytou start filling up the storage system, it will start overwriting older copies, so this may not be exactly what you want, either.
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39640380
We have a small company that uses a modified Contribute script that syncs two folders.

In essence, when any file is added or changed in the main folder, the new or changed file is synced to the 'archive' folder.  No overwrites, No deletions.  Users have Read only to the Archive folder.  After a set period of time the folder is pruned, deleting any item over X days.
0
 

Author Comment

by:defecta
ID: 39640429
@ThinkSpaceSolutions: This is the sort of solution I have just been looking into since posting. A script that moves/renames/modifies attributes of new files so that unprivileged users can't change or delete them.

I have used Apple Automator (on Mac OS X of course) with great success to complete this sort of task quickly, easily and reliably. But as there is not a native Windows equivalent that doesn't require some competent scripting abilities and have been looking at Windows equivalents. So far I have found some Java software called "Action(s)" that I am looking at currently.

Your proposal sounds like the best solution to this problem. I am not familiar with Contribute scripts and my googling hasn't turned up any clues as to how you are acheiving this. Would you mind elaborating on it?
0
 

Author Comment

by:defecta
ID: 39640465
Can anyone comment if an Auditing policy in concert with NTFS permission to acheive my intended outcome? I have zero experience with Auditing Policies and how they work except for the 5 minutes I have just spent looking at them now.

EDIT: looking a little closer Auditing will only track who has done what, not prevent it. Forget I asked.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
ID: 39640591
I don't have the exact script handy, but this should help to get you going.

What the following code is doing is grabbing any files that have been created in the last day (/maxage:1) and copying them to the destination folder, under a newly created folder with today's date:

    $date = Get-Date -Format "yyyy-MM-dd"
    $source = "\\Source\Folder"
    $dest = "\\Destination\Folder"
    $destin = $dest + "\" + $date
    mkdir $destin
    robocopy /s /r:1 /w:10 /maxage:1 $source $destin

Open in new window


So, let's say there have been 3 edited files along with 4 new files in the last 24 hours.  These seven files are found in \\Source\Folder and copied over to \\Destination\Folder\2013-11-11

This keeps a daily revision of documents.  If something is deleted, you can retrieve it from previous day(s).  If a file is changed, you have a daily backup that provides revision levels.

To create the purge of old documents, use something like the following which will remove any folder older than 90 days ($Days = "90"):

$Now = Get-Date
$Days = "90"
$Target = "\\Destination\Folder"
$LastWrite = $Now.AddDays(-$Days)

$Folders = get-childitem -path $Target | 
Where {$_.psIsContainer -eq $true} | 
Where {$_.LastWriteTime -le "$LastWrite"} 

    foreach ($Folder in $Folders)
    {
    write-host "Deleting $Folder" -foregroundcolor "Red"
    Remove-Item $Folder -recurse -Confirm:$false
    }

Open in new window


Even better - Combine the two codes and run once daily!

Let me know if you need/want to make any changes.
0
 

Author Comment

by:defecta
ID: 39655344
@ThinkSpaceSolutions: Thanks, I think I have found a way to improve your script by using a Powershell script that watches for a file creation event with the help of WMI.

But my Powershell scripting sucks and I have been trying and failing to write a loop so it will continue to check after a file has been created. And then I need to pass the newly detected files to a copy command like you have suggested.

I'm sure I am biting off more than I can chew but I am sure someone else could benefit from the effort put into such a script too so I figured what the hey. =)

Here is the code I have borrowed from 'Hey Scripting Guy'.  
# ----------------------------------------------------------------------------- 
# Script: TemporaryWMIEventToMonitorFolderForNewFiles.ps1 
# Author: ed wilson, msft 
# Date: 07/13/2012 16:05:50 
# Keywords: WMI, Events and Monitoring 
# comments: creates temporary event consumer to watch files. Also has a function 
# to cleanup events and subscribers after running the script. 
# hsg-7-17-2012 
# ----------------------------------------------------------------------------- 
$query = @" 
 Select * from __InstanceCreationEvent within 10  
 where targetInstance isa 'Cim_DirectoryContainsFile'  
 and targetInstance.GroupComponent = 'Win32_Directory.Name="c:\\\\test"' 
"@ 
Register-WmiEvent -Query $query -SourceIdentifier "MonitorFiles" 
$fileEvent = Wait-Event -SourceIdentifier "MonitorFiles" 
$fileEvent.SourceEventArgs.NewEvent.TargetInstance.PartComponent 
 
Function Remove-WMIEventAndSubscriber 
{ 
 Get-EventSubscriber | Unregister-Event 
 Get-Event | Remove-Event 
} #end function Remove-WmiEventAndSubscriber

Open in new window


I was trying to add a 'do until' loop but I cant figure out the syntax so it loops after the file detection event. Any suggestions?

EDIT: I think I will open another question for the scripting advice. I will accept ThinksPaceSolutions answer for this.
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39655429
Hey defecta.  I look forward to your next post!  Sounds like you are having fun with this little project at any rate!
0
 

Author Comment

by:defecta
ID: 39655479
Until I post a new question, this is what I am working on now. I think my answer is in the second last post by Robert.
http://social.technet.microsoft.com/Forums/scriptcenter/en-US/c75c7bbd-4e32-428a-b3dc-815d5c42fd36/powershell-check-folder-for-new-files?forum=ITCG
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now