Art of the DBA Rotating Header Image

June 26th, 2012:

A short ride on a PoSH machine

I want to talk about one line of code.  Ok, well, two lines of code, but the first line isn’t that important, much like the opening act of a rock concert.  Let’s just pretend that the first line, while necessary, just gets us warmed up for the main event.

My problem was this:  I was collecting a SQL Server inventory but couldn’t retrieve all the information I was looking for.  I had started with some of the elements of Allen White’s(b|tscript as well as what Collen Morrow(b|t) assembled, but I was still missing the TCP/IP address and SQL port.  In my shop we use static addresses and I wanted to have that information handy.  So, to the Google-mobile.

My searching led me to a Sean McCown(b|t) video that showed me how to grab the TCP/IP address, so I was halfway home.  However, I just couldn’t find a clear way to grab the port information.  I think I was stretching my fingers to start pulling my hair out when I bumbled into the ManagedComputer class in the SMO.  Down the rabbit hole I went, using Get-Member, and eventually came up with this:

$managedcomp = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $sname
$port=$managedcomp.ServerInstances[$iname].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value

Hopefully that doesn’t look to intimidating, but let’s break it down a bit.  The ManagedComputer class (in this case $managedcomp) represents the physical host for the machine.  Using this, we can then access the various properties of that.  In succession, we have:

  • ServerInstances – An array of all the named instances installed on the machine.  I’m passing a variable to this, which happens to be the name of the instance I want the port for.
  • ServerProtocols – An array of the different protocols, so in this case we want the TCP/IP protocols.
  • IPAddresses – Note, these are the different address configurations SQL is listening on, but it’s what appears in the SQL Server configuration tool.  Your array elements are named IPN or IPAll (if it applies to everything).  Since I’m looking for the port that is assigned for all my addresses, I’ll look at the IPAll object.
  • IPAddressProperties – Simple enough, the properties of the IPAddress object, and we want the TcpPort property specifically and its value.

How did I get here?  Making extensive use of Get-Member.  Much like Theseus in the Labyrinth, I used that to figure out where to go next each time I went to a deeper property level.

And this is what I hope people take away from this.  There’s so much information buried in the .Net objects that we can take advantage of, especially by using Powershell.  I’ll be the first to admit that there might be a better way to do this(please tell me if there is).  This shows, though, that by using the tools in front of me, I was able to expose the secrets of this particular set of objects and bend them to my will.

 

P.S.  For those interested, I’ll post the full server inventory script here in the near future.