Necessity is the Mother of Automation

Mother Necessity, where would we be?
Mother Necessity, where would we be?
If you have worked with me or are a frequent reader of my blogs or a frequent attendee of my presentations, you know I hate doing things manually. Over my career, I have found that one of the biggest enemies of automation is being able to get someone else to do it manually. If you can get someone else to do the work, it’s not your problem, right? I have seen manual processes linger on for years when it could have been fixed in a couple of hours.

I believe it was Plato that said, “Necessity is the mother of invention.” At least that’s what the internet says, and you’re not allowed to post anything on the internet if it’s not true, right? I have found that necessity is the mother of automation. Or the cynical viewpoint: it’s only necessary if you’re the one who has to deal with it.

I came across one of these processes recently. After performing a planned failover of our Availability Groups for patching one weekend, one of our apps was not working Monday morning. The team asked us to start running their smoke test after a failover to ensure that the app is in working order.

<lecture from DBA about why relying on a manual step to avoid fixing a bug is bad deleted for brevity>

They sent me the directions for running the smoke test. I had asked them during the omitted section above if it could be automated, and they said that it could not. Now that I had their instructions for how to run it, it became immediately obvious why they thought it couldn’t be automated … but they were totally wrong.

The process was to call their command line tool from the directory where it is stored. The part that they thought made it un-automatable (making up my own words is fun!) was that they frequently deploy new versions to that folder structure and you have to choose the subdirectory that was added most recently. What’s not automatable about that?

PowerShell can do it!

What can run any command-line utility? PowerShell. What can easily get a list of folders and sort them? PowerShell. What’s as fun as monkey with a yoyo? Writing PowerShell.

You guessed it. I used PowerShell. The folder structure for the command-line tool is like this:

  • \\Server\Share\AppName\FolderNamedAfterDateDeployed
  • \\Server\Share\AppName\NewerFolderNamedAfterDateDeployed
  • \\Server\Share\AppName\NewerFolderNamedAfterDateDeployed
  • \\Server\Share\AppName\NewerFolderNamedAfterDateDeployed
  • \\Server\Share\AppName\NewerFolderNamedAfterDateDeployed

So I enumerated the subfolders in that folder (gci $directory), sorted them by creation date (sort creationtime), and then returned the name of last 1 in the list (select name -last 1). Then it was just joining the pieces of the path together with Join-Path and calling the tool with Invoke-Expression.

The completed script

I also needed to account for a couple of command line parameters that the tool accepts including letting the user specify the email to send the results report to. Here is the complete script:

param ([string]$email)

if (!$email) {
	$email = "[email protected]"
	write-host "     Sending to dbateam alias" -f green
} else {
	write-host "     Sending to $email" -f green
}
$emailees = "emailees=$email"
$test = "smoketest"
$directory = "\\Server\Share\Smoketest.App"
$Subdirectory = gci $directory | sort creationtime | select name -last 1
$SubPath = Join-Path $directory $subdirectory.name
$Path = Join-Path $SubPath "x64\Smoketest.App.exe" 
Invoke-Expression "$Path $test $emailees"
Moral of the story

A couple of best practices have wormed their way out of this story.

  1. Almost anything can be automated with a little creativity and motivation
  2. Don’t let people push their manual work-arounds on to you because that just ensures they will never fix it

For your amusement, Mother Necessity by Schoolhouse Rock:

Reposted with permissions from SQLSoldier.com.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *