TechCartNow.com

Utility to schedule automatic daily alert notification based on data retrieved from MySql database table: PowellShell script for reading data from MySql Database & Sending email

by · August 28, 2020 · 350 Views

Recently, I have created a small utility to schedule  a daily alert notification to the user based on data retrieved from one of my MySql database table (Alerts table: which contains messages related to my application level violations). In this post, I will explain how that can be achieved in few steps using a simple PowerShell script.

Step 1: PowellShell script (database-alerts.ps1) for reading data from MySql Database & Sending email

Script (database-alerts.ps1): In this script, we are connecting to database (techcartnow), reading today’s messages form alerts table & sending emails if there are any violation messages retrieved from database.

Try
{
  [void][system.reflection.Assembly]::LoadFrom(“C:\Program Files (x86)\MySQL\Connector NET 8.0\Assemblies\v4.5.2\MySQL.Data.dll”)

  #Create a variable to hold the connection:
  $myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection

  #Set the connection string:
  $myconnection.ConnectionString = "Data Source=127.0.0.1; Initial Catalog=techcartnow; uid=root; Password=root; Port=3306"

  #Call the Connection object’s Open() method:
  $myconnection.Open()
  
  $SMTPServer = "smtp.office365.com"
  $SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 587)
    
  Try
  {
    $command = $myconnection.CreateCommand()
    
    $command.CommandText = "SELECT Message FROM alerts where DATE(CreatedDateUtc) = DATE(UTC_DATE());";

    $reader = $command.ExecuteReader()
    #The data reader will now contain the results from the database query.

    #Processing the Contents of a Data Reader
    #The contents of a data reader is processes row by row:

    $Body = "";

    while ($reader.Read()) {
      #And then field by field:
      for ($i= 0; $i -lt $reader.FieldCount; $i++) {
      $Body += $reader.GetValue($i).ToString() + [Environment]::NewLine
      }
    }

    if($Body -ne "")
    {
      $EmailFrom = "alert@techcartnow.com"
      $EmailTo = "kapil.khandelwal@techcartnow.com"
      $Subject ="ALERT:: Violation Alert."
      $Body = "Following violation were found: " + [Environment]::NewLine + $Body
      $SMTPClient.EnableSsl = $true
      $SMTPClient.Credentials = New-Object System.Net.NetworkCredential("alert@techcartnow.com", "Password");
      $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)
    }

    $reader.Close()
    $reader.Dispose()
    write-output "Success"
  }
  Catch
  {
    write-output "Failure:: Innner"
  }
  Finally
  {
    write-output "Cleanup Started"
    $myconnection.Close()
    $myconnection.Dispose()
    $SMTPClient.Dispose()
    write-output "Cleanup Finished"
  }
}
Catch
{
  write-output "Failure:: Outer"
}

Note: Change the Database connection & SMTP settings

Step 2:Use “Windows Task Scheduler” for executing the PowerShell script on daily at a given time.

PowerShell Alert Notification

Here:

Program: powershell

Arguments: -File C:\database-alerts.ps1

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *