Tag Archives: Powershell

Test PowerShell scripts with VirtualBox

I’m at the point where I am going to deploy some PowerShell scripts to my end-users, and I want to test the scripts on a fresh installation of Windows before trying them on the user’s workstations.

I use VirtualBox to create virtual machines for Powershell testing. Virtual box works on Linux, Mac and Windows host machines. My 8 gig Win 7 box works fine with one or two “guest OS’s”. On my 4 gig (ancient) iMac, it works, but its pretty slow.

One thing that I find amazing, is you have to run updates on all those Windows Virtual machines. Don’t expect to be fully productive on Tuesdays or Wednesdays, when Microsoft sends out Windows updates. It isn’t unusual for updates to run for an hour or more.

Be sure to install the VirtualBox “guest additions” within your Windows VM, once you’ve got your Windows VM up and running. You may also want to change the network settings to “bridged”, so that your VM is on the same network subnet as your host machine.

One other disconcerting thing; your Windows 7 desktop may come up with a black background depending on whether you are running the aero interface and other such fripperies. You can turn all this stuff on if you want; but it will slow down the performance.

More details on setting up VirtualBox are located  here and here.

winvm

If you have installed Windows 7, you may find that it has come with Powershell V.2 out of the box. You can test that by starting a PowerShell session from the command box (just type Powershell.exe).  Once Powershell is up, issue the following command at the prompt:

get-host | select-object version 

If it isn’t 4.0 or later, download the latest version of the Windows Management Framework from Microsoft.

Get-StateAbbreviation

Here is an updated version of my state abbreviation function, with changes prompted by Jeffery Hicks.
I’ve also added the Canadian provinces to the mix. The main change (apart from changing the name of the function to conform to the conventional Powershell verb-noun nomenclature), is to add the CmdletBinding section to specify a single parameter which needs to be supplied to run the function. The neat thing about this is that if you issue the command without the parameter it will automatically prompt for input.

function Get-StateAbbreviation {
#
# Takes upper, lower, or mixed case state name
# and return the two-letter abbreviation. 
# LK 3/17/15  rev. 3/26/15 per Jeffery Hicks 

# Still To Do: 
#   1. Full documentation 
#   2. Allow input from the pipeline 
#   3. Place in a module 

# Example calls: 
#   Get-StateAbbreviation Vermont     <-don't use parentheses
#   Get-StateAbbreviation -StateName Vermont
#   Get-StateAbbreviation "Vermont"
#   Get-StateAbbreviation $MyStateName 
# Note that this function has to appear before it is called in the code
# if it isn't part of a module. 

[CmdletBinding()]
param (
[Parameter(Mandatory=$True)]
   [string]$StateName
)

Switch ($StateName.ToUpper()) {

($StateName="ALABAMA")        {$shortenState="AL"}
($StateName="ALASKA")         {$shortenState="AK"}
($StateName="ARIZONA")        {$shortenState="AZ"}
($StateName="ARKANSAS")       {$shortenState="AR"}
($StateName="CALIFORNIA")     {$shortenState="CA"}
($StateName="COLORADO")       {$shortenState="CO"}
($StateName="CONNECTICUT")    {$shortenState="CT"}
($StateName="DELAWARE")       {$shortenState="DE"}
($StateName="FLORIDA")        {$shortenState="FL"}
($StateName="GEORGIA")        {$shortenState="GA"}
($StateName="HAWAII")         {$shortenState="HI"}
($StateName="IDAHO")          {$shortenState="ID"}
($StateName="ILLINOIS")       {$shortenState="IL"}
($StateName="INDIANA")        {$shortenState="IN"}
($StateName="IOWA")           {$shortenState="IA"}
($StateName="KANSAS")         {$shortenState="KS"}
($StateName="KENTUCKY")       {$shortenState="KY"}
($StateName="LOUISIANA")      {$shortenState="LA"}
($StateName="MAINE")          {$shortenState="ME"}
($StateName="MARYLAND")       {$shortenState="MD"}
($StateName="MASSACHUSETTS")  {$shortenState="MA"}
($StateName="MICHIGAN")       {$shortenState="MI"}
($StateName="MINNESOTA")      {$shortenState="MN"}
($StateName="MISSISSIPPI")    {$shortenState="MS"}
($StateName="MISSOURI")       {$shortenState="MO"}
($StateName="MONTANA")        {$shortenState="MT"}
($StateName="NEBRASKA")       {$shortenState="NE"}
($StateName="NEVADA")         {$shortenState="NV"}
($StateName="NEW HAMPSHIRE")  {$shortenState="NH"}
($StateName="NEW JERSEY")     {$shortenState="NJ"}
($StateName="NEW MEXICO")     {$shortenState="NM"}
($StateName="NEW YORK")       {$shortenState="NY"}
($StateName="NORTH CAROLINA") {$shortenState="NC"}
($StateName="NORTH DAKOTA")   {$shortenState="ND"}
($StateName="OHIO")           {$shortenState="OH"}
($StateName="OKLAHOMA")       {$shortenState="OK"}
($StateName="OREGON")         {$shortenState="OR"}
($StateName="PENNSYLVANIA")   {$shortenState="PA"}
($StateName="RHODE ISLAND")     {$shortenState="RI"}
($StateName="SOUTH CAROLINA")   {$shortenState="SC"}
($StateName="SOUTH DAKOTA")     {$shortenState="SD"}
($StateName="TENNESSEE")        {$shortenState="TN"}
($StateName="TEXAS")            {$shortenState="TX"}
($StateName="UTAH")             {$shortenState="UT"}
($StateName="VERMONT")          {$shortenState="VT"}
($StateName="VIRGINIA")         {$shortenState="VA"}
($StateName="WASHINGTON")       {$shortenState="WA"}
($StateName="WEST VIRGINIA")    {$shortenState="WV"}
($StateName="WISCONSIN")        {$shortenState="WI"}
($StateName="WYOMING")          {$shortenState="WY"}
($StateName="WASHINGTON DC")    {$shortenState="DC"}
($StateName="ALBERTA")               {$shortenState="AB"}
($StateName="BRITISH COLUMBIA")      {$shortenState="BC"}
($StateName="MANITOBA")              {$shortenState="MB"}
($StateName="NEW BRUNSWICK")         {$shortenState="NB"}
($StateName="NEWFOUNDLAND")          {$shortenState="NL"}
($StateName="LABRADOR")              {$shortenState="NL"}
($StateName="NORTHWEST TERRITORIES") {$shortenState="NT"}
($StateName="NOVA SCOTIA")           {$shortenState="NS"}
($StateName="NUNAVUT")               {$shortenState="NU"}
($StateName="ONTARIO")               {$shortenState="ON"}
($StateName="PRINCE EDWARD ISLAND")  {$shortenState="PE"}
($StateName="QUEBEC")                {$shortenState="QC"}
($StateName="SASKATCHEWAN")          {$shortenState="SK"}
($StateName="YUKON")                 {$shortenState="YT"}

Default {$shortenState="XX"}

} # Switch 

return $shortenState

} # Function

Create U.S. State Abbreviations from State Names

function shortenState([string]$longName) {
# LK 3/17/15
# Takes upper, lower, or mixed case state name
# and returns the two-letter abbreviation.
# Example call:
#   shortenstate Vermont     <-don't use parentheses
#   "Vermont" | shortenState <- sent via a pipe
# Note that this function has to appear *before* it is called in a Powershell script.
Switch ($LongName.ToUpper()) {

  ($LongName="ALABAMA")        {$shortenState="AL"}
  ($LongName="ALASKA")         {$shortenState="AK"}
  ($LongName="ARIZONA")        {$shortenState="AZ"}
  ($LongName="ARKANSAS")       {$shortenState="AR"}
  ($LongName="CALIFORNIA")     {$shortenState="CA"}
  ($LongName="COLORADO")       {$shortenState="CO"}
  ($LongName="CONNECTICUT")    {$shortenState="CT"}
  ($LongName="DELAWARE")       {$shortenState="DE"}
  ($LongName="FLORIDA")        {$shortenState="FL"}
  ($LongName="GEORGIA")        {$shortenState="GA"}
  ($LongName="HAWAII")         {$shortenState="HI"}
  ($LongName="IDAHO")          {$shortenState="ID"}
  ($LongName="ILLINOIS")       {$shortenState="IL"}
  ($LongName="INDIANA")        {$shortenState="IN"}
  ($LongName="IOWA")           {$shortenState="IA"}
  ($LongName="KANSAS")         {$shortenState="KS"}
  ($LongName="KENTUCKY")       {$shortenState="KY"}
  ($LongName="LOUISIANA")      {$shortenState="LA"}
  ($LongName="MAINE")          {$shortenState="ME"}
  ($LongName="MARYLAND")       {$shortenState="MD"}
  ($LongName="MASSACHUSETTS")  {$shortenState="MA"}
  ($LongName="MICHIGAN")       {$shortenState="MI"}
  ($LongName="MINNESOTA")      {$shortenState="MN"}
  ($LongName="MISSISSIPPI")    {$shortenState="MS"}
  ($LongName="MISSOURI")       {$shortenState="MO"}
  ($LongName="MONTANA")        {$shortenState="MT"}
  ($LongName="NEBRASKA")       {$shortenState="NE"}
  ($LongName="NEVADA")         {$shortenState="NV"}
  ($LongName="NEW HAMPSHIRE")  {$shortenState="NH"}
  ($LongName="NEW JERSEY")     {$shortenState="NJ"}
  ($LongName="NEW MEXICO")     {$shortenState="NM"}
  ($LongName="NEW YORK")       {$shortenState="NY"}
  ($LongName="NORTH CAROLINA") {$shortenState="NC"}
  ($LongName="NORTH DAKOTA")   {$shortenState="ND"}
  ($LongName="OHIO")           {$shortenState="OH"}
  ($LongName="OKLAHOMA")       {$shortenState="OK"}
  ($LongName="OREGON")         {$shortenState="OR"}
  ($LongName="PENNSYLVANIA")   {$shortenState="PA"}
  ($LongName="RHODE ISLAND")   {$shortenState="RI"}
  ($LongName="SOUTH CAROLINA") {$shortenState="SC"}
  ($LongName="SOUTH DAKOTA")   {$shortenState="SD"}
  ($LongName="TENNESSEE")      {$shortenState="TN"}
  ($LongName="TEXAS")          {$shortenState="TX"}
  ($LongName="UTAH")           {$shortenState="UT"}
  ($LongName="VERMONT")        {$shortenState="VT"}
  ($LongName="VIRGINIA")       {$shortenState="VA"}
  ($LongName="WASHINGTON")     {$shortenState="WA"}
  ($LongName="WEST VIRGINIA")  {$shortenState="WV"}
  ($LongName="WISCONSIN")      {$shortenState="WI"}
  ($LongName="WYOMING")        {$shortenState="WY"}
 
Default {$shortenState="XX"}
  } #switch

return $shortenState

} # function shortenState

# The following lines import a .csv file, and modify the state field
# to the two-letter abbreviation 

$Deliveries=Import-Csv "c:userslarrypowershellworldship.csv"

$Deliveries | Foreach-Object ($_) {

  if ($_.State.length -gt 2) {$_.State = shortenState($_.State)}

}

Powershell: Limit API iterations in a single call

Problem:

Many APIs limit the number of iterations that you can make in a single API call. For example, Brightpearl limits you to getting information for a maximum of 200 orders in a single API call. If you place a call with more than 200 orders, it will simply return an error message. SmartyStreets also places a limit of 100 addresses that you can validate with a single API call.

Solution:

Dave Wyatt at PowerShell.org provides the following solution.

Lets assume there is an array of 1000 addresses which are returned by convertfrom-csv. Here are the first couple of records from the original .csv file.

PS>cat lapsed.csv
Addressee,first,spouse,Organization,Street,City,State,ZIP
Joe Dokes,Joe, Mary,,,601 W 57TH St Apt 361,New York,NY,10019
Mary Smith ,Mary,Howard,,347 Poor Farm Rd,Colchester,VT,05446
Lu-Anne Jorden,Lu-Anne,Jess,,9603 North Kiowa Rd.,Parker,CO,80138

