Saturday, December 27, 2008

Using ffmpeg to split MOD movie files.

My wife and I recently purchased a Panasonic SDR-H40 for capturing video of our son and daughter, after our Canon MiniDV crapped out last year. It was disappointing when the Canon failed again...TWO times this thing decides to get tape debris caught in the heads. This was the most delicate camera ever owned, and captured excellent quality footage, but I will NEVER buy another tape-based machine.

The Panasonic falls short in the quality department as compared to the Canon, and we knew that going in. The maximum video quality is 10mbps, which seems to be pretty good quality on a laptop. I haven't tried it on a analog TV yet.

Image representing YouTube as depicted in Crun...Image via CrunchBase

I needed to post these clips on YouTube for family and friends located in various parts of the planet, and rapidly discovered that the MOD files stored on the SDR-H40 can be accessed easily as plugging in the USB card, and waiting for udev to automatically mount it on my shiny new installation of Ubuntu.

I discovered that the SDR-H40 encodes the video in MPEG2 and audio in Dolby Digital (2ch)/MPEG1 Audio Layer2. I seem to remember reading somewhere that this was a non-standard combination of video/audio. This was confirmed when I attempted to simply rename the files to either mpeg or mp2 and load them into Cinelerra. Sometimes I had audio, sometimes not. Sometimes video, sometimes not. I'm not as familiar with Cinelerra as I am with Premier, but thought they looked rather similar...not.

I eventually found that using ffmpeg could potentially do what I needed, but didn't want to type in a command for every file I had, splitting them into 10 minute segments that YouTube demands. The following script was what I eventually came up with, after scouring the web for examples from seemingly ffmpeg experts. Thanks Experts!!!!

UPDATE:
I've found that while the below script worked well for splitting longer videos efficiently, it failed to produce satisfactory results. There was a serious quality issue with the compression method that I was using, and it needed to change.

I started experimenting with different compression methods, but couldn't find one that worked. I finally just uploaded a .MOD video file (mpeg2video) and YouTube converted it! Great, but not so great since these videos in MPEG2 format get rather large. Downloading and installing WinFFMPEG resolved my lack of knowledge in the video compression area, as it has a built in set of methods, and shows you the command line used to produce conversions. VERY handy.

Unfortunately, the videos produced by this and many cameras like it format the size to 704x480, which looks best played at a 16:9 aspect ratio, but YouTube insists on 4:3. I began to play with padding, cropping, resizing, but nothing seemed to work. I was finally able to find an obscure thread on YouTubes' Community Help Forums (Search for 704x480) where the last post addresses this easily; simply tag your video with "yt:stretch=16:9". Wow, that's it? GREAT!

Below is my modified script, using the borrowed template XVID compression method from WinFFMPEG.

#!/bin/sh

# This script will recursively find all
# mod files beneath it in the file system
# and convert them to MPEG4 XVID format.
# If they are longer than 10 minutes,
# they will be split into 10 minute chunks.

FILES=`find . -type f -name "*.mod"`

for F in $FILES
do
		
	#
	# First, get file names from input
	######################################
	DIR=`dirname $F`  	
	FNAME=`basename $F`
	BASE=`basename $F .mod`
	EXT="mod"

	#
	# Now, get the length of the clip
	######################################
	HOURS=`ffmpeg -i $F 2>&1 | grep "Duration" | cut -d ' ' -f 4 | sed s/,// | cut -d ":" -f 1`
	MIN=`ffmpeg -i $F 2>&1 | grep "Duration" | cut -d ' ' -f 4 | sed s/,// | cut -d ":" -f 2`
	SEC=`ffmpeg -i $F 2>&1 | grep "Duration" | cut -d ' ' -f 4 | sed s/,// | cut -d ":" -f 3 | cut -b -2`
	
	echo "The duration of $BASE.$EXT is $HOURS:$MIN:$SEC."

	#
	# If minutes are greater than 10, we need to cut it up for YouTube
	##################################################################
	if [ $MIN -gt "10" ]; then
	
	echo "$FNAME is longer than 10 minutes long. Splitting now..."
	
	#
	# We need to get the minutes, so I've split these up into BMIN and SMIN, e.g. 15 minutes, BMIN=1 SMIN=5
	####################
	BMIN=`echo $MIN | cut -c 1`
	SMIN=`echo $MIN | cut -c 2`

	# Set a counter
	i=0
	
		#
		# Loop over the video, grabbing it in 10 minute increments.
		# I know I'll NEVER record anything that exceeds 1 hour.
		# While I'm sure this can be done easier, egg-nog and whiskey
		# prevents me from thinking of one...
		##########################################################
		while [ $i -le $BMIN ]
		do
		
			case $i in
				0)
				STIME="00:00:00"
				FTIME="00:10:00"
				;;
				1)
				STIME="00:10:00"
				if [ $MIN -lt "20" ]; then
					FTIME=$HOURS:$MIN:$SEC
				else
					FTIME="00:20:00"
				fi
				;;
				2)
				STIME="00:20:00"
				if [ $MIN -lt "30" ]; then
					FTIME=$HOURS:$MIN:$SEC
				else
					FTIME="00:30:00"
				fi
				;;
				3)
				STIME="00:30:00"
				if [ $MIN -lt "40" ]; then
					FTIME=$HOURS:$MIN:$SEC
				else
					FTIME="00:40:00"
				fi
				;;
				4)
				STIME="00:40:00"
				if [ $MIN -lt "50" ]; then
					FTIME=$HOURS:$MIN:$SEC
				else
					FTIME="00:50:00"
				fi
				;;
				5)
				STIME="00:50:00"
				if [ $MIN -lt "60" ]; then
					FTIME=$HOURS:$MIN:$SEC
				else
					FTIME="00:60:00"
				fi
				;;
				6)
				STIME="00:60:00"
				FTIME="01:00:00"
				;;
			esac
			
			ffmpeg -i $F -f avi -ss $STIME -t $FTIME -r 29.97 -vcodec libxvid -vtag XVID -maxrate 1800kb -b 1500kb -qmin 3 -qmax 5 -bufsize 4096 -mbd 2 -bf 2 -flags +4mv -trellis -aic -cmp 2 -subcmp 2 -g 300 -acodec libmp3lame -ar 48000 -ab 128kb -ac 2 $DIR/$BASE-$i.avi
			i=`/usr/bin/expr $i + 1`
		done


	else
		ffmpeg -i $F -f avi -r 29.97 -vcodec libxvid -vtag XVID -maxrate 1800kb -b 1500kb -qmin 3 -qmax 5 -bufsize 4096 -mbd 2 -bf 2 -flags +4mv -trellis -aic -cmp 2 -subcmp 2 -g 300 -acodec libmp3lame -ar 48000 -ab 128kb -ac 2 $DIR/$BASE.avi
	fi

done
Reblog this post [with Zemanta]

Tuesday, December 16, 2008

GFI LANguard 9 Review

As a consultant, I used GFI LANguard (7?...it was at least 2 years ago) as a tool, in conjunction with nmap and some others, to perform security audits for our clients.

Now I've an opportunity to use it again, and agreed to give it a review.

Environment

The download from GFI's website was surprisingly small; only 50mb.

The installation was straight forward, with only two questions; installation location, and initial credentials to use for scanning your domain. The UI is no different, very intuitive. I'd expect nothing else from GFI, since most of their products are the same way.

The product is broken up into four components:

  • Management Console - the central location for launching scans, view saved scans, configure options, and use specialized network security tools.
  • Attendant Service - runs scheduled scans and patch deployments.
  • Patch Agent Service - handles the deployment of patches, service packs and software updates.
  • Script Debugger - use a vbscript compatible language to write your own vulnerability checks.

The strategy suggested by LANguard for vulnerability management is:

  • Scan
  • Analyze
  • Remediate

or, wash, rinse, repeat :-)

Network Audit

The first window you see upon program launch is the Network Audit tab. Here you have the option to scan the localhost, an entire network, launch a custom scan, or set up a scheduled scan. I opted to scan the entire network, knowing that it will stop after the 4th host is found, since that is the limit to my NFR key (in addition to not being able to receive product updates...which begs the question; How recent is my security vulnerability DB?) I digress....

During a scan operation, you can easily navigate through most of the program without interrupting it. I found going from inside the Network Audit tab, which shows you a more bland Scan status, I could switch to the more familiar Analyze page, where you can see the actual scan threads, and any error messages generated.

It took some time to scan; about 2 hours, but it did come back with a bevy of security vulnerabilities. I decided to scan a single Windows 2003 Standard server, which is a DC and application server. This took only 15 minutes, and the results are listed to the right.

What I like about this layout is that you get some similar options to the older version: A quick-launch option at the top, an overview of the scan on the left, and the details displayed on the right as you click on the overview items.

In addition to showing you patch vulnerabilities, LANguard also checks for configuration issues. My server has an ASP application that runs on a private LAN, with no publicly available pages, and I was shown some best-practice information, with direct links to MS KB articles. User accounts, network ports, hardware devices, applications installed, network shares, some security policies, and many more details are also displayed. Quite nice to get a top-down list like that, relieving you of having to dig through a series of MMC's, and other UI's to collect this type of info.

Once you've reviewed the scan, you click the Remediate link, and you are shown a list of all patches available, with options to sort by computers, patches, or deployment status. It's presented in a 3 step process; 1. Choose computer, 2. Choose patches, 3. Launch. In this area you are also able to deploy service packs and custom software, as well as given the ability to uninstall said software. There are also some handy links for changing credentials, computer profiles, deployment options and patch auto-download options.

You may also schedule your software deployment from the launch area.

Dashboard

The Dashboard shows you a pretty picture of your last scan, overall vulnerability level, vulnerability distribution, most vulnerable computers, and a time-line of vulnerability. You can also get a snapshot of your scheduled operations here.

The Dashboard will not show a vulnerability rating without valid credentials to the target computer. I found this to be a bit problematic trying to scan my Cisco ASA 5510 configured to authenticate against RADIUS. I tried both a domain account and local, which both failed the SSH connection test. My guess is that it hits it so many times that AAA marks my RADIUS server as failed, and falls back to LOCAL authentication...

Configuration

Options include;

  • Scanning Profiles allow you to customize scans, assessments and the network/software audit.
  • Scheduled Scans
  • Computer Profiles are useful if you have some non-Windows computers or devices with differing credentials than the Domain.
  • Applications Inventory will list all apps found, and give you the opportunity to uninstall invalid ones!
  • Microsoft Updates mimics WSUS in a simplified manner, allowing you to approve patches, and schedule automatic download of them.
  • Alerting is configured here, and fine tuned scanning profiles for notifications.
  • Database Maintenance Options is helpful to clean up your DB, host it on a SQL server, and perform other maintenance tasks.
  • Program Update for scheduling application updates.

Utilities

At first glance, I nearly glazed over this area, until I saw the Enumerate Users, Enumerate Computers, SNMP Audit, SNMP Walk, and SQL Server Audit tools. These are all great tools to have, but tThe configuration of the SQL Server Audit tool reveals a user to perform a password guess brute force attack on, and the list is quite weak. Go find more if you want to use this tool. The same holds true when using the SNMP Audit tool.

Personal Notes

Some particular things I noticed during my testing, not all good or bad, were;

  • LANguard licensing is comprised of a SMA (Software Maintenance Agreement) which controls product updates and Vulnerability Assessment definitions, and a limit to the number of machines that can be scanned and stored in the database. I scratched my head for 15 minutes trying to figure out why I couldn't simply scan 4 different machines, and since LANguard stores the machines scanned in the DB, you can't scan different targets until they are removed.
    • Go to Configure, Database Maintenance Options.
    • Choose Manage List of Scanned Computers
    • Delete computers to make room for new!
Reblog this post [with Zemanta]

Sunday, December 07, 2008

Chucky Cheese

Yesterday, attended a birthday party for our friends 2 year old at Chucky Cheese.

My first thought was that this was going to be fun for our young son, who's not had this kind of experience before. We try to expose him to as many things (read: safe, reasonable. i.e. Not bungee jumping. Yet.) as we can.

What we encountered can only be described as what we used to call in the Army, as a "cluster f$%k". It was 20 degrees outside. There were at least 30 people waiting to enter, most for parties. We were all freezing our asses off, half inside the building, half outside. Would that be "half-assed"? I digress.

The young man checking guests in, appeared new, because he was not bothering to check if some of us were attending parties, or just looking for somewhere to drink while our kids spread our particular variety of bactiria and virus around.

So we waited. And waited. And waited. Finally the manager comes over to do her job, and find out why we are all standing around like cattle at the slaughter house. Moo.

I mention the drinking thing, because I cannot believe that they serve beer! What the HELL are these people thinking?

  1. Put diverse group of people together, in tightly packed, noisy, sweaty room.
  2. Introduce some folks that would really be at home, watching the Packers.
  3. Serve much beer.
This is surely not a good idea. What jug-head at corporate thought this up? Those on the board clearly don't get down to visit some of these locations, nor read the papers. </rant>

Friday, November 28, 2008

Symantec Endpoint Protection and Outlook's 0x800CCC0F Error

After upgrading from SEP 11 MR2 to MR3, my users 0x800CCC0F Outlook error stopped....for one day, then re-appeared. This issue ONLY occurred during his mail retrieval process.

During my troubleshooting, I had initially opened a command prompt, and issued the command:

telnet pop.myserver.com 110

And received an inline PGP reply, that it was proxying the connection. That prompted me to do a little Googling, but revealed nothing.

I found a MS Kb article that pointed to some troubleshooting steps, but they didn't help at all, and neither did Symantec's kb or forums

Much to my surprise and glee, he started to experiment with his POP settings. After setting his POP connection to SSL, his problems went away!

Thursday, November 20, 2008

Symantec Endpoint Protection MR2 to MR3 Upgrade

Not much to mention...it_just_works. THIS TIME.

Steps for upgrading:

    1. Download MR3
    2. Stop all SEPM services.
    3. Run installer over the top of previous installation.

Interestingly, researching an Outlook error 0x800CCC0F while POP'ing email down from our mail host, I found this article about how much better MR3 will perform, even over the likes of v10.

Hopefully my test client will not experience any issues, as the SEPM surely didn't.

Wednesday, November 19, 2008

Redeploy Symantec Endpoint Security Client

I need a method to reinstall a SEP client package. Unfortunately, SEPM doesn't have a method in their GUI to do this...*nudgenudgewinkwink*, you must use the Migration and Deployment Wizard, and choose the default option Deploy the Client, and then Select and Existing Package to Deploy.

I found a clue in a thread at Symantec's forums where one can use the %PROGRAMFILES%\Symantec\Symantec Endpoint Protection Manager\tomcat\bin\ClientRemote.exe utility, which is usually only revealed when you use the wizard, or initially install the product.

You'll find your repository of packages you created in said installation at %PROGRAMFILES%\Symantec\Symantec Endpoint Protection Manager\Inetpub\ClientPackages, one folder for each package represented in SEPM, with a sub-folder called full. Unfortunately the folder names are named using the package checksum number, and I can't immediately tell by looking at the GUI which one is which. Fortunately for me, I only created two packages; a 32bit and 64bit version. If you look at the contents of the directory, it's pretty easy to determine which one is 32bit or 64bit. There will be a Symantec AntiVirus Win64.msi file in the 64bin version folder.

Simply choose the folder for your architecture, and push it to the client.

Monday, November 03, 2008

The Sinowal Trojan Steals You Blind

This morning, home sick, reading /. I find this story about the Sinowal Trojan.

Evidently, starting in 2006, this Trojan has been stealing sensitive data from thousands of Internet users across the globe, except for those in Russia. Seems even the Russian mob has a heart.

This all raises a question I've often asked myself. I've been managing enterprise environments for years now, using a variety of methods to protect the sheep, in hopes that the worst is avoided; a full network infection.

It's happened to me once, when I worked for the Racine Art Museum. I'd been hired to oversee the IT side of a new museum we were raising capitol for. The environment was rather new at the time; NT Back Office server, 2000 clients, Trend AV suite.

Unfortunately, as in some environments like this, there are applications that require elevated privileges to run. I suspect that this may have had something to do with the rapid spread of this virus. The signs were odd; in each network share on the server, .msg files started to mysteriously appear. A cursory search on the web revealed that this was a strain of virii that spread itself in this manner, eventually filling up all volumes on all systems and bringing the network to a screeching halt. I was able to quickly find a fix and apply it, saving us from disaster.

Which brings me to the question that I've been wondering...

Just how many more of these types of virii exist, and have not been detected? If this one was able to survive for this period of time before discovery, I must conclude that there are more out there, undiscovered, collecting data, sending it to some Russian mobster, so they can go on with their mobbing ways.

I'm not snubbing the AV companies by any stretch. They have all they can do to keep up with the virii Jones' next door. What frightens me is that while technology is a wonderful thing, we also grow increasingly reliant on it, and the AV companies to protect us from the bad guys. Can we expect them to do a satisfactory job for $59.99?

With that growing reliance, we will also see a parallel in the growth of cyber crime. I already stay away from questionable websites, don't open mail from unknown senders, or run software from unknown sources. I have a popular AV package and Spyware protection solution running on my systems. This doesn't gaurantee my safety, and I certainly don't like the idea of some idiot obtaining my SSN, opening an account at Best Buy, and running up several thousand dollars of the latest-and-greatest HD/Gaming system bundle. What measures will we need to take to secure our sensitive data beyond AV/Spyware/Trojan protection?

Surely, someone will develop a system that will cross check our Credit Card number with a PSK embedded in our head some day.... Until then, I'll cross my fingers and toes.

Thursday, October 30, 2008

'Twas the Night Before Elections

The GOP made me do it...and a friend who forwarded it.

'Twas the night before elections
And all through the town
Tempers were flaring
Emotions all up and down!

I, in my bathrobe
With a cat in my lap
Had cut off the TV
Tired of political crap.

When all of a sudden
There arose such a noise
I peered out of my window
Saw Obama and his boys

They had come for my wallet
They wanted my pay
To give to the others
Who had not worked a day!

He snatched up my money
And quick as a wink
Jumped back on his bandwagon
As I gagged from the stink

He then rallied his henchmen
Who were pulling his cart
I could tell they were out
To tear my country apart!

'On Fannie, on Freddie,
On Biden and Ayers!
On Acorn, On Pelosi'
He screamed at the pairs!

They took off for his cause
And as he flew out of sight
I heard him laugh at the nation
Who wouldn't stand up and fight!

So, I leave you to think
On this one final note-
IF YOU DONT WANT SOCIALISM GET OUT AND VOTE!!!!

Friday, October 17, 2008

Is MedCo the Anti-Christ of drug suppliers?

I just had a frustrating experience with my health insurance company, UHC, and their preferred drug supplier, MedCo.

My wife received a call from MedCo, and suggested that they could save us money on our prescriptions.

Interestingly, they knew about every prescription my wife had, and the physicians that prescribed them. I'm sure the lawyers have that loop-hole covered...

Back to our savings. They indicated they could save us plenty in co-pays, but my wife clearly stated our plan was about to change, and she needed to talk to me first, so NO, DON'T SHIP IT was the answer.

Funny. The agent clearly knows more about my wife's health care, because a few days later, we received our first shipment of medication.

My wife called and complained to the agent, who indicated they could not take the drugs back. She protested, and demanded to speak to the agents supervisor, who suggested we would have to submit an appeal in writing to UHC. Bah!

I called a couple of days later, and talked to an agent at UHC, who had no knowledge of this appeal process, and insisted that MedCo would need to be contacted directly, as UHC had nothing to do with the dispersement of medication. I contacted MedCo, and was informed that it wasn't my wife that authorized the shipment, as they initially charged, but the physician.

I can only guess they contacted the physician, and acquired a prescription for these drugs, and had them shipped to our home! This absolutely baffles me, and my wife is now contacting our physicians to demand they stop this, and find out who authorized it.

After doing some cursory searches on Google, I found this blog, where folks are still singing the woes of the likes of MedCo. Seems they've been paying out millions in settlements for years, due to improprieties.

Just Google MedCo lawsuit.

This also seems highly unethical, and that the physicians should watch their back, since I'll be calling my lawyer soon.

Friday, October 03, 2008

Awwwwww, crap!

After all the work I did on these batch files that decrypt PGP files, and un-tar the contents, I've found an error, and a pretty major *duh* one at that.

Seems my logic was way off when it came to doing some date checking. Since the files I check have a naming convention that uses the previous days date, I was simply subtracting one from %TODAY%. Seems ok, right?

No.

This is the *duh* part. I simply subtracted 1. From 1001, that makes 1000. That's not a day, when you are using MMDD as the convention. What I was looking for was 930. NOT the same.

SSSssoooooo, I added a bit I found on Experts Exchange (THANK you..for saving me MUCHO time).

I was also having some issues trapping the ERRORLEVEL. Apparently, since FOR loops in DOS batch scripts are executed as one command, PGP never has a chance to pass it's exit code to my IF check, so this was also modified.

The modded file is here.

Wednesday, October 01, 2008

shBrushBatch for Google SyntaxHighlighter

I really like highlighting my code in this blog. Ever since finding the Google SyntaxHighlighter Widget at FaziBear's, I've been going through all my old code, and cleaning up my posts.

I read a nice article at WaltCo Tech about extending it to incorporate other brushes for WordPress. He also seems to do what I do, but a bit better when it comes to explaining things: WriteItDownNowBeforeYouForgetHowYouDidThatOldMan

I'm really not that old, but I do tend to stick my fingers in many things, and coding tends to be one of them. At times, I'll find myself trying in vain to use a function from VB in PHP, or something of that nature. One of the hazards of not being fluent maybe?

In any case, I've created my own brush for DOS Batch scripts:

dp.sh.Brushes.Batch = function()
{
    var builtins =  'APPEND ATTRIB CD CHDIR CHKDSK CHOICE CLS COPY DEL ERASE DELTREE ' +
                    'DIR EXIT FC COMP FDISK FIND FORMAT FSUTIL HELP JOIN ' +
                    'LABEL LOADFIX MK MKDIR MEM MEMMAKER MORE MOVE MSD PCPARK ' +
                    'PRINT RD RMDIR REN SCANDISK SHARE SORT SUBST SYS ' +
                    'TIME DATE TREE TRUENAME TYPE UNDELETE VER XCOPY';

    var keywords =  'DO ELSE FOR IN CALL CHOICE GOTO SHIFT PAUSE ERRORLEVEL ' +
      'IF NOT EXIST LFNFOR START SETLOCAL ENDLOCAL ECHO SET';

    this.regexList = [
 {regex: new RegExp('REM.*$', 'gm'), css: 'comment'},
 {regex: new RegExp('::.*$', 'gm'), css: 'comment'},
 {regex: dp.sh.RegexLib.DoubleQuotedString, css: 'string'},
 {regex: dp.sh.RegexLib.SingleQuotedString, css: 'string'},
 {regex: new RegExp('[()[\\]{}]', 'g'), css: 'delim'},
 {regex: new RegExp('%\\w+%', 'g'), css: 'vars'},
 {regex: new RegExp('%%\\w+', 'g'), css: 'vars'},
 {regex: new RegExp('\\w+=', 'g'), css: 'vars'},
 {regex: new RegExp('@\\w+', 'g'), css: 'keyword'},
 {regex: new RegExp(':\\w+', 'g'), css: 'keyword'},
 {regex: new RegExp('\\s/\\w+', 'g'), css: 'flag'},
 {regex: new RegExp(this.GetKeywords(builtins), 'gm'), css: 'builtin'},
 {regex: new RegExp(this.GetKeywords(keywords), 'gm'), css: 'keyword'}
 ];

    this.CssClass = 'dp-batch';

    this.Style =    '.dp-batch .builtin {color: maroon; font-weight: bold;}' +
                    '.dp-batch .comment {color: gray;}' +
                    '.dp-batch .delim {font-weight: bold;}' +
                    '.dp-batch .flag {color: green;}' +
                    '.dp-batch .string {color: red;}' +
                    '.dp-batch .vars {color: blue;font-weight: bold;}';

}

dp.sh.Brushes.Batch.prototype = new dp.sh.Highlighter();
dp.sh.Brushes.Batch.Aliases = ['batch', 'dos'];

I'm really quite pleased with the results, and hope you find it useful!

Saturday, September 27, 2008

Google SyntaxHighligter Widget

If you code, and like to save snippets like I do, you MUST get this.

Friday, September 26, 2008

More PGP Fun

This process outlined is not unlike what I wrote about in my post here, but involves a bit more logic.

The script has to check for files that were posted that morning, but also have a file naming convention of the previous day. I've also got about 200 EMR postscript files to sort by date of service.

First, the source PGP/tar files must be checked for existence, and must be a pair with the proper date formatted file name.

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION

:: //////////////////////////////////////////////
::
::  Set path variables for key access, passphrase,
::  and source our key files.

SET PGPPATH=C:\PGP_keys\
SET PGPPASS=MYPGPPASS

:: The "KEY" to a successful import of our keys is running these coMMands as the user
:: that will be running this script.
::  pgp +batchmode -ka %PGPPATH%SECRING.SKR
::  pgp +batchmode -ka %PGPPATH%PUBRING.PKR
::  pgp -ke 0xEC671710 <-- after this coMMand, you will be asked to trust the key; this must be done!

:: //////////////////////////////////////////////
::
::  Set our working dir, destination dir, _LOGFILE
::  and get in there!
SET _LOGFILE=H:\COVNENT\LOGS\MEDRECLOG.TXT
SET _OPATH=H:\CLIENT\MEDICA~1
SET _WORKDIR=H:\CLIENT\WORKDIR
H:
CD \
CD COVNENT\

:: //////////////////////////////////////////////
::
:: Log beautification

ECHO --------------------------------------------------------------------------- >> %_LOGFILE%
ECHO -- START %DATE% - %TIME% -- >> %_LOGFILE%
ECHO --------------------------------------------------------------------------- >> %_LOGFILE%

::  //////////////////////////////////////////////
::
::  First test for existance of files, and
::  notify us if they don't exist.

DIR /B *.TAR.PGP
IF ERRORLEVEL 1 SET _MSG=No Medical Record files were found. & ECHO #### ERROR - NO FILES FOUND #### >> %_LOGFILE% & GOTO SENDMAIL

:: //////////////////////////////////////////////
::
::  Make sure our file count is correct. It should be 2.

DIR /B *.TAR.PGP | FIND /C /V "" > COUNT.TXT
FOR /F "tokens=1" %%f IN (COUNT.TXT) DO (
IF NOT %%f==2 SET _MSG=The file count of the Medical Record files is %%f, and 2 were expected. & DEL COUNT.TXT & ECHO #### ERROR - FILE COUNT WAS ONLY 
%%f. #### >> !_LOGFILE! & GOTO SENDMAIL
)

:: //////////////////////////////////////////////
::
::  Delete the count file check
DEL COUNT.TXT


:: //////////////////////////////////////////////
::
::  Now, get the date in the form of MMDD, subtract 1, and compare it to
::  what we received from CLIENT, which SHOULD
::  be one less than today. If not, send an email so it
::  can be checked out.

FOR /F "tokens=2-4 delims=/ " %%f IN ('DATE /T') DO (
 SET MM=%%f
 SET DD=%%g
 SET YYYY=%%h
)

::  Substract your days here
SET /A DD=1%DD% - 100 - 1
SET /A MM=1%MM% - 100

:CHKDAY
IF /I %DD% GTR 0 GOTO DONE
SET /A MM=%MM% - 1
IF /I %MM% GTR 0 GOTO ADJUSTDAY
SET /A MM=12
SET /A YYYY=%YYYY% - 1

:ADJUSTDAY
IF %MM%==1 GOTO SET31
IF %MM%==2 GOTO LEAPCHK
IF %MM%==3 GOTO SET31
IF %MM%==4 GOTO SET30
IF %MM%==5 GOTO SET31
IF %MM%==6 GOTO SET30
IF %MM%==7 GOTO SET31
IF %MM%==8 GOTO SET31
IF %MM%==9 GOTO SET30
IF %MM%==10 GOTO SET31
IF %MM%==11 GOTO SET30
REM ** Month 12 falls through

:SET31
SET /A DD=31 + %DD%
GOTO CHKDAY

:SET30
SET /A DD=30 + %DD%
GOTO CHKDAY

:LEAPCHK
SET /A TT=%YYYY% %% 4
IF NOT %TT%==0 GOTO SET28
SET /A TT=%YYYY% %% 100
IF NOT %TT%==0 GOTO SET29
SET /A TT=%YYYY% %% 400
IF %TT%==0 GOTO SET29

:SET28
SET /A DD=28 + %DD%
GOTO CHKDAY

:SET29
SET /A DD=29 + %DD%
GOTO CHKDAY

:DONE
SET _TODAY=%MM%%DD%

FOR /F %%f IN ('DIR /B *.TAR.PGP') DO (
SET _FILE=%%f
REM /////////////////////////////////////////
REM This bit checks and removes leading zeros
REM our MMDD check
SET _MCHECK=!_FILE:~13,1!
SET _DCHECK=!_FILE:~15,1!
IF !_MCHECK!==0 (SET _FMM=!_FILE:~14,1!) ELSE (SET _FMM=!_FILE:~13,2!)
IF !_DCHECK!==0 (SET _FDD=!_FILE:~16,1!) ELSE (SET _FDD=!_FILE:~15,2!)
SET _FDATE=!_FMM!!_FDD!
IF NOT !_FDATE! == !_TODAY! SET _MSG=The date of the Medical Record file, %%f, was not what was expected. It should be !_TODAY!. --- FILE DATE = 
!_FDATE!, RUN DATE = !_TODAY! & ECHO #### ERROR - DATE MISMATCH - FILE DATE = !_FDATE!, RUN DATE = !_TODAY! #### >> !_LOGFILE! & GOTO SENDMAIL
)

:: //////////////////////////////////////////////
:: Now that we've passed the initial conditions
:: move the files to our workdir

MOVE *.TAR.PGP %_WORKDIR%
CD WORKDIR


:: //////////////////////////////////////////////
::
::  Loop through our tarred and encrypted files,
::  decrypt them and enter a log entry for each
::  file processed. We loop, becuase pgp won't
::  deal with wildcards in the file name.

FOR /F %%f IN ('DIR /B ZYX*.TAR.PGP') DO (SET _ZYXFILE=%%f)
PGP -p %_ZYXFILE%
IF %ERRORLEVEL% NEQ 0 (SET _ERR=%ERRORLEVEL% & SET _FILE=%_ZYXFILE% & SET _MSG=There has been a PGP Decryption Error on the Medical Record file, 
%_ZYXFILE%. NO FILES WERE EXTRACTED. & GOTO PGPERR) ELSE (ECHO DECRYPTED %_ZYXFILE% >> %_LOGFILE% & MOVE %_ZYXFILE% %_OPATH%)

::  /////////////////////////////////////////////
::  Now do the same for XYZ

FOR /F %%f IN ('DIR /B XYZ*.TAR.PGP') DO (SET _XYZFILE=%%f)
PGP -p %_XYZFILE%
IF %ERRORLEVEL% NEQ 0 (SET _ERR=%ERRORLEVEL% & SET _FILE=%_XYZFILE% & SET _MSG=There has been a PGP Decryption Error on the Medical Record file, 
%_XYZFILE%. NO FILES WERE EXTRACTED. & GOTO PGPERR) ELSE (ECHO DECRYPTED %_XYZFILE% >> %_LOGFILE% & MOVE %_XYZFILE% %_OPATH%)



:: //////////////////////////////////////////////
::
::  Loop through our tarred files, working on ZYX
::  files, then extract them to our working directory
::  We loop, because 7z won't deal with wildcards
::  in the file name.

FOR /F %%f IN ('DIR /B ZYX*.TAR') DO (
FOR /F "tokens=3" %%C IN ('C:\Progra~1\7-zip\7z.exe l %%f ^| FIND /I "files"') DO SET _FCOUNT=%%C
"%PROGRAMFILES%"\7-zip\7z.exe x %%f -aoa -o!_OPATH!\ZYX\
IF !ERRORLEVEL! NEQ 0 (SET _ERR=!ERRORLEVEL! & SET _FILE=%%f & SET _MSG=There has been a untar error on the Medical Record file, %%f. & GOTO TARERR) 
ELSE (ECHO EXTRACTED !_FCOUNT! RECORDS FROM %%f. >> !_LOGFILE! & DEL %%f)
)

:: //////////////////////////////////////////////
::
::  Do the same for XYZ files

FOR /F %%f IN ('DIR /B XYZ*.TAR') DO (
FOR /F "tokens=3" %%C IN ('C:\Progra~1\7-zip\7z.exe l %%f ^| FIND /I "files"') DO SET _FCOUNT=%%C
"%PROGRAMFILES%"\7-zip\7z.exe x %%f -aoa -o%_OPATH%\XYZ\
IF !ERRORLEVEL! NEQ 0 (SET _ERR=!ERRORLEVEL! & SET _FILE=%%f & SET _MSG=There has been a untar error on the Medical Record file, %%f. & GOTO TARERR) 
ELSE (ECHO EXTRACTED !_FCOUNT! RECORDS FROM %%f. >> !_LOGFILE! & DEL %%f)
)

GOTO EOF




:PGPERR
:: //////////////////////////////////////////////
::
:: Log all PGP errors, send an email, and quit
IF %_ERR%==33 ECHO #### %_FILE% CAUSED ERROR - DECOMPRESSION ERROR #### >> %_LOGFILE%
IF %_ERR%==32 ECHO #### %_FILE% CAUSED ERROR - DECRYPTION ERROR #### >> %_LOGFILE%
IF %_ERR%==31 ECHO #### %_FILE% CAUSED ERROR - PUBLIC KEY DECRYPTION ERROR #### >> %_LOGFILE%
IF %_ERR%==30 ECHO #### %_FILE% CAUSED ERROR - SIGNATURE CHECK ERROR #### >> %_LOGFILE%
IF %_ERR%==19 ECHO #### %_FILE% CAUSED ERROR - KEY SIGNATURE REMOVAL ERROR #### >> %_LOGFILE%
IF %_ERR%==18 ECHO #### %_FILE% CAUSED ERROR - KEY SIGNATURE ERROR OR KEY SIGNATURE REVOKE ERROR #### >> %_LOGFILE%
IF %_ERR%==17 ECHO #### %_FILE% CAUSED ERROR - KEYRING CHECK ERROR #### >> %_LOGFILE%
IF %_ERR%==16 ECHO #### %_FILE% CAUSED ERROR - KEYRING REMOVAL ERROR #### >> %_LOGFILE%
IF %_ERR%==15 ECHO #### %_FILE% CAUSED ERROR - KEYRING VIEW ERROR #### >> %_LOGFILE%
IF %_ERR%==14 ECHO #### %_FILE% CAUSED ERROR - KEYRING EDIT ERROR #### >> %_LOGFILE%
IF %_ERR%==13 ECHO #### %_FILE% CAUSED ERROR - KEYRING EXTRACT ERROR #### >> %_LOGFILE%
IF %_ERR%==12 ECHO #### %_FILE% CAUSED ERROR - KEYRING ADD ERROR #### >> %_LOGFILE%
IF %_ERR%==11 ECHO #### %_FILE% CAUSED ERROR - NON-EXISTING KEY ERROR #### >> %_LOGFILE%
IF %_ERR%==10 ECHO #### %_FILE% CAUSED ERROR - KEY GENERATION ERROR #### >> %_LOGFILE%
IF %_ERR%==7 ECHO #### %_FILE% CAUSED ERROR - OUT OF MEMORY ERROR #### >> %_LOGFILE%
IF %_ERR%==6 ECHO #### %_FILE% CAUSED ERROR - PROCESS INTERRUPTED #### >> %_LOGFILE%
IF %_ERR%==5 ECHO #### %_FILE% CAUSED ERROR - BAD ARGUMENT #### >> %_LOGFILE%
IF %_ERR%==4 ECHO #### %_FILE% CAUSED ERROR - BATCHMODE ERROR #### >> %_LOGFILE%
IF %_ERR%==3 ECHO #### %_FILE% CAUSED ERROR - UNKNOWN FILE #### >> %_LOGFILE%
IF %_ERR%==2 ECHO #### %_FILE% CAUSED ERROR - FILE NOT FOUND #### >> %_LOGFILE%
IF %_ERR%==1 ECHO #### %_FILE% CAUSED ERROR - INVALID FILE #### >> %_LOGFILE%
GOTO SENDMAIL

:TARERR
:: //////////////////////////////////////////////
::
:: Log all tar errors, send an email, and quit
IF %_ERR%==255 ECHO #### %_FILE% CAUSED ERROR - USER STOPPED PROCESS #### >> %_LOGFILE%
IF %_ERR%==8 ECHO #### %_FILE% CAUSED ERROR - NOT ENOUGH MEMORY #### >> %_LOGFILE%
IF %_ERR%==7 ECHO #### %_FILE% CAUSED ERROR - COMMAND LINE ERROR #### >> %_LOGFILE%
IF %_ERR%==2 ECHO #### %_FILE% CAUSED ERROR - FATAL ERROR #### >> %_LOGFILE%
GOTO SENDMAIL



:SENDMAIL
:: //////////////////////////////////////////////
::
:: Set the temp file location
SET _TEMPMAIL=%TEMP%\TEMPMAIL.%RANDOM%.TXT

:: //////////////////////////////////////////////
::
:: Echo the basic headers to the temp file

ECHO TO: "Croson, John" ^ > %_TEMPMAIL%
ECHO FROM: "IHBS Administrator" ^ >> %_TEMPMAIL%
ECHO SUBJECT: SCRIPT FAILURE >> %_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Echo the blank line that separates the header from the body text

ECHO.>>%_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Echo the body text to the temp file

ECHO %_MSG% >> %_TEMPMAIL%
ECHO Check %_LOGFILE% for details.>> %_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Move the temp file to the mail pickup directory

MOVE %_TEMPMAIL% C:\INETPUB\MAILROOT\PICKUP
ENDLOCAL
EXIT

:EOF
START "EMS File Sorting" /MIN WSCRIPT.EXE C:\SCRIPTS\ManageFiles.vbs
ENDLOCAL
EXIT

Now that the files are decrypted and un-tarred, they must be sorted into an archive directory and a processing directory. This is easier using vb scripting, and WSH objects.

Option Explicit

Dim FSO
Dim sFolder
Dim sFile
Dim sWffSrc
Dim sSfhSrc
Dim sWffArch
Dim sSfhArch
Dim sWffAqu
Dim sSfhAqu
Dim sFileColl
Dim inFile
Dim i, l, arrFileLines()
Dim sDate
Dim sDay
Dim sMonth
Dim sYear
Dim net
Dim sMail
Dim sStartTime
Dim sFinishTime
Dim sNumWffFiles
Dim sNumSfhFiles

' ///////////////////////////////////////////
' Get our path environments worked out...

sWffSrc = "H:\CLIENT\Medical Records\WFF\xtra\audit\"
sSfhSrc = "H:\CLIENT\Medical Records\SFH\xtra\audit\"
sWffArch = "H:\CLIENT\Medical Records\WFF\rpt_arch\"
sSfhArch = "H:\CLIENT\Medical Records\SFH\rpt_arch\"
sWffAqu = "M:\Aquarius\4CLI\WFF\"
sSfhAqu = "M:\Aquarius\4CLI\SFH\"

' ///////////////////////////////////////////
' Set the start time
sStartTime = Now()


' ///////////////////////////////////////////
' Create the FSO object we need to use
Set FSO = CreateObject("Scripting.FileSystemObject")


' ///////////////////////////////////////////
' Before we do anything, make sure we have
' an H and M drive
Set net = CreateObject("WScript.Network")
If FSO.GetDriveName("M:") = "" Then
net.MapNetworkDrive "M:", "\\MYServer\ScanDocs","False"
End If

If FSO.GetDriveName("H:") = "" Then
net.MapNetworkDrive "H:", "\\MYServer\Download","False"
End If


' ///////////////////////////////////////////
' Set the folder and file collection
Set sFolder = FSO.GetFolder(sWffSrc)
Set sFileColl = sFolder.Files

' ///////////////////////////////////////////
' Loop through each file in sWffSrc
' and create an array of lines in each file

For Each sFile in sFileColl
Set inFile = FSO.OpenTextFile(sWffSrc & sFile.Name, 1)
Do Until inFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = inFile.ReadLine
i = i + 1
Loop
' close the file, we don't need it anymore
inFile.Close

 ' ///////////////////////////////////////////
 ' Now read the contents of the file, from the bottom up,
 ' since we only need the %date% string to determine where
 ' the file belongs in our filesystem

  For l = Ubound(arrFileLines) to LBound(arrFileLines) Step -1
       If Left(arrFileLines(l),6) = "%date%" Then
         sDate = Replace(Mid(arrFileLines(l),7,8), "/", "")
         sMonth = Left(sDate, 2)
         sYear = Left(Year(Now), 2) & Right(sDate, 2)
         sDay = Mid(sDate, 3, 2)

         ' ///////////////////////////////////////////
         ' Check for the existence of the archive folders
           ' ///////////////////////////////////////////
           ' First check for the year
           If FSO.FolderExists(sWffArch & sYear) Then
              'MsgBox "Folder " & sWffArch & sYear & " exists."
           
             ' ///////////////////////////////////////////
             ' Now check for the month
             If FSO.FolderExists(sWffArch & sYear & "\" & sMonth & sYear) Then
              'MsgBox "Folder " & sWffArch & sYear & "\" & sMonth & sYear & " exists."
           
                   ' ///////////////////////////////////////////
                   ' Now check for the day
                       If FSO.FolderExists(sWffArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear) Then
                          'MsgBox "Folder " & sWffArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear & " exists."

                       Else
                         FSO.CreateFolder(sWffArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear)
                         'MsgBox "Folder " & sWffArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear & " created."

                       End If 'Day check
                    
             Else
             ' ///////////////////////////////////////////
             ' Month doesn't exist, and anything below it
             ' so create it.
               FSO.CreateFolder(sWffArch & sYear & "\" & sMonth & sYear)
               FSO.CreateFolder(sWffArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear)             
             End If 'Month check
           Else
             ' ///////////////////////////////////////////
             ' Year doesn't exist, and nothing below it
             ' so create it.
             FSO.CreateFolder(sWffArch & sYear)
             FSO.CreateFolder(sWffArch & sYear & "\" & sMonth & sYear)
             FSO.CreateFolder(sWffArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear)
           End If 'Year check
       ' ///////////////////////////////////////////
       ' Now lets copy/move the file, since we've
       ' created the folders
       ' ///////////////////////////////////////////
       ' First copy file to the aquarius folders for indexing
       ' But make the destination folder if it doesn't exist
       If FSO.FolderExists(sWffAqu & sMonth & sDay & sYear) Then
           FSO.CopyFile sWffSrc & sFile.Name, sWffAqu & sMonth & sDay & sYear & "\"

       Else
           FSO.CreateFolder(sWffAqu & sMonth & sDay & sYear)
           FSO.CopyFile sWffSrc & sFile.Name, sWffAqu & sMonth & sDay & sYear & "\"
       End If

       ' ///////////////////////////////////////////
       ' Now copy the file to the archive folder
       ' and delete the original (move fails if file exists
       ' and copy overwrites without error).
       FSO.CopyFile sWffSrc & sFile.Name, sWffArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear & "\"
       FSO.DeleteFile sWffSrc & sFile.Name

       ' ///////////////////////////////////////////
       ' Get out of this loop since we've found %date%
       Exit For
       End If '%date% check
  Next
sNumWffFiles = sNumWffFiles + 1
Next

' ///////////////////////////////////////////
' Now do the same thing for the SFH files
' ///////////////////////////////////////////

' ///////////////////////////////////////////
' Clear our objects, and reset them
Set sFolder = Nothing
Set sFileColl = Nothing

' ///////////////////////////////////////////
' Set the folder and file collection
Set sFolder = FSO.GetFolder(sSfhSrc)
Set sFileColl = sFolder.Files

' ///////////////////////////////////////////
' Loop through each file in sSfhSrc
' and create an array of lines in each file

For Each sFile in sFileColl
Set inFile = FSO.OpenTextFile(sSfhSrc & sFile.Name, 1)
Do Until inFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = inFile.ReadLine
i = i + 1
Loop
' close the file, we don't need it anymore
inFile.Close

 ' ///////////////////////////////////////////
 ' Now read the contents of the file, from the bottom up,
 ' since we only need the %date% string to determine where
 ' the file belongs in our filesystem

  For l = Ubound(arrFileLines) to LBound(arrFileLines) Step -1
       If Left(arrFileLines(l),6) = "%date%" Then
         sDate = Replace(Mid(arrFileLines(l),7,8), "/", "")
         sMonth = Left(sDate, 2)
         sYear = Left(Year(Now), 2) & Right(sDate, 2)
         sDay = Mid(sDate, 3, 2)
         ' ///////////////////////////////////////////
         ' Check for the existence of the archive folders
           ' ///////////////////////////////////////////
           ' First check for the year
           If FSO.FolderExists(sSfhArch & sYear) Then
             ' ///////////////////////////////////////////
             ' Now check for the month
             If FSO.FolderExists(sSfhArch & sYear & "\" & sMonth & sYear) Then
                   ' ///////////////////////////////////////////
                   ' Now check for the day
                       If FSO.FolderExists(sSfhArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear) Then
                       Else
                         FSO.CreateFolder(sSfhArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear)
                       End If 'Day check
             Else
             ' ///////////////////////////////////////////
             ' Month doesn't exist, and anything below it
             ' so create it.
               FSO.CreateFolder(sSfhArch & sYear & "\" & sMonth & sYear)
               FSO.CreateFolder(sSfhArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear)
             End If 'Month check
           Else
             ' ///////////////////////////////////////////
             ' Year doesn't exist, and nothing below it
             ' so create it.
             FSO.CreateFolder(sSfhArch & sYear)
             FSO.CreateFolder(sSfhArch & sYear & "\" & sMonth & sYear)
             FSO.CreateFolder(sSfhArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear)
           End If 'Year check

       ' ///////////////////////////////////////////
       ' Now lets copy/move the file, since we've
       ' created the folders
       ' ///////////////////////////////////////////
       ' First copy file to the aquarius folders for indexing
       ' But make the destination folder if it doesn't exist
       If FSO.FolderExists(sSfhAqu & sMonth & sDay & sYear) Then
           FSO.CopyFile sSfhSrc & sFile.Name, sSfhAqu & sMonth & sDay & sYear & "\"
       Else
           FSO.CreateFolder(sSfhAqu & sMonth & sDay & sYear)
           FSO.CopyFile sSfhSrc & sFile.Name, sSfhAqu & sMonth & sDay & sYear & "\"
       End If

       ' ///////////////////////////////////////////
       ' Now copy the file to the archive folder
       'MsgBox "File " & sSfhSrc & sFile.Name & vbNewLine & " moved to " & vbNewLine & sSfhArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear & "."
       FSO.CopyFile sSfhSrc & sFile.Name, sSfhArch & sYear & "\" & sMonth & sYear & "\" & sMonth & sDay & sYear & "\"
       FSO.DeleteFile sSfhSrc & sFile.Name

       ' ///////////////////////////////////////////
       ' Get out of this loop since we've found %date%
       Exit For
       End If '%date% check
  Next
sNumSfhFiles = sNumSfhFiles + 1
Next

' ///////////////////////////////////////////
' Set the finish time
sFinishTime = Now()

' //////////////////////////////
' Now send an email notification that the
' files are ready.
Set sMail = FSO.CreateTextFile("C:\Temp\TEMPMAIL.TXT", True)
sMail.WriteLine("TO: " & Chr(34) & "JOHN CROSON" & Chr(34) & "<john.croson@ihbsonline.com>," & Chr(34) & "John Croson" & Chr(34) & "<pcnorb@yahoo.com>")
sMail.WriteLine("FROM: " & Chr(34) & "IHBS Administrator" & Chr(34) & "<admin@ihbsonline.com>")
sMail.WriteLine("SUBJECT: EMS EMR File Sorting")
sMail.WriteBlankLines(1)
sMail.WriteLine("Electronic Medical Record sorting is finished.")
sMail.WriteBlankLines(1)
sMail.WriteLine("Start time was " & sStartTime & ". Finish time was " & sFinishTime & ".")
sMail.WriteLine("The number of WFF files was " & sNumWffFiles & ". The number of SFH files was " & sNumSfhFiles & ".")
sMail.Close
FSO.MoveFile "C:\Temp\TEMPMAIL.TXT", "C:\Inetpub\MAILROOT\PICKUP\"

Friday, September 12, 2008

Using Batch to decrypt PGP and more

This script was recently written to automatically process files on our FTP server. They come in daily, in pairs, and dated with todays date, e.g. XXERUP0912.dat.pgp.

The script checks for existence, ensures a pair is found, makes sure they are the right and same date, then decrypts them and calls an external program I wrote to inject them into a SQL database for querying via ASP, which I also wrote. If any of the steps fail, it gets logged, and an email notification is sent to a number of folks.

I had a bit of an issue with expansion, but found the error of my ways in short order...

@ECHO On
SETLOCAL ENABLEDELAYEDEXPANSION

:: //////////////////////////////////////////////
::
::  Set path variables for key access, passphrase,
::  and source our key files.

SET PGPPATH=C:\PGP_Keys\
SET PGPPASS=MYPGPPASS

:: The "KEY" to a successful import of our keys is running these commands as the user
:: that will be running this script.
::  pgp +batchmode -ka %PGPPATH%SECRING.SKR
::  pgp +batchmode -ka %PGPPATH%PUBRING.PKR
::  pgp -ke 0xEC671710 <-- after this command, you will be asked to trust the key; this must be done!


:: //////////////////////////////////////////////
::
::  Set our working dir, destination dir, _LOGFILE
::  and get in there!

SET _LOGFILE=H:\COVNENT\LOGS\DEMOUPDATELOG.TXT
SET _OPATH=H:\COVNENT\MEDICA~1\
H:
CD COVNENT

:: //////////////////////////////////////////////
::
:: Log beautification
ECHO --------------------------------------------------------------------------- >> %_LOGFILE%
ECHO -- START %DATE% - %TIME% -- >> %_LOGFILE%
ECHO --------------------------------------------------------------------------- >> %_LOGFILE%

::  //////////////////////////////////////////////
::
::  First test for existance of files, and
::  notify us if they don't exist.

DIR /B *ERUP*.PGP
IF NOT %ERRORLEVEL%==0 SET _MSG=No EMS Demographic Update files were found. & ECHO #### ERROR - NO FILES FOUND #### >> %_LOGFILE% & GOTO SENDMAIL


:: //////////////////////////////////////////////
::
::  Make sure our file count is correct. It should
::  be 2.
DIR /B *ERUP*.PGP | FIND /C /V "" > COUNT.TXT
FOR /F "tokens=1" %%f IN (COUNT.TXT) DO (
IF NOT %%f==2 SET _MSG=The file count of the EMS Demographic files is %%f, and 2 were expected. & DEL COUNT.TXT & ECHO #### ERROR - FILE COUNT WAS ONLY %%f. #### >> %_LOGFILE% & GOTO SENDMAIL
)


:: //////////////////////////////////////////////
::
::  Now, get the MMDD, and compare it to
::  what we received from Wheaton, which SHOULD
::  be the same. If not, send an email so it
::  can be checked out.


DEL COUNT.TXT
SET _TODAY=%DATE:~4,2%%DATE:~7,2%

FOR /F %%f IN ('DIR /B *ERUP*.PGP') DO (
SET _FILE=%%f
SET _FDATE=!_FILE:~6,4!
IF NOT !_FDATE! == !_TODAY! SET _MSG=The date of the EMS Update Demographic files do not match. --- FILE DATE = !_FDATE!, RUN DATE = !_TODAY! & ECHO #### ERROR - DATE MISMATCH - FILE DATE = !_FDATE!, RUN DATE = !_TODAY! #### >> %_LOGFILE% & GOTO SENDMAIL
)

:: //////////////////////////////////////////////
::
::  Loop through our encrypted files,
::  decrypt them and enter a log entry for each
::  file processed.

FOR /F %%f IN ('DIR /B *ERUP*.PGP') DO (
PGP +BATCHMODE %%f
IF %ERRORLEVEL% NEQ 0 (SET _ERR=!ERRORLEVEL! & SET _FILE=%%f & SET _MSG=There has been PGP decryption error on the EMS Demographic Update file, %%f. & GOTO PGPERR) ELSE (ECHO DECRYPTED %%f >> %_LOGFILE% & DEL %%f)
)

:: //////////////////////////////////////////////
::
::  Run our utility to import the EMS update
::  data into the database, echoing any output
::  to our log file.

C:\SCRIPTS\EMSUpdate.exe >> %_LOGFILE% 2>&1
GOTO EOF

:PGPERR
:: //////////////////////////////////////////////
::
:: Log all PGP errors, send an email, and quit
IF %_ERR%==33 ECHO #### %_FILE% CAUSED ERROR - DECOMPRESSION ERROR #### >> %_LOGFILE%
IF %_ERR%==32 ECHO #### %_FILE% CAUSED ERROR - DECRYPTION ERROR #### >> %_LOGFILE%
IF %_ERR%==31 ECHO #### %_FILE% CAUSED ERROR - PUBLIC KEY DECRYPTION ERROR #### >> %_LOGFILE%
IF %_ERR%==30 ECHO #### %_FILE% CAUSED ERROR - SIGNATURE CHECK ERROR #### >> %_LOGFILE%
IF %_ERR%==19 ECHO #### %_FILE% CAUSED ERROR - KEY SIGNATURE REMOVAL ERROR #### >> %_LOGFILE%
IF %_ERR%==18 ECHO #### %_FILE% CAUSED ERROR - KEY SIGNATURE ERROR OR KEY SIGNATURE REVOKE ERROR #### >> %_LOGFILE%
IF %_ERR%==17 ECHO #### %_FILE% CAUSED ERROR - KEYRING CHECK ERROR #### >> %_LOGFILE%
IF %_ERR%==16 ECHO #### %_FILE% CAUSED ERROR - KEYRING REMOVAL ERROR #### >> %_LOGFILE%
IF %_ERR%==15 ECHO #### %_FILE% CAUSED ERROR - KEYRING VIEW ERROR #### >> %_LOGFILE%
IF %_ERR%==14 ECHO #### %_FILE% CAUSED ERROR - KEYRING EDIT ERROR #### >> %_LOGFILE%
IF %_ERR%==13 ECHO #### %_FILE% CAUSED ERROR - KEYRING EXTRACT ERROR #### >> %_LOGFILE%
IF %_ERR%==12 ECHO #### %_FILE% CAUSED ERROR - KEYRING ADD ERROR #### >> %_LOGFILE%
IF %_ERR%==11 ECHO #### %_FILE% CAUSED ERROR - NON-EXISTING KEY ERROR #### >> %_LOGFILE%
IF %_ERR%==10 ECHO #### %_FILE% CAUSED ERROR - KEY GENERATION ERROR #### >> %_LOGFILE%
IF %_ERR%==7 ECHO #### %_FILE% CAUSED ERROR - OUT OF MEMORY ERROR #### >> %_LOGFILE%
IF %_ERR%==6 ECHO #### %_FILE% CAUSED ERROR - PROCESS INTERRUPTED #### >> %_LOGFILE%
IF %_ERR%==5 ECHO #### %_FILE% CAUSED ERROR - BAD ARGUMENT #### >> %_LOGFILE%
IF %_ERR%==4 ECHO #### %_FILE% CAUSED ERROR - BATCHMODE ERROR #### >> %_LOGFILE%
IF %_ERR%==3 ECHO #### %_FILE% CAUSED ERROR - UNKNOWN FILE #### >> %_LOGFILE%
IF %_ERR%==2 ECHO #### %_FILE% CAUSED ERROR - FILE NOT FOUND #### >> %_LOGFILE%
IF %_ERR%==1 ECHO #### %_FILE% CAUSED ERROR - INVALID FILE #### >> %_LOGFILE%
GOTO SENDMAIL


:SENDMAIL
:: //////////////////////////////////////////////
::
:: Set the temp file location
SET _TEMPMAIL=%TEMP%\TEMPMAIL.%RANDOM%.TXT

:: //////////////////////////////////////////////
::
:: Echo the basic headers to the temp file
ECHO TO: "Croson, John" ^<JOHN.CROSON@IHBSONLINE.COM^> > %_TEMPMAIL%
ECHO FROM: "IHBS Administrator" ^<ADMIN@IHBSONLINE.COM^> >> %_TEMPMAIL%
ECHO SUBJECT: EMS Demographic Update Failure >> %_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Echo the blank line that separates the header from the body text

ECHO.>>%_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Echo the body text to the temp file

ECHO %_MSG% >> %_TEMPMAIL%
ECHO Check %_LOGFILE% for details.>> %_TEMPMAIL%

:: //////////////////////////////////////////////
::
:: Move the temp file to the mail pickup directory
:: adjust this location for your system

MOVE %_TEMPMAIL% C:\INETPUB\MAILROOT\PICKUP
GOTO EOF


:EOF
ENDLOCAL
:: EXIT

Now for the bit that pushes it to the MSSQL server.

I wrote this small vb program that steps through each line in the flat text file, converts the line feeds from Unix to Windows, sanitizes the data, and injects it into a MS SQL database.

Here is the asp page that queries the database. Yes, I'm aware of SQL injection, but since this is a private page on an intranet web, I don't need to deal with script kiddies... ;)

<%@LANGUAGE=VBSCRIPT%>

<%

'Check when the last update was done
' Set the connection info
Conn = "Provider=SQLOLEDB; Data Source=IBSFP; Initial Catalog=dbUpdates; User ID=user; Password=pwd" 
'Create the connection
Set oCon = Server.CreateObject("ADODB.Connection")
'Open the connection object
oCon.Open Conn
'fetch the recordsets
Set oRst = Server.CreateObject("ADODB.Recordset")
sSql="SELECT MAX(update_date) AS update_date, MAX(file_date) AS file_date FROM tblPatient"
oRst.Open sSql,oCon,1,1
'Do While Not oRst.EOF
FileDate=oRst.Fields("file_date")
LastUpdate=oRst.Fields("update_date")
'Loop
Set oCon=Nothing
Set oRst=Nothing


	' Customization variables
	DebugFlag = False        ' set TRUE for debugging
	QueryForm = Request.ServerVariables("PATH_INFO")


If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
        CaseNo = Request.Form("CaseNo")
        MRN = Request.Form("MRN")

	       If Request.Form("Action") = "Search" Then
        	    NewQuery = TRUE
		End If
End If


%> 





EMS Demographic Update Search

Last Updated on <%=LastUpdate%>, with a file date of <%=FileDate%>.

    Important notes on use:
  • Search on Case Number OR MRN. Not both.
  • Wildcards are not used, and will return nothing.
  • Do not use letters in your MRN search, you will find nothing.


<% If DebugFlag then %>
	Case Number	= <%=CaseNo%>
	MRN		= <%=MRN%>
	NewQuery	= <%=NewQuery%>
	strSql		= <%=strSql%>
    
<% End If %> <% If NewQuery Then ' Set the connection info ConnStr = "Provider=SQLOLEDB; Data Source=IBSFP; Initial Catalog=dbUpdates; User ID=user; Password=pwd" 'Create the connection Set objConnect = Server.CreateObject("ADODB.Connection") 'Open the connection object objConnect.Open ConnStr ' Make sure a query is used If Trim(CaseNo) = "" And Trim(MRN) = "" Then Response.Write "

You failed to query for anything...
Try again.

" Response.End End If 'Set the query statement strSql = "Select tblGuarantor.*, tblPatient.*, tblSubscriber.* From " _ & "tblGuarantor Inner Join " _ & "tblPatient On tblGuarantor.case_number = tblPatient.case_number Inner Join " _ & "tblSubscriber On tblGuarantor.case_number = tblSubscriber.case_number WHERE (" If Trim(CaseNo) <> "" Then strSql = strSql & "tblSubscriber.case_number = '" & CaseNo & "'" End If If Trim(MRN) <> "" And Trim(CaseNo) = "" Then ' MRN Sql search strSql = strSql & "tblSubscriber.mrn = '" & MRN & "'" End If strSql = strSql & ")" 'fetch the recordsets Set objRst = Server.CreateObject("ADODB.Recordset") If DebugFlag Then Response.Write "
" & strSQL & "
" End If objRst.Open strSql,objConnect,1,1 Do While Not objRst.EOF If DebugFlag Then Response.Write objRst.Fields.Count For i = 0 to objRst.Fields.Count - 1 Response.Write "

" & objRst.Fields.Item(i).Name & " - " & objRst.Fields.Item(i) & "
" Next End If CaseNumber = objRst.Fields("case_number") %>

Case Number : <%=objRst.Fields("case_number")%>
MRN :<%=objRst.Fields("campus") & objRst.Fields("mrn")%>
File Date :<%=objRst.Fields("file_date")%>
Date Modified :<%=objRst.Fields("update_date")%>
Patient Information
Name :<%=objRst.Fields("pname")%>
Address 1 :<%=objRst.Fields("paddress1")%>
Address 2 :<%=objRst.Fields("paddress2")%>
City :<%=objRst.Fields("pcity")%>
State :<%=objRst.Fields("pstate")%>
Zip :<%=objRst.Fields("pzip")%>
Phone :<%=objRst.Fields("area") & "-" & Left(objRst.Fields("pphone"),3) & "-" & Right(objRst.Fields("pphone"),4)%>
Gender :<%=objRst.Fields("psex")%>
DOB :<%=objRst.Fields("dob")%>
SSN :<% Select Case Trim(objRst.Fields("ssn")) Case "" 'Do nothing Case "NONE" Response.Write objRst.Fields("ssn") Case Else Response.Write Left(objRst.Fields("ssn"),3) & "-" & Mid(objRst.Fields("ssn"),4,2) & "-" & Right(objRst.Fields("ssn"),4) End Select %>
Marital Status :<%=objRst.Fields("marital_status")%>
Cheif Complaint :<%=objRst.Fields("cc")%>
Personal MD :<%=objRst.Fields("per_md")%>
Admitting MD :<%=objRst.Fields("adm_md")%>
Disposition :<%=objRst.Fields("disposition")%>
Admit Date-Time :<%=objRst.Fields("adm_date") & " - " & objRst.Fields("adm_time")%>
Discharge Date :<%=objRst.Fields("dc_date")%>
Other :<%=objRst.Fields("pother")%>
Guarantor Information
Name :<%=objRst.Fields("gname")%>
Address 1 :<%=objRst.Fields("gaddress1")%>
Address 2 :<%=objRst.Fields("gaddress2")%>
City :<%=objRst.Fields("gcity")%>
State :<%=objRst.Fields("gstate")%>
Zip :<%=Left(objRst.Fields("gzip"),5) & "-" & Right(objRst.Fields("gzip"),4)%>
Phone :<%=Left(objRst.Fields("gphone"),3) & "-" & Mid(objRst.Fields("gphone"),5,3) & "-" & Right(objRst.Fields("gphone"),4)%>
Employer :<%=objRst.Fields("employer")%>
Employer Address 1 :<%=objRst.Fields("employer_address1")%>
Employer Address 2 :<%=objRst.Fields("employer_address2")%>
Employer City :<%=objRst.Fields("employer_city")%>
Employer State :<%=objRst.Fields("employer_state")%>
Employer Zip :<%=objRst.Fields("employer_zip")%>
Employer Phone :<%=Left(objRst.Fields("employer_phone"),3) & "-" & Mid(objRst.Fields("employer_phone"),5,3) & "-" & Right(objRst.Fields("employer_phone"),4)%>
Injury Date :<%=objRst.Fields("injury_date")%>
Injury Date :<%=objRst.Fields("injury_code")%>
Other :<%=objRst.Fields("gother1")%>
Subscriber Information
Primary Employer :<%=objRst.Fields("pri_sub_emp")%>
Secondary Employer :<%=objRst.Fields("sec_sub_emp")%>
Tertiary Employer :<%=objRst.Fields("tert_sub_emp")%>
Primary Subscriber :<%=objRst.Fields("pri_sub")%>
Secondary Subscriber :<%=objRst.Fields("sec_sub")%>
Tertiary Subscriber :<%=objRst.Fields("tert_sub")%>
Gender :<%=objRst.Fields("ssex")%>
SSN :<% Select Case Trim(objRst.Fields("sssn")) Case "" 'Do nothing Case "NONE" Response.Write objRst.Fields("sssn") Case Else Response.Write Left(objRst.Fields("sssn"),3) & "-" & Mid(objRst.Fields("sssn"),4,2) & "-" & Right(objRst.Fields("sssn"),4) End Select %>
Employer Name :<%=objRst.Fields("emp_name")%>
Employer Phone :<%=objRst.Fields("emp_phone")%>
Chief Complaint :<%=objRst.Fields("chief_complaint")%>
Hospital Discharge Date :<%=objRst.Fields("hdc_date")%>
Hospital Admit Mode :<%=objRst.Fields("hp_adm_mode")%>
Hospital Disposition :<%=objRst.Fields("hp_disp")%>
Other :<%=objRst.Fields("sother")%>
Campus :<%=objRst.Fields("campus")%>
<% objRst.MoveNext Loop strISql = "SELECT * FROM tblInsurance WHERE (case_number = '" & Trim(CaseNumber) & "')" Set objIRst = Server.CreateObject("ADODB.Recordset") objIRst.Open strISql,objConnect,1,1 If DebugFlag Then %>
<%=strISql%>
<%Response.Write objIRst.Fields.Count For i = 0 to objIRst.Fields.Count - 1 Response.Write "

" & objIRst.Fields.Item(i).Name & " - " & objIRst.Fields.Item(i) & "
" Next%> <% End If %>

Insurance Information

<% 'Shade every other record strOddColor = "bgcolor='#FFFFFF'" strEvenColor = "bgcolor='#C0C0C0'" intLineNum = 0 If Not objIRst.BOF And Not objIRst.EOF Then Do While Not objIRst.EOF ' This bit sets the alternating shades of rows intLineNum = intLineNum + 1 If intLineNum Mod 2 = 0 Then strLineColor = strEvenColor Else strLineColor = strOddColor End If %> > <% objIRst.MoveNext Loop else ' NOT objIRst.EOF Response.Write "

No patients matched your search.

" end if %>
Ins No. Name Add. 1 Add. 2 City State Zip Sub Name Group Name Group Number Policy Number Ins Code Gender DOB Other
<%=objIRst.Fields("ins_number")%> <%=objIRst.Fields("iname")%> <%=objIRst.Fields("iaddress1")%> <%=objIRst.Fields("iaddress2")%> <%=objIRst.Fields("icity")%> <%=objIRst.Fields("istate")%> <%=objIRst.Fields("izip")%> <%=objIRst.Fields("sub_name")%> <%=objIRst.Fields("group_name")%> <%=objIRst.Fields("group_number")%> <%=objIRst.Fields("policy_number")%> <%=objIRst.Fields("insurance_code")%> <%=objIRst.Fields("extra")%> <%=objIRst.Fields("auth")%> <%=objIRst.Fields("iother")%>
<% objRst.close Set objRst = Nothing Set objConnect = Nothing ' End NewQuery End If %>

Hope this helps someone out there.

Tuesday, August 19, 2008

Windows Search; the honeymoon is over

Well, after my short test of Windows Search, I've come to this conclusion; Not ready for prime-time. My expectation was that it Should Just Work (tm).

I really think they tried to come up with a good search tool, but this doesn't come close to the polished utility I expected.

It seems to have issues with resetting to default index locations periodically, not providing an uninstallation method, rebooting without an option to delay it when installing, etc. etc.

Just check the forums here: http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=127&SiteID=1

I'm moving on to have a look at Locate32.net, an application not unlike updatedb in the unix world.

I should have stuck with this one to begin with, since I AM a Linux snob.

Sunday, August 17, 2008

Windows Search indexing status; 149,000 files and counting

I'm keeping a close eye on the status of Windows Search on my Windows 2003 Server. The SAN shares out hundreds of gb of data, but I only need to index about 100gb... :-0

When I initially installed the new Windows Service, it occurred to me that I could roll it out to all the users, and let them index what they wanted...rrriiiiiight. I read an article that indicated when WS4 is installed, it automatically adds mapped locations to the index. WOW, that would be tons of network traffic, even if the service does throttle it back to minimize network impact.

I found it to be a better implementation to install it on the server, wait for the index to complete, then roll it out to the users, and control the index locations via GPO, keeping them off the network.

Unfortunately, the index database has grown to 25gb, and shows no signs of stopping. Good thing I decided to home the database on a volume with enough room...

I'll post back when the indexing process completes. In a day or two, or more.

Saturday, August 16, 2008

Windows Search

I'd written an article a couple of months ago about leveraging Microsoft Indexing Services in the enterprise to facilitate searching network shares and publishing an ASP interface to an intranet for your users.

My environment consists of a Windows 2003 Standard server providing the Indexing Services, a second AD / ASP / MS SQL 2k Windows 2003 Standard server with attached SAN and numerous network shares holding nearly 1tb in size, indexed by the Indexing Server. The Indexing Server also hosts the ASP pages for user searches.

I've found out several things lately about Indexing Services, and would like to take a moment to point out some basic principle's of this technology:

  1. It's not necessary for the windows workstations to have Indexing Services enabled to enjoy a speedy network file search experience. In fact, many people will recommend that this service be turned off and disabled on workstations, since it can slow performance.
  2. To give Windows Desktop Search (WDS) the ability to benefit from indexed network shares, set up Indexing Services on those servers hosting the files. WDS will find the index and search it. There is one important caveat; while Indexing Services is indexing data, that database is not available for searching, and WDS reverts to simple file searching, which is snot-slow. The client will also not benefit from any iFilters that may be utilized by said Indexing Server.
  3. As pointed out above, in my environment the users do not have the luxury of using WDS to search the servers index database, since they are homed on a different server; until now.

I recently read an interesting article on Windows Search 4, a replacement for WDS 3. I'll note that WDS 3 caused many issues in my environment when it was released by Windows Update Services as a patch to WDS 2.x, causing me to do the RunAroundAndRemove dance.

As I mentioned in my other article, I wanted to provide a better search mechanism, and at the time the ASP page was the ticket, since I had the spare server, and didn't want to bog my app server down.

When I found Windows Search 4, I immediately saw light at the end of the tunnel. There are many features in this that are lacking in WDS:

  1. Group Policy for management.
  2. Operates in low priority I/O, and utilizes resource management resulting in better performance.
  3. Vista style preview handlers that allow document browsing in the preview pane.
  4. Support for EFS-encrypted shares.
  5. Federation of searches to remote indexes.

I immediately installed the client on my test XP workstation, and the server component on my production *gulp* server. Note: It WILL reboot, so perform this during off hours! If I had a "dev" environment, I'd use it...

After configuring the server index locations, you will be able to search them from your Windows Search 4 clients.

I have to say, I'm still testing this, but it works very well, doesn't seem to suffer from some of the shortcomings of Indexing Services, and looks shiny!

Tuesday, July 15, 2008

Importing CSV to MS SQL -or- Did I Do This Right?

I was trying to find a quick and dirty method of exporting, and subsequently importing CSV data into MS SQL; programmatically.

I'm dealing with rather small amounts of data, so this method may not work for many, but it Works For Me (tm).

My source data comes from an AIX / uSQL box running Misys Tiger. Yes, I realize I could query that database directly, if I purchased the Transoft ODBC drivers for Server 2003, but at a couple grand it's much more cost effective to do it this way. The easiest method to retrieve my data is to use Misys Query, create a scheduled job, and export the resulting data to a network location for DTS (Data Transformation Services) to pick it up.

The data is a rather exhaustive list of Insurance Companies, and their related plans. As one could imagine, this data is ever changing, a result of new "product lines" being developed by the lumbering health engine we call commercial payors. In the past, we exported the reports to PDF, and used the built-in search functionality to find the payor information needed. Unfortunately, it still leads to a "hunt-and-peck" situation, depending on how the user forms their search query. I felt I could dramatically speed up this operation by using MS SQL and ASP.

There are two reports, one for Plans, and one for Companies. The reports contain relate-able information, so I can use this to query and join results from the two tables created for each.

Misys Query contains a Cognos Scheduler component that can be set to run reports at a pre-defined time. It's pretty easy to schedule a report: open said report, run it, and when it's done, select Report, Schedule. Options for running it once or recurring, where and how to output the results, and even a command file option, when it's done running. The documentation explains about using security options, but this is not necessary, since Misys Query handles storing the authentication information needed for Cognos Scheduler. The other thing that threw me was that there was no option to run it on demand; you MUST schedule it...so testing forces you to set a time in the near future to observe it. Don't forget to re-set the time to the one you wanted originally!

The next step was to create a DTS job to take this exported CSV information, and import it into my database. DTS is really a great tool for this and makes it quite simple, IMHO.

After creating my database and tables, I set up my DTS job:

My steps were...

  1. To create a data source for your CSV file, either click and drag the circled icon to the workspace, or double-click it...
  2. A new widow will appear. Fill in the New Connection Box with a meaningful title, and in the Data Source drop-down box, select Text File (Source). Next to the File Name box, browse to the location of your CSV file, and click OK.
  3. The Text File Properties window appears. The option Delimited should be selected, with File Type being ANSI, Row Delimeter is {CR}{LF} and the Text Qualifier of Double Quote {"}. Change these as they suit your environment. There is also an option to skip rows, and to inform the processor that the first row contains column names. This comes handy in later steps. Clicking Next reveals your data, as it will appear during the transformation process. If this is not correct, click the back button and change it. Otherwise, click finish, and OK.
  4. You should now have an icon on the workspace that represents what we just created. Next, lets create the database connection object. As before, either drag and drop the MS OLE DB Provider for SQL Server object to the workspace, or double-click it to have one added. The easiest method is to right-click the workspace, and select "Add Connection". The default is the one we need.
  5. Give the new connection name something meaningful, choose your environments server and authentication method, and your database. Click OK.
  6. Right-Click the workspace, select Add Task, Transform Data Task. This changes your icon to this:
  7. Click on your Text data source to select it as your data source. The cursor will then change to a "Select Destination....", so select your DB connection next. A line appears between your sources, indicating the flow of data. Double-click that Data Transform Task.
  8. Again, give this task a meaningful title. Click the Destination tab.
  9. Select the table you'd like this data pushed to. Click the Transformations Tab.
  10. This is where most of the work is performed. You can see from the screenshot, that my fields don't map up, because I created an indexed field called ID. Probably didn't need it, but as my memory tried to recall what I'd learned in DBA classes, it was wise to have one... For each link, highlight it, and click the Edit button above. The next window that appears lets you set the source and destination fields. Make sure they match what you'd like, and click OK to save the results. In my environment I need to clear out all fields before inputting the new ones, and delete the source CSV files when finished.
  11. Right-Click the workspace, Add Task, Execute SQL Task. Give it a meaningful name, and in my case, truncate table my_tableofdata; in the SQL Statement box was enough to get the job done.
  12. Now to ensure this is performed before all other tasks. Right-Click the Transform Data Task you created earlier (the arrow pointing from data source to data destination).
  13. Select workflow properties. Click New. From the Source Step drop-down, select your Execute SQL Task. Now you can see why I suggested using meaningful names for these different objects... ;). Leave the Precedence set to Success. This ensures that the import of data only occurs when the truncate finishes successfully. A white/green striped arrow appears, pointing from the SQL command to the Text Data source.
  14. The last step is to add an Execute Process task. Right-Click the workspace, select Add Task, and then Execute Process.
  15. Give it a meaningful name, and in my case, the command is %COMSPEC% (cmd.exe, or the Windows Environment Variable that points to the Command Interpreter), with the arguments /C del C:\Path\To\Mfile.CSV.
  16. The last step in this is to create a Workflow object that fires when my Transformation of data occurs successfully.
Keep in mind, that this was all done by a NON-DBA, and a noob. I only posted this for those of you that might find it helpful, and hope that others with more experience (most, compared to me) can point out some time-saving and helpful hints that I can learn from.

Tuesday, July 01, 2008

Code highlighting in blogs

I've looked around a bit for a utility for highlighting my code samples here on BlogSpot, and found a couple of useful tools.

The first is code2html, which has been around for some time, and turns your perl, python and other types into pretty, formatted html. It works well, is CGI, but has some limits to its' implementation.

Then I found a great swiss-army knife of a utility, called highlight.js. It will automatically detect code blocks, and highlight them for you. It does have issues with too much code, or intermixed code on one page, but does a nice job. If it fails to correctly identify your code, a sample export page is included with the package that allows you to manually format your code to html.

Happy highlighting!

NPI Search Redundancy

Most healthcare professionals know at this point that all providers of health care, require NPI (National Provider Identifier) numbers. Without one, it will become increasingly difficult for claims to be paid by commercial payers, and impossible to collect medicare and medicaid payments.

Since we are a coding/billing/collection/management agency, we have frequented the NPPES (National Plan & Provider Enumeration System) to lookup NPI information.

Unfortunately, there have been brief periods of downtime of the site, causing us to implement our own solution: a backup of the registry.

I have a daily cron job that downloads the NPI database, push it into MySQL, giving us an albeit slow, but accurate access to an off-line version of this data.

The shell script below performs the retrieval:

#!/bin/sh
WORKDIR="/srv/htdocs/npi"
LOG="$WORKDIR/npi.log"
MONTH=`date +%b`
LASTMONTH=`date +%b --date='1 month ago'`
YEAR=`date +%Y`
URL="http://nppesdata.cms.hhs.gov/"
CFILE="NPPES_Data_Dissemination_${MONTH}_${YEAR}.zip"
LFILE="NPPES_Data_Dissemination_${LASTMONTH}_${YEAR}.zip"

cd $WORKDIR

# Attempt to download the file. --progress=dot:mega is used to prevent
# WGET_OUTPUT from getting too long.
FILE=$(2>&1 wget --timestamping --progress=dot:mega \
           "${URL}${CFILE}")


if [ "$?" -eq "0" ]; then
     echo "Retrieved zip file for ${MONTH} on `date`." > $LOG
     BOOL="1"
else
     FILE=$(2>&1 wget --timestamping --progress=dot:mega \
           "${URL}${LFILE}")
     if [ "$?" -eq "0" ]; then
             echo "Retrieved zip file for ${MONTH} on `date`." > $LOG
     else
             echo "There was a problem retreiving the file." > $LOG
     fi
fi

if [ "$BOOL" -eq "1" ]; then
    #Find npidata in zip file
    OFILE=`unzip -l ${CFILE} | grep csv | grep -vi 'fileheader' | awk '{ print $4 }'`

    #Now unzip it
    unzip ${CFILE} ${OFILE}
else
    #Find npidata in zip file
    OFILE=`unzip -l ${LFILE} | grep csv | grep -vi 'fileheader' | awk '{ print $4 }'`
   
    #Now unzip it
    unzip ${LFILE} ${OFILE}
fi

#Rename file
mv ${OFILE} source.csv

#Import data
echo "Importing ${OFILE} data..." >> ${LOG}
mysql -uroot -pmypass < /srv/htdocs/npi/npi.sql echo "Imported ${OFILE}." >> ${LOG}

#Remove source data
rm -f /srv/htdocs/npi/source.csv

#Remove the zip file
rm -f /srv/htdocs/npi/*.zip

Here is the npi.sql file used to import the data into the database. The data imported is trimmed down, since we didn't need all of it.

USE npi;
TRUNCATE TABLE npidata;
LOAD DATA INFILE '/srv/htdocs/npi/source.csv' INTO TABLE npidata FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(npi,entity_type_code,replacement_npi,ein,legal_business_name,last_name,first_name,
middle_name,prefix,suffix,credential,other_organization_name,other_organization_name_type_code,
other_last_name,other_first_name,other_middle_name,other_prefix,other_suffix,other_credential,
other_last_name_type_code,first_line_business_mailing_address,second_line_business_mailing_address,
business_mailing_address_city_name,business_mailing_address_state_name,business_mailing_address_postal_code,
business_mailing_address_country_code,business_mailing_address_telephone_number,
business_mailing_address_fax_number,first_line_business_practice_location_address,
second_line_business_practice_location_address,business_practice_location_address_city_name,
business_practice_location_address_state_name,business_practice_location_address_postal_code,
business_practice_location_address_country_code,business_practice_location_address_telephone_number,
business_practice_location_address_fax_number,enumeration_date,last_update_date,
npi_deactivation_reason_code,npi_deactivation_date,npi_reactivation_date,gender,authorized_official_last_name,
authorized_official_first_name,authorized_official_middle_name,authorized_official_title_or_position,
authorized_official_telephone_number);

And here is a look at the MySQL table used for this

mysql> describe npidata;
+-----------------------------------------------------+--------------+------+-----+---------+-------+
| Field                                               | Type         | Null | Key | Default | Extra |
+-----------------------------------------------------+--------------+------+-----+---------+-------+
| npi                                                 | varchar(20)  |      | PRI |         |       |
| entity_type_code                                    | char(2)      | YES  |     | NULL    |       |
| replacement_npi                                     | varchar(20)  | YES  |     | NULL    |       |
| ein                                                 | varchar(20)  | YES  |     | NULL    |       |
| legal_business_name                                 | varchar(255) | YES  |     | NULL    |       |
| last_name                                           | varchar(255) | YES  |     | NULL    |       |
| first_name                                          | varchar(255) | YES  |     | NULL    |       |
| middle_name                                         | varchar(255) | YES  |     | NULL    |       |
| prefix                                              | varchar(255) | YES  |     | NULL    |       |
| suffix                                              | varchar(255) | YES  |     | NULL    |       |
| credential                                          | varchar(255) | YES  |     | NULL    |       |
| other_organization_name                             | varchar(255) | YES  |     | NULL    |       |
| other_organization_name_type_code                   | varchar(10)  | YES  |     | NULL    |       |
| other_last_name                                     | varchar(255) | YES  |     | NULL    |       |
| other_first_name                                    | varchar(255) | YES  |     | NULL    |       |
| other_middle_name                                   | varchar(255) | YES  |     | NULL    |       |
| other_prefix                                        | varchar(255) | YES  |     | NULL    |       |
| other_suffix                                        | varchar(255) | YES  |     | NULL    |       |
| other_credential                                    | varchar(255) | YES  |     | NULL    |       |
| other_last_name_type_code                           | varchar(10)  | YES  |     | NULL    |       |
| first_line_business_mailing_address                 | varchar(255) | YES  |     | NULL    |       |
| second_line_business_mailing_address                | varchar(255) | YES  |     | NULL    |       |
| business_mailing_address_city_name                  | varchar(255) | YES  |     | NULL    |       |
| business_mailing_address_state_name                 | varchar(5)   |      | PRI |         |       |
| business_mailing_address_postal_code                | varchar(20)  | YES  |     | NULL    |       |
| business_mailing_address_country_code               | varchar(5)   | YES  |     | NULL    |       |
| business_mailing_address_telephone_number           | varchar(20)  | YES  |     | NULL    |       |
| business_mailing_address_fax_number                 | varchar(20)  | YES  |     | NULL    |       |
| first_line_business_practice_location_address       | varchar(255) | YES  |     | NULL    |       |
| second_line_business_practice_location_address      | varchar(255) | YES  |     | NULL    |       |
| business_practice_location_address_city_name        | varchar(255) | YES  |     | NULL    |       |
| business_practice_location_address_state_name       | varchar(5)   | YES  |     | NULL    |       |
| business_practice_location_address_postal_code      | varchar(20)  | YES  |     | NULL    |       |
| business_practice_location_address_country_code     | varchar(5)   | YES  |     | NULL    |       |
| business_practice_location_address_telephone_number | varchar(20)  | YES  |     | NULL    |       |
| business_practice_location_address_fax_number       | varchar(20)  | YES  |     | NULL    |       |
| enumeration_date                                    | varchar(15)  | YES  |     | NULL    |       |
| last_update_date                                    | varchar(15)  | YES  |     | NULL    |       |
| npi_deactivation_reason_code                        | varchar(255) | YES  |     | NULL    |       |
| npi_deactivation_date                               | varchar(15)  | YES  |     | NULL    |       |
| npi_reactivation_date                               | varchar(15)  | YES  |     | NULL    |       |
| gender                                              | char(2)      | YES  |     | NULL    |       |
| authorized_official_last_name                       | varchar(255) | YES  |     | NULL    |       |
| authorized_official_first_name                      | varchar(255) | YES  |     | NULL    |       |
| authorized_official_middle_name                     | varchar(255) | YES  |     | NULL    |       |
| authorized_official_title_or_position               | varchar(255) | YES  |     | NULL    |       |
| authorized_official_telephone_number                | varchar(20)  | YES  |     | 0       |       |
+-----------------------------------------------------+--------------+------+-----+---------+-------+

I then use a very simple php page to serve query's of the database:

<?php
function formatPhoneNumber($strPhone) {

        if (strlen($strPhone) != 10) {
                $strPrefix = substr($strPhone, 0, 3);
                $strNumber = substr($strPhone, 6, 4);
                $strPhone = $strPrefix."-".$strNumber;
                return $strPhone;
        }

      $strArea = substr($strPhone, 0, 3);
      $strPrefix = substr($strPhone, 3, 3);
      $strNumber = substr($strPhone, 6, 4);

       $strPhone = "(".$strArea.") ".$strPrefix."-".$strNumber;

       return ($strPhone);
}


function formatZipCode($strZipCode) {

        if (strlen($strZipCode) !=9) {
                return $strZipCode;
        }

        $strFirst = substr($strZipCode, 0, 5);
        $strSecond = substr($strZipCode, 5, 4);
        $strZipCode = $strFirst."-".$strSecond;
        return $strZipCode;

}

$db = "npi";
$user = "npi";
$pass = "npi";
$host = "localhost";

$con = mysql_connect($host, $user, $pass) or die("Could not connect; " . mysql_error());
$db_selected = mysql_select_db($db);

$mode=$_GET["mode"];

if ($mode > '1') {
?>
      <table class="report"><tbody>
<?php
         $sql = "SELECT * FROM npidata WHERE npi = \"$mode\"";
        $result = mysql_query($sql,$con);

        //echo $sql . "\n"; //debug

        $row = mysql_fetch_row($result);

        //echo "<pre>";
        //print_r($row); //debug
        //echo "</pre>";

        echo "<tr><td><span class=label>NPI</span></td><td>".$row[0]."</td></tr>";
        echo "<tr><td><span class=label>Full Name</span></td><td>".$row[8]." ".$row[6]." ".$row[7]." ".$row[5]."</td></tr>";
        echo "<tr><td><span class=label>Mailing Address</span></td><td>".$row[19]."<br>".$row[20]."<br>".$row[21]."<br>".$row[22].", ".$row[23]." ".formatZipCode($row[24])."</td></tr>";
        echo "<tr><td><span class=label>Phone</span></td><td>".formatPhoneNumber($row[26])."</td></tr>";
        echo "<tr><td><span class=label>Fax</span></td><td>".formatPhoneNumber($row[27])."</td></tr>";
        echo "<tr><td><span class=label>Practice Address</span></td><td>".$row[28]."<br>".$row[29]."<br>".$row[30].", ",$row[31]." ".formatZipCode($row[32])."</td></tr>";
        echo "<tr><td><span class=label>Phone</span></td><td>".formatPhoneNumber($row[34])."</td></tr>";
        echo "<tr><td><span class=label>Fax</span></td><td>".formatPhoneNumber($row[35])."</td></tr>";
        echo "</tbody></table>";

        mysql_free_result($result);
        mysql_close($con);

} else if ($_SERVER['REQUEST_METHOD'] != 'POST') {
?>

<form name="form1" action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" onsubmit="return validate_form(this)">
<table border="0" cellspacing="5">
<tr><td>Last Name</td><td><input type="text" size="30" id="lname" name="lname"></td></tr>
<tr><td>First Name</td><td><input type="text" size="30" id="fname" name="fname"></td></tr>
<tr><td>State</td><td>
<select name="state" size="1" id="state">
<option value=""><select state here></option>
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
<option value="CA">California</option>
<option value="CO">Colorado</option>
<option value="CT">Connecticut</option>
<option value="DE">Delaware</option>
<option value="DC">Dist of Columbia</option>
<option value="FL">Florida</option>
<option value="GA">Georgia</option>
<option value="HI">Hawaii</option>
<option value="ID">Idaho</option>
<option value="IL">Illinois</option>
<option value="IN">Indiana</option>
<option value="IA">Iowa</option>
<option value="KS">Kansas</option>
<option value="KY">Kentucky</option>
<option value="LA">Louisiana</option>
<option value="ME">Maine</option>
<option value="MD">Maryland</option>
<option value="MA">Massachusetts</option>
<option value="MI">Michigan</option>
<option value="MN">Minnesota</option>
<option value="MS">Mississippi</option>
<option value="MO">Missouri</option>
<option value="MT">Montana</option>
<option value="NE">Nebraska</option>
<option value="NV">Nevada</option>
<option value="NH">New Hampshire</option>
<option value="NJ">New Jersey</option>
<option value="NM">New Mexico</option>
<option value="NY">New York</option>
<option value="NC">North Carolina</option>
<option value="ND">North Dakota</option>
<option value="OH">Ohio</option>
<option value="OK">Oklahoma</option>
<option value="OR">Oregon</option>
<option value="PA">Pennsylvania</option>
<option value="RI">Rhode Island</option>
<option value="SC">South Carolina</option>
<option value="SD">South Dakota</option>
<option value="TN">Tennessee</option>
<option value="TX">Texas</option>
<option value="UT">Utah</option>
<option value="VT">Vermont</option>
<option value="VA">Virginia</option>
<option value="WA">Washington</option>
<option value="WV">West Virginia</option>
<option value="WI">Wisconsin</option>
<option value="WY">Wyoming</option>
</select>

</td></tr>
<tr><td> </td><td><input value="Search" type="submit"></td></tr>
</tbody></table>


       <a href="javascript:history.go(-1)">Cancel</a>
<?php
} else if ($_SERVER['REQUEST_METHOD'] = 'POST') {

$fname = $_POST["fname"];
$fname = strtoupper($fname);

$lname = $_POST["lname"];
$lname = strtoupper($lname);

$state = $_POST["state"];
$state = strtoupper($state);

if ($fname == "") {
#RUN QUERY WITHOUT FNAME
        $sql = "SELECT * FROM npidata WHERE UPPER(last_name) = \"$lname\" AND UPPER(business_mailing_address_state_name) = \"$state\"";
} else {
        $sql = "SELECT * FROM npidata WHERE UPPER(last_name) = \"$lname\" AND UPPER(first_name) = \"$fname\" AND UPPER(business_mailing_address_state_name) = \"$state\"";
}

$result = mysql_query($sql,$con);

//echo $sql . "\n"; //debug
?>
<table class="report"><tbody>
<tr>
       <th>NPI</th>
       <th>First Name</th>
       <th>Last Name</th>
       <th>Address</th>
       <th>City</th>
       <th>State</th>
<form name="form1" method="post">
<?php

        while ($row = mysql_fetch_array($result)) {

                //echo "<pre>";
                //print_r(mysql_fetch_array($result)); //debug
                //echo "</pre>";

                echo "<tr><td><a href=\"" . $_SERVER['PHP_SELF'] . "?mode="  . $row[0] . "\">" . $row[0] . "</a></td>";
                echo "<td>" . $row[6] . "</td>";
                echo "<td>" . $row[5] . "</td>";
                echo "<td>" . $row[20] . "<br>" . $row[21] . "</td>";
                echo "<td>" . $row[22] . "</td>";
                echo "<td>" . $row[23] . "</td></tr>";

        } //end while

?>

       </form>
       </tbody>
       </table>
<?php
mysql_free_result($result);
mysql_close($con);

} // end if

?>