query oracle database and move some of the folders and subfoders to other directory

Question

Hi, pretty new to powershell, I would like to write a script to delete some employee photos that stored in our shared network directory. the photos are files named by employeeID.
Detailed like below:
folder directory structure is like below: \myserver\00\employid\photo.jpg
So basically the top directory is 00, 01, 02, 03... 99. this is the top directory worked like groups.
then under above directory, there is a folder named by employeeID.
for example 00\987688
then under each employeeID, there are 3 photos files, named photo.jpg, photoNormal.jpg, and photoThumb.jpg.
For example 00\987688\photo.jpg.

I would like first query oracle database to do a query : select empoyeeID from employees where active ='No' and terminate date > 5 years ago.

My final purpose is to for each employee in the query, if their photos has a time stamp older than 10 years, then move their photo to another directory \myserver\archivedpictures\employeeID\ to archive and delete their photo directory from the orginial folders.

How can I structure the script, and what critical statements needed? For oracle connection what to use.
And how to structure the loops for folders?
Thanks

Answer 1

If you are new to Powershell, start by downloading and reviewing this document.

https://www.sapien.com/books_training/Windows-PowerShell-4

Ignore the Oracle requirement initially. Start by putting a few user id's in a text file and use that to do initial testing. Build sections of code, test them, and then add to it. Start with looping through the id's.

$UsersHome = '\\server\share'   
$AllIds = Get-Content 'C:\temp\ids.txt'  
foreach ($id in $AllIds) {  
    "Processing $id"  
}  

Then use the id to find the subdirectory. You may find more than one.

$UserFolders = Get-ChildItem -Path $UsersHome -Directory -Filter $id -Recurse  
foreach ($uf in $UserFolders) {  
    "I found this folder {0}" -f $uf.fullname  
}  

Then look for the photo files.

$jpgs = Get-ChildItem -Path $uf.fullname -Filter *.jpg  
foreach ($pic in $jpgs) {  
    $age = New-TimeSpan -Start $pic.LastWriteTime -end (Get-Date)             
    "{0} was modified {1}, it's age in days is {2}" -f $pic.name, $pic.LastWriteTime,  $age.Days  
}  

At this point, you test the age and copy or move the items that you want. If you don't want to process all jpg files, you could hard code the 3 that you are interested in. Use Test-Path to verify that they exist. Use Move-Item or Copy-Item.
Add the -Whatif switch when first testing to verify that you have the correct destination folder/file name.

Answer 2

Thank you, for the link and all the test script, and also couple of days ago I bought a book too.
To put the id in the text file is smart idea to test.
I will try it, in case in future I need more fields other than only 1 ID field, for example employeeID, employname , and use them for the directory or file name, then I may still need to connect to oracle database and get the two fields.
What then is the best approach to connect to database, maybe query and loop each record? how to loop each record?

Thanks