PowerShell Invoke-RestMethod to talk to the Rackspace Webmil API

Working with JSON in PowerShell took some getting used to, but now that I’ve got the hang of it, the power that it opens up is amazing. I was recently tasked with “go figure out the RackSpace API“. I love how clearly laid out my goals can be sometimes… Anyways, I’m stoked that it came up!  Having spent the last few weeks researching REST web service technologies has opened up a whole new world of scripting and development possibilities to me. For way too long I’ve thought of the internet as a series of web pages for displaying data to people, but never really wrapped my head around the concept of the web service that allows you to programmatically fetch the data and consume it for your own needs… And now that it’s clicked, I’m all the more excited to continue trying to move my career away from an IT support role and into more of a programmer/developer role.

Anyways, enough of that, lets break it down.  First off, here is the schema for the table that I created to collect the data from the web service into. Basically it has one row for each property in the RackSpace API, with a Primary Key set on the [name] field and some default values set for the columns that don’t allow nulls:

CREATE TABLE [dbo].[vRSExMailbox](
	[name] [nvarchar](255) NOT NULL,
	[displayName] [nvarchar](255) NULL,
	[size] [float] NOT NULL,
	[currentUsage] [float] NOT NULL,
	[isHidden] [bit] NOT NULL,
	[visibleInRackspaceEmailCompanyDirectory] [bit] NOT NULL,
	[enabled] [bit] NOT NULL,
	[lastLogin] [datetime] NULL,
	[firstName] [nvarchar](255) NULL,
	[lastName] [nvarchar](255) NULL,
	[jobTitle] [nvarchar](255) NULL,
	[company] [nvarchar](255) NULL,
	[department] [nvarchar](255) NULL,
	[businessNumber] [nvarchar](255) NULL,
	[homeNumber] [nvarchar](255) NULL,
	[mobileNumber] [nvarchar](255) NULL,
	[faxNumber] [nvarchar](255) NULL,
	[pagerNumber] [nvarchar](255) NULL,
	[addressLine1] [nvarchar](255) NULL,
	[city] [nvarchar](255) NULL,
	[state] [nvarchar](255) NULL,
	[zip] [nvarchar](255) NULL,
	[country] [nvarchar](255) NULL,
	[notes] [nvarchar](255) NULL,
	[customID] [nvarchar](255) NULL,
	[hasBlackBerryMobileService] [bit] NOT NULL,
	[hasActiveSyncMobileService] [bit] NOT NULL,
	[samAccountName] [nvarchar](255) NULL,
 CONSTRAINT [PK_vRSExMailbox] PRIMARY KEY CLUSTERED 
(
	[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[vRSExMailbox] ADD  CONSTRAINT [DF_vRSExMailbox_size]  DEFAULT ((0)) FOR [size]
GO

ALTER TABLE [dbo].[vRSExMailbox] ADD  CONSTRAINT [DF_vRSExMailbox_currentUsage]  DEFAULT ((0)) FOR [currentUsage]
GO

ALTER TABLE [dbo].[vRSExMailbox] ADD  CONSTRAINT [DF_vRSExMailbox_isHidden]  DEFAULT ((1)) FOR [isHidden]
GO

ALTER TABLE [dbo].[vRSExMailbox] ADD  CONSTRAINT [DF_vRSExMailbox_visibleInRackspaceEmailCompanyDirectory]  DEFAULT ((0)) FOR [visibleInRackspaceEmailCompanyDirectory]
GO

ALTER TABLE [dbo].[vRSExMailbox] ADD  CONSTRAINT [DF_vRSExMailbox_enabled]  DEFAULT ((0)) FOR [enabled]
GO

ALTER TABLE [dbo].[vRSExMailbox] ADD  CONSTRAINT [DF_vRSExMailbox_hasBlackBerryMobileService]  DEFAULT ((0)) FOR [hasBlackBerryMobileService]
GO

ALTER TABLE [dbo].[vRSExMailbox] ADD  CONSTRAINT [DF_vRSExMailbox_hasActiveSyncMobileService]  DEFAULT ((0)) FOR [hasActiveSyncMobileService]
GO

Then here is the script that connects to the API, then parses each users mailbox and inserts the data into SQL. I’ll break-down some of the sections in more detail later:

Import-Module SQLPS -DisableNameChecking

#$debug = $true
$sqlServer = "SERVER"
If ($debug) {$sqlDatabase = "Inventory_Dev"}
Else {$sqlDatabase = "Inventory"}
Invoke-Sqlcmd -Query "DELETE FROM [$SQLDatabase].[dbo].[vRSExMailbox]" -ServerInstance $SQLServer -Database $SQLDatabase

$userKey = "your-key-here"
$secretKey = "your-key-here"
$userAgent = "Mozilla/5.0 (Windows NT; Windows NT 6.1; en-US) WindowsPowerShell/3.0"
$strDomain = "https://api.emailsrvr.com/v0/customers/999999/domains/example.com"

Function Get-SignatureHash([String] $signatureData) {
	$byteArray = [System.Text.Encoding]::ASCII.GetBytes($signatureData)
	$sha =  New-Object -TypeName System.Security.Cryptography.SHA1CryptoServiceProvider
	$result = $sha.ComputeHash($byteArray)
	$signature = [System.Convert]::ToBase64String($result)
	return $signature
}

Function Get-Header {
	$dateTime = (Get-Date).ToString("yyyyMMddHHmmss")
	$signatureData = "$userKey$userAgent$dateTime$secretKey"
	$hashSecret = Get-SignatureHash($signatureData)
	$xApiSignature = "$userKey`:$datetime`:$hashSecret"
	$header = @{"X-Api-Signature"="$xApiSignature"}
	Return $header
}

for ($i = 0; $i -le 2; $i++) {

	switch ($i) {
		0 {$uri = "$strDomain/ex/mailboxes?size=250"}
		1 {$uri = "$strDomain/ex/mailboxes?size=250&offset=250"}
		2 {$uri = "$strDomain/ex/mailboxes?size=250&offset=500"}
		default {$uri = "$strDomain/ex/mailboxes"}
	}
	$header = Get-Header
	$dataset = Invoke-RestMethod -Uri $uri -Headers $header -Method Get -ContentType "application/json"
	$index = $dataset.mailboxlist.mailboxes.mailbox

	ForEach ($item in $index) {

		$userId = $item.name
		$uri = $uri = "$strDomain/ex/mailboxes/$userId"

		$header = Get-Header
		$userMailbox = Invoke-RestMethod -Uri $uri -Headers $header -Method Get -ContentType "application/json"

		$name = $userMailbox.mailbox.name
		$displayName = $userMailbox.mailbox.displayName
		$size = $userMailbox.mailbox.size
		$currentUsage = $userMailbox.mailbox.currentUsage
		$isHidden = $userMailbox.mailbox.isHidden
		$visibleInRackspaceEmailCompanyDirectory = $userMailbox.mailbox.visibleInRackspaceEmailCompanyDirectory
		$isEnabled = $userMailbox.mailbox.enabled
		$lastLogin = $userMailbox.mailbox.lastLogin
		$firstName = $userMailbox.mailbox.contactInfo.firstName
		$lastName = $userMailbox.mailbox.contactInfo.lastName
		$jobTitle = $userMailbox.mailbox.contactInfo.jobTitle
		$company = $userMailbox.mailbox.contactInfo.company
		$department = $userMailbox.mailbox.contactInfo.department
		$businessNumber = $userMailbox.mailbox.contactInfo.businessNumber
		$homeNumber = $userMailbox.mailbox.contactInfo.homeNumber
		$mobileNumber = $userMailbox.mailbox.contactInfo.mobileNumber
		$faxNumber = $userMailbox.mailbox.contactInfo.faxNumber
		$pagerNumber = $userMailbox.mailbox.contactInfo.pagerNumber
		$addressLine1 = $userMailbox.mailbox.contactInfo.addressLine1
		$city = $userMailbox.mailbox.contactInfo.city
		$state = $userMailbox.mailbox.contactInfo.state
		$zip = $userMailbox.mailbox.contactInfo.zip
		$country = $userMailbox.mailbox.contactInfo.country
		$notes = $userMailbox.mailbox.contactInfo.notes
		$customID = $userMailbox.mailbox.contactInfo.customID
		$hasBlackBerryMobileService = $userMailbox.mailbox.hasBlackBerryMobileService
		$hasActiveSyncMobileService = $userMailbox.mailbox.hasActiveSyncMobileService
		$samAccountName = $userMailbox.mailbox.samAccountName

		$sqlQuery = "INSERT INTO [$SQLDatabase].[dbo].[vRSExMailbox] VALUES ('$name','$displayName','$size'," + `
			"'$currentUsage','$isHidden','$visibleInRackspaceEmailCompanyDirectory','$isEnabled','$lastLogin'," + `
			"'$firstName','$lastName','$jobTitle','$company','$department','$businessNumber','$homeNumber'," + `
			"'$mobileNumber','$faxNumber','$pagerNumber','$addressLine1','$city','$state','$zip','$country'," + `
		 	"'$notes','$customID','$hasBlackBerryMobileService','$hasActiveSyncMobileService','$samAccountName')"
		Write-Output $SQLQuery
		Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
		Start-Sleep -Seconds 1
	}
}

Ok, lets break this down… First the global declarations at the top. The two Key variables you get from RackSpace and are what is used to create the SHA1 hash that gets sent through with the header of the HTTPS packet to authenticate your request. $strDomain is the root URI for pretty much anything your going to throw at the API, and then I just tack on the rest of the URI path throughout the script. $userAgent is a bit tricky actually. Initially I was using a hook into IE that I got from RackSpace that looked like this: $userAgent= [Microsoft.PowerShell.Commands.PSUserAgent]::Internetexplorer. And it did work, however it also required that I tag each of the Invoke-RestMethod commands with an extra bit of code to tell it what the UserAgent to use (-UserAgent $userAgent). After using Fiddler2 to sniff the HTTP packet that was being sent through by a default Invoke-RestMethod command, I saw that I could set the $userAgent variable to “Mozilla/5.0 (Windows NT; Windows NT 6.1; en-US) WindowsPowerShell/3.0” and it would no longer need that extra bit on the Invoke. Whatever method you go with, just make sure that the $userAgent is the same when passed through the SHA1 hash functions and the Invoke-RestMethod command.

Speaking of the SHA1 stuff… this is the most important part of the code with dealing with the RackSpace API. Take a look at this reference if you want to know the details on the syntax behind the hash, needless to say, this should work for anyone without needing any real modification other than updating the global variables that contain your API keys.

Function Get-SignatureHash([String] $signatureData) {
	$byteArray = [System.Text.Encoding]::ASCII.GetBytes($signatureData)
	$sha =  New-Object -TypeName System.Security.Cryptography.SHA1CryptoServiceProvider
	$result = $sha.ComputeHash($byteArray)
	$signature = [System.Convert]::ToBase64String($result)
	return $signature
}

Function Get-Header {
	$dateTime = (Get-Date).ToString("yyyyMMddHHmmss")
	$signatureData = "$userKey$userAgent$dateTime$secretKey"
	$hashSecret = Get-SignatureHash($signatureData)
	$xApiSignature = "$userKey`:$datetime`:$hashSecret"
	$header = @{"X-Api-Signature"="$xApiSignature"}
	Return $header
}

With those in place, you just need to refresh your header before each Invoke-RestMethod to get a new hash. If you are testing this at a command-line you’ll want to copy and paste in the two commands on top of each other; The longer you wait to kick off the Invoke-RestMethod, the higher the chance that the time stamp embedded into the hash will expire.

As a final note, I do plan one major update to this script, which I’ll post once I have it worked out. Right now the way this script works, I have to know ahead of time that my company has between 500 and 750 mailboxes (hence the switch in the code that determines which set of 250 mailboxes to parse next).  If you have more than that, you’ll have to add in more pages, or reduce them according to your needs.  There is an operator in the API to get properties on the domain, which I’ll use to pull the number of mailboxes, then dynamically assign the number of times to loop the main body of the script to deal with the needed number of pages.  Unfortunately at the moment I’m having authentication issues, but I’ll update more as I dig deeper into the world of web service scripting.

Advertisements
This entry was posted in Programming and tagged , , , , , , . Bookmark the permalink.

One Response to PowerShell Invoke-RestMethod to talk to the Rackspace Webmil API

  1. Pingback: Complete RackSpace Webmail Inventory | Rich Hopkins

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s