## Wednesday, October 4, 2017

### HFM 11.1.2.4.205 and SmartView

Oracle has acknowledged a bug in HFM 11.1.2.4.205 PSU and is working on a fix.

The observed behavior is you have an HFM data grid open in SmartView and you get the session expired / disconnected message well in advance of the defined session timeout setting.

I've noticed this seems to happen more frequently if your user has a bunch of VB SmartView retrieval functions on their retrieval template instead of defining a traditional ad-hoc grid.  This is pure speculation on my part as I don't have too many systems I'm supporting that are on this particular patch.  It seems that each VB retrieval cell maintains its own connection, and apparently once one cell gets disconnected, all of them do and your back-end XFM ODL logs are flooded with messages.

Oracle Development has indicated the bug will be fixed in the upcoming patch HFM 11.1.2.4.206 PSU.

For now, the only known workaround available for the issue is to downgrade to HFM 11.1.2.4.204 PSU.

## Monday, September 25, 2017

### Apply CVE-2017-9805 today - updated top of post

Update:  Please scroll through the comments and review the comment posted by user cjratliff on October 4, 2017 at 3:02 PM.  He has posted a tip about Oracle Knowledge Base article # 2255054.1 and a response from Oracle Support that I have yet to receive on my SR.  Ugh!!!  This stuff is a nightmare to navigate. Thank you, cjratliff .

Update Part Two:  This information in this blog post is now obsolete.  I have kept the original content as-is for historical reference purposes.  As of April 2018, there is a newer WebLogic Server 10.3.6 patch, designated patch # "GFWX" 27395085.  To fully remediate the security issues address by this patch, you need (as per Oracle):

"Supported versions of Java SE are: JDK6u191, JDK7u181 and JDK8u172. Your version must be one of these versions or higher."

There was a bit of buzz on the Internet a few weeks ago concerning a zero-day exploit discovered within Apache Struts 2.  I did some sleuthing around in my EPM 11.1.2.4 on-premises lab and found evidence of Struts within several WebLogic processes (Calculation Manager, EPM Foundation, and more).

This past Friday, September 22, Oracle issued a Security Alert they've named CVE-2017-9805.  This includes a fix for Apache Struts 2 within WebLogic 10.3.6, which every EPM 11.1.2.3 and 11.1.2.4 system on the face of the planet uses behind the scenes.

The procedure to apply this patch is different from how we normally apply EPM patches.  On each server in the environment where you have a folder named \Oracle\Middleware\wlserver_10.3, you will want to edit this file:

Windows:
\Oracle\Middleware\utils\bsu\bsu.cmd

UNIX:
/Oracle/Middleware/utils/bsu/bsu.sh

Modify the MEM_ARGS line to be as so:

set MEM_ARGS=-Xms2048m -Xmx2048m

If you don't do this, the patch utility runs for a long time and then fails with an OutOfMemory exception error.

Next, copy the unzipped contents of the patch into this folder:

\Oracle\Middleware\utils\bsu\cache_dir

You would then shutdown EPM web services and execute the bsu script you edited earlier, and examine the output.

I  would take things 1 step further and blow away the /cache and /tmp folders for each WebLogic Managed Server underneath \Oracle\Middleware\user_projects\domains\EPMSystem\servers

Stay safe out there!

Update: I recommend using the command-line patch interface rather than the graphical interface. On a 4.0GHz machine with solid state disk, it takes 12 minutes just for the GUI to render. Another 12 minutes to process the patch after you are given the opportunity to click. With the command-line interface, you can eliminate the first 12 minutes.

Also, this patch has a conflict with the April 2017 critical patch "RVBS". RVBS needs to be rolled back first. This adds another 12 minutes.

## Wednesday, August 30, 2017

### Financial Reporting 11.1.2.4.706 web designer 403-Forbidden error

The latest patch for Hyperion Financial Reporting, which is Patch Set Update 11.1.2.4.706, contains a flaw that causes the web designer to be unusable.  We get a 403-Forbidden error when trying to connect to the web designer URL http://OHShost:19000/frdesigner

This worked in PSUs 11.1.2.4.700 through 11.1.2.4.705.

