FoxDeploy.com

[PSCustomObject] Need not apply;How PowerShell 2.0 handles objects

Today at a client I had to export some Exchange Information from a one of their Exchange boxes. A simple task, but I kept experiencing some strange issues when I would run my script in PowerShell on the Exchange servers.

The only data I needed was RecipientType, LastLoggedOn and some other similar info, which is also easy to get. It needed to be extracted from Exchange and made ready for assimilation into a larger spreadsheet, so exporting as a Custom Object of some form would be the way to go.

I quickly came up which this script, which should have been more than up to the task. However, the output was very bizarre.

$Array = New-Object -TypeName System.Collections.ArrayList
 
Function Get-MarkInfo {
   ForEach ($user in $users){
      $mbx = Get-Mailbox $user
      $mbxstat = Get-MailboxStatistics $user
      [PSCustomObject]$obj=@{Name=$user;RecipientType=$mbx.RecipientType;LastLoggedOnUserAccount=$mbxstat.LastLoggedOnUserAccount;LastLogOffTime=$mbxstat.LastLogOffTime;LastLogonTime=$mbxstat.LastLogonTime}
      $Array.Add($obj) | Out-Null
   }
   $Array | export-csv C:\temp\EmailExport.Csv
}

This shouldn’t export these sorts of things

Oh no…why is my glorious [PSCustomObject] master race appearing as a hash table? No one asked for key:value pairs!

A hashtable? I never ordered a hashtable!  The CSV output was even worse

No properties? This is what happens when you export a hashtable and don’t enumerate

This was very puzzling. I tried everything under the sun to try and determine why my beautiful little PSCustomObject was coming out as an ugly hashtable. Surely there is an explanation somewhere!  I tried adding Format-Table commands everywhere you could think of, and sorts of craziness with using accelerators to try and force my PSCustomObject to be treated with the respect it deserves.

I ended up digging through old blogposts about ‘Whats New In PowerShell v2’ and v3, when finally I stumbled on these posts: http://stackoverflow.com/questions/14967920/powershell-3-pscustomobject-displays-in-stupid-mode-via-a-variable http://www.jonathanmedd.net/2011/09/powershell-v3-creating-objects-with-pscustomobject-its-fast.html

[PSCustomObject] casting is a new feature in PowerShell version 3.0!

A quick check…

There It was, right in front of me

I’d been working on a 2.0 version server all along! :(

I did some googling to try to refresh myself on the old way to do it, and even posted on PowerShell.org.

http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/19/create-custom-objects-in-your-powershell-script.aspx

MikeFRobbins( http://mikefrobbins.com/, https://twitter.com/mikefrobbins) chimed in with a text-book perfect solution to my issue. So, I present to any of you who ever run into this same issue, the PowerShell 2.0 approach to creating and exporting a custom object.

$Array = New-Object -TypeName System.Collections.ArrayList
 
Function Get-MarkInfo {
   ForEach ($user in $users){
      $mbx = Get-Mailbox $user
      $mbxstat = Get-MailboxStatistics $user
 
      $ObjectProperties = @{
         Name = $user
         RecipientType=$mbx.RecipientType
         LastLoggedOnUserAccount=$mbxstat.LastLoggedOnUserAccount
         LastLogOffTime=$mbxstat.LastLogOffTime
         LastLogonTime=$mbxstat.LastLogonTime
         }
 
      $obj = New-Object PSObject -Property $ObjectProperties
      $obj | ft
      $Array.Add($obj) | Out-Null
      }
   $Array | ft
   }

September Atlanta PowerShell Users Group!

Come see me speak on my home turf!

https://www.eventbrite.com/e/atlanta-powershell-user-group-september-meeting-tickets-13037013057

Session Topic

Stephen Owen of iVision will give a review of the best new features in PowerShell version 5, including what’s new in the recent WMF and Resource Kits.  He will cover usages cases for some interesting new cmdlettes like Convert-FromString, and also do an overview with examples of some of the recent DSC Goodness released

Meeting Location

iVision, a long time community sponsor,  has graciously agreed to allow to hold our meeting at their offices in Midtown Atlanta.

1430 West Peachtree Street NW Suite 425 Atlanta, GA 30309 - See more at: http://ivision.com/company/contact-us/#sthash.TTyjn77t.dpuf

Directions: http://ivision.com/company/directions Parking: The venue does not have free parking. It should cost you about $5 to park.

Have questions about Atlanta PowerShell User Group September Meeting?Contact Atlanta PowerShell User Group

One Inch of Power: Get-Battery info!

This ‘One Inch of Power’ is a new piece I want to start doing, cramming a lot of functionality into an inch or less of PowerShell code.

Most of these will be ten lines or less, and do something useful. So, here we go!

Ever feel like your battery is letting you down? Thanks to modern Battery WMI compatibility, we can get a battery to report to us its supposed capacity, and then dig through various WMI classes to find out if the battery is really performing as well as it should be.

Remember that most laptop batteries are only going to perform at full power for a year worth of charging cycles, after which you’ll get reduced performance. My battery on my laptop was starting to slouch, so I decided to write this up.

You should run this code once while charging, and then rerun this function to see what the actual output is of your battery.

Function Get-BatteryStats{
$batteryStatus = gwmi batterystatus -name root\wmi
$batterystats  = gwmi win32_portablebattery
$missing = [Math]::Round(((1-($batteryStatus.RemainingCapacity[0]/$batterystats.DesignCapacity))*100), 2)
 
$batteryReport = [pscustomobject]@{'Charging'=$batteryStatus.Charging[0];'PluggedIn'=$batteryStatus.PowerOnline[0];'ListedCapacity'=$batterystats.DesignCapacity;'ActualCapacity'=$batteryStatus.RemainingCapacity[0];'Percent Remaining'=[Math]::Round((($batteryStatus.RemainingCapacity[0]/$batterystats.DesignCapacity)*100),2);MissingCapacity=if ([math]::Sign($missing) -eq '-1'){"ReRunOnBattery"}ELSE{$missing}}
$batteryReport | ft 
 
if ($batteryReport.ActualCapacity -gt $batteryReport.ListedCapacity){Write-Warning "Battery Reports greater than possible capacity, try rerunning this code when unit is not plugged in to AC power"}
if ($batteryReport.MissingCapacity -eq 'ReRunOnBattery'){Write-Warning "Can't obtain missing power, rerun while unit is not plugged in to AC power"}ELSE{"You're missing out on $missing percent of your battery, time to replace!"}
}

Time to buy a new battery!

Working with SQL using PowerShell

I’ve seen a lot of people ask about how to do this, so I thought I’d provide some information on the matter. Previously I had a project in which I had to pull a ton of data from an extended SCCM database and massage it before importing into MDT to setup location settings and things like that for an automated zero-touch install with very granular configuration settings determined based on workstation location.

I had to learn this the hard way, but it saved me weeks worth of painstakingly combing through records, and was also pretty fun once I dug into the guts, and was able to get old customized SQL records to jive with the Microsoft Deployment Toolkit Database!  It’s fun to teach machines to talk to each other!

Because many PowerShell enthusiasts may not be familiar with SQL, in this post I’ll walk through the steps to install SQL, setup a database and then create a table, before digging into how to directly influence SQL from the PowerShell prompt.

Setting up our SQL Database Engine

First things first, we’ll create our SQL Test DB. I’ll start with Sql Server express 2008 with tools, since it is so common and easy.  Get the bits here http://www.microsoft.com/en-us/download/details.aspx?id=22973.  Once it finished downloading, run the install, selecting Installation and then ‘New SQL Server Stand-alone installation or add features to an existing installation”

You want the top option

The SQL install wizard will check system state before getting you too deeply into the install.

If you run into this ‘Need to Restart’ rule, you can bypass the restart by deleting the items under the following key.

HKLM:\\SYSTEM\\CurrentControlSet\\Control\\Session Manager 

and delete all items under PendingFileRenameOperations (Taken from http://www.sqlcoffee.com/Troubleshooting051.htm).  You can then rerun the Rule Check by clicking re-run.

All systems are go!

Install Setup Support Files, if needed, then correct any dire errors under Setup Support Rules, if encountered.  Select Free edition, then at the Feature selection screen pick at a minimum Database Engine Services and Management Tools - Basic

On the next screen, pick where you want your DB to go, and specify an instance name (I’m leaving this default).  Click Next.

For Accounts, select NT Authority System and local service, as this is just our test.  If you’re setting up production SQL, you should know that many applications which run on SQL won’t install if you leave the barn-doors open from a security standpoint.

Good enough for a test environment, but not recommended when going production

Click Next

Ensure you specify Add Current User to SQL Server Administrators

next next next next next next next, and then when this loading bar shows up, go ahead and grab a sandwich.

It will pause at this screen until roughly the heat-death of the universe.

Once the wizard finally completes, you should have SQL Installed!

Creating a Table

Alright, now that the fun of the installation wizard is behind us, it is time to open up management studio and create our first SQL database and table, then we’ll do some PowerShell!  First things first, fire up Management Studio.  and for your engine, select localhost, and leave the authentication as ‘Windows Authentication’ if you followed my steps earlier.

Now, click localhost in Object explorer on the side, then click databases, right-click and make a new database.

Pick whatever you’d like for a name. I choose FoxDeploy.  You can safely leave everything else as the default for our purposes, then pick next. Select your Database and browse to tables and pick new table

Pick a name for the table on the far right side, and then add some columns to your sheet. In my screen shot, I’ve selected the datatype of nchar(10), but I would recommend picking a type such as MAXVARCHAR or something similar in your own test. The nchar(10) datatype will limit the number of characters you can input, and can give you some puzzling errors.

On the right side, select the name of the table, I choose Powershell,

Finally, click this button to generate a change script you’ll execute which will spin up the table we need to insert data.

A box should appear, offering you the option to create a Save Change Script.  This is incredibly strongly recommended if ever touching production SQL.

You can copy the query out of this window and run it directly, which saves a step of running the transaction log file created

Click New Query, paste the script in, and hit f5! And hold on to your butts!

If everything worked correctly, you should receive confirmation in the messages window below.

Most of the time in SQL you won’t see this message….

Now hit F5 at the base level of the DB view and you should see your new table

Now we have a database engine running locally, with a database named foxdeploy, and a table titled powershell. The table should have two columns, filename and size.  This is another good time to grab a drink, as we’re finally about to begin coding!

SQL And PowerShell - Better Together?

Before I get into how to do the PowerShell bit, I want to tell a story which came up when I thought of this section heading.

Years ago I worked at a bank in telemarketing position. One day, we had a huge meeting where they had us hundred employees go into a room, and gave us mouse pads, shirts, and mugs, then go to our seats.  You can always tell how cheesie or impactful a reorg will be based on the amount of swag they give out. They even had candy in our seats when we got there.  My cheese-alert was off the radar.

They splurged for thousands and thousands of custom re-packaged Reeses’ cups with the company logo on it, with the saying ‘like chocolate and peanut butter, customer service and sales are better together!’ on the wrapping.

Turns out they were essentially laying off the custom service folks and making us do their jobs. Yay!  Who doesn’t want to call in to activate their credit card and get a hard-sale for a home-equity line, or call about an over limit fee and be sold useless credit insurance.

At least we got free candy.

Well, as it turns out, PowerShell and SQL do work pretty well together.  Better than a Reese’s cup…nah, maybe not quite that well.

Most of the magic of pulling info from and putting stuff into SQL revolves around using the Invoke-SQLCmd cmdlette to perform native language queries on the SQL databases and tables. This useful tool is installed as part of SQL Management Studios.  What we’ll be doing is gathering information with PowerShell and sending it down the pipeline to

To do our work, we’re concerned with the following syntax of the Invoke-SQLCmd cmdlette:

PS SQLSERVER:\\> "database","serverinstance","query" | % {get-help Invoke-Sqlcmd -Parameter $\_}

\-Database A character string specifying the name of a database. Invoke-Sqlcmd connects to this database in the instance that is specified in -ServerInstance. If -Database is not specified, the database that is used depends on whether the current path specifies both the SQLSERVER:\\SQL folder and a database name. If the path specifies both the SQL folder and a database name, Invoke-Sqlcmd connects to the database that is specified in the path. If the path is not based on the SQL folder, or the path does not contain a database name, Invoke-Sqlcmd connects to the default database for the current login ID. If you specify the -IgnoreProviderContext switch, Invoke-Sqlcmd does not consider any database specified in the current path, and connects to the database defined as the default for the current login ID.

Required? false Position? named Default value Accept pipeline input? false Accept wildcard characters? false

\-ServerInstance A character string or SMO server object specifying the name of an instance of the Database Engine. For default instances, only specify the computer name: "MyComputer". For named instances, use the format "ComputerName\\InstanceName".

Required? false Position? named Default value Accept pipeline input? true (ByValue) Accept wildcard characters? false

\-Query Specifies one or more queries to be run. The queries can be Transact-SQL or XQuery statements, or sqlcmd commands. Multiple queries separated by a semicolon can be specified. Do not specify the sqlcmd GO separator. Escape any double quotation marks included in the string. Consider using bracketed identifiers such as \[MyTable\] instead of quoted identifiers such as "MyTable".

Required? false Position? 1 Default value Accept pipeline input? false Accept wildcard characters? false 

We’ll be specifying a ServerInstance, Database and a query to run. Now, the -Query parameter is expecting native SQL language, here is an example of the SQL Transaction Query we’ll be running:

insert into Tablename (ColumnName1, ColumnName2)
 VALUES ('Value1', 'Value2') 

Not too bad to work with. You specify the ColumnName first, and then the Values to go into each row within that column.  This won’t be too hard to do, but let’s define our $database, $server and then do a test-run to give it a try, remembering that our column names are filename and size.

$database = 'foxdeploy' $server = '.' $table = 'dbo.powershell\_test'

Invoke-Sqlcmd -Database $database -ServerInstance $server \` -Query "insert into $table (filename, size) VALUES ('test','45')" 

When you execute this code, you won’t get any update at all from the console.  It is necessary to test and see that the change occurred:

```powershell Invoke-Sqlcmd -Database $database -ServerInstance $server -Query “select * from $table”

![sql15](../assets/images/2014/09/images/sql15.png)

Sweet!  Like Candy!

## The Practical-ish Example

Now, let's use a practical example. In this situation, let's list out the ten biggest files in a folder and select the filename and size, and then import those properties into our SQL Database. Keep in mind, the sky is the limit here, you could be polling for biggest mailboxes and then doing something with those users, dump records from AD into an analysis services instance for some in-depth reporting, do some datamining or whatever you can put your mind to.  This is purely proof-of-concept.

For a preview, these are the files in the directory I'm listing out:

![sql16](../assets/images/2014/09/images/sql16.png) If all goes well, these should appear as entries in our DB

What we'll do is setup variables to simplify our command structure (and let us re-use this code in the future).  We'll then get  and sort a list of the files, and then pull values from each record, which we'll store in a variable.  We'll then use Invoke-SQLCmd to act on this data, and insert one row for each item:

```powershell

$database = 'foxdeploy' $server = '.' $table = 'dbo.powershell\_test'

Get-ChildItem c:\temp | sort Length -desc | select -First 10 | % { $Name = $\_.Name $FileSize = $\_.Length $query = "insert into $table (filename, size) VALUES (\`'$Name\`', \`'$FileSize\`')" 

Write-Host "About to Execute:\`t $query" Invoke-Sqlcmd -database $database -ServerInstance $server -query "insert into $table (filename, size) VALUES (\`'$Name\`', \`'$FileSize\`')" }

And now, let’s run one more query to see if our changes took…

Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "select \* from $table" 

Blam! We’re pulling this data that we just added into SQL back from our database!

It worked!  To round things out, here is a screen shot from SQL management studio which shows that the entries now exist within the table from that side too.

Winding Down

While it is very unlikely that you’ll need to import a list of files into your SQL DB anytime soon, but just think of the possibilities.

You can take these principles and do anything you put your mind to with them.  This is one of the best features of PowerShell, this infinitely rewarding aspect of it.  A little bit of tinkering in your spare time one day can lead become the start of a powerful new tool to you’ll come back to over and over again in the future.  Just ten minutes of reading up on best practices can lead to a lifetime of positive ripples.

Be sure to check back later, when I’ll flesh this out with some code samples of deleting rows, updating rows, and creating/dropping tables. I hope this was helpful!

Adding -WhatIf Support to your scripts the right way, and how you shouldn't do it

If you are a prudent sysadmin or consultant, you probably rely on using the -WhatIf command before ever running any serious PowerShell cmdlets in your environment. Since we love it so much, shouldn’t we build this very same courtesy into our tools that others will use?

Before I learned how easy it is to add -WhatIf support to my Functions and Scripts, I’d often write something ugly like this:

Function Do-Stuff{

param([switch]$WhatIf)

ForEach($item in $args){ 
  if ($WhatIf){ 
    Write-host "WhatIf: Would be performing \`$Action on $item" -ForegroundColor Cyan 
    }
    ELSE {
      "Actually performing \`$Action on $item" 
      } 
    }
} 

Aside: This is amended from an actual script in production that I’ve created. It was made a LONG time ago.

Yep, it’s not very nice and I’m deeply, deeply ashamed.

I mean, it worked:

It’s almost an example of what not to do! This little snippet has been simplified to be as easy to read as it gets, the thing would get VERY nasty when working with a complex script, as I’d pretty much have to write all of my code twice. Once to actually do the thing, and another to describe what I was doing in this pseudo-WhatIf message.

It wasn’t uncommon to see this sort of thing over and over and over. Every single decision tree in my scripts would be made an extra six or seven lines longer because of this forking.  In fact, people often called me something that sounded just like ‘That Forking PowerShell guy’.   :(

BUT NO MORE!

Changing this little sucker to work the right way is easy, and only requires a few edits.

First off, we’re going to delete the [switch], and we’re going to add in a proper array/parameter to capture our input. We’ll also change line 5 from $args to $Objects, to match.

Function Do-Stuff{ 
  param([string[]]$Objects) 
  ForEach($item in $Objects){ 
    if ($WhatIf){ 
      Write-host "WhatIf: Would be performing \`$Action on $item" -ForegroundColor Cyan } 
    ELSE { 
      "Actually performing \`$Action on $item" 
      } 
    }
} 

We have to dump the  use of $args because we’ll be telling PowerShell to treat our code as a proper cmdlet, which means we can’t use catchalls like $args anymore.

Next, we’ll add in the magic sauce:

[CmdletBinding(SupportsShouldProcess=$true)]

. Add this little guy right after the Function Declaration like so:

Function Do-Stuff{ [CmdletBinding(SupportsShouldProcess=$true)] param([string[]]$Objects) 

Finally, we’ll gut the existing decision structure, and replace it with this:

 ForEach($item in $Objects){
    if ($pscmdlet.ShouldProcess("$item", "DoStuff")){ 
      "Actually performing \`$Action on $item" 
      } 
 }

In line 2 above, we’re specifying the syntax to be displayed to the user. In this case, if the user were to run

Do-Stuff -Objects "hello","sweetie" -WhatIf 

it would say:

What if: Performing the operation "DoStuff" on target "hello". What if: Performing the operation "DoStuff" on target "sweetie".

You also use this same structure to provide messages to the user which are displayed with Warning and ConfirmImpact, but I’ll get into those examples in a later date.

“But Stephen/FoxDeploy/PowerShell guy”, you ask, voice raising up a plaintive octave” what IS all of this $pscmdlet stuff anyway? You never defined $pscmdlet anywhere in that code!”

I’m happy you asked. $pscmdlet is the guts of what is letting us do a -WhatIf so easily. Well, this and [cmdletbinding].

In simple terms, what we’re doing here is replacing our janky $WhatIf switch for proper -WhatIf support, which is provided to us via the $pscmdlet, a special PowerShell variable created at runtime, and that object’s method .ShouldProcess(). We don’t need to define or mess with $pscmdlet at all, it is provided to our function to use during execution because we told PowerShell to treat this like real compiled code (think the built-in cmdlets).

See, adding that [CmdletBinding] declaration to our function told PowerShell that we wanted to treat this code as a default cmdlet, which exposes a lot of additional functionality. For instance, adding CmdletBinding lets us specify all sorts of additional tools our script can use, from ConfirmImpact to Paging, to WhatIf support. You can read more about CmdletBinding in the built in help system.

Get-help about_Functions_CmdletBindingAttribute 

Some of this stuff is hard to approach, which is why a deep-dive into what Cmdlet Binding is and what it offers is coming in a future post.

As for $PSCmdlet itself, we’re essentially peering under the covers into the PowerShell runspace here with this variable. It’s a bit confusing, but for now, know that you can’t use $pscmdlet.ShouldProcess() without adding [CmdletBinding] to your code.

Here is the completed sample of how to use -WhatIf

Function Do-Stuff{ 
  [CmdletBinding(SupportsShouldProcess=$true)] param([string[]]$Objects)

ForEach($item in $Objects){ 
  if ($pscmdlet.ShouldProcess("$item", "DoStuff")){ 
    "Actually performing `$Action on $item" 
  }
} 
} 

And the final result:

I hope you liked this shallow dive into how-to -WhatIf and how not to do it!

PowerShelling FizzBuzz

I recently learned about the FizzBuzz test, which is meant to help interviewers determine if a prospective hire can understand the fundamental logic needed to program.

The premise is this:

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.

I thought this sounded like a fun challenge, so I decided to try my hand at it.

At first, this seemed deceptively easy. Set up a For-Each loop with $num incrementing. If the number is divisibile by 3, then performing a modulo operation will result in a remainder of zero.

Perform the same if the number is divisble by five. Then write either Fizz or Buzz based on which was true.

However, if the number is divisible by BOTH, write out Fizzbuzz instead, and then proceed. The problem here was catching for the AND condition, very similar to a binary math operation. I eventually discarded my ForEach and instead used a While Loop, as the behavior of Continue and Break within a For loop lead to undesired outcomes.

Here is my answer:

Function Start-FizzBuzz { 
  $num = 0 while ($num -lt 100) { 
    #-lt is shorthand for LessThan 
    $fizz = $false 
    $buzz = $false 
    $num++ 
    if (($num % 3) -eq 0) {
      $fizz = $true;
    } 
    if (($num % 5) -eq 0) {
      $buzz = $true;
    }

    if (($fizz) -and ($buzz)){ 
      "FIZZBUZZ" continue
       #continue tells PowerShell to continue on to the next object in series, and gets around a situation where we'd get output of FIZZ, BUZZ, FIZZBUZZ 
       }
    if ($fizz){
       "FIZZ" CONTINUE 
      } 
    if ($buzz){
    "BUZZ"
    CONTINUE 
    }
     $num 
     #if we didn't write Fizz, Buzz or FizzBuzz, write the number itself 
     }
  }