Here is the command that we use to read in the list into the variable $bigLlist

$bigList =(cat lapsed.csv | convertfrom-csv | 
Select-Object Addressee, Organization, Street, City, State, Zip )

$bigList is a custom object with the following layout:

PS>$biglist | get-member
TypeName: Selected.System.Management.Automation.PSCustomObject
Name         MemberType    Definition 
----         ----------    ---------- 
Equals       Method        bool Equals(System.Object obj) 
GetHashCode  Method        int GetHashCode() 
GetType      Method        type GetType() 
ToString     Method        string ToString() 
Addressee    NoteProperty  System.String Addressee=Kamal Aboul-Hosn 
City         NoteProperty  System.String City=New York 
Organization NoteProperty  System.String Organization= 
State        NoteProperty  System.String State=NY 
Street       NoteProperty  System.String Street=601 W 57TH St Apt 361
ZIP          NoteProperty  System.String ZIP=10019

If you look at this in Powershell, it prints out the contents of each record.

Addressee    : Joe Dokes
Organization :
Street       : 109 Fern Ct.
City         : Delray Beach
State        : FL
ZIP          : 33444

Addressee    : Mary Smith
Organization :
Street       : 205 Dorado Dr
City         : Cherry Hill
State        : NJ
ZIP          : 08034

Addressee    : Lu-Anne Jorden
Organization :
Street       : PO Box 81666
City         : Fairbanks
State        : AK
ZIP          : 99708

Ok, so now we have the full list as an object. The list now needs to be subdivided into groups of 100.

$counter = @{ Value = 0 }
$groupSize = 100
$groups = $bigList | Group-Object -Property { [math]::Floor($counter.Value++ / $groupSize) }

The $counter variable is a hash table, initialized to zero.
The $groupsize variable is the size of the individual group that can be sent. In our example it is set to 100, for a maximum of 100 addresses to be sent at a time.
The $groups variable creates a custom object, with the following members:

PS>$groups | gm
   TypeName: Microsoft.PowerShell.Commands.GroupInfo

Name        MemberType Definition                                                      
----        ---------- ----------                                                      
Equals      Method     bool Equals(System.Object obj)                                  
GetHashCode Method     int GetHashCode()                                               
GetType     Method     type GetType()                                                  
ToString    Method     string ToString()                                               
Count       Property   int Count {get;}                                                
Group       Property   System.Collections.ObjectModel.Collection[psobject] Group {get;}
Name        Property   string Name {get;}                                              
Values      Property   System.Collections.ArrayList Values {get;}                      

If you print out the contents of $groups, you see the following list. (I’ve truncated for readability…)

PS>$groups
Count Name     Group                                                                                                              
----- ----     -----                                                                                                              
  100 0        {@{Addressee=Kamal Aboul-Hosn; Organization=; ...
  100 1        {@{Addressee=Chandler Dawson; Organization=; ...   
  100 2        {@{Addressee=Sidsel Heney; Organization=; ...
  100 3        {@{Addressee=John Marchetti; Organization=; ...
  100 4        {@{Addressee=Jane Ramsey; Organization=; ...
   59 5        {@{Addressee=James Tulloh; Organization=; ... 

This shows that I have 559 names in the original file which has been divided up into 5 groups of 100 and one of 59 names.

The next and final step is to iterate through each group and make the API call.

 
foreach ($group in $groups)
{
    $littleList = $group.Group | ConvertTo-Json
  
$Output = Invoke-RestMethod -Uri $Uri -Body $littlelist -ContentType application/json -Method Post 
}

The steps are:
For each group
Convert the addresses in one group to JSON
Assign it to the variable $littlelist
Send the contents of $littlelist as the body of the API call.
End Loop.

Debugging in PowerShell

The PowerShell integrated scripting environment (ISE) has many of the trappings of a full-fledged programming GUI, including a capability for debugging with breakpoints. There is a TechNet article on debugging from which this discussion is cribbed.  The ISE has a subset of the command-driven breakpoint capability, in that it only allows the setting of line breakpoints. In the command environment, you can also set variable breakpoints, which execute when the value of a variable changes, and command breakpoints, which execute when a certain command is reached.

Breakpoints can only be set for a script that has been saved.

In the ISE window a breakpoint can be set from the debug menu, (Toggle Breakpoint)  or by pressing F9 when the cursor is on the line that you want to use for the breakpoint.  Once set, the line will be highlighted.

After setting a least one breakpoint, you can run the single-stepper. This executes the script one line at time.

Step Into: Executes the current statement and stop at the next statement. If the statement is a function or script, it goes into the function or script and then stops.  F11

Step Over: Execute the current statement and stop at the next statement.  If the statement calls a function or script, it executes the function or script and then returns to the next statement in the original script. F10

Step Out: Executes the current function and then returns to the level above in the call stack. If there are statements remaining in the sub-function, those are executed before the return. Essentially this is something like “finish running this function, and return…”

Continue: Execute to the next breakpoint without single-stepping.

What I’ve been doing as I’ve been learning PowerShell is single stepping through a script, which allows me to look at the effect of a single statement before moving on to the next statement.  This involves setting a breakpoint a the top of the script, and then hitting F11 to toggle through the script.

Calling sub-scripts.

Scripts can be called from other scripts using the Invoke-Expression commandlet.  Example:

Invoke-Expression -Command ./PSFTPDEMO.ps1

If the subscript is located in the current working directory, or within the same directory as the main script, it needs to be prefaced with the ./ path as shown above.

The subscript inherits all variables from the main script unless those variables are declared private in the main script.

PowerShell: Formatting Dates

In Brightpearl, if you want to select orders from a specific date, the date needs to be entered in the format of YYYY-MM-DD. By default PowerShell returns dates in a format based on your “culture” setting; in the U.S. this means the default is MM-DD-YYYY. (Who made this up by the way?) In fact…a standard call to the Get-Date returns a “long date / time” string.

PS>Get-Date
Thursday, November 20, 2014 12:46:19 PM

To just get the date, you add a couple parameters:

PS>Get-Date -DisplayHint Date -Format d
11/20/2014

This returns the system date in the default culture format.

To transform this to YY-MM-DD, there is a parameter which takes a Unix format string.

PS>Get-Date -UFormat %Y-%m-%d
2014-11-20

TechNet has a reference for all of the possible combinations and strings for the UFormat parameter.

Here’s the format for an order search using a hard coded date.
PS>$BPOrders=Invoke-RestMethod `
-Uri http://ws-use.brightpearl.com/public-api/nationalgardening/order-service/order-search?placedOn=2014-11-20 `
-Headers $headers `
-Method Get

We can put the date in a variable, and use that in the API call:

$Today=Get-Date -UFormat %Y-%m-%d

$BPOrders=Invoke-RestMethod `
-Uri http://ws-use.brightpearl.com/public-api/nationalgardening/order-service/order-search?placedOn=$Today `
-Headers $headers `
-Method Get

Odds and Sods

FileMaker 13 is back at Tech Soup. A one-year subscription to FileMaker Server is $649. A full license for FileMaker Pro (the desktop client) is $194. Unfortunately, they don’t offer non-profit pricing for FileMaker Pro Advanced at TechSoup, but you can inquire directly at FileMaker, where there are frequent deals. During November they are offering a 2 for 1 deal for FM Pro and FM Advanced when you buy direct.

PITA of the week: The new OSX Yosemite transmits search data by default to Apple, Microsoft, and god-knows-where. This is a reversal from previous versions of OSX. There is a fix.

Powershell is turning out to be pretty amazing. There is an entertaining introductory video series from Microsoft Virtual Academy which includes Jeffery Snover, the original PowerShell author who explains why they do things the way they do.  

Ozzie Zehner is a green-technology skeptic, in the sense that he suggests that our infatuation with alternative energy like photovoltaics and windmills really perpetuates the energy status quo. His top suggestions for committed environmentalists; empower women and girls.

Green Illusions pioneers a critique of alternative energy from an environmental perspective, arguing concerned citizens should instead focus on walkable communities, improved consumption, governance, and most notably, women’s rights.

PowerShell: Moved to new blog

PowerShell was clogging up Tech For Non-Profits. For the past several weeks I wrote a bunch of posts about using Windows PowerShell to access web APIs.  This has escalated to the extent that I thought it would worth an entire blog, so I’ve moved these posts to the brand-new shiny PowerShell Notebook. where I’m groping around for PowerShell mastery.  So far I have code to show how to access the following APIs:

Brightpearl
SmartyStreets
Bit.ly
Goo.gl

There are also entries about scripting FTP commands, and some general discussion of text processing. 
Take a look at PoweShellnotebook.com

Powershell: Use the SmartyStreets API

SmartyStreets is an address validator for U.S. postal addresses.  Feed SmartyStreets an address, like “11 Church Street, Burlington VT” and, if the address is matchable with the official U.S. postal service address, it will be returned, including the 9 digit zip code.  The SmartyStreets API has some of the best API documentation. Here is the PowerShell  code to validate a single address.

<#Powershell Code to query SmartyStreets API 
Provide address validation for a single U.S. address submitted to the API
LK 11/12/2014 
#>

$Output=""

$Uri="https://api.smartystreets.com/street-address?"+
"street=11+Church+Street&"+
"city=Burlington&"+
"state=VT&"+
"auth-id=myauthid"+
"auth-token=myauth-token"

$Output=Invoke-RestMethod -Uri $Uri -ContentType application/json -Method Get 

$Output.delivery_line_1
$Output.last_line
$Output.metadata

Athe auth-id and auth-token are values that you obtain from the Smartystreets site, which validate your account.

The result of the code is placed in the variable $Output.

Running this program provides the following output, the two validated address lines, and a slew of meta-data related to the address, including the county, gps coordinates, etc.

11 Church St
 Burlington VT 05401-4417
record_type : S
 zip_type : Standard
 county_fips : 50007
 county_name : Chittenden
 carrier_route : C009
 congressional_district : AL
 rdi : Commercial
 elot_sequence : 0196
 elot_sort : A
 latitude : 44.47953
 longitude : -73.21282
 precision : Zip9
 time_zone : Eastern
 utc_offset : -5
 dst : True

You can see the returned fields by piping $Output to Get-Member  The delivery_line_1, and last_line contain the validated address with nine-digit zip code.

PS >$Output | Get-Member
TypeName: System.Management.Automation.PSCustomObject
Name                   MemberType   Definition
 ----                   ----------   ----------
 Equals                 Method       bool Equals(System.Object obj)
 GetHashCode            Method       int GetHashCode()
 GetType                Method       type GetType()
 ToString               Method       string ToString()
 analysis               NoteProperty System.Management.Automation.PSCustomObject analysis=@{dpv_match_code=Y; dpv_footnotes=AA...
 candidate_index        NoteProperty System.Int32 candidate_index=0
 components             NoteProperty System.Management.Automation.PSCustomObject components=@{primary_number=11; street_name=C...
 delivery_line_1        NoteProperty System.String delivery_line_1=11 Church St
 delivery_point_barcode NoteProperty System.String delivery_point_barcode=054014417112
 input_index            NoteProperty System.Int32 input_index=0
 last_line              NoteProperty System.String last_line=Burlington VT 05401-4417
 metadata               NoteProperty System.Management.Automation.PSCustomObject metadata=@{record_type=S; zip_type=Standard; ...

Pass parameters to executables from the PS command line.

Working from within the PowerShell ise sometimes you still need to run an external editor to edit a text file. I use Notepad++ for this purpose and it is a pain to invoke it from within the command line and to pass the name of the file that I want to edit at the same time.  Here is how you can start NotePad++ by itself.

& "C:Program Files (x86)Notepad++notepad++.exe"

Note that the full command string is within double quotes, to accommodate the spaces in the folder names. Because the strings are quoted, we also need to to preface the string with the at “&” sign, which means “execute this….”

Now, typically I’ll have an existing text or html file that I already want to edit.  This can be appended to the command.

& "C:Program Files (x86)Notepad++notepad++.exe" winners.txt

Notepad++ will open multiple files in separate tabs that are passed to it when opening.

& "C:Program Files (x86)Notepad++notepad++.exe" winners.txt,loosers.txt

More details are in this article at Windows IT Pro.