Script needed for checking file date/time and email results.

bfuchs
bfuchs used Ask the Experts™
on
Hi Experts,

I have the following command running as a batch file (on task scheduler) in an hourly bases.
xcopy "C:\Users\bfuchs\Dropbox\Caspio\caspiotables.zip" f:\Conversion\Caspio\ /y

Open in new window


would like to have this running in another pc as well as a backup.

However would prefer to have the following modifications

1- To first check for date/time that file f:\Conversion\Caspio\caspiotables.zip when it was modified, if it was not within the hour then perform the task.
2- Get an email informing it was done (so i know the 1st one failed).

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
Doing both those things, date comparisons, and sending email, are pretty hard in BAT scripts.  Email isn't possible without use of extra utilities, like BLAT.  And date math is very ugly unless a VBS helper script is used.

This would really be a great use for Powershell in my opinion, both of those things would be a lot easier.  You might want to reask the question as looking for a Powershell script if you want to pursue that...


»bp
Hi,
Well I didn't specify on my question which language to be used, although would prefer either DOS or VB script (as I'm more familiar with them), however would accept any solution as long it works.
Would add Powershell to the topics.
Thanks,
Ben
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
This uses PowerShell for the heavy lifting, but for your convenience, it's disguised as batch, so save it as Whatever.cmd
@PowerShell.exe -Command "Invoke-Expression -Command ((Get-Content -Path '%~f0' | Select-Object -Skip 2) -join [environment]::NewLine)"
@exit /b %Errorlevel%

$path = "C:\Users\bfuchs\Dropbox\Caspio\caspiotables.zip"
$path = "C:\Temp\test.txt"
$destination = "f:\Conversion\Caspio"
$sendMailArgs = @{
	To =			"to@domain.com"
	From =			"from@domain.com"
	Subject =		"Problems with $([IO.Path]::GetFileName($path))"
	SmtpServer =	"smtp.domain.com"
}

$body = $null
If ($item = Get-Item -Path $path -ErrorAction SilentlyContinue) {
	$current = (Get-Date).AddHours(-1)
	If ($item.LastWriteTime -lt $current) {
		$body = "File '$($path)' is older than one hour: $($item.LastWriteTime)!"
		Try {
			Copy-Item -Path $path -Destination $destination -Force -ErrorAction Stop
		} Catch {
			$body += "`r`nThe file could not be copied to '$($destination)' either:`r`n$($_.Exception.Message)"
		}
	}
} Else {
	$body = "Expected file '$($path)' not found!"
}
If ($body) {
	$body | Write-Warning
	Send-MailMessage @sendMailArgs -Body $body
}

Open in new window

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Hi,
Not sure if I'm testing it right, tried the following
saved your code as testCopy.cmd
replaced f:\Conversion\Caspio\caspiotables.zip file with an old file
double clicked on batch to see if file gets replaced with new one, and it didn't.
Let me know
Thanks,
Ben
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
The issue is probably that there's a leftover from testing - remove line 5, <$path = "C:\Temp\test.txt">
And don't double-click the file, you won't see any messages. Open a command prompt, "cd /d ..," into the folder where you saved the script, start it from there.
Hi,
okay will test it tom.
Good night!
Thanks,
Ben
Hi oBdA,

Was not in at work, just got a chance to test it.
So far didnt work.
Hope I'm testing correct...see attached.

Thanks,
Ben
Untitled.png
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Then the file should be current (that is, less than an hour old).
This now will print a message as well if this is the case:
@PowerShell.exe -Command "Invoke-Expression -Command ((Get-Content -Path '%~f0' | Select-Object -Skip 2) -join [environment]::NewLine)"
@exit /b %Errorlevel%

$path = "C:\Users\bfuchs\Dropbox\Caspio\caspiotables.zip"
$destination = "f:\Conversion\Caspio"
$sendMailArgs = @{
	To =			"to@domain.com"
	From =			"from@domain.com"
	Subject =		"Problems with $([IO.Path]::GetFileName($path))"
	SmtpServer =	"smtp.domain.com"
}

