Monday, June 26, 2017

Quick & Dirty FDMEE File Trigger Alarm

From time to time, our FDMEE data integration jobs depend upon an upstream system delivering a file to us.  Things can get hectic when the file isn't delivered on time, due to a delay or hiccup upstream.

This post offers a set of scripts you may automate, so that you can stay informed whenever a file is late to arrive.

Script #1: The DOS wrapper script

echo off
REM FileAge.bat
REM
REM This script sends an email if a specified file has a timestamp
REM that is older than a specified number of minutes.
REM
REM Syntax:  FileAge.bat \full\path\to\file Minutes
REM Example: FileAge.bat D:\Oracle\FDMEE\Inbox\FileTrigger.txt 120
REM The above example would test if the file is 2 hours old or not.
REM
REM  Written on 10/01/2014 by Dave Shay (Datavail)
REM Modified on 12/15/2014 by Dave Shay - Added check for prior alert to prevent duplicate emails.
REM Modified on MM/DD/YYYY by Your Name - Briefly list changes

REM Variable declaration section
REM ----------------------------------------------

REM Multiple emails may be specified in the EMAILDISTRO variable.
REM Use a comma-delimited list of emails with no spaces

set EMAILDISTRO=HypAdmins@yourcompany.com
set EMAILFROM=DoNotReply@yourcompany.com

REM Internal company email server settings.
REM Consult your IT department regarding the SMTP server name.
REM The SMTP server should allow anonymous relaying inside the company firewall.
set SMTPSERVER=smtp.yourcompany.com

set ALARMFLAG=%1.alarm

powershell.exe D:\Scripts\FileAge.ps1 %1 %2
 
if %errorlevel% == 0 (
 if exist %ALARMFLAG% (   
  echo All Clear email is sent out.
  powershell.exe Send-MailMessage -from %EMAILFROM% -To %EMAILDISTRO% -Subject 'All Clear %1 File Watcher' -smtpServer %SMTPSERVER% -Body 'The file %1 has been updated on %COMPUTERNAME%. Please do not reply to this automated message.'

  DEL %ALARMFLAG% 
  exit 0
 )
 exit 0
)

if %errorlevel% == 1 (
 if exist %ALARMFLAG% (
  echo File is older than the specified number of minutes. Sending email.
  exit 0
 )
        
 echo File is older than the specified number of minutes. Sending email.
 powershell.exe Send-MailMessage -from %EMAILFROM% -To %EMAILDISTRO% -Subject '%1 File Watcher' -smtpServer %SMTPSERVER% -Body 'File %1 on %COMPUTERNAME% is older than %2 minutes.  Please check the servers for hung processes.  Please do not reply to this automated message.'
 echo "Alarm conditions exits" > %ALARMFLAG%
 exit 0
)

Script #2: The PowerShell script invoked behind the scenes

# FileAge.ps1
#
# This Windows PowerShell script compares a specified file against
# a specified number of minutes.  If the file's timestamp indicates
# an age that is equal to or newer than the specified number of minutes,
# then a 0 is returned.  Otherwise a 1 is returned.
#
# Syntax: powershell FileAge.ps1 \full\path\to\file minutes
# Example: powershell FileAge.ps1 D:\oracle\FDMapp\Inbox\FileTrigger.txt 120
# The above example would test if the file is 2 hours old or not.
#
# If you receive a security policy error about “unsigned” Powershell scripts when 
# running this process, open a command prompt and type:
# powershell.exe Set-ExecutionPolicy Unrestricted
#
#  Written on 10/01/2014 by Dave Shay (Datavail)
# Modified on MM/DD/YYYY by Your Name - Briefly list changes

param($FileToCheck, $Minutes)

$lastWrite = (get-item $FileToCheck).LastWriteTime
$timespan = new-timespan -minutes $Minutes

if (((get-date) - $lastWrite) -gt $timespan) 
{
 # File is older than specified number of minutes
    exit 1
} else 
{
 # File is equal to or newer than specified number of minutes
    exit 0
}

Putting it all together!


One time only, open a DOS command prompt and paste this command, so that your Powershell script will work:

powershell.exe Set-ExecutionPolicy Unrestricted

Now you're ready to go into the Windows Task Scheduler, or your favorite scheduling tool, and create a job that fires off:

D:\Scripts\FileAge.bat \path\to\file number_of_minutes

For example:

D:\Scripts\FileAge.bat D:\Oracle\FDMEE\inbox\hourlyload.txt 59

In this example, you could setup the job to run hourly, and it would send an email alert if you hourlyload.txt file is 59 minutes old or older.

No comments:

Post a Comment