Skip to content
Archive of posts filed under the Scripts category.

Checking a Field for NULL in PowerShell

It’s been a long time (over 2 months) since I last posted.  I’ll try to get back into a rhythm of posting at least weekly.  Anyway, this is something that occurred to me at work when writing a script.

I usually avoid nullable columns, but sometimes date fields make sense to be null (rather than use sentinel values like 1/1/1900).  In this case, I had a nullable date column and I needed to check in PowerShell whether the field was in fact null or not.  In SQL, I would have just used an IS NULL, or used the IsNull() function to replace the null value with something a little easier to deal with.  My first (feeble) attempt was to do this:

if (!$_.completedDate){
# it’s null
}

Unfortunately for me, that doesn’t work.  Next, I used this (which worked, but wasn’t very satisfactory either):

if ($_.completedDate.ToString() -eq ''){
# it’s null
}

Realizing that I was being stupid, I googled “PowerShell SQL NULL and after looking at several pages which didn’t really address the issue, I found this. A little work to change it into a function, and voilà.

function is-null($value){
  return  [System.DBNull]::Value.Equals($value)
}

A few quick tests and this is what I wanted. Now, my code looks like this:

if (is-null $_.completedDate){
# it’s null
}

I find it hard to believe I haven’t written this function before (or seen it).

By the way…be watching the SQL PowerShell Extensions project.  Chad released version 2.1, which includes SQL mode for the ISE (really nice).  I know he and several others are collaborating on an update which should be out sometime soon.

-Mike

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

The PowerShell Bug That Wasn’t, and More Package Management

Have you ever tracked down a bug, been confident that you had found the root of your problems, only to realize shortly afterwords that you missed it completely?

What I posted yesterday as a bug in PowerShell (having to do with recursive functions, dot-sourcing, and parameters) seemed during my debugging session to clearly be a bug. After all, I watched the parameter value change from b to a, didn’t I? Sure did. And in almost every language I’ve ever used, that would be a bug. On the other hand, PowerShell is the only language that I know of that has dot-sourcing. Here’s a much simpler code example which shows my faulty thinking:

function f($x){
   if ($x -eq 1){
      write-host $x
      . f ($x+1)
      write-host $x
   }
}

f 1

Here, we have a simple “recursive function” which uses dot-sourcing to call itself. In my mind, how this would have worked is as follows:

  • We call the function, passing 1 for $x
  • The if condition is true, so it prints 1 and calls the function, passing 2 for $x
  • In the inner call, the if condition is false, so nothing happens
  • We pop back to the calling frame, where $x is 1 and print it

If it weren’t for that pesky dot operator, that would have been accurate.

The problem is, the dot operator changes the scoping of the inner call.  Here’s what the about_operators help topic, has to say about the dot sourcing operator:

        Description: Runs a script so that the items in the script are part of the calling scope.

Which is not a surprise…really.  The reason I was using the dot operator in my package management code was to make sure that functions defined in the scripts it was calling would be included in the existing scope, rather than their script scope.  The problem was one of nearsightedness.  I was so focused on the fact that the dot sourcing was making the functions part of the caller’s scope that I didn’t consider that variable declarations (including parameters) would also be in the caller’s scope.

So, the correct interpretation of the above script is:

  • We call the function, passing 1 for $x
  • The if condition is true, so it prints 1 and calls the function, passing 2 for $x
  • The parameter is named $x, so $x in is set to 2 (overwriting the $x that was set to 1)
  • In the inner call, the if condition is false, so nothing happens
  • We pop back to the calling frame, where $x is 2 and print 2.

The trick here is that the function f dot-sourced something that set $x to 2.  The fact that it was f is incidental.  It didn’t have to be.

Maybe this example will make it more clear:

function f($x){
    write-host $x
    . g
    write-host $x
 }

function g{
   $x = "Hello, World!"
}
f 1

If we were doing this without dot-sourcing, we would expect to see the number 1 printed out twice. However, since we dot-sourced g, the assignment in the function body of g happens in the scope of f. In other words, it’s as if the $x=”Hello, World!” were executed inside f. Thus, the output of this script is 1, followed by “Hello, World!”.

So, it wasn’t a bug, it was just me not being thorough in applying my understanding of dot-sourcing.

Now, on with Package Management.
First, to fix the problem caused by the parameter being overwritten (which it is, it’s just that it’s expected to be). I hadn’t worked out a way to fix the problem before I went to bed last night, but as I was rolling this stuff around in my head (which is when I figured out that it wasn’t really a bug), I thought of a simple solution. Since we can expect that sometimes the $filename parameter in the require (and reload) function will be overwritten by the a value in the dot-sourced script, we just need to make sure we’re done using it at that point. So, I simply made the assignment to the dictionary before dot-sourcing.  Here’s the updated code:

$global:loaded_scripts=@{pkg_utils='INITIAL'}

function require($filename){
	if (!$global:loaded_scripts[$filename]){
	   $global:loaded_scripts[$filename]=get-date
	   . scripts:\$filename.ps1
	}
}
function reload($filename){
	$global:loaded_scripts[$filename]=get-date
	. scripts:\$filename.ps1
}

To add modules, we need to do a few extra things:

  • We need to detect if we’re running in 2.0 or not
  • We need to see if there is a module with the given name
  • We need to see if the module is already loaded or not (in the case of require…it won’t matter for reload

Fortunately, none of those are very difficult.  Here’s the updated code (including modules). I even added some comments to make the flow more clear:

$global:loaded_scripts=@{pkg_utils='INITIAL'}

function require($filename){
    if ($global:loaded_scripts[$filename]){
          # this function has already loaded this (script or module)
          return
    }
    if ($psversiontable){
       # we're in 2.0
       if (get-module $filename -listavailable){
               #the module exists in the module path
         	   $global:loaded_scripts[$filename]=get-date
               import-module $filename
               return
       }
    }
    #it wasn't a module...so dot-source the script
    $global:loaded_scripts[$filename]=get-date
    . scripts:\$filename.ps1

}
function reload($filename){
    if ($psversiontable){
        # we're in 2.0
        if (get-module $filename -listavailable){
           #the module exists in the module path
           $global:loaded_scripts[$filename]=get-date
           import-module $filename
           return
        }
    }
    # it wasn't a module...so dot-source the script.
  	$global:loaded_scripts[$filename]=get-date
	. scripts:\$filename.ps1
}

That’s it for today. Let me know what you think.

-Mike

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

Package Management and a PowerShell Bug

UPDATE: I have worked out how the behavior described at the end of this post is not a bug, but in fact just PowerShell doing what it’s told. Don’t have time to explain right now, but I’ll write something up later today. I also worked out how to “fix” the behavior.

For a long time now, I’ve been dissatisfied with what I call “package management” in PowerShell.  Those of you who know me will be shocked that anything in PowerShell is less than perfect in my eyes, but this is one place that I feel let down.  Modules in 2.0 remedy the situation somewhat, but it still isn’t quite what I want or am used to in other languages.

Let me give an example.  In VB.NET, if you need to use the functions in an assembly, you put “Imports AssemblyName” at the top of your script.  In C#, you would have “Using AssemblyName”.  In Python, there would be “Import Something”.

In PowerShell 1.0, you had nothing.  In 2.0, you could create a module manifest which would specify either RequiredModules or ScriptsToProcess (or several other things to do upon loading the module).  The problems I see  with using the module manifest are:

  • What if I’m not writing a module?  There’s no such thing as a “script manifest”
  • What if the script or module that is required performs some initialization that should only be done once per session?
  • What if the script or module that is required performs expensive initialization?

Because of these reasons (and because I only started using 2.0 when it went RTM) I wrote a couple of quick functions to do what I thought made sense.

$global:loaded_scripts=@{pkg_utils='INITIAL'}

function require($filename){
	if (!$global:loaded_scripts[$filename]){
	   . scripts:\$filename.ps1
	   $global:loaded_scripts[$filename]=get-date
	}
}
function reload($filename){
	. scripts:\$filename.ps1
	$global:loaded_scripts[$filename]=get-date
}

To use these you need to create a psdrive called scripts: with code like this (probably in your profile):

New-PSdrive -name scripts -PSprovider filesystem -root \\PathToYourLibraries | Out-Null

Then, also in your profile, you’ll want to dot-source the file you put these functions in (for example, package_tools.ps1):

. scripts:\package_tools.ps1

Once you have those set up, you can dot-source the require function to make sure that a script has been loaded as such:

. require somelibrary

I have the functions I use divided by “subject” into several library scripts, and make sure that at the top of each script, I use “. require” to ensure that any prerequisites are already loaded.

Now for the PowerShell bug (which took me a long time to track down).
Create 2 files, a.ps1 and b.ps1 in your scripts: directory.

# a.ps1
write-host "this is script a"
#b.ps1
write-host "this is script b"
write-host "this script loads a"
. require a

After dot-sourcing package_tools, run the following commands:

. require b

You should get output that looks something like this:

this is script b
this script loads a
this is script a

Everything looks good until you inspect the $global:loaded_scripts variable:

ps> $loaded_scripts

Name                           Value
----                           -----
a                              1/19/2010 11:23:09 PM
package_tools                  INITIAL

Although b.ps1 was indeed dot-sourced (you can see the output), and the only code-path through the require function that would dot-source it would also add an entry to $loaded_scripts, there is no such entry. The problem is that when b.ps1 called the require function (to load a.ps1), the $filename variable in the calling context (where it should have been “b”) was overwritten by the call with “a” as a parameter. Walking through the code in a debugger confirms the problem.

Have you ever seen problems with recursion and dot-sourcing in PowerShell? Can you see any way around the problem I’ve described? For instance, saving the $filename in a variable and restoring it after the dot-source call (line 5 above) doesn’t help, because the same code-path is followed in the recursive call, and that variable is overwritten as well.

Even with this bug, I find the require function (and reload, which I didn’t discuss, but always loads the script in question) to be very helpful. I also have extended them to include importing modules, if they exist. I’ll discuss them in my next post, coming soon.

-Mike

P.S. Here‘s a question I posted to StackOverflow.com about these functions back in November of 2008.

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

SQL PowerShell Extensions (SQLPSX) 2.0 Released

The first module-based release of the SQL PowerShell Extensions (SQLPSX) was released recently on CodePlex.  It features very handy wrappers for most of the SMO objects used to manipulate SQL Server metadata, SSIS packages, Replication, and (new in the 2.0 release) an ADO.NET module which I wrote based on the code in this post.  There’s also a data-collection process and Reporting Services reports to help you get your SQL Server installations under control.

Chad Miller, the driving force behind SQLPSX, has put a lot of effort into this release, and you’ll find really good examples of advanced functions (with comment-based help, even).

If you deal with SQL Server in any way, you’ll almost certainly be able to use this set of modules to streamline your scripting experience (and probably learn something about SMO in the process).

You can find the release here.

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

Get-EventLog and Get-WMIObject

Recently, we had an occasion to write a process to read event logs on several sql servers to try to determine login times for different sql and Windows logins.  Since we have begun using PowerShell v2.0, and since get-eventlog now has a -computername parameter, it seemed like an obvious solution.

The event message we were interested in looked something like “Login succeeeded for uesr ‘UserName’ ….”.  The code we were trying to use was:

get-eventlog -computername $servername -logname Application -message "Login succeeded for user*" -after ((get-date).AddDays(-1))

I expected that, given a date parameter and a leading string to match wouldn’t be too bad, but this ended up taking several minutes per server.  As there are over a hundred servers to scan, that didn’t work well for us.

We ended up falling back to get-wmiobject.

$BeginDate=[System.Management.ManagementDateTimeConverter]::ToDMTFDateTime((get-date).AddDays(-1))
get-wmiobject -class win32_ntlogevent -computerName $servername -filter "(EventCode=18453)  and (LogFile='Application') and (TimeGenerated >'$BeginDate')"

Cons:

  • We have to encode the date parameter (instead of using a nice datetime parameter like get-eventlog has)
  • We have to write a WQL where-clause to match the parameters

Pros:

  • We get to use the event code (rather than a string match)
  • The code is orders of magnitude faster (39 servers in 13 minutes as a test case)

I think that you might have a positive experience using get-eventlog if you need to scan a range of time (for instance if you’re reporting on what happened on the server), but if you need to look for a specific event (or set of events) you’re probably going to want to use get-wmiobject.

-Mike

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

An Overlooked Parameter

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.

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

Flexible Filtering

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

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

A Handy Trick I’ve Started to Use a Lot

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_file\config.file" -f $_)}

Here, we use the format operator with a D2 format spceifier (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

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

Executing SQL the Right Way in PowerShell

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

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark

PowerShell ETS (Extended Type System)

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=laptop\sqlexpress;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:\types\scheduledTasks_type.ps1xml), you would issue the command “update-typedata c:\types\scheduledTasks_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

  • Digg
  • Slashdot
  • Reddit
  • Tumblr
  • Delicious
  • Twitter
  • Google Bookmarks
  • StumbleUpon
  • Technorati Favorites
  • Google Reader
  • Share/Bookmark