Listing files older than a set date 2

[Leozack]

Hi all - was given what I thought was a simple task but it's done my head in trying it with vbs/powershell/commandprompt etc. Simply list all files in a folder and its subfolders that are older than 2005, in csv format for excel.

A remarkably good way seemed to be a commandline that only took 10m to run on the large network path of 320k files/330g

forfiles /S /D -01/01/2005 /C "cmd /c echo @fdate,@relpath" > d:\testoutput.txt

Only downside is it doesn't show the file owners which they wanted.

So I tried various VBS and though I've got a few scripts (some list all the information but run for hours) and the shortest I have is the following which isn't recursive and doesn't show the owner on half the files for no reason (some show fine)

Dim strfolder, Objshell2
Set objshell2 = CreateObject("Wscript.shell")

' Doesn't care if UNC has terminating backslash or not
strFolder = "D:\Data"
GetFileAttributes(strFolder)

Private Sub GetFileAttributes (StrFolder)
Dim objShell, objFolder, objFSO, strFileName, strDisplayText, i
Set objShell = CreateObject ("Shell.Application")
Set objFolder = objShell.Namespace (strFolder)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set f1 = objFSO.CreateTextFile(objshell2.ExpandEnvironmentStrings("%TEMP%\testfile.txt"), True)

Dim arrHeaders(13)
For i = 0 to 13
	arrHeaders(i) = objFolder.GetDetailsOf (objFolder.Items, i)
Next
For Each strFileName in objFolder.Items
	strDisplayText = ""
	If (Mid(objFolder.GetDetailsOf (strFileName, 3),7,4) < 2012) Then
		strDisplayText = objFolder.GetDetailsOf (strFileName, 0) 'File Name
		strDisplayText = strDisplayText & "," & objFolder.GetDetailsOf (strFileName, 3) 'Date Modified
		strDisplayText = strDisplayText & "," & objFolder.GetDetailsOf (strFileName, 10) 'Owner
		f1.Writeline(strDisplayText)
	End If
Next
End Sub

'Display Text file on screen
objshell2.run "%TEMP%\testfile.txt"

So I tried dabbling with powershell which I've never really used before and got this

GCI -recurse D:\Data | where {$_.lastwritetime -lt "1/1/2005"} | select LastWritetimeUTC, FullName | Export-Csv d:\testfiles.txt -notype

which works well but doesn't have owner information and shows the full path whereas just the subpath would be better.
I don't know how much memory it would take up either or how long it'd take to run on 320k files weighing 330g on a network drive.

I'm sure there are simple fixes for any of these?

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[markdmac]

Had to use different paths and dates for my testing purposes based on the content I have. This shoudl give you what you want.

$Files = GCI -recurse C:\Temp | where {$_.lastwritetime -lt "3/1/2015"}

ForEach ($File in $Files)
{
  $Object = New-Object PSObject                                       
  $Object | add-member Noteproperty LastWriteTime $File.LastWriteTime
  $Object | add-member Noteproperty FullName $File.FullName
  $Acl = Get-Acl -Path $File.FullName 
  $Object | add-member Noteproperty Owner $Acl.Owner
  $Object | Export-CSV C:\Temp\AclList.csv -Append -NoTypeInformation
}

I hope that helps.

Regards,

Mark

No trees were harmed in posting this message, however a significant number of electrons were terribly inconvenienced.

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER.

 

[Leozack]

Looks good - but giving it a test run on my PC here I get lots of this stuff. So it seems it can't converts a date without the time to a datetime type? And it can't find a path for the acl object

Bad argument to operator '-lt': Could not compare "28/06/2015 17:00:22" to "29/
6/2015". Error: "Cannot convert value "29/6/2015" to type "System.DateTime". Er
ror: "String was not recognized as a valid DateTime."".
At D:\users\me\desktop\listfiles.ps1:1 char:85
+ $Files = GCI -recurse d:\Users\me\AppData\Local\Temp | where {$_.lastwritet
ime -lt <<<< "29/6/2015"}
+ CategoryInfo : InvalidOperation: ) [], RuntimeException
+ FullyQualifiedErrorId : BadOperatorArgument

Get-Acl : Cannot validate argument on parameter 'Path'. The argument is null or
empty. Supply an argument that is not null or empty and then try the command a
gain.
At D:\users\me\desktop\listfiles.ps1:8 char:23
+ $Acl = Get-Acl -Path <<<< $File.FullName
+ CategoryInfo : InvalidData: ) [Get-Acl], ParameterBindingVali
dationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.Power
Shell.Commands.GetAclCommand

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[markdmac]

