Powershell find old files and insert them on excel

HI Guys,

I need to find files that havent been accesses for a month on one of our shared drive and then put the filenames in an excel report, how can i do this?

Regards,
K
chglAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SubsunCommented:
Try..
GCI '\\Server\Share' -Recurse | 
	?{$_.LastAccessTime -le (Get-date).AddMonths(-1)} | 
  Select FullName,CreationTime,LastAccessTime,LastWriteTime |
Export-Csv \\Server\Share\report.csv -NTI

Open in new window

0
Barry CunneyCommented:
Hi K
Output to CSV file and then CSV file can be opened in Excel

$pathtocheck = "\\share\folder"


$files = get-childitem $pathtocheck -Recurse

Foreach ($f in $files)

{

$access = $f | % {(get-date) – $_.LastAccessTime } 
if ($access.days -le 30) 
{ 

	$outputfilename = ( "c:\30dayReport.csv") 
	#$outputfilename 
	$f |format-table -property Fullname |out-file $outputfilename -append 

} 

Open in new window

0
chglAuthor Commented:
will this report include the file names?
0
Challenges in Government Cyber Security

Has cyber security been a challenge in your government organization? Are you looking to improve your government's network security? Learn more about how to improve your government organization's security by viewing our on-demand webinar!

chglAuthor Commented:
Hi Bcunney,

i tested the script and it failed
0
SubsunCommented:
To get file name, you can select the name property in output..
GCI '\\Server\Share' -Recurse | 
	?{$_.LastAccessTime -le (Get-date).AddMonths(-1)} | 
  Select Name,FullName,CreationTime,LastAccessTime,LastWriteTime |
Export-Csv \\Server\Share\report.csv -NTI

Open in new window

0
chglAuthor Commented:
Subsun - your script works well, what does the GCI and -Nti mean?

Is gci same as dir?
0
SubsunCommented:
Yes.. GCI and DIR are same, both are alias of Get-ChildItem

And –NTI is the alias of -NoTypeInformation
0
chglAuthor Commented:
Hi Subsun,

Below is my script so far

$pathname = Read-Host "Please Enter Path Name: "
GCI $pathname -Recurse | 
	?{$_.LastAccessTime -le (Get-date).AddMonths(-1)} | 
  Select FullName,CreationTime,LastAccessTime,LastWriteTime |
Export-Csv c:\TrainingDivisionReport.csv -NTI 

Open in new window


Now i need the last bit of the name to be last bit of the share. e.g \\fileandprint01\Shared\Information_Technology\IT Service 2007

so the csv name to c:\itservice2007.csv

how can i do this, thank your help :)
$pathname = Read-Host "Please Enter Path Name: "
GCI $pathname -Recurse | 
	?{$_.LastAccessTime -le (Get-date).AddMonths(-1)} | 
  Select FullName,CreationTime,LastAccessTime,LastWriteTime |
Export-Csv c:\TrainingDivisionReport.csv -NTI 

Open in new window

0
SubsunCommented:
Change last line to..
Export-Csv "c:\$(($pathname -split "\\")[-1]).csv" -NTI  

Open in new window

0
chglAuthor Commented:
thank you very much!! this is fantastic!

what does the [-1] mean?
0
chglAuthor Commented:
how do i know give the option to delete those files?
0
SubsunCommented:
[-1] will get you the last value from an array..

$pathname -split "\\" will split the string  and store it in a array..


($pathname -split "\\")[-1]
Above code will select the last value from the array..
Example..
0
SubsunCommented:
Do you want to remove all files which are there in report?
If yes try...
Import-Csv c:\TrainingDivisionReport.csv | % {remove-item $_.FullName}

Open in new window

0
chglAuthor Commented:
Thanks for the explanation.

I mean deleting the files
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Instead of   ($pathname -split "\\")[-1]   you can use   Split-Path $pathname -leaf, which is more straight-forward ("get the leaf of the pathname").
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.