I've opened an SR with Oracle and will share the solution once it is discovered.

## Tuesday, August 29, 2017

### ASO, ESSCMDQ, and Essbase 11.1.2.4.015+

Some of my clients automate EPMA's command line batch utility to pump metadata updates into ASO Essbase cubes.  Anybody who has done this for any length of time knows about the ASO design flaw involving an internal dimension restructure counter that is capped at 255.

Oracle published a workaround for this a while ago, in the form of the ESSCMDQ utility.  Once installed and we automate the suggested ASO restructure script, this was a reliable way to avoid the 255 limitation.... until very recently!

A few patches ago, ESSCMDQ started behaving... erratically.  Sometimes the restructure script would complete, and other times ESSCMDQ would crash.  The crash message would complain about a DLL within the Microsoft Windows version of the 11.1.2.4 Essbase Client, such as ESSOPGN.dll.

It just so happens that Oracle discovered this and published an update, but you have to dig for it within the Knowledge Base!

The Knowledge Base article # on support.oracle.com is "2273191.1".  This article acknowledges the bug and includes a download link to a version of ESSCMDQ.exe recompiled for Essbase 11.1.2.4.015 and higher.  Unfortunately, this version isn't included on the main download page for ESSCMDQ.

Oracle is issuing patches for on-premises Essbase 11.1.2.4 at the pace of about once every 2 months.  Since 11.1.2.4.0 first came out, as of this writing 19 cumulative patches have been issued for Essbase. This means if you are using ASO and are on 11.1.2.4.015 through 11.1.2.4.019, you will want to hit the "2273191.1" article and grab the newer version of ESSCMDQ.

## Friday, August 11, 2017

### Blank Member Selection window in Planning 11.1.2.4 & IE11???

I recently worked with a client who upgraded from Oracle Hyperion Planning 11.1.2.2.300 to 11.1.2.4. One of their Planning administrators tried to create a new form and complained the Member Selection pop-up window is blank in IE11.

The frustrating thing is the window renders correctly in IE8.  What's going on here?

This:

When we migrate a Planning application via LCM from one environment/version to another, the Application Properties are typically included within the migrated artifacts.  ORACLE_ADF_UI = false is the culprit here!

"Sherman, set the wayback machine to Hyperion Planning patch set 11.1.2.2.300!"

When Oracle rolled out PSU 11.1.2.2.300 for Planning, they introduced the ADF user interface.  This interface is what we know and love today in 11.1.2.4.  Back in the 11.1.2.2.300 days, however, some people were still using browsers older than IE9, which couldn't fully utilize ADF.  So as a workaround, Oracle documented a method to deliberately disable ADF.  By manually adding the ORACLE_ADF_UI property and setting it to false, one could force Planning to behave as it did in releases prior to 11.1.2.2.300.

The problem here is IE11 doesn't know how to render the Member Selection pop-up window when ORACLE_ADF_UI is present and set to false.

A few final notes:

When either deleting this property or changing it from false to true, it is necessary to stop and restart the Planning web service before the change takes effect.

Oracle also advises that once we are running in ADF mode and new Planning web forms are designed, it is not advisable to revert back to non-ADF mode.  The forms you built in ADF mode might not render the way you intend when you revert to non-ADF mode.

## Saturday, August 5, 2017

### LCM Backups, Windows 2012, and You

If you have an on-premises Oracle EPM / Hyperion system 11.1.2.0 or later (and you really should be on 11.1.2.4 by now!), this post is for you.

Traditional relational database backups and routine disk backups / VM snapshots are great things to have automated, but are not sufficient in and of themselves to fully protect your EPM system from disaster.

"Disaster", in the context of this post, is an Administrator or PowerUser doing any of the following:
• Edit the design of a Planning web form, Calculation Manager rule, or Financial Report in such a way that it is no longer usable.  Adding insult to injury, the developer doesn't remember which exact changes were made, and there's no Edit->Undo after clicking Save.
• A click-and-drag operation in the EPMA Dimension Library gone horribly wrong.
• Delete a folder hierarchy within Reporting Framework in EPM Workspace.  (Yes, it gives an "Are you sure?" prompt, and yes, I've had to do a restore because someone clicked Yes in Production by mistake).
The 3 examples cited above, all of which I have experienced in "real life", cannot be fixed by a simple VM snapshot restore.  The types of objects mentioned above reside as records within various relational repositories, so you would need to bring the EPM system offline and restore to your last good relational backup.

In the case where Reporting Framework is concerned, we additionally need to restore the ReportingAnalysis\data folder on the RAF Agent server, and that needs to be synchronized with the RAF relational restore.

But I digress....

Best practice is to automate nightly exports via Oracle's LCM command-line utility for EPM.  If you're unfamiliar with this utility, read LCM user guide Chapter 7.

My personal preference is to maintain multiple rolling rotations of LCM backups.  This is because sometimes a problem isn't reported for a few days.

And now we get to the nugget of why I'm writing this post today....

Certain LCM artifacts have extremely deep directory paths.  Reporting Framework and Financial Close Management immediately jump to mind.  In Windows Server 2008 R2, rotating and pruning the LCM export folders wasn't a problem.  But with 11.1.2.4's support for Windows Server 2012, we hit a new issue we didn't have to deal with in the past:  Microsoft's deep directory path character limitation.

In Windows Server 2012, try running RMDIR /S /Q on your oldest LCM folder for RAF or FCM.  You will likely see a failure message stating the directory path is too deep.

So off we go to our favorite web search engine to find a solution.  The 2 most frequently posted solutions are too "clunky" to use, in my opinion:
1. Mount a temporary drive to a point in the path before the # of characters reach 255-260.  CD to it and delete from there.  Then delete the temporary drive.
2. Use a tool like 7-zip, which uses a different API and doesn't have the character limitation.  You can navigate to the parent folder and shift-delete it, and it is gone.
I really don't like either of the above for my automated LCM backups.  When I want to roll off the oldest backup, here's what I do.

Create this Jython script:

# rmRotation7.py
#
# This Jython script removes the oldest LCM backup folder.
# We use this technique to work around the Windows Server 2012
# limitation concerning directories containing deep pathnames.
#
#  Written on 11/02/2016 by Dave Shay (Datavail)
# Modified on MM/DD/YYYY by Your Name - Briefly list changes

import shutil

shutil.rmtree('E:/Backup/LCM/Rotation7')


Why Jython? Because that shutil.rmtree function does everything we need with just 2 lines of code, and all modern Hyperion systems have access to Jython!

OK, so how do we invoke it?

Paste these 2 lines of code into your LCM automation wrapper script:

SET CLASSPATH=%CLASSPATH%;E:\Oracle\Middleware\oracle_common\modules\oracle.jrf_11.1.1\jrf-wlstman.jar
%JAVA_HOME%\bin\java weblogic.WLST E:/Scripts/rmRotation7.py

The first line is what prepares your DOS shell so that it can run Jython scripts. It does run a little slow and will delay your script for a few seconds. The second line invokes your Jython script and prunes the folder named in rmRotation7.py.

There are other ways to tackle this problem, such as installing 3rd party tools like Cygwin. My preference when working with customers' Hyperion systems is to utilize the framework already available. In my opinion, it makes knowledge transfer and ongoing maintenance a little easier. When we instead install a 3rd party tool, now we've introduced yet another thing we need to potentially patch and maintain.

## Friday, August 4, 2017

### FDMEE Batch Wrapper

I recently helped a customer troubleshoot why their FDMEE data load automation stopped working.  The solution was actually quite simple, and so I thought I'd share it here!

The Observed Symptom

An upstream system automatically provides ASCII text files for FDMEE to load.  The timing of the delivery varies from day to day, so a batch process is kicked off by the Windows Task Scheduler to continually check for the files' presence and process them when discovered.

On an intermittent basis, the FDMEE load doesn't properly complete.  The files are detected, the FDMEE load is attempted and fails, and then the files are moved into an archive folder by the consultant's DOS wrapper script.

Root Cause Analysis

The consultant setup the FDMEE automation via a Windows Task Scheduler job, which triggered to run "On Startup".  The script loops with a 60 second pause.  Once files are detected in the expected folder location, the pre-delivered FDMEE load utility is invoked.  The files are moved into an archive folder after this runs.

Unfortunately, the FDMEE server was rebooted via Windows Update.  Some time after the reboot, the files were delivered and picked up by the automation, but FDMEE has not yet finished its Oracle WebLogic startup sequence.

Plain English:  The FDMEE's load utility couldn't process the file, since FDMEE wasn't online.

The Fix

FDMEE 11.1.2.4 is one of the services that takes the longest to complete its WebLogic start-up sequence.  Depending upon the computing resources available, this can require 3-5 minutes or longer.

When FDMEE is fully initialized and ready to accept connections, the system is listening to TCP port 6550 (this is the default FDMEE port unless someone changed it).

We add 1 line to the top of our FDMEE automation wrapper script:

powershell D:\Scripts\WaitForFDMEE.ps1

Next, we create the WaitForFDMEE.ps1 script.  Here is the script in full:

# WaitForFDMEE.ps1
#
# This script loops until FDMEE's port is online.
#
# 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 08/04/2017 by Dave Shay (Datavail)
# Modified on MM/DD/YYYY by Your Name - Briefly list changes

$ErrorActionPreference = "SilentlyContinue" # Loop forever until FDMEE is online do {$socket = new-object System.Net.Sockets.TcpClient("localhost", 6550)

if ($socket -eq$null)
{
write-host "FDMEE isn't fully initialized yet.  Sleeping 20 seconds..."
powershell.exe Start-Sleep -s 20
}
} until ($socket -ne$null)

write-host "FDMEE is ready to accept connections."


Finally, we copy & paste this line to the command prompt. This prevents a Powershell security error. We only need to issue this command one time.

powershell.exe Set-ExecutionPolicy Unrestricted

And that's it! The FDMEE automation wrapper script now sleeps until it detects that FDMEE is online.

## Wednesday, July 12, 2017

### Finding EPM-Relevant Windows End-User Registry Keys

This is a follow-up to my previous post, Desktop Registry File

9 times out of 10, whenever we implement a new on-premises Oracle EPM / Hyperion system, we receive complaints about intermittent browser or SmartView lock-ups.  This can happen when we're hitting Oracle's EPM Cloud instances, too.

Any EPM consultant worth their salt knows the root cause: missing or incorrect Windows Registry keys on the end-user's workstation.

I always find it tedious having to crawl through the regedit tool to find the various keys to see how they're set.  How about a simple script that does the checking for you?

@echo off
REM EPMClientRegCheck.bat
REM
REM This simply script displays the values of the Windows Registry key entries
REM relevant to browser and SmartView timeouts, and SmartView flickering.
REM
REM  Written 07/12/2017 by Dave Shay (Datavail)
REM Modified MM/DD/YYYY by Your Name - Briefly list changes

echo ------------------------
echo KeepAliveTimeout should be set to dword:2bf20
echo Here is the current value:
reg query "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /v KeepAliveTimeout

echo ------------------------
echo ServerInfoTimeout should be set to dword:2bf20
echo Here is the current value:
reg query "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /v ServerInfoTimeout

echo ------------------------
echo ReceiveTimeout should be set to dword:75300
echo Here is the current value:
reg query "HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /v ReceiveTimeout

echo ------------------------
echo Here is the current value:

echo ------------------------
echo DisableAnimations should be set to dword:1
echo Here is the current value:
reg query HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Graphics /v DisableAnimations

echo All done checking!
pause


Double-click the .bat file and you'll see something like this:

------------------------
KeepAliveTimeout should be set to dword:2bf20
Here is the current value:

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings
KeepAliveTimeout    REG_DWORD    0x2bf20

------------------------
ServerInfoTimeout should be set to dword:2bf20
Here is the current value:

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings
ServerInfoTimeout    REG_DWORD    0x2bf20

------------------------
ReceiveTimeout should be set to dword:75300
Here is the current value:

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings

------------------------
Here is the current value:

ERROR: The system was unable to find the specified registry key or value.
------------------------
DisableAnimations should be set to dword:1
Here is the current value:
ERROR: The system was unable to find the specified registry key or value.
All done checking!
Press any key to continue . . .


Here we see two ERROR messages advising us of missing keys. (These keys in particular are the ones that prevent SmartView graphic flickering issues in Office 2013)

Easy and simple.  I like that!

## Tuesday, July 11, 2017

### Desktop Registry File

Whenever I begin a new customer engagement, I always provide Oracle's recommended Windows registry settings that prevent IE & SmartView timeouts.  Some customers have complained about SmartView graphic flickering issues, which require additional registry changes.

Here, therefore, is the combined registry file I use that address all of these issues.

If applying on a one-off basis, simply copy & paste all of the text you see below this paragraph into Notepad or your favorite editor.  Name the file whatever you want, but change the filename's extension from .txt to .reg.  Double-click the .reg file and then click OK/Yes when prompted to merge the changes into your registry.  If you have many Hyperion users who need these changes, instead forward the paragraph below to your IT department and ask they be pushed out via "Group Policy Update" - your IT department will know what that means.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]
"KeepAliveTimeout"=dword:0002bf20
"ServerInfoTimeout"=dword:0002bf20

[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Toolbars]

[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Graphics]
"DisableAnimations"=dword:1


## Saturday, July 8, 2017

### Easy Way to Change EPM Workspace Window Title

I'm often asked for ways to help discriminate between Production and lower environments, by changing how EPM Workspace looks.  There are some blog posts out in the wild that suggest you replace certain files within user_projects/domains/EPMSystem/servers/FoundationServices0/tmp, but I'm not a big fan of that approach.

To change the EPM Workspace title that appears to the right of the Oracle logo, or whatever custom logo you've replaced it with, run this SQL statement against your EPM System Registry database:

UPDATE hss_component_property_values
SET property_value = 'EPM Workspace 11.1.2.4 - Datavail Demo Lab'
WHERE property_name = 'ApplicationName'

You can set property_value to be whatever you want.  The property_name value is case-sensitive and must be 'ApplicationName' exactly.

EPM Foundation caches this property when it is first initialized, so you will need to stop and restart your EPM Foundation service in order for this change to take effect.

No editing .war files inside of .ear files, and no replacing contents of /tmp folders.  Problem solved!

## Wednesday, July 5, 2017

### Financial Reporting 11.1.2.4.700+ Logging Bug

If you're on EPM 11.1.2.4 and have applied any of the recent patches for Hyperion Financial Reporting 11.1.2.4.700 or higher, this post is for you!

Hop onto your Financial Reporting host server and inspect this folder:

\Oracle\Middleware\user_projects\domains\EPMSystem\servers\FinancialReporting0\logs\

Look for a file here named FRLogging.log.  How large has it grown?

In all versions older than HFR 11.1.2.4.700, FRLogging.log would rotate automatically.  Starting in 11.1.2.4.700, however, this behavior broke and the log grows indefinitely.

This log in particular contains the details about the reports your users are running.  By default, each report generates 3-5 pages of log entries.  If your users heavily rely upon Hyperion Financial Reporting during month-end and/or quarter-end close, this log file can grow very quickly!

Root Cause and Workaround!

Edit this file:

\Oracle\Middleware\user_projects\domains\EPMSystem\config\fmwconfig\servers\FinancialReporting0\logging.xml

Find this section within the file:

The above block of XML is entirely missing the rotation-related settings. Here's a corrected version you can use to replace the above:

The above is what I prefer to use. This will cause your logging.xml file to rotate daily, with a maximum retention of 7 days' worth of files.

One thing to bear in mind: This logging.xml file resides both on your Hyperion Financial Reporting host machine and also the WebLogic Admin Server's machine. Whenever WebLogic Admin Server is running, and Financial Reporting is restarted, the logging.xml file on the WAS server is pushed to the HFR server. Also, if you have HFR clustered across multiple instances/machines, you'll need to edit logging.xml within FinancialReporting1, FinancialReporting2, etc., as appropriate.

Update: I've submitted an Oracle SR to issue a BUG about this issue, as I've encountered this in every single 11.1.2.4 implementation I've done across multiple Oracle customers.

## Tuesday, July 4, 2017

### Custom JDBC URL for Hyperion Planning in Full SSL

One thing that's lacking in Oracle's documentation and knowledge base is how to correctly configure a Hyperion Planning data source when your database server only permits SSL connections.  This has led to frustrating searches through online blogs, Microsoft documentation (in the case when our database is MS SQL Server), and so on.

Here is the underlying problem:

Once your DBA has configured the back-end relational database to only accept SSL client connections, it is impossible to create (or use) a data source within Hyperion Planning's data source administration screen.  This is because, unlike the EPM System Configurator, there is no checkbox for enabling SSL, and no input boxes to specify your Java keystore and password.  This means we have to click the checkbox for a custom JDBC URL, and we must figure out the syntax ourselves.

After reaching multiple dead-ends, I stumbled upon the solution.  Because I had already gone through the steps of configuring SSL for the various databases, and I deployed the applications to WebLogic, the JDBC URL syntax we need to emulate has already been provided!

Solution

On any of your EPM servers where you've deployed to WebLogic, navigate to this folder:

Open any one of these *.jdbc.xml files and examine it.  Assuming the JDBC data source you're examining has been correctly configured for SSL via the EPM System Configurator, you'll have the exact syntax that's needed for this specific environment.

Let's walk through a specific example.  Suppose we have already created our Java keystore with a default password of "changeme", as per Oracle's documentation within epm_security.pdf, and we've saved it as:
E:\Oracle\Middleware\ssl\myIdentity.jks

Furthermore, let's say we're using Microsoft SQL Server as our database (2008 or 2012, but you want to be using 2012 by now), and we have empty database named "Vision" for our new Planning data source.

In this scenario, here's the custom JDBC URL we'd copy & paste into the data source creation screen.  Bear in mind, the text you see below is all one line.  I've added linebreaks for ease of reading:

jdbc:weblogic:sqlserver://SQLSERVERHOSTNAME:1433;
databaseName=Vision;
ENCRYPTIONMETHOD=SSL;
TRUSTSTORE=E:\Oracle\Middleware\ssl\myIdentity.jks;
VALIDATESERVERCERTIFICATE="true";


Personally, I find configuring EPM for full end-to-end SSL extremely time consuming and tedious. My preference is to put an SSL offloader in front of Oracle HTTP Server, but some IT organizations are becoming increasingly concerned about hardening their data's security as it passes through their internal network.

## Wednesday, June 28, 2017

### Older Hyperion Systems and SQL Server 2008

Most Hyperion / Oracle EPM shops tend to drag their feet when the topic of upgrading comes around. It's OK, you're in good company! Upgrading can be a difficult process, depending upon which Hyperion modules you have and how far behind in versions you are.
(Disclaimer: If all you have is just Essbase, upgrading is super easy. Otherwise, upgrading can be a "project")

If your EPM environment is version 11.1.2.3 or older, chances are high you're hosted on Windows Server 2008 R2. While I'm not afraid of UNIX, most Hyperion implementers seem to be, and thus the vast majority of EPM environments I've seen are Windows-based. Furthermore, just over half tend to use SQL Server rather than Oracle for their back-end relational databases.

So, this blog post is directed to the community who is using SQL Server 2008 or SQL Server 2008 R2 for their Hyperion environments.

As you prepare your capital budget for fiscal 2018, make sure you include a Hyperion system upgrade. Here's why:
Microsoft Extended Support ends for SQL Server 2008 R2 on July 9, 2019

If you wait until the last minute to plan your upgrade -- that is to say, sometime in 2019 -- you will likely have to wait in line. Us EPM on-premises infrastructure consultants will be busier than a roofing contractor after a thunderstorm rolls through town.

Now is the perfect time to plan your on-premises Hyperion upgrade. Oracle Sales is heavily pushing their Cloud offerings, so the pace of new on-premises implementations is decreasing. This means you could get a good deal on an upgrade project between now and 3rd Quarter 2018.

## 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

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: