Archive for category: Scripting

Running automated processes seems to be an very easy task. However, this may become a little bit more complicated for running critical unattended operations. Imagine the situation when you have to run critical, automated process at night while your business relies on successful completion of it.

For example, if you are running ETL process at night that loads the data to be available for the users next morning. You want to be warned if something is wrong as you won’t have enough time to load it next morning say before 9am. During the process, you may get either data or the infrastructure related error – one of the servers may be down for a few seconds causing entire operation to fail.

The script below resolves some of the potential problems that may occur in similar scenario by auto-retrying processes on error and sending SMS alerts directly to your mobile, so you will probably be alerted much earlier unless you read emails at night 🙂

In the script, you can define multiple input files to be run by the process you define. For example you may define multiple dtx packages to be run one by one. Whatever the process will run, you need to make sure that it will return 0 on successful completion or return non zero integer on error. If the exe process will not close itself returning the exit code, then it will hang forever.

Except for input files and exe process, you also need to define execution wait times, email recipient list, smtp server and database connection if you need to log audit information. If you want to send SMS alerts, you will also need to create account on txtlocal.co.uk.

For this example I have defined three csv files (data1.csv, data2.csv, data2.csv) to be run in notepad (with auto retry twice for each of them), closing notepad manually will simulate successful completion. Of course you need to adjust it to your requirements.

 param (
[string[]]$inputFiles = @(
#add paths to more files if needed here
#format: input file path;NumberOfRetries,
#########################################

"c:\data1.csv;2",
"c:\data2.csv;2",
"c:\data3.csv;2",

#########################################
""),
[string]$exePath = "C:\Windows\System32\notepad.exe",
[string]$LoadSuccessfulNotificationList = "email1@mydomain.com;email2@mydomain.com",
[string]$ErrorNotificationList = "email1@mydomain.com;email2@mydomain.com",
[int]$RetryWaitTimeSec = 60,
[int]$NextFileExecWaitTimeSec = 3,
[string]$connString = "Data Source=myServer,1433;Initial Catalog=MyDatabase;integrated security=SSPI;persist security info=False;Trusted_Connection=Yes"
)

[System.Reflection.Assembly]::LoadWithPartialName("System.Web") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions") | out-null

##define phone numbers to send error alerts to (format: 441234567890,441234567892)
$global:numbers = [System.Web.HttpUtility]::UrlEncode("441234567890");

#####################no need to edit below

$global:LogInfo = "";
$global:SmsLogInfo = "";
$global:errorFlag = 0;
[int]$firstFlag = 1;


function SendSms([string]$smsMessage)
{
    if($smsMessage.trim().Length -eq 0) { return;}
    if($smsMessage.trim().Length -gt 155) 
    {
      $smsMessage = $smsMessage.trim().Substring(0,150) + "...";
    }

    $username = [System.Web.HttpUtility]::UrlEncode("mail@mydomain.com");
    $hash = [System.Web.HttpUtility]::UrlEncode("my hash - get it from txtlocal.co.uk");##if not working please check credentials at https://control.txtlocal.co.uk/docs/
    ###
    $message = [System.Web.HttpUtility]::UrlEncode($smsMessage);
    $sender = [System.Web.HttpUtility]::UrlEncode("My alerts");
    ###
    $data = 'username=' + $username + '&hash=' + $hash + '&numbers=' + $global:numbers + "&sender=" + $sender + "&message=" + $message;
    $response = Invoke-WebRequest -Uri "https://api.txtlocal.com/send/?$data";
    ###
    $ser = New-Object System.Web.Script.Serialization.JavaScriptSerializer;
    $result = $ser.DeserializeObject($response.Content);

    return $result;
}

Function LogToDatabase($exitCode,$inputFilePath,$duration,$cpu,$description)
{
    $inputFileName = [System.IO.Path]::GetFileName($inputFilePath);

    $SqlConnection = new-object System.Data.SqlClient.SqlConnection;
    $SqlConnection.ConnectionString = $connString;
    $SqlCommand = $SqlConnection.CreateCommand();
    $SqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure;
    $SqlCommand.CommandText = "MonitorAudit_Save";

    $SqlCommand.Parameters.AddWithValue("@PCName", [Environment]::MachineName + " - " + [Environment]::UserDomainName + "/" + [Environment]::UserName) 
    $SqlCommand.Parameters.AddWithValue("@ExePath", $exePath) | Out-Null

    $SqlCommand.Parameters.AddWithValue("@ExitCode", $exitCode) | Out-Null
    $SqlCommand.Parameters.AddWithValue("@InputFilePath", $inputFilePath) | Out-Null
    $SqlCommand.Parameters.AddWithValue("@InputFileName", $inputFileName) | Out-Null

    $SqlCommand.Parameters.AddWithValue("@Duration", $duration) | Out-Null

    $SqlCommand.Parameters.AddWithValue("@CPU", $cpu) | Out-Null
    $SqlCommand.Parameters.AddWithValue("@Description", $description) | Out-Null

    $SqlConnection.Open()
    $SqlCommand.ExecuteNonQuery()
    $SqlConnection.Close()
}

 Function SendEmail($SendEmailsTo_, $EmailSubject_, $changes_)
 {
   $emailFrom = "errors@mydomain.com" 
   $smtpserver="mysmtp_server" 
   $smtp=new-object Net.Mail.SmtpClient($smtpServer) 
 
   foreach ($email in $SendEmailsTo_.split(';'))
   {   
      if($email.Length -gt 0)
      {
        $smtp.Send($emailFrom, $email, $EmailSubject_, $changes_);
      }
   }
 }
 
