An Overlooked Parameter

Posted by on

This isn’t so much a post as an extended pingback.  This Post by Jeffrey Snover on the PowerShell Team Blog explains how to use the -expandproperty parameter of the select-object cmdlet.

I had never noticed that parameter and was also annoyed by writing this all the time:

get-something | foreach {$_.SomeProperty}

It was an idiom that I was using a lot that felt like it didn’t fit.</p> <p>As he points out, this can be replaced with the non-looping:

get-something | select-object -expandProperty SomeProperty

It’s longer if you don’t use aliases (and prefix-shortened parameternames), but I think it reads a lot better.

Let me know what you think. Was this a surprise to you, or have you used the -expandProperty parameter before?

Mike

P.S. You should definitely follow the PowerShell Team Blog…it is always worthwhile.

Writing your own PowerShell Hosting App (Part 4)

Posted by on

WARNING:  This is a long post with lots of code!  🙂

In the last post, we got to the point that we ran into the limitatoin of simply running scripts through a bare runspace. You can accomplish quite a bit, but to have the full shell experience, you’ll want to actually create a the host objects, so that the PowerShell engine will know how to handle interacting with the environment. The hint that we were at this point was the error message “System.Management.Automation.CmdletInvocationException: Cannot invoke this function because the current host does not implement it.” Creating a host that does implement “it” is not too difficult, but involves a lot of code. Without further ado, here we go.

There are three classes to inherit from to implement a custom host. They are:

  • System.Management.Automation.Host.PSHost
  • System.Management.Automation.Host.PSHostUserInterface
  • System.Management.Automation.Host.PSHostRawUserInterface

These classes are declared as MustInherit (which is the same as Abstract in C#), and each declares several properties and methods as MustOverride.  To easily generate code for these methods and properties (in SharpDevelop…each tool may or may not have a way to do this),  I wrote simple stub classes for these as follows:

Public Class PowerShellWorkBenchHost
    Inherits System.Management.Automation.Host.PSHost

 End Class

Public Class PowerShellWorkBenchHostUI
	Inherits System.Management.Automation.Host.PSHostUserInterface

  End Sub

End Class

Public Class PowerShellWorkBenchHostRawUI
		Inherits System.Management.Automation.Host.PSHostRawUserInterface

End Class

Then, I’m put the cursor in the blank line under the inherits clause in the first class,  PowerShellWorkBenchHost, and selected Auto Code Generation from the Tools menu.  This brings up a dialog that lets you indicate what code to generate.  One of the options is “Abstract class overridings”, which is what we want.  Selecting that shows us a checkbox for the Abstract (MustInherit) class that we’re inheriting from (PSHost).  Checking PSHost and clicking OK fills in the member definitions with some default behavior as shown below:

Auto Code Generate Dialog

Auto Code Generate Dialog

Public Class PowerShellWorkBenchHost
    Inherits System.Management.Automation.Host.PSHost
	Public Overloads Overrides ReadOnly Property Name() As String
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property Version() As Version
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property InstanceId() As Guid
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property UI() As System.Management.Automation.Host.PSHostUserInterface
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property CurrentCulture() As System.Globalization.CultureInfo
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property CurrentUICulture() As System.Globalization.CultureInfo
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides Sub SetShouldExit(exitCode As Integer)
		Throw New NotImplementedException()
	End Sub

	Public Overloads Overrides Sub EnterNestedPrompt()
		Throw New NotImplementedException()
	End Sub

	Public Overloads Overrides Sub ExitNestedPrompt()
		Throw New NotImplementedException()
	End Sub

	Public Overloads Overrides Sub NotifyBeginApplication()
		Throw New NotImplementedException()
	End Sub

	Public Overloads Overrides Sub NotifyEndApplication()
		Throw New NotImplementedException()
	End Sub

 End Class

Repeating that for the other two classes results in the following:

Public Class PowerShellWorkBenchHostRawUI
		Inherits System.Management.Automation.Host.PSHostRawUserInterface

	Public Overloads Overrides Property ForegroundColor() As ConsoleColor
		Get
			Throw New NotImplementedException()
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

	Public Overloads Overrides Property BackgroundColor() As ConsoleColor
		Get
			Throw New NotImplementedException()
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

	Public Overloads Overrides Property CursorPosition() As System.Management.Automation.Host.Coordinates
		Get
			Throw New NotImplementedException()
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

	Public Overloads Overrides Property WindowPosition() As System.Management.Automation.Host.Coordinates
		Get
			Throw New NotImplementedException()
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

	Public Overloads Overrides Property CursorSize() As Integer
		Get
			Throw New NotImplementedException()
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

	Public Overloads Overrides Property BufferSize() As System.Management.Automation.Host.Size
		Get
			Throw New NotImplementedException()
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

	Public Overloads Overrides Property WindowSize() As System.Management.Automation.Host.Size
		Get
			Throw New NotImplementedException()
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

	Public Overloads Overrides ReadOnly Property MaxWindowSize() As System.Management.Automation.Host.Size
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property MaxPhysicalWindowSize() As System.Management.Automation.Host.Size
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property KeyAvailable() As Boolean
		Get
			Throw New NotImplementedException()
		End Get
	End Property

	Public Overloads Overrides Property WindowTitle() As String
		Get
			Throw New NotImplementedException()
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

	Public Overloads Overrides Function ReadKey(options As System.Management.Automation.Host.ReadKeyOptions) As System.Management.Automation.Host.KeyInfo
		Throw New NotImplementedException()
	End Function

	Public Overloads Overrides Sub FlushInputBuffer()
		Throw New NotImplementedException()
	End Sub

	Public Overloads Overrides Sub SetBufferContents(origin As System.Management.Automation.Host.Coordinates, contents As System.Management.Automation.Host.BufferCell(,))
		Throw New NotImplementedException()
	End Sub

	Public Overloads Overrides Sub SetBufferContents(rectangle As System.Management.Automation.Host.Rectangle, fill As System.Management.Automation.Host.BufferCell)
		Throw New NotImplementedException()
	End Sub

	Public Overloads Overrides Function GetBufferContents(rectangle As System.Management.Automation.Host.Rectangle) As System.Management.Automation.Host.BufferCell(,)
		Throw New NotImplementedException()
	End Function

	Public Overloads Overrides Sub ScrollBufferContents(source As System.Management.Automation.Host.Rectangle, destination As System.Management.Automation.Host.Coordinates, clip As System.Management.Automation.Host.Rectangle, fill As System.Management.Automation.Host.BufferCell)
		Throw New NotImplementedException()
	End Sub

End Class

That’s a lot of code, but it’s not so bad, since I didn’t actually have to write it.  Also, since all of the members simply throw NotImplementedException, it doesn’t accomplish anything.

But it should be clear that a big part of what we need to do now is to fill in the method bodies that implement the features we want to have in our host.

To actually use these new classes in conjunction with the runspace and pipeline that we created last week, we’ll need to modify that code (but only slightly) to reference the new host class:

Sub RunToolStripMenuItem1Click(sender As Object, e As EventArgs)
	Dim host as New PowerShellWorkBenchHost
        Dim r As Runspace=RunspaceFactory.CreateRunspace(host)
        r.Open()
        Dim p As Pipeline=r.CreatePipeline(txtScript.Text)
        p.Commands.add(new Command("out-string"))
        Dim output As Collection(Of psobject)
        output=p.Invoke()
        For Each o As PSObject In output
            txtOutput.AppendText(o.ToString()+vbcrlf)
        Next
End Sub

If you run the app at this point, it will blow up when you try to run anything.  That’s because there are certain things that must be implemented for the custom host to function.  Other things only need to be implemented if you want to use those features in your host.   The not-so-nice thing is that I haven’t ever found a list that tells you what you actually need to do, so it’s a trial and error kind of thing.  What I did was to put breakpoints on all of the throw statements that were generated, and run the app over and over, trying to run a simple “dir”, and implementing the methods that got hit.  Doing that showed me that the following are pretty much essential to implement:

  • PSHost.UI
  • PSHost.Name
  • PSHost.InstanceID
  • PSHost.CurrentCulture
  • PSHost.CurrentUICulture
  • PSHostUserInterface.RawUI
  • PSHostRawUserInterface.BufferSize

Fortunately, these are all pretty easy to implement.  The Name and InstanceID can be constants, the UI and RawUI properties need to return instances of the classes we inherited from PSHostUserInterface and PSHostRawUserInterface.  The CurrentCulture and CurrentUICulture I just pulled from the Threading.Thread.CurrentThread object (which has CurrentCulture and CurrentUICulture properties).  The BufferSize property refers to the size of the “window” that the console will be writing output to, measured in characters.  I made it 80×80, just to have something to work with.
Here’s what those methods look like (I omitted all of the methods that still throw exceptions to make the listing smaller, but you still need the definitions in your code)

Public Class PowerShellWorkBenchHost
    Inherits System.Management.Automation.Host.PSHost
    Private _instanceID As New Guid("eb30b404-18c2-455d-8271-423039280b9b" )
    private _UI as New PowerShellWorkBenchHostUI
	Public Overloads Overrides ReadOnly Property Name() As String
		Get
			return "PowerShellWorkBenchHost"
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property Version() As Version
		Get
			return new Version(1,0,0)
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property InstanceId() As Guid
		Get
			return _instanceID
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property UI() As System.Management.Automation.Host.PSHostUserInterface
		Get
			return _UI
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property CurrentCulture() As System.Globalization.CultureInfo
		Get
		  Return Threading.Thread.CurrentThread.CurrentCulture
		End Get
	End Property

	Public Overloads Overrides ReadOnly Property CurrentUICulture() As System.Globalization.CultureInfo
		Get
			  Return Threading.Thread.CurrentThread.CurrentUICulture
		End Get
	End Property

 ' LOTS OF OMITTED CODE

 End Class

Public Class PowerShellWorkBenchHostUI
	Inherits System.Management.Automation.Host.PSHostUserInterface
	private _RawUI as New PowerShellWorkBenchHostRawUI
	Public Overloads Overrides ReadOnly Property RawUI() As System.Management.Automation.Host.PSHostRawUserInterface
		Get
			return _RawUI
		End Get
	End Property

'LOTS OF OMITTED CODE

End Class

Public Class PowerShellWorkBenchHostRawUI
		Inherits System.Management.Automation.Host.PSHostRawUserInterface

	Public Overloads Overrides Property BufferSize() As System.Management.Automation.Host.Size
		Get
			return new system.management.automation.host.size(80,80)
		End Get
		Set
			Throw New NotImplementedException()
		End Set
	End Property

'LOTS OF OMITTED CODE

End Class

Now we’re using the host, and actually getting output.  We’re not getting any more output than we were before using the host, but since we haven’t implemented any real host functionality, that’s to be expected.

I was hoping to get something cool (like write-host) working in this post, but I’m afraid it’s already way too long.  I’ll try to bang out another entry tomorrow to follow up.

I’m also thinking of creating a project on CodePlex for the code that I’m writing.  Obviously the limited functionality that I’m implementing in the tutorial is not something that everyone would want, but as a community driven project, it could eventually become considerably better (and since the source would be available, you could take it and do what you want with it).  Just a thought at this point…let me know what you think.

Mike

P.S.  I really would like to hear what you think about this series (and the blog in general).

Writing your own PowerShell Hosting App (Part 3)

Posted by on

In the last post we started building the app, but ran into a problem with output.   We were able to get output from some scripts (dir, for example, gave incomplete output), but others didn’t give us anything useful at all (get-service, returned “System.ServiceProcess.ServiceController” over and over).

The reason for this is simple.  PowerShell cmdlets (and by extension, scripts) return objects,  not strings.  To get string output, we need to tell the script to output strings rather than ask each object that is output to give us its string representation by calling ToString() on them.

To do this, we could try to do something like surround the script that’s passed in with parentheses, and add “| out-string”, but there’s an easier solution.  The object we’re using to run our scripts is called a Pipeline.  As such, it has a method to append commands.  The “corrected” code is this:

Sub RunToolStripMenuItem1Click(sender As Object, e As EventArgs)
        Dim r As Runspace=RunspaceFactory.CreateRunspace
        r.Open
        Dim p As Pipeline=r.CreatePipeline(txtScript.Text)
        p.Commands.add(new Command("out-string"))
        Dim output As Collection(Of psobject)
        output=p.Invoke()
        For Each o As PSObject In output
            txtOutput.AppendText(o.ToString()+vbcrlf)
        Next
End Sub

The only new line is the one that contains the “out-string”.  We can even leave the ToString() calls, because we know that string objects’ ToString() will just output the string itself, or at least we hope it would.

With that, here’s the output for “get-service” (note: I changed the font to a fixed-width font):

Fixed Output

That’s much nicer and even has column headers like we’d expect.  With that change, cmdlets that output objects directly to the pipeline will work fine.  But what about cmdlets that output text to the host (like the write-* cmdlets other than write-output)?  Simply trying “Write-host ‘Hello, World.'” gives us a big fat error, but one that gives us and idea what we need to do to fix it: “System.Management.Automation.CmdletInvocationException: Cannot invoke this function because the current host does not implement it.”

That seems like a pretty good breaking point.  Implementing the host (which pretty much involves inheriting from a couple of classes and implementing some basic methods) will take some time, but most of it’s pretty easy.

One thing that I should mention.  I haven’t been specific about what version of PowerShell this series is using.  The reason is that the code so far will work on either 1.0 or 2.0 (and I anticipate that the rest of the code will as well, but I haven’t written the rest yet).  In fact, the custom host that I use at work has no problems running on either 1.0 or 2.0.  I’ve been very impressed with the PowerShell team and their commitment to making PowerShell 2.0 backwards compatible as far as possible.  I expected that this effort would end as soon as I got into the object model, but I have yet to find anything that I’ve written for 1.0 that hasn’t worked in 2.0.  Now there’s a lot of stuff that can be written for 2.0 that won’t work in 1.0, but that’s to be expected.

Speaking of 2.0, the final release of 2.0 (for XP and Win2k3) showed up today, much to my surprise.  Kudos again to the PowerShell team for a very quick release schedule following last week’s Windows 7 release.  If you haven’t already, I definitely recommend getting 2.0 downloaded and installed so you can try out all of the neat stuff that’s included.  I especially recommend trying the out-gridview cmdlet!

Mike

Writing your own PowerShell Hosting App (Part 2)

Posted by on

In the last post, I discussed some of the reasons why you might want to write your own PowerShell hosting app.  I realized later that I didn’t define what that meant.

In general, there are 2 ways to include PowerShell technology in an application.

  1. Use the PowerShell objects (in the System.Management.Automation.* namespaces) to execute scripts, and use the objects that are returned in your code.
  2. Create a custom “host” for PowerShell, providing the PowerShell engine with the ability to interact with the environment.

With the first option, you have access to the input, output, and error streams of the PowerShell pipeline (which is how PowerShell represents a piece of running code).  With the second option, you also have the ability to handle other output like debug, verbose, and warning, as well as handling prompts for things like read-host and get-credential.

In general, you can get quite a lot done with the first approach, and that’s how we’re going to start.  Adding the custom host won’t involve rewriting much code, so it makes more sense to start out easy.

A few more things before we start coding:  First,I’m going to use VB.NET rather than C#.  I know this is probably a turn off for some of you (sorry), but there are a some good (I think) reasons to do this.

  1. Almost all example .Net code dealing with PowerShell is C#
  2. Administrators are more likely to be familiar with vbscript, so VB.NET may be more approachable.
  3. Most of the actual code for dealing with PowerShell is pretty simple, so it won’t be hard for C# folks to modify it.
  4. (the real reason)  I don’t have a history of writing C#, and I don’t really want to start my efforts in that direction in a blog post.  🙂

And now, on to the code.  I’m going to use SharpDevelop, because it’s possible that you want to do something like this, but don’t have the budget (as an admin) to have development tools.  SharpDevelop is a free, open-source IDE for .NET languages.  It is very similar to Visual Studio, and includes a lot of features.  Did I mention that it’s free?

Now, on to coding.  I’m envisioning a simple screen with an area to enter PowerShell code, and an area to view the output.  I started by creating a new VB.NET Windows Application.  I then added a menustrip, a splitter, and two textboxes (one above the splitter, and one below).  I set both textboxes to multiline and set their dock property to fill.  I also right-clicked on the menustrip and selected “Insert Standard Items”  Clicking the Run button should give you something that looks like this:

Screenshot 1

Screenshot 1

It’s nothing spectacular, but this isn’t a post about writing a spectacular interface.  This is about PowerShell in a GUI.  Now to add the PowerShell.

You’re going to need to reference to System.Management.Automation (right-click on the References node in the Projects window, select Add Reference, and select System.Management.Automation from the list on the GAC tab).   You will probably want to add the following to the top of the .vb file:

Imports System.Management.Automation
Imports System.Management.Automation.Runspaces
imports System.Collections.ObjectModel

Now, we’re going to need to add a menu item to run the code.  I added a “Run” menu with an item under it called “Run Script” that has a shortcut key of F5.  Double-clicking on the “Run Script” menu item brings up the code editor in the handler for the menu item.  Here’s the code I put in it.

    Sub RunToolStripMenuItem1Click(sender As Object, e As EventArgs)
        Dim r As Runspace=RunspaceFactory.CreateRunspace
        r.Open
        Dim p As Pipeline=r.CreatePipeline(txtScript.Text)

        Dim output As Collection(Of PSObject)
        output=p.Invoke()
        For Each o As PSObject In output
            txtOutput.AppendText(o.ToString()+vbcrlf)
        Next
    End Sub

With that, I clicked the run button in SharpDevelop, and behold:

The Finished(?) App

The Finished(?) App

Typing the simplest PowerShell script I could think of (dir) and selecting Run (or hitting F5) provides the desired output.  That’s a good start, but to see a shortcoming, look what happens when I try something more adventurous (After shrinking the size of the app so the screenshots aren’t so obnoxious):

Not So Useful Output

Not So Useful Output

Since the System.Process.ServiceController class didn’t have a very friendly ToString() method, we’re kind of out of luck. And hey, the output from the “dir” example wasn’t anywhere close to what you would normally see in a PowerShell prompt. What’s up with that?

Tune in next time for the reason. I’ll try to get it written Saturday, but no promises.

In the meantime, play around with the code and see what you can accomplish. And let me know what you think of my choice of VB.NET and SharpDevelop.

Writing your own PowerShell Hosting App (Part 1. Introduction)

Posted by on

I’ve mentioned before that I use a homegrown PowerShell host in my work.  I have been more than pleasantly surprised at how easy and how rewarding this is.  In the last few weeks, I’ve seen a few articles that have gotten me thinking about writing a series of blog posts about how to get started.

Before actually writing anything, it’s good to ask yourself…why in the world would I write a host when there are so many out there already (ISE and PowerGUI are notable free examples)?  This is a really important question and one that will stop most projects in their tracks.  Most people can get what they need using an existing host.  Here are some of the reasons I chose to write  a host:

  • I wanted complete control over the environment, as I knew (hoped) that I would be spending a lot of time using it
  • I wanted to be able to interact with the environment in ways that the existing tools didn’t allow
  • I was constrained to use PowerShell 1.0 (which eliminates the ISE)

But probably the most pressing reason in reality was:

  • I had a book (link) that explained the technology and I wanted to play  🙂

Unlike most (some?) administrators, I have a development background and even have Visual Studio installed on my machine, so testing the waters of writing a host wasn’t a big investment of time, and the pleasure of seeing something like this come together was well worth it.

Here are the posts that got my mind going again:

Create your own IDE in 10 minutes

How to Host PowerShell in a WPF Application

In the next post, I’ll start the project and give you something to look at.

Let me know if there’s anything specific you’d like to see (or have experience implementing).

Mike

Flexible Filtering

Posted by on

When writing a “get-” function in PowerShell, you often run into the issue of filtering your data.  Do you want to include any filtering parameters?  Do you want to allow lists of values?  Do you want to provide “include” or “exclude” parameters?  What about wildcards?  I got tired of writing the same kind of code over, so I wrote a fairly general-purpose filter-list function.

Here it is (example usages follow):

function filter-list($list, $filterString,$propertyName,[switch]$help ){
 if ($help)
 {
 $msg = @"
Filter a list using a filterstring and a property name. Wildcards are allowed, as well as
a prefix of Not:, which means to include values that don't match the pattern.
If filterString is an array, it lists property values that will remain in the list.

Usage: filter-list list filterString propertyname [-help]
"@
 Write-Host $msg
 return
 }
 if (!$list) {return}
 if ($filterString -is [Array]){
 $list = @($list | ?{ $filterString -icontains $_.$propertyName})
 } else
 {
 if (($filterString) -and ($filterString -ne '')){
 if ($filterString -ilike 'Not:*'){
 $list = @($list | ? {$_.$propertyName -inotlike $filterString.Replace('Not:','')})
 } else {
 $list = @($list | ? {$_.$propertyName -ilike $filterString})
 }
 }
 }
 return $list
}

Here’s a simple function (not really useful) that uses this:

function new-dir{
 Param($path, $extension)
 $list=dir $path
 $list=filter-list $list $extension Extension

 return $list
}

new-dir -path c:temp -ext '.LOG','.TXT'
new-dir -path c:temp -ext 'Not:.LOG'
new-dir -path c:temp -ext 'Not:.c*'

Now, without really writing any new code, we have a parameter that can take simple values, lists of values, wildcards, and include/exclude logic.

Let me know what you think of this.

Mike

A Handy Trick I’ve Started to Use a Lot

Posted by on

If you’re like me, you hate to do the same thing over and over.  That’s what programming is for, right?  To handle automating tedious procedures?  Unfortunately, it’s not at all appropriate to run off and build an app every time you need to do the same thing 3 times.  If you try that, you’ll have a lot of chances to write apps,  but probably will be looking for a new job because it takes you way too long to get anything accomplished.

Scripting is the short answer to the dilemma above.  PowerShell is one of the latest entries into the scripting world, and to my tastes, one of the best.

Here’s something I used several times in the last few days.  I can’t remember quite where I saw it first, but it was in a PowerShell blog about looping (I think).

Anyway, the problem is that I needed to edit config files on the servers in a farm.  Fortunately, the servers were numbered sequentially.  So, what I wrote was (suitably sanitized for public consumption):

1..9 | % { notepad "server$_`c$path_to_config_file\config.file" }

That popped the first 9 files up in notepad, ready to be edited.  The trick is to use the range syntax to create a list of numbers, and use % to loop through them.

If you need a longer range (with leading zeroes, of course), it’s not too hard.

1..20 | % {notepad ("server{0:D2}\c$\path_to_config_fileconfig.file" -f $_)}

Here, we use the format operator with a D2 format specifier (2 digits, leading zeros).  See here for more examples of format operators in PowerShell.

When you’re dealing with dozens of servers, tricks like this can save you a lot of time.

Let me know what you think.  What “idioms” in PowerShell do you find yourself using a lot?

Mike

Is it just me? (Or does PowerShell remind you of SQL?)

Posted by on

When preparing a PowerShell training class for a group of DBAs, I realized that there were some parallels between basic SQL and basic PowerShell commands.

A (very) basic SQL statement has the form:

SELECT <COLUMNS>
FROM <TABLE>
WHERE <CONDITION>
ORDER BY <EXPRESSION>

I noticed that a very common idiom for PowerShell pipelines* was:

<data source cmdlet> | select-object <properties> | where-object <CONDITION> |  sort-object <EXPRESSION>
 

By “<data source cmdlet>”, I mean some cmdlet that puts a bunch of objects in the pipeline, like get-childitem, get-process, get-task, etc.

Part of the power of SQL is that it doesn’t matter what kind of data is in the tables, the same form of SQL statement works the same way (predictability).  This is one of the things I love about PowerShell.  It doesn’t matter what kinds of data is returned by a cmdlet.  The same form of PowerShell pipeline* will perform the same kind of predictable operations on it.  I know that this is often mentioned in tutorials and videos about PowerShell, but this was when it really struck me. 

A few other SQL/PowerShell comparisons might be:

SQL

PowerShell
GROUP BY group-object
SUM(), AVG(), etc. measure-object
Cursors foreach-object loops
SELECT DISTINCT select-object –unique
SELECT TOP n select-object –first n

 

Obviously, this comparison breaks down pretty quickly.  There isn’t really a parallel that I can find to JOIN statements, which make SQL so powerful, and clearly there’s a lot of powershell scripts that don’t fit the pattern I’m describing.  I think, though, that it’s a useful comparison and can help get people “over the hump” in their quest to master PowerShell.

Let me know what you think.

Mike

* A Pipeline in PowerShell is a sequence of cmdlets where each takes the output of the previous cmdlet as its input.

Executing SQL the Right Way in PowerShell

Posted by on

We all know that using string concatenation (or substitution) in SQL is a “bad thing”.  If you google “SQL Injection”, you’ll find hundreds of pages that tell you not to do things this way and that you need to use parameterized queries.  However, I still see a lot of code in PowerShell that does this:

$cmd.ExecuteNonQuery("delete from Table1 where Column1='$value'")

Since code like this is obviously prone to SQL injection attacks, it must be that doing it the right way is difficult, right?  Actually, no.  Here’s a simple function that allows you to run parameterized queries easily using dictionaries.

function exec-query( $sql,$parameters=@{},$conn,$timeout=30,[switch]$help){
 if ($help){
 $msg = @"
Execute a sql statement.  Parameters are allowed.
Input parameters should be a dictionary of parameter names and values.
Return value will usually be a list of datarows.
"@
 Write-Host $msg
 return
 }
 $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
 $cmd.CommandTimeout=$timeout
 foreach($p in $parameters.Keys){
 [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
 }
 $ds=New-Object system.Data.DataSet
 $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
 $da.fill($ds) | Out-Null

 return $ds
}

Example usage:

$conn=new-object data.sqlclient.sqlconnection "Server=servername;Integrated Security=True"
$conn.open()
exec-query 'select * from sys.databases' -conn $conn
exec-query 'select FirstName,LastName from AdventureWorks2008.Person.Person where FirstName=@fname' -parameter @{fname='Mike'} -conn $conn

As you can see from the second example, using a parameterized function wasn’t much harder than it would have been to do string concatenation or substitution.  And you don’t have to worry about little Bobby Tables.

The code above still needs some work to take care of output parameters, but I’ll save that for another post.

Let me know if you have any questions, comments, or complaints.

Mike

PowerShell ETS (Extended Type System)

Posted by on

In a recent post , I showed how to get a list of Scheduled Tasks as objects using PowerShell and Import-CSV.   In that, I included the following line of code:

$task.PSObject.TypeNames.Insert(0,"DBA_ScheduledTask")

In this post, I’ll try to explain why I did that.

There are several approaches to making objects in PowerShell.

  • Use new-object to create an object from an existing class (.NET, COM, ADSI, WMI)
  • Use add-member to add properties and methods to an existing object
  • Use ETS (the Extended Type System) to add properties and methods to an existing class

Obviously, the first approach (new-object)  makes sense if there is an existing class that you know fits the purposes you have in mind.  It helps if there’s a constructor that takes arguments that you have lying around, too.  For example:

 $conn=new-object data.sqlclient.sqlconnection "Server=laptopsqlexpress;Integrated Security=True"

But often, you’re working with a domain-specific object that doesn’t exist in the .NET framework or anything else.

The second approach (using add-member) is useful, for example,  if you need to create a record from a collection of properties.  You can also add methods using add-member.   One thing that you’ll run into with using add-member is that you have to remember to add the same members (the same set of properties and methods) to each similar object that you’re dealing with.  This is a lot different than when you create a class definition and instantiate objects from the class.

The third approach is similar to the second, in that you specify properties and methods to add, but in this case, you specify them in a configuration file ( *.ps1xml) that is added with the update-typedata cmdlet.   In this file, you name the class that you want to extend, and list the property and method definitions that you want to add.  After the update-typedata cmdlet is issued, all objects of the type listed will have the new properties and methods.

One especially nice feature of the third approach is that the name of the class you want to extend does not have to be the actual name of the type of the objects.  What it really does is match the values in the object’s PSObject.TypeNames property.  For most objects, this property is a list of the inheritance chain for the object, going back to System.Object.  However, as in the above example, you are free to add (or remove) items from this list.  Above, I added “DBA_ScheduledTask” to the list.  That means that if I have a .ps1xml configuration file that has ETS info in it for the type “DBA_ScheduledTask”, that set of properties and methods would be available from the objects I create.

I often use functions that return data out of a database.  PowerShell is nice enough that it acts like DataRows are objects, using column names for properties.  This makes it very easy to use the data in PowerShell without doing much to it.  By adding a specific “type name” to the TypeNames collection, I can instantly make the DataRow records into full-fledged objects (by including an appropriate update-typdata command in my profile).  In the post about Scheduled Tasks, it would make sense to use ETS to specify the Run and Delete methods.  Here’s what that would look like:

<?xml version="1.0" encoding="utf-8" ?>
<Types>
 <Type>
 <Name>DBA_ScheduledTask</Name>
 <Members>
 <ScriptMethod>
 <Name>Run</Name>
 <Script>
 schtasks.exe /RUN /TN $this.TaskName /S $this.HostName
 </Script>
 </ScriptMethod>
 <ScriptMethod>
 <Name>Delete</Name>
 <Script>
 schtasks.exe /DELETE /TN $this.TaskName /S $this.HostName
 </Script>
 </ScriptMethod>
 </Members>
 </Type>
</Types>

With this in a file (for instance c:typesscheduledTasks_type.ps1xml), you would issue the command “update-typedata c:typesscheduledTasks_type.ps1xml”, and the Run and Delete methods would be added to items of that type.  Of course, you’d want to modify the code in the previous post to not add those methods with add-member.

I tend to use a mix of add-member and ETS to create the objects I want.  You have to be careful when using ETS to remember that if you want the extra properties, you have to have loaded the ps1xml file in the session you’re running.  This often isn’t the case if you’re remoted into a server troubleshooting something.  For that reason, it’s usually preferrable (in my opinion) to use add-member most of the time.  I use a custom PowerShell host that allows me to use ETS data to specify context menus for different types of objects and customize them through the ps1xml files.  Since I don’t expect to find those context menus when I’m in the text console on a server, it doesn’t get me into trouble.

Let me know if you have any questions, comments, or complaints.

Mike