Easy-formating for PowerShell with New-TableFormat

Most of the PowerShell I write ends up as Functions: rather than scripts and it’s worth differentiating  between the two.

• A function is something which can be called from somewhere else.
• A script is carries out a particular task (which might be to define some function(s) , or might be done with functions )

Several of my 10 tips for better functions are about being smart with inputs and outputs (which is less important for one off scripts). I should have included an extra point “Ask if constants should really be defaults for parameters” . If I notice “I am solving one specific case of X, with a value set to Y, so I can solve other X-problems if I allow the value to be changed.” it makes something reusable out of a one-off. For example, I put a wrapper round PowerShells great “Select-String” cmdlet to search PS1 files , then I realised that I might want to search .SQL or .XML files, but usually it would be PS1. So “*.PS1” became the default value for a -include parameter.

Recently I put together a simple function named Get-SQL – it uses ODBC to send SQL commands – in  my case to MySQL on a Linux box but it could be any ODBC source. To avoid constantly creating and tearing sessions, it saves an ODBCconnection object in a global variable between invocations . Once it has been run for the first time in a session I can type Get-SQL “command” and use the existing connection: and I’ve made sure SQL statements can be piped in (that’s what I mean by being smart about inputs).

In it’s basic form Get-SQL takes 3 parameters, a connection string ($connection) a SQL statement ($SQL) and -ForceNew switch, which remakes the connection if it already exists
if ($forceNew -or (-not $global:ODBCconn)) {
    $global:ODBCconn = New-Object System.Data.Odbc.OdbcConnection($connection)
    $global:ODBCconn.open()
}
if ($sql) {
    $adapter = New-Object system.Data.Odbc.OdbcDataAdapter(
    New-Object system.Data.Odbc.OdbcCommand($sql,$ODBCconn))
    $table = New-Object system.Data.datatable
    [void]$adapter.fill($table)
    $table
}
else      {$ODBCconn }


So, if a SQL statement is passed, any data rows it generates will be returned; but if I am just setting up a connection for the rest of my session the ODBC connection object is returned.

PS C:\Users\james\Documents\windowsPowershell> Get-SQL -connection "DSN=foo-Customer5"
ConnectionString  : DSN=foo-Customer5
ConnectionTimeout : 15
Database          : foo
DataSource        : 192.168.1.234 via TCP/IP
ServerVersion     : 5.0.77
Driver            : myodbc5.dll
State             : Open
Site              :
Container         :

I want to show something shorter and simpler: I could finish with
$ODBCConn | format-table DataSource , Database, State
that might be acceptable now, but I don’t know what problems I’m storing up for the future by not returning the full ODBC object.
The better option is to write a Formatting XML file – so the object remains the same but PowerShell displays it the way I want. But writing and debugging the XML longhand is tedious. So the temptation is to go with the format-table method and fix things up later; but there is a short-cut to writing the XML which avoids parking the problem for later.. Richard mentions that it came up during the PowerShell Deep Dive in Vegas, And I downloaded it from here.  (Slightly oddly you need to put Function New-TableFormat { } into the editor, and paste the code block in then run it)

To use it you get the output the way you want with Format-Table and then replace “Format-Table” in the command line with New-TableFormat (with a couple of extra bits of finesse if needed). So it took about a minute to get the ODBC connection  to display like this

Data Source                Database State
-----------                -------- -----
192.168.1.234 via TCP/IP   foo      Open  

I’ve also created wrappers around /n software’s Netcmdlets to make it easier to work with the linux server, and in one of those  I call
Connect-SSH -Server $server -Credential $credential -Force
which would normally output this :

AuthMode         : Password
CertPassword     :
CertStore        : MY
CertStoreType    : User
CertSubject      :
Config           :
Credential       : System.Management.Automation.PSCredential
FirewallHost     :
FirewallPassword :
FirewallPort     : 0
FirewallType     : None
FirewallUser     :
Force            : True
LocalIP          :
Password         :
Port             : 22
Server           : 192.168.1.234
ShellPrompt      :
SSHAccept        :
Timeout          : 10
User             :

It took about Two minutes to get Add-Member to show what the host thinks its name is, and create a formatting XML file to get the result to look like this:

Connected to   Port  Host name        User name
------------   ----  ---------        ---------
192.168.4.71   22    foo.contoso.com  \root

It was all of another minute to create a manifest file to load my scripts & formatting XML as a PowerShell module and have everything neat and tidy. Cracking stuff