$inputFiles | Foreach-Object {
   
  if($_.trim().Length -gt 0)  {

      [int]$numOfRetries = 1;
      if($_.split(';').Length -gt 1) {
        $numOfRetries = $_.split(';')[1]; if($numOfRetries -lt 1) { $numOfRetries = 1 }
      }

      if($firstFlag -eq 0 -and $NextFileExecWaitTimeSec -gt 0){
        $global:LogInfo += "Waiting $NextFileExecWaitTimeSec seconds to execute next input file...`r`n`r`n";
        Start-Sleep -s $NextFileExecWaitTimeSec
      }
      if($firstFlag -ne 0) { $firstFlag = 0;}

      for ($i = 0; $i -le $numOfRetries -1; $i++) {
       try
        {
           [string]$inputFilePath = $_.split(';')[0]; "processing $inputFilePath ..."; $dateStart = (Get-Date).ToString(); [DateTime]$dateStartObj = (Get-Date);

           if(-not(Test-Path $inputFilePath))
           {
              throw New-Object System.Exception ("Input file not found: $inputFilePath");
           }

           if(-not(Test-Path $exePath))
           {
              $global:errorFlag =1;
              throw New-Object System.Exception ("Process exe not found: $exePath");
           }

           #add -windowstyle Hidden when in production
           $exeProcess= Start-Process $exePath -Wait -PassThru -ErrorAction SilentlyContinue  -WarningAction SilentlyContinue -ArgumentList " `"$inputFilePath`"" 
           

           #log info
           $cpu = $exeProcess.CPU.ToString("##.#"); $dateEnd = (Get-Date).ToString(); $global:LogInfo += " $inputFilePath `r`nAverage CPU: $cpu | Start time $dateStart | End time $dateEnd`r`n`r`n";

           if(-not $exeProcess.ExitCode -eq 0)
           {
              throw New-Object System.Exception ("execution error");
           }
           
           #uncomment below line to log to database
           #LogToDatabase $exeProcess.ExitCode $inputFilePath (Get-Date).Subtract($dateStartObj).TotalSeconds $exeProcess.CPU.ToString("##.#") "OK" | Out-Null

           break;
        } 
        catch { 
           $global:errorFlag =1;
          try
           {
           $inputFileName = [System.IO.Path]::GetFileName($inputFilePath);
           } catch { $inputFileName = ""; }

           $msg = "$LogInfo Unexpected Error when processing $inputFileName. Error: $_";  Write-Host $msg -foreground Red;
           $global:LogInfo += "###########`r`nUnexpected Error when processing $inputFileName`r`n" + "Error: $_ `r`n##########`r`n`r`n";
 
           $global:SmsLogInfo += "Error processing: " + $inputFileName + " |"; 

           $cpu = "0";
           if ($exeProcess -ne $null) { $cpu = $exeProcess.CPU.ToString("##.#"); }

           #uncomment below line to log to database
           #LogToDatabase 1 $inputFilePath (Get-Date).Subtract($dateStartObj).TotalSeconds $cpu "Error: $_" | Out-Null

           #SendEmail $ErrorNotificationList "loading error" $msg
           if($i -gt $numOfRetries - 1 -or $i -eq $numOfRetries -1) { break;};

           #retry it
           $global:LogInfo += "Waiting $RetryWaitTimeSec seconds to retry...`r`n`r`n";
           Start-Sleep -s $RetryWaitTimeSec 
        } 
      }
   }
} 

if($errorFlag -eq 0) {
   SendEmail $LoadSuccessfulNotificationList "Loading process successful" "Processing has been successful for following input files: `r`n-------------------------`r`n$LogInfo Have a nice day :)";
}
else
{
   SendEmail $ErrorNotificationList "Loading process finished with errors" "Processing has NOT been successful for some of the input files: `r`n-------------------------`r`n$LogInfo";
   SendSms $global:SmsLogInfo;
}

Enjoy!
Automated-execution