$body = $null
If ($item = Get-Item -Path $path -ErrorAction SilentlyContinue) {
	$current = (Get-Date).AddHours(-1)
	If ($item.LastWriteTime -lt $current) {
		$body = "File '$($path)' is older than one hour: $($item.LastWriteTime)!"
		Try {
			Copy-Item -Path $path -Destination $destination -Force -ErrorAction Stop
			$body += "`r`nThe file was copied to '$($destination)':`r`n$($_.Exception.Message)"
		} Catch {
			$body += "`r`nThe file could not be copied to '$($destination)' either:`r`n$($_.Exception.Message)"
		}
	} Else {
		"File '$($path)' is current, nothing to do: $($item.LastWriteTime)"
	}
} Else {
	$body = "Expected file '$($path)' not found!"
}
If ($body) {
	$body | Write-Warning
	Send-MailMessage @sendMailArgs -Body $body
}

Open in new window

Hi oBdA,

Still not working, see attached.

I think the problem is you're checking for date of source file if current, instead of the destination file.
How do I modify that?

Thanks,
Ben
Untitled.png
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Sorry, my bad; misunderstood that.
@PowerShell.exe -Command "Invoke-Expression -Command ((Get-Content -Path '%~f0' | Select-Object -Skip 2) -join [environment]::NewLine)"
@exit /b %Errorlevel%

$path = "C:\Users\bfuchs\Dropbox\Caspio\caspiotables.zip"
$destination = "f:\Conversion\Caspio"
$sendMailArgs = @{
	To =			"to@domain.com"
	From =			"from@domain.com"
	Subject =		"Problems with $([IO.Path]::GetFileName($path))"
	SmtpServer =	"smtp.domain.com"
}

$body = $null
$current = (Get-Date).AddHours(-1)
$copyPath = Join-Path -Path $destination -ChildPath $([IO.Path]::GetFileName($path))
If ($item = Get-Item -Path $copyPath -ErrorAction SilentlyContinue) {
	If ($item.LastWriteTime -lt $current) {
		$body = "File '$($copyPath)' is older than one hour: $($item.LastWriteTime)!"
	} Else {
		"File '$($copyPath)' is current, nothing to do: $($item.LastWriteTime)."
	}
} Else {
	$body = "Expected file '$($copyPath)' not found!"
}
If ($body) {
	Try {
		$copiedItem = Copy-Item -Path $path -Destination $destination -Force -ErrorAction Stop -PassThru
		$body += "`r`nThe file was successfully copied to '$($destination)'."
		If ($copiedItem.LastWriteTime -lt $current) {
			$body += "`r`nThe source file is already older than one hour: $($copiedItem.LastWriteTime)!"
		}
	} Catch {
		$body += "`r`nThe file could not be copied to '$($destination)' either:`r`n$($_.Exception.Message)"
	}
	$body | Write-Warning
	Send-MailMessage @sendMailArgs -Body $body
}

Open in new window

Hi,
Okay the first part is finally working.
now my question, see error emailing on attached.
assuming credentials are missing, where do i add them in the script?
Thanks,
Ben
Untitled.png
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Assuming you don't want to save the credentials in plain text: to save the credentials as secure object, logon with the same user account used to run the task, and on the machine where the task will be executed. In PowerShell, enter (adjust the path as required, and set the variable in the script accordingly):
Get-Credential | Export-Clixml -Path "C:\Users\bfuchs\caspiotables.xml"

Open in new window

@PowerShell.exe -Command "Invoke-Expression -Command ((Get-Content -Path '%~f0' | Select-Object -Skip 2) -join [environment]::NewLine)"
@exit /b %Errorlevel%

$credentialFile = "C:\Users\bfuchs\caspiotables.xml"
$path = "C:\Users\bfuchs\Dropbox\Caspio\caspiotables.zip"
$destination = "f:\Conversion\Caspio"
$sendMailArgs = @{
	To =			"to@domain.com"
	From =			"from@domain.com"
	Subject =		"Problems with $([IO.Path]::GetFileName($path))"
	SmtpServer =	"smtp.domain.com"
	Credential =	Import-CliXml -Path $credentialFile
	UseSSL =		$true
}

$body = $null
$current = (Get-Date).AddHours(-1)
$copyPath = Join-Path -Path $destination -ChildPath $([IO.Path]::GetFileName($path))
If ($item = Get-Item -Path $copyPath -ErrorAction SilentlyContinue) {
	If ($item.LastWriteTime -lt $current) {
		$body = "File '$($copyPath)' is older than one hour: $($item.LastWriteTime)!"
	} Else {
		"File '$($copyPath)' is current, nothing to do: $($item.LastWriteTime)."
	}
} Else {
	$body = "Expected file '$($copyPath)' not found!"
}
If ($body) {
	Try {
		$copiedItem = Copy-Item -Path $path -Destination $destination -Force -ErrorAction Stop -PassThru
		$body += "`r`nThe file was successfully copied to '$($destination)'."
		If ($copiedItem.LastWriteTime -lt $current) {
			$body += "`r`nThe source file is already older than one hour: $($copiedItem.LastWriteTime)!"
		}
	} Catch {
		$body += "`r`nThe file could not be copied to '$($destination)' either:`r`n$($_.Exception.Message)"
	}
	$body | Write-Warning
	Send-MailMessage @sendMailArgs -Body $body
}

Open in new window

Hi,

Did as instructed, see attached.
also added on top.
$credentialFile = "C:\Users\bfuchs\caspiotables.xml"

Open in new window

but results was same as previous, missing authentication...

Thanks,
Ben
Untitled.png
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:

also added on top.
That wasn't the only change in the script I posted - see line 12 and 13.
Hi,
Did that as well...
What else could be missing?
Thanks,
Ben
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Can't help if you don't post the error you're getting.
See attached script.
Attached error 2nd.

Thanks,
Ben
Untitled.png
Untitled.png
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Add
port = 587
to $sendMailArgs (or whatever port your smtp server expects).
still not working
see attached.

Thanks,
Ben
Untitled.png
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Definitely not an issue with the script, assuming you changed everything as said.
The version below works with my email provider.
The credentials xml file must obviously contain the complete logon information for the SMTP server, very probably the same as in the From field.
According to the documentation, this setup should work just fine:
How to set up a multifunction device or application to send email using Office 365
https://support.office.com/en-us/article/How-to-set-up-a-multifunction-device-or-application-to-send-email-using-Office-365-69f58e99-c550-4274-ad18-c805d654b4c4?ui=en-US&rs=en-US&ad=US

@PowerShell.exe -Command "Invoke-Expression -Command ((Get-Content -Path '%~f0' | Select-Object -Skip 2) -join [environment]::NewLine)"
@exit /b %Errorlevel%

$credentialFile = "C:\Users\bfuchs\caspiotables.xml"
$path = "C:\Users\bfuchs\Dropbox\Caspio\caspiotables.zip"
$destination = "f:\Conversion\Caspio"
$sendMailArgs = @{
	To =			"to@domain.com"
	From =			"from@domain.com"
	Subject =		"Problems with $([IO.Path]::GetFileName($path))"
	SmtpServer =	"smtp.office365.com"
	Credential =	Import-CliXml -Path $credentialFile
	UseSSL =		$true
	Port =			587
}

$body = $null
$current = (Get-Date).AddHours(-1)
$copyPath = Join-Path -Path $destination -ChildPath $([IO.Path]::GetFileName($path))
If ($item = Get-Item -Path $copyPath -ErrorAction SilentlyContinue) {
	If ($item.LastWriteTime -lt $current) {
		$body = "File '$($copyPath)' is older than one hour: $($item.LastWriteTime)!"
	} Else {
		"File '$($copyPath)' is current, nothing to do: $($item.LastWriteTime)."
	}
} Else {
	$body = "Expected file '$($copyPath)' not found!"
}
If ($body) {
	Try {
		$copiedItem = Copy-Item -Path $path -Destination $destination -Force -ErrorAction Stop -PassThru
		$body += "`r`nThe file was successfully copied to '$($destination)'."
		If ($copiedItem.LastWriteTime -lt $current) {
			$body += "`r`nThe source file is already older than one hour: $($copiedItem.LastWriteTime)!"
		}
	} Catch {
		$body += "`r`nThe file could not be copied to '$($destination)' either:`r`n$($_.Exception.Message)"
	}
	$body | Write-Warning
	Send-MailMessage @sendMailArgs -Body $body
}

Open in new window

hi.

The credentials xml file must obviously contain the complete logon information for the SMTP server
All it prompted me was for login and password, and i see these info stored there.
Just tried now with port 25 and same error.
how about if we try writing it in the script (for test purpose)?

Thanks,
Ben
Hi oBda,
This is actually working, I was entering my usernamesame as windows AD (which is how I log on to my Outlook portal), however when tried with full name it worked.
Thank you very much!

P.S. I should leave file as is and run that from task scheduler?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Yes, that's why it's "disguised" as batch. You can just use it as the program to run.
Note that the file with the credentials can only be decrypted on the machine where it was created, and by the account that encrypted it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial