Check Updates to file

andyh · January 22, 2021, 5:01am

I’m trying to make sure a file in a folder on Network share is being updated. There is a new file everyday is the folder at
midnight. I have pieces of code, but having trouble putting it together to get the desired results.

This is my requirements.

First check to make sure server is running – srv1
Make sure I find the most recent file and it’s current as of today\ when I run this script.
Make sure file has been updated in the last 15 minutes.
Send alerts if
a) server unavailable
b) File recording data isn’t created as of today
b) No updates to file in past 15 minutes.

The file has a Header and this is the data:
2021-01-19 23:59:34;0;1

Thanks.

#$path = ‘\svr1\Environmental\Environmental_Data*.csv’
$path = ‘c:\temp\Environmental_Data*.csv’
$file = Get-ChildItem -Path $Path |?{-not $_.PsIsContainer} | Sort CreationTime | Select -Last 1

Write-Output "The most recently created file is $($file.Name)"

$Line=Get-Content -Path $file | Select-Object -last 1

$Line

ralphmwr · January 22, 2021, 5:38am

You have a good start. I think I would start by working through each one of your 4 requirements.

There are many ways you can do this. Maybe the simplest is a ping. PowerShell has a native cmdlet for that.

$serverup = Test-Connection -ComputerName "svr1" -Quiet

Now $serverup will be $True or $False depending on the outcome of the Test-Connection cmdlet

Looks like you already got a good start on this with the code you posted. If you are using PS Vers 5.1 or later, you can make use of the -file switch for Get-ChildItem that will make your code a little cleaner although doubtful any directories will contain “.csv” at the end of the title.

$file = Get-ChildItem -Path $Path -File | 
            Sort-Object -Property CreationTime | 
                Select-Object -Last 1

$current = $file.CreationTime.Date -eq (Get-Date).Date

Now $current will be $true or $false if it matches today’s date

Since you didn’t give much detail on the csv file, I assume new data is appended so the latest is at the bottom. Also, you didn’t give the headers so I don’t know the property names.

$lastupdate = Import-Csv $file.fullname -Delimiter ";" | Select-Object -Last 1

ralphmwr · January 22, 2021, 5:42am

I forgot the “Send Alerts” part of your requirement. Not sure what kind of alert you want to send. It could be log entry, write to the host, email, etc. I would get everything working first before sending alerts.

andyh · January 22, 2021, 5:43am

The file details are:
2021-01-19 23:59:34;0;1

Header - Update_Date_Time;sw1;sw2

And yes the newest record is at the bottom of the file. I’m looking to send the alert to an email group.

Thanks.

ralphmwr · January 22, 2021, 6:29am

ok. Given those headers you can reference the properties needed with the property dereference operator (.) like this:

$updatecurrent = [datetime]$lastupdate.Update_Date_Time -ge (Get-Date).AddMinutes(-15)

Now $updatecurrent will be $true or $false

As far as email alerts go, there is a Send-MailMessage cmdlet that MAY work, although when you read the documentation (Get-Help Send-MailMessage), it says it is obsolete, so no promises. If that doesn’t work for you, you’ll probably have to look at other options like GitHub - jstedfast/MailKit: A cross-platform .NET library for IMAP, POP3, and SMTP.

Now all you need to do is put this together. I’ve walked through each step. Just finish it off with a control statement like this:

if (-not ($serverup -and $current -and $updatecurrent)) {
    #send alert here
}

Post back with your formatted script and if you have any errors.

andyh · January 22, 2021, 7:10am

Ok I’ll give it a shot putting the pieces together.

THanks for replies … will post back

andyh · January 26, 2021, 6:00am

I tried to create an email then call that piece based upon conditions, but I have something wrong in my logic.

# Define parameters for the send mail message cmdlet

$EmailSplat = @{
    
	To = 'xxxx'
	SmtpServer = 'xxxx'
    From = 'xxxxx'
    Priority = 'High'
}

$serverup = Test-Connection -ComputerName "xxxxxxxx" -Quiet

$serverup

$path = 'c:\temp\Environmental_Data*.csv'
$file = Get-ChildItem -Path $Path -File | 
            Sort-Object -Property CreationTime | 
                Select-Object -Last 1

$current = $file.CreationTime.Date -eq (Get-Date).Date

$updatecurrent = [datetime]$file.Update_Date_Time -ge (Get-Date).AddMinutes(-15)

$updatecurrent

# Your first condition: 'Check if the server is reachable'
if (-not $serverup) {
   write-host 'Server Connection issue'
    $EmailSplat.Subject = 'Server Check'
    $EmailSplat.Body = 'Shared folder not reachable.'
    write-host @EmailSplat
    Send-MailMessage @EmailSplat
	exit 5



# Your second condition 'If the file exists and was created today, but has no content.'
} elseif (($file) -and ($current -eq (Get-Date).Date)) {
   # Do 
   write-host 'Found but Empty'
    $EmailSplat.Subject = 'File Found'
    $EmailSplat.Body = 'File Not Created Today'
    Send-MailMessage @EmailSplat



# Your second condition 'If the file exists and was created today, but hasn't been updated in last 15 Minutes.'
} elseif ($updatecurrent) {
   # Do 
   write-host 'Found but not Updated!!!'
    $EmailSplat.Subject = 'File Found'
    $EmailSplat.Body = 'File not updated in last 15 Minutes.'
    Send-MailMessage @EmailSplat


# Your third condition and the default condition if it does not match the other conditions
} else {
    write-host 'Normal Processing'
    $EmailSplat.Subject = 'File Found'
    $EmailSplat.Body = 'Normal Processing.'
    Send-MailMessage @EmailSplat
}

andyh · January 26, 2021, 6:01am

I tried to create an email then call that piece based upon conditions, but I have something wrong in my logic.

# Define parameters for the send mail message cmdlet

$EmailSplat = @{
    
	To = 'xxxx'
	SmtpServer = 'xxxx'
    From = 'xxxxx'
    Priority = 'High'
}

$serverup = Test-Connection -ComputerName "xxxxxxx" -Quiet

$serverup

$path = 'c:\temp\Environmental_Data*.csv'
$file = Get-ChildItem -Path $Path -File | 
            Sort-Object -Property CreationTime | 
                Select-Object -Last 1

$current = $file.CreationTime.Date -eq (Get-Date).Date

$updatecurrent = [datetime]$file.Update_Date_Time -ge (Get-Date).AddMinutes(-15)

$updatecurrent

# Your first condition: 'Check if the server is reachable'
if (-not $serverup) {
   write-host 'Server Connection issue'
    $EmailSplat.Subject = 'Server Check'
    $EmailSplat.Body = 'Shared folder not reachable.'
    write-host @EmailSplat
    Send-MailMessage @EmailSplat
	exit 5



# Your second condition 'If the file exists and was created today, but has no content.'
} elseif (($file) -and ($current -eq (Get-Date).Date)) {
   # Do 
   write-host 'Found but Empty'
    $EmailSplat.Subject = 'File Found'
    $EmailSplat.Body = 'File Not Created Today'
    Send-MailMessage @EmailSplat



# Your second condition 'If the file exists and was created today, but hasn't been updated in last 15 Minutes.'
} elseif ($updatecurrent) {
   # Do 
   write-host 'Found but not Updated!!!'
    $EmailSplat.Subject = 'File Found'
    $EmailSplat.Body = 'File not updated in last 15 Minutes.'
    Send-MailMessage @EmailSplat


# Your third condition and the default condition if it does not match the other conditions
} else {
    write-host 'Normal Processing'
    $EmailSplat.Subject = 'File Found'
    $EmailSplat.Body = 'Normal Processing.'
    Send-MailMessage @EmailSplat
}

ralphmwr · January 26, 2021, 6:06am

Are you getting errors? What is happening?

andyh · January 26, 2021, 6:14am

Cannot convert null to type “System.DateTime”.
At C:\powershell\check_file_and alarm.ps1:22 char:1

$updatecurrent = [datetime]$file.Update_Date_Time -ge (Get-Date).AddM …
CategoryInfo : InvalidArgument: ( , RuntimeException
FullyQualifiedErrorId : nullToObjectInvalidCast

ralphmwr · January 26, 2021, 6:46am

I see. What the error is telling you is that $file.update_date_time is $null. Which makes sense because $file is a file object and there is no such property as update_date_time in a file object. What you want is the last row in that csv file which has a property called update_date_time because you told me that was one of the headers. So you need to change

$updatecurrent = [datetime]$file.Update_Date_Time -ge (Get-Date).AddMinutes(-15)

To this

$lastupdate = Import-Csv $file.fullname -Delimiter ";" | Select-Object -Last 1
$updatecurrent = [datetime]$lastupdate.Update_Date_Time -ge (Get-Date).AddMinutes(-15)

andyh · January 26, 2021, 7:03am

Thanks I think that did it. DO you see anything else that may make it more useful? Could I log the info to a SQL table so I have history in case of email
send failure?

Thanks for replies and advice.

andyh · January 26, 2021, 7:10am

I did notice that if no entry in last 15 minutes it error’d with: The last entry was over 8 hours ago.

Cannot convert null to type “System.DateTime”.
At C:\powershell\check_file_and alarm.ps1:23 char:1

$updatecurrent = [datetime]$lastupdate.Update_Date_Time -ge (Get-Date …
CategoryInfo : InvalidArgument: ( , RuntimeException
FullyQualifiedErrorId : nullToObjectInvalidCast

ralphmwr · January 26, 2021, 8:03am

You can always tinker with the code to make it cleaner or more useful. You might want to put the whole thing in an endless while loop so it runs every 30 min i.e.

While ($true) {

   #... code here

   Start-Sleep -Seconds 1800
} #end while loop

You could also create a Windows Task to run the script at a specific time. You are limited to your own imagination.

I’m sure you could but it would be much more complex. You might want to simplify it and just write data to a plain text file or csv somewhere. See Export-Csv and Set-Content

The error message tells you what you need to know. Look at the last line of the csv file. This is saying that the Update_Date_Time column is null (empty). When troubleshooting these types of issues, always look at the error message and figure out what it is telling you. Then you can either step through in debug mode (ISE or VSCode) to see the values of variables at different points of execution or just add print outs to make it return values to the screen. For example you could add a $lastupdate printout to see what was returned from your Import-Csv command.

andyh · January 26, 2021, 8:03am

I had an error in the delimeter, so that resolved the error, but how about the logging feature(SQL - Table)?

Thanks.

ralphmwr · January 26, 2021, 8:07am

[quote quote=288145]I had an error in the delimeter, so that resolved the error, but how about the logging feature(SQL – Table)?

Thanks.

[/quote]
Looks like we replied at the same time. See my last. If you really want to go with SQL there is a module for that, but like I said it may be more complicated than necessary. I’ll add one other suggestion if you don’t want to just write the data to a file (Export-Csv or Set-Content) and that is create custom log entries New-WinEvent.

https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15

andyh · January 26, 2021, 8:12am

Could I find last entry in file not Null and compare against that?

ralphmwr · January 26, 2021, 8:16am

[quote quote=288154]Could I find last entry in file not Null and compare against that?

[/quote]
Absolutely. Just filter for it.

$lastupdate = Import-Csv $file.fullname -Delimiter ";" | 
    Where-Object {$_.Update_Date_Time} |
        Select-Object -Last 1

The question is why is it empty?

andyh · January 27, 2021, 2:24am

I was also thinking checking for last entry is good to make sure it didn’t missing writing, but that will require that I schedule a task to run more
frequently. Is there a way to say check for gaps in the recordings? What I mean would be to schedule process hourly, but see if there where any
gaps in that time frame that where longer than 15 minutes. The process writing to csv is supposed to record an entry every minute and a new file
begins at midnight.

2021-01-27 00:01:00;0;1
2021-01-27 00:02:00;0;1
<><> Gap <><>
2021-01-27 00:20:00;0;1
2021-01-27 00:21:00;0;1
…
If I ran the process at say 1:00am it would detect that readings where missed <><>Gap<><>. (15 minute gap of no recordings).

Thanks.

ralphmwr · January 27, 2021, 3:05am

Sure. You would need to iterate through each row and do the math to check the time against the previous row. For example:

$updates = Import-Csv $file.fullname -Delimiter ";"

$gaps = foreach ($update in $updates) {
    if ($prevupdate -and ($update.Update_Date_Time -gt $prevupdate.AddMinutes(15))) {
        [pscustomobject]@{Time1=$prevupdate;Time2=$update.Update_Date_Time}
    } #if    
    $prevupdate = $update.Update_Date_Time
} # foreach loop

$gaps

Topic		Replies	Views
best way to work with versioning PowerShell Help	1	154	May 12, 2020
Help with check write date and email send PowerShell Help	2	406	August 14, 2020
Help with 'LastWriteTime' - Folder Checking script PowerShell Help	1	156	January 20, 2022
Watch Folder For A Missing File PowerShell Help	4	173	December 5, 2013
Copy latest file from a path location to Sharepoint PowerShell Help	1	164	September 22, 2021

Check Updates to file

Related Topics