Building PowerShell Scripts to Tie Disparate Products Together

The PowerShell scripting language has been called “automation glue” more times than I can count. It’s a fitting description if you’ve worked with it a lot. You may get the automation part of the title right away if you’ve merely heard of PowerShell and what it can do but the “glue” part may leave you wondering why.

PowerShell was born at Microsoft and thus was built for Microsoft Windows. It had a lot of support for manipulating various “Windows-y” things like files, folders, registry items, certificates and the like. There was no mention or really any kind of development work that was going in to make PowerShell cross platform or much support for even working with other systems remotely.

Today it’s 2017, PowerShell is in version 5.1 and has matured greatly. We now have true cross-platform PowerShell where we can run PowerShell on Windows, Linux and MacOS. On top of that, we have hundreds of built-in cmdlets that even if we still need to use Windows, we can easily make calls to other systems to gather and manipulate that information remotely.

The key to building any tool that integrates disparate systems together is first determining how system A works, how system B works and then create a “translation table” between. To “glue” various systems together, you must first understand each system and then build a common framework between them. For example, Get-Vm is a command that can not only retrieve virtual machines from Hyper-V but VMware as well. Under the hood though, the user has no idea and if written right, the command itself can infer the type of virtual machine the user needs with logic.

Related: Creating A Timeout Feature In Your PowerShell Scripts

Inferring a user’s intent in a command that abstracts away the underlying commands is critical in developing a common framework between systems.

To make this sink in, let’s take two completely different products and “glue” them together with PowerShell; Twitter and SQL Server. Twitter and SQL Server; these are definitely two disparate products, but by using PowerShell, we can bring them together to create a nice, little tool. For this tool, we’ll be pulling data from Twitter and then inserting it into SQL Server.

To bring these two products together, we can either build the commands we need ourselves or rely on the community. I’m not one to reinvent the wheel so let’s find existing tools that do the work for us. For Twitter, I can use the MyTwitter PowerShell module and for SQL Server I’ll be relying on the built-in SqlServer module that comes when SQL Server Management Studio is installed. Once both of these modules are installed, we now have the tooling in place to “talk” to each product from PowerShell. This is step #1.

Step #2 is developing the code necessary to pull data from Twitter. As an example, maybe I’d like to pull all tweets from a particular user’s timeline. I can use the Get-TweetTimeline function for this.

$tweetTimeLine = Get-TweetTimeline -UserName "sstranger" -MaximumTweets 10

The Get-TweetTimeline function returns a PSCustomObject type. This is a generic object type that is great for converting various systems into a common framework. Luckily, this command already does it for us. If not, we’d need to convert that output.

Next, we need to work with a SQL database. Upon investigation, I’ve decided the best way to insert tweets into a database is to just send a SQL INSERT query to the database. To do that, I need to build a string which I can then pass to the SQL database. This is a problem because our tweets are PSCustomObject while our database needs a string. We need to create a helper function to do the translation.

Let’s say that Get-TweetTimeline returns an object like this:

[pscustomobject]@{
     UserName = 'adbertram'
     Time = '1/1/01 4:00AM'
     Tweet = 'this is a tweet'
 }

I then have a SQL database with fields of UserName, Time and Tweet. I now need to create the sample INSERT query.

INSERT TweetTable VALUES( 'adbertram', '1/1/01 4:00AM','this is a tweet')

Now that I know what the INSERT query should look like and what a sample tweet object looks like I can build the translation.

```PowerShell function Add-TweetToDatabase { param( [Parameter(Mandatory,ValueFromPipeline)][pscustomobject[]]$Tweet ) process { $conn = New-Object System.Data.SqlClient.SqlConnection(“Datace=SQL1; Initial Catalog=Test; Integrated Security=SSPI”) $conn.Open() $cmd = $conn.CreateCommand()

    foreach ($t in $Tweet) {
         $cmd.CommandText ="INSERT Table1 VALUES ($t.UserName, $t.Time, $t.Tweet)"
         $cmd.ExecuteNonQuery()
     }
     $conn.Close()
 }

}

Get-TweetTimeline -UserName “sstranger” -MaximumTweets 10 | Add-TweetToDatabase ```

Notice how I created a “translation” function that accepted a PSCustomObject and chose to use the ValueFromPipeline parameter attribute. There’s now a seamless link between Twitter and our SQL database!

This is just a non-working example of how you can link two disparate systems together. The key is to figure out how to work with each system and develop one or more abstraction functions to make it seem like they were always meant to work together!