Tuesday, May 13, 2008

Scheduled and automated Secure FTP (SSL)

A few months ago I had a request from one of our department members to automate a tedious process. One of our health systems crank out a plain text file which on a daily basis needs to be sent off to an insurance carrier. The insurance company uses FTP to facilitate the "upload" of data, but there is a catch. Because this information contains patient health data, you would not want to send it in plain text. Their method of securing the data is to use certificate based secure FTP. I should state that there are other methods of securing FTP traffic, namely with SSH tunneling. Using certificates for an FTP server is not common practice, and so automating it was difficult.

Here is the script that I created which happily runs on an Ubuntu Linux box. Note that names, passwords, and the like have been removed to protect confidentiality.

#!/bin/bash
#
# HL7 FTP drop
# By Steve Ballantyne 02-28-08
#

# Calculate the date in specified format and make a directory with it
TIME=`date +%m-%d-%Y`
# Build working directory
if [ -f /data/healthinsurance/README.txt ]
then
sleep 0
else
mkdir "/data/healthinsurance/$TIME"
fi

cd "/data/healthinsurance/$TIME"

# Check for SERVER Share
if [ -f /mnt/healthinsurance/README.txt ]
then
mv /mnt/healthinsurance/Submissions/healthinsurance/working/KCHDEM* /data/healthinsurance/$TIME
else
#Map to SERVER drive
mount -t smbfs -o username=ballantynesd,password=password "//SERVER/e$" /mnt/healthinsurance
# Now move it
mv /mnt/healthinsurance/Submissions/healthinsurance/working/KCHDEM* /data/healthinsurance/$TIME
fi

# Go put the file on the server
curl --ftp-ssl ftp://ftp.generichealthcare.com -u username:password -T /data/healthinsurance/$TIME/KCHDEM*.txt --insecure
# Done - Exit


Here's the play-by-play of what this script does.

1) A check is made for a README file. If this file doesn't exist, someone may have messed with our directory structure.
2) A date variable is created in the form of MM-DD-YYYY. Then, a directory is created using that variable, and we change directory, or 'cd', into it.
3) We check to see if a README file exists on a shared drive, which has been mounted. If it does not exist - then the drive has come un-mounted so we run through a routine to re-mount the Windows share.
4) Once the mounting issue is resolved, we move the file from the SERVER into that local folder we created with the date variable.
5) With the file in our grips, we use 'curl' to send the file to the health insurance company. Note that we needed two special switches: '--ftp-ssl' and '--insecure'. The '--insecure' was required in my case because the certificate being used for this connection was self-signed.

Now that we understand what we are doing, how will we automate it?

I saved the above script into a file called /root/healthins.sh. Then I did a 'chmod 755 healthins.sh' so that it was executable. Next, I became root by using 'su -' and supplying the root password. Finally, I ran 'crontab -e' which allowed me to edit the root users cron entries.

This is the line I added to the bottom of my scheduled jobs (it was empty).

1 1 * * * /root/healthins.sh >> /root/healthins.log 2>>&1

That line says that on the first minute of the first hour of every day ... run the script called 'healthins.sh' which exists in the /root directory. Then, redirect all of the output from this process into a log file called 'healthins.log' which also exists in root. The '>>' indicates that this file should be appended. That is, *added to* and not overwritten.

I'm happy to say that this has been running well, aside from a mistake I had made in my logging process. But I figured since I was in fixing this - I should document this process for future generations. If this helped you in some way, won't you please drop me a line?

-Steve Ballantyne