I think you are probably running it on an older version of PowerShell. Please update to 4.0.

Also not certain but it looks like you may have entered a date format at dd/mm/yyyy but that code is looking for mm/dd/yyyy format. Not certain but that is a guess based on the error you posted.

I hope that helps.

Regards,

Mark

No trees were harmed in posting this message, however a significant number of electrons were terribly inconvenienced.

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER.

 

[Leozack]

I fixed the 29/6 to be American 6/29 and now I just get this

Export-Csv : A parameter cannot be found that matches parameter name 'Append'.
At D:\users\me\desktop\listfiles.ps1:10 char:76
+ $Object | Export-CSV d:\Users\Jazz\AppData\Local\Temp\AclList.csv -Append <
<<< -NoTypeInformation
+ CategoryInfo : InvalidArgument: ) [Export-Csv], ParameterBind
ingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Comm
ands.ExportCsvCommand

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

I get the same error here about Append - is this a v4 feature? Is there anyway to do it differently? I am not responsible for the version of PS used here. If I run get-host I get this

Name : ConsoleHost
Version : 2.0
InstanceId : 7547120f-d769-4790-bad1-8953d922fa6c
UI : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture : en-GB
CurrentUICulture : en-US
PrivateData : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace : System.Management.Automation.Runspaces.LocalRunspace

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[markdmac]

You won't be able to get the data you want with an old version of PowerShell like that as far as I know. The code I provided will give you what you requested, but you need to request a PowerShell upgrade.



I hope that helps.

Regards,

Mark

No trees were harmed in posting this message, however a significant number of electrons were terribly inconvenienced.

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER.

 

[Leozack]

Unfortunately stuck with PS2 for now.
Whilst your code gives me nice csvs like this (albeit only the last line is kept)

LastWriteTime	FullName	Owner
02/06/2015 16:34	D:\Users\me\AppData\Local\Temp\test\file.exe	BUILTIN\Administrators

I found I can append if I use text based output not csv eg instead of

$Object | Export-CSV d:\Users\me\AppData\Local\Temp\AclList.csv -Append -NoTypeInformation

I use

Add-Content d:\Users\me\AppData\Local\Temp\AclList.csv `n$Object

then it works perfectly as far as appending goes but the output looks like this

@{LastWriteTime=06/02/2015 16:34:40; FullName=D:\Users\me\AppData\Local\Temp\test\file.exe; Owner=BUILTIN\Administrators}

for each line. So clearly there are ways to make this work but I need to be writing the value data rather than the key/value pairs - also need to delimit by , not ;

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

Ok so I tried to cut the script down to this

$Files = GCI -recurse d:\Users\me\AppData\Local\Temp | where {$_.lastwritetime -lt "6/19/2015"}

ForEach ($File in $Files)
{
  $Acl = Get-Acl -Path $File.FullName 
  Add-Content d:\Users\me\AppData\Local\Temp\AclList.csv `n$File.LastWriteTime","$File.FullName","$Acl.Owner
}

But it just output content like

file.exe.LastWriteTime	file.exe.FullName	System.Security.AccessControl.FileSecurity.Owner

so I set it to now be

$Files = GCI -recurse d:\Users\me\AppData\Local\Temp | where {$_.lastwritetime -lt "6/19/2015"}

ForEach ($File in $Files)
{
  $Acl = Get-Acl -Path $File.FullName 
  Add-Content d:\Users\me\AppData\Local\Temp\AclList.csv ($File.LastWriteTime.ToString() + "," + $File.FullName.ToString() + "," + $Acl.Owner.ToString())}

and now it seems to work nicely - appending lines to the csv with the right content eg

02/11/2014 13:44	D:\Users\me\AppData\Local\Temp\Skype	BUILTIN\Administrators
02/11/2014 01:53	D:\Users\me\AppData\Local\Temp\FXSAPIDebugLogFile.txt	BUILTIN\Administrators
02/06/2015 16:34	D:\Users\me\AppData\Local\Temp\2EC26B12-5BDD-41C5-923C-C5EB1C2E96D9\Foxit Cloud 3.5.116.602 Beta for Reader 7.0.exe	BUILTIN\Administrators
02/11/2014 13:44	D:\Users\me\AppData\Local\Temp\Skype	BUILTIN\Administrators
02/11/2014 01:53	D:\Users\me\AppData\Local\Temp\FXSAPIDebugLogFile.txt	BUILTIN\Administrators
02/06/2015 16:34	D:\Users\me\AppData\Local\Temp\2EC26B12-5BDD-41C5-923C-C5EB1C2E96D9\Foxit Cloud 3.5.116.602 Beta for Reader 7.0.exe	BUILTIN\Administrators

but I noticed it still had folders in there not just files so I edited the where filter to cut out folders so the working script is this

$Files = GCI -recurse d:\Users\me\AppData\Local\Temp | where {($_.lastwritetime -lt "6/19/2015") -and (!$_.PSIsContainer)}

ForEach ($File in $Files)
{
  $Acl = Get-Acl -Path $File.FullName 
  Add-Content d:\Users\me\AppData\Local\Temp\AclList.csv ($File.LastWriteTime.ToString() + "," + $File.FullName.ToString() + "," + $Acl.Owner.ToString())
}

and it seems to output exactly what I want eg running it 3 times gives

02/11/2014 01:53	D:\Users\me\AppData\Local\Temp\FXSAPIDebugLogFile.txt	BUILTIN\Administrators
02/06/2015 16:34	D:\Users\me\AppData\Local\Temp\2EC26B12-5BDD-41C5-923C-C5EB1C2E96D9\Foxit Cloud 3.5.116.602 Beta for Reader 7.0.exe	BUILTIN\Administrators
02/11/2014 01:53	D:\Users\me\AppData\Local\Temp\FXSAPIDebugLogFile.txt	BUILTIN\Administrators
02/06/2015 16:34	D:\Users\me\AppData\Local\Temp\2EC26B12-5BDD-41C5-923C-C5EB1C2E96D9\Foxit Cloud 3.5.116.602 Beta for Reader 7.0.exe	BUILTIN\Administrators
02/11/2014 01:53	D:\Users\me\AppData\Local\Temp\FXSAPIDebugLogFile.txt	BUILTIN\Administrators
02/06/2015 16:34	D:\Users\me\AppData\Local\Temp\2EC26B12-5BDD-41C5-923C-C5EB1C2E96D9\Foxit Cloud 3.5.116.602 Beta for Reader 7.0.exe	BUILTIN\Administrators

Of course I can never settle and I remembered it'd be nicer to lose the initial path off the file paths leaving just relative paths. After much fiddling around til 1am with the replace option (it's case sensitive) I realised I had to set the input path at the top to be the way it was output in the excel (upper case D) rather than copied from my explorer path bar (which had lower case d) which is a shame if you have to run the script first to see how it will be! There must be a way to grab that info at the start of the script to 'correct' the path variable but it's too late to figure that right now. Eitherway it now looks like this

$Path = "D:\Users\me\AppData\Local\Temp\"

$Files = GCI -recurse $Path | where {($_.lastwritetime -lt "7/19/2015") -and (!$_.PSIsContainer)}

ForEach ($File in $Files)
{
  $Acl = Get-Acl -Path $File.FullName 
  Add-Content $Path\AclList.csv ($File.LastWriteTime.ToString() + "," + $File.FullName.ToString().replace($Path,'') + "," + $Acl.Owner.ToString())
}

giving results like this

30/06/2015 21:16	~DFB0B8BE87CEA69F79.TMP	BUILTIN\Administrators
02/07/2015 01:12	~DFC51C8970E605B53B.TMP	BUILTIN\Administrators
02/06/2015 16:34	2EC26B12-5BDD-41C5-923C-C5EB1C2E96D9\Foxit Cloud 3.5.116.602 Beta for Reader 7.0.exe	BUILTIN\Administrators
28/06/2015 16:56	Adobe_ADMLogs\Adobe_ADM.log	BUILTIN\Administrators
28/06/2015 16:56	Adobe_ADMLogs\Adobe_GDE.log	BUILTIN\Administrators
28/06/2015 23:15	Skype\DbTemp\temp-36uymibJMScf8Q3clz1hhUyQ	BUILTIN\Administrators
28/06/2015 23:14	Skype\DbTemp\temp-RfxEXjMwLuQvkHeSPiZhTFa4	BUILTIN\Administrators

Of course I've no idea how much overhead all these filters and string commands will add if running on 320000 files but hopefully not much :S

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

Of course today I try it in here and find that due to some files around the place not having an owner (?) the script fails, saying you can't run a method on something null-valued
So instead the script is changed to this (with some other tweaks)

$Source = "D:\Data\"
$Dest = "D:\FileList.csv"

$Files = GCI -recurse $Source | where {($_.lastwritetime -lt "7/19/2015") -and (!$_.PSIsContainer)}

ForEach ($File in $Files)
{
  $Acl = Get-Acl -Path $File.FullName 
  Add-Content $Dest ($File.LastWriteTime.ToString() + "," + $File.FullName.ToString().replace($Source,'') + "," + $Acl.Owner)
}

though I'm thinking why doesn't owner info show up as "System.Security.AccessControl.FileSecurity.Owner" like it did before? Oh well. As long as this works consistently I'm ok with it.

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

Having spend 1.5 hours running the script (only the last 10m or so was writing the csv file) I have found 2 problems :

1 - lots of owners show up as their SIDs eg O:S-1-5-21-1614895754-1677128483-839522115-2544
2 - people had commas in some folder names which messes up the csv file - I will have to enclose the data values in "quotes" which is annoying - I've changed the line to be

Add-Content $Dest ("`"" + $File.LastWriteTime.ToString() + "`",`"" + $File.FullName.ToString().replace($Source,') + "`",`"" + $Acl.Owner + "`"")

The only actual errors that showed whilst it ran were these 2 which appeared about half hour apart

Get-ChildItem : The specified network name is no longer available.
At G:\file properties\listfiles.ps1:4 char:13
+ $Files = GCI <<<<  -recurse $Source | where {($_.lastwritetime -lt "1/1/2005") -and (!$_.PSIsContainer)}
    + CategoryInfo          : ReadError: (S:\Development ...iday 4th August:String) [Get-ChildItem], IOException
    + FullyQualifiedErrorId : DirIOError,Microsoft.PowerShell.Commands.GetChildItemCommand

Get-Acl : Attempted to perform an unauthorized operation.
At G:\file properties\listfiles.ps1:8 char:17
+   $Acl = Get-Acl <<<<  -Path $File.FullName
    + CategoryInfo          : NotSpecified: (:) [Get-Acl], UnauthorizedAccessException
    + FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.GetAclCommand

It's possible that amongst the whole folder tree there are 1 or 2 files that even I don't have access to or don't have access to the ACL or something I suppose?

Not sure how to solve the owners showing as SIDs though - if I check them in file properties they show as SIDs too so I guess they're no longer in AD (these are files last modified before 2005 afterall ...)

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

OK got a working CSV now with the values "quoted" to allow for commas in them. Took an hour to scan the 320,000 files 320g before writing the 6m csv file for 15m during which point it got this error presumably as I didn't have access to one of the files to get the ACL info?

Get-Acl : Attempted to perform an unauthorized operation.
At G:\file properties\listfiles.ps1:8 char:17
+   $Acl = Get-Acl <<<<  -Path $File.FullName
    + CategoryInfo          : NotSpecified: (:) [Get-Acl], UnauthorizedAccessException
    + FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.GetAclCommand

Anyway just reporting an eventual success using powershell 2 Sorry for the spam!

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[markdmac]

You can add an:

$ErrorActionPreference = "SilentlyContinue"

at the top of your script to ignore the error and continue.

If the code I provided was helpful, please remember to click "Great Post? Star it!" so others will know it was helpful.



I hope that helps.

Regards,

Mark

No trees were harmed in posting this message, however a significant number of electrons were terribly inconvenienced.

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER.

 

[Leozack]

Currently it continues anyway so it's probably better to leave the error showing. I've made it a little nicer with a queue as to how far through it is and a prompt at the end so the window won't disappear even if you ran the script directly.

$Source = "S:\Development Control\"
$Dest = "D:\FileList.csv"
$OlderThan = "1/1/2005"


[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null

$Files = GCI -recurse $Source | where {($_.lastwritetime -lt $OlderThan) -and (!$_.PSIsContainer)}

write-Host "Scanned Source - Now processing " -ForegroundColor Green
$([char]7)

ForEach ($File in $Files)
{
  $Acl = Get-Acl -Path $File.FullName 
  Add-Content $Dest ("`"" + $File.LastWriteTime.ToString() + "`",`"" + $File.FullName.ToString().replace($Source,'') + "`",`"" + $Acl.Owner + "`"")
}

[System.Windows.Forms.MessageBox]::Show("Finished processing - process complete")

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[markdmac]

You can use the transcript to record the error to a text file.

I hope that helps.

Regards,

Mark

No trees were harmed in posting this message, however a significant number of electrons were terribly inconvenienced.

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER.