Archive for the ‘Code’ Category

Use PowerShell Instead of cURL

I recently had to come up with a way to schedule regular downloads of a file from a website that uses cURL. cURL is a command line tool for transferring data with URL syntax.  Getting cURL approved for use in my company is a bit difficult, so I set out to find a PowerShell solution. What I found was surprisingly easy to write and implement. You may want to take a look at the cURL Sourceforge page to get more information. Keep in mind that cURL parameters are specific to the website you are downloading from so every website will be different. You may need to contact that site’s tech support to find out what you need to pass if you can’t figure it out from the URL.

As always, if you use this script, please give credit.

$path     = "c:\PoShOut\"
$username = "username"
$password = "password"
$filename = "somefile.zip"
$somefile = "downloadedfile.zip"
 
# Create COM object, open a connection to www.somewhere.com,
# and set the header type
$document = New-Object -ComObject msxml2.ServerXMLHTTP
$document.open("POST", "https://www.somewhere.com", $false)
$document.setRequestHeader("Content-Type", "application/x-www-form-urlencoded")
 
# Create the necessary login and report information from the parms we passed
$postline = "user=" + $username + "&password=" + $password + "&filename=" + $filename
 
# Login to Markit with the information we passed above
$document.send($postline)
 
# Check to see if we have a document matching the parms we passed
$response = $document.getResponseHeader("Content-Type")
 
# If we have a document, make sure it's a Zip file, get the filename,
# and put the Zip into a Byte array. Once the file is downloaded, write
# the Byte array to a file.
If($response.Substring(0,17).ToLower() -eq "application/x-zip")
{
     $filename = $response.Substring(24)
     [Byte[]]$file = $document.responseBody
     [System.IO.File]::WriteAllBytes($path + $filename,$somefile)
}

Really Kill that Excel Object

If you’ve tried to use Excel 2007 or above with any of your Powershell scripts, you know that more often than not, when you quit Excel, a process stays running.  Not a huge deal until you’ve got a script scheduled to run several times a week and you end up silly with memory-eating Excel processes. You could kill all EXCEL.EXE processes, but what if you’ve got a scheduler that may be running other Excel processes as well?  If you kill all  running EXCEL.EXE processes, you’ll also be killing other Excel processes that should be running.

I’ve managed to put together some code that will determine what Excel processes are running before your create your Excel object and kill only the process spawned by your script.

## Grab all EXCEL.EXE processes before we create our Excel object.  This will find
## all Excel processes that we don't want to kill.  After we have those, we create
## our Excel object and immediately look at the Excel processes afterward.  There
## obviously a slight chance that you'll grab a process that spawned between our
## $startprocs and $endprocs scans, but it's a pretty small chance.
$startprocs = Get-Process | Where {($_.Name.ToLower() -eq "excel")}
$excel      = New-Object -ComObject Excel.Application
$endprocs   = Get-Process | Where {($_.Name.ToLower() -eq "excel")}
 
//             Your Code Goes Here                                        //
 
## Try to quit Excel (after you've saved your doc).  It's probably not going to work,
## but we're gonna try anyway.
$excel.Quit()
 
## Check to make sure $startprocs isn't null.  If it is null and we don't check, the
## script will blow.  If $startprocs is null, we're going to kill the only running
## Excel process...the one we created.
If($startprocs -gt $null)
{
     $compare = Compare-Object $startprocs $endprocs
     $process  = $compare | % {If($_.SideIndicator -match "=>") {$_.InputObject}}
}
else
{
     $process = $endprocs
}
 
Kill -Id $process.Id

Use Powershell to Pass Embedded Credentials

I needed to pass the ID/password of a service account to a Microsoft HPC grid for some jobs we were submitting automatically.  Powershell was able to do that pretty easily.

***WARNING***  This script reads a domain ID and password from an unencrypted file.  Our implementation of this script used a protected file.  Yours should too!  You’ve been warned.  I’m not responsible if your account password is compromised.

$domain      = "[Your domain name]"
$filename    = "[Path to filename]\filename"
$username    = $domain + "\" + ($username.substring(($username.Length - 5),5))
$password    = Get-Content $filename | ConvertTo-SecureString -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential($username,$password)

A couple notes here.  The $filename variable is used to get the entire path of the password file.  For ease of scripting, I’ve created a file named the same as the user ID I’m using with no file extension.  In the $username line, I’ve concatenated the $domain variable, a backslash, and the user ID that I’ve derived by taking the substring of the length of the $filename variable, minus the length of the ID, plus the length.  There is a cleaner way to do this, but the ID I’m using will never change (yes, I said never), so I chose this method.

$username    = $domain + "\" + ($username.substring(($username.Length - 5),5))

From that point, we read the password from the file and pipe it into the ConvertTo-SecureString commandlet.  Since we are using a plaintext password here (see the warning above), we have to use the -AsPlainText and  -Force parameters.  If you don’t use these, PoSh will complain, you’ll be frustrated.

$password    = Get-Content $filename | ConvertTo-SecureString -AsPlainText -Force

The last line is used to actually pass the credentials to a variable that can be used in a script that requires authentication.

$credentials = New-Object System.Management.Automation.PSCredential($username,$password)