Monitor a folder and send email and check SQL Server

[abenitez77]

I am using powershell to check a folder and when a file gets added to the folder, it queries a sql table and if there hasn't been a file added in the last 6 hours then it will send an email to several people letting them know that the file was copied/uploaded to that folder.

I can send the email when a file gets added, but when I added the code to check the SQL table, it stopped working. Can someone help me figure out the rest of this script?

# make sure you adjust this to point to the folder you want to monitor
$PathToMonitor = "U:\temp\test"

explorer $PathToMonitor

$FileSystemWatcher = New-Object System.IO.FileSystemWatcher
$FileSystemWatcher.Path  = $PathToMonitor
$FileSystemWatcher.IncludeSubdirectories = $true

# make sure the watcher emits events
$FileSystemWatcher.EnableRaisingEvents = $true

# define the code that should execute when a file change is detected
$Action = {
    $details = $event.SourceEventArgs
    $Name = $details.Name
    $FullPath = $details.FullPath
    $OldFullPath = $details.OldFullPath
    $OldName = $details.OldName
    $ChangeType = $details.ChangeType
    $Timestamp = $event.TimeGenerated 
    $JustPath = Path.GetDirectoryName($FullPath)

    # SQL Work ---------------------------------------------------------------------
    $Server = 'SQL01'
    $Database = 'FTP_Uploads'
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection

    $text = "{0} was {1} at {2}" -f $FullPath, $ChangeType, $Timestamp

    $sql = "IF Not Exists (Select 1 From Transmit Where DateDiff(IsNull(TimeGenerated, '01/01/2020 01:00:00 PM'), '$Timestamp') < 6 ) AND PathOnly = '$JustPath' ) 
            BEGIN 
            Insert Transmit(FullPath, PathOnly, TimeGenerated)  
            Values('$FullPath', '$JustPath', '$Timestamp')
            END " 


    Write-Host ""
    Write-Host $text -ForegroundColor Green
    Write-Host $sql

    # you can also execute code based on change type here
    switch ($ChangeType)
    {
                'Created' { 
                    # Check SQL to see if there has been a file ftp'd in the last 6 hours ------ 
                    $Command.CommandText = $sql
                    $Command.ExecuteReader()
                    $Connection.Close()

                    # Send Email ---------------------------------
                    $EmailFrom = “email1@domain1.com”
                    $EmailTo = “email2@domain2.com, email3@domain3.com”
                    $Subject = “FTP Notification” 
                    $Body = $text 
                    $SMTPServer = “smtp.office365.com”
                    $SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)
                    $SMTPClient.EnableSsl = $true
                    $SMTPClient.Credentials = New-Object System.Net.NetworkCredential(“email1@domain1.com”, “password”);
                    $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)
                    Start-Sleep -Seconds 5
                    $SMTPClient.Dispose() 

                    # this executes only when a file was renamed
                    $text = "File {0} was Created" -f $FullPath
                    Write-Host $text -ForegroundColor Yellow
                  }

        default { Write-Host $_ -ForegroundColor Red -BackgroundColor White }
    }
}

# add event handlers
$handlers = . {
    Register-ObjectEvent -InputObject $FileSystemWatcher -EventName Changed -Action $Action -SourceIdentifier FSChange
    Register-ObjectEvent -InputObject $FileSystemWatcher -EventName Created -Action $Action -SourceIdentifier FSCreate
    Register-ObjectEvent -InputObject $FileSystemWatcher -EventName Deleted -Action $Action -SourceIdentifier FSDelete
    Register-ObjectEvent -InputObject $FileSystemWatcher -EventName Renamed -Action $Action -SourceIdentifier FSRename
}

Write-Host "Watching for changes to $PathToMonitor"

try
{
    do
    {
        Wait-Event -Timeout 1
        Write-Host "." -NoNewline

    } while ($true)
}
finally
{
    # this gets executed when user presses CTRL+C
    # remove the event handlers
    Unregister-Event -SourceIdentifier FSChange
    Unregister-Event -SourceIdentifier FSCreate
    Unregister-Event -SourceIdentifier FSDelete
    Unregister-Event -SourceIdentifier FSRename
    # remove background jobs
    $handlers | Remove-Job
    # remove filesystemwatcher
    $FileSystemWatcher.EnableRaisingEvents = $false
    $FileSystemWatcher.Dispose()
    "Event Handler disabled."
}

 

[fredericofonseca]

First thing that is wrong is that ExecuteReader - as you are not returning a recordset you should use instead ExecuteNonQuery (or similar)

I would also add some try/catch blocks around the opening of the connection and the execution of the command and log any errors explicitly - it is possible that the fact that this is an event handler is hiding some errors

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886