Power Shell

From vwiki
Revision as of 14:38, 11 February 2011 by Sstrutt (talk | contribs) (→‎Useful Sites: Added info for Win2k8)
Jump to navigation Jump to search

Getting Started

Useful Sites

Subject specific useful links are listed in the sections below, the following provide links to installers and general documentation

Whilst Win2008 ships with Powershell it isn't neccessarily available, to install...

  1. Go to Server Manager
  2. Go into Features, then Add Features
  3. Tick Windows Powershell, and then Next

Normally a restart isn't required

Execution Policy

On the first run you need to allow Powershell to scripts (you need to run this command as an administrator, so if you're using Windows 7, for example, you'll need to start the Powershell console as an administrator, regardless of whether you're currently logged in as an admin). If you have no admin rights over the PC you're using, then you'll need to select the Suspend option rather than Yes (sets just for your current session, doesn't try to write to the registry). 

 Set-ExecutionPolicy RemoteSigned

It is possible to bypass the Execution Policy entirely (though you do so at your own risk, should only be used to run a script you trust where you haven't the time to fix the underlying problem, and should be reverted afterwards)... 

 Set-ExecutionPolicy Bypass

Installed Version

To check the main installed version use the following command... 

 get-host | select version

.

However, if you might have installed something other than the normal RTM or GA release version you'll need to the registry key HKLM\Software\Microsoft\PowerShell\1, which will have the following values of interest...

Value Data Meaning
Install 1 Installed (not version number)
PID 89383-100-0001260-04309 RTM (Release to Manufacturing)
PID 89393-100-0001260-00301 RC2 (Release Candidate 2)

For more info on release version acronyms, see Software Release Life Cycle

Help Commands

Get-Help <cmd>              # Provides help for CmdLets, use wildcards to broaden results.
<object> | Get-Member       # Provides information about an object
<variable>.gettype()        # Provides variable type info (string, array, etc)

Include Files

In order to include another Powershell script in a parent script, use a . and then the path to the file (there's a space between them), eg 

. .\lib\include_file.ps1

Useful One-Liners

Command Description
Get-Content <file-path> | Out-GridView Display (log)file in the nice Grid View window
(Get-Location -PSProvider FileSystem).ProviderPath Current working directory

Variables

All variable names are prefixed with a $, and are case insensitive (though there's no reason to not use CamelBack notation if that floats your boat).

Apart from a few reserved words and characters there's little restriction on what can be used, though note that this flexibility can cause the occasional issue, whereby PowerShell gets confused as to where a variable name finishes. Variable names can be enclosed in { } in order to delimit them, eg ${varname}.

Powershell is all about manipulating objects, and its variables are all essentially the same, not being specifically defined as an object, string, integer, etc. Which is normally useful, however sometimes you need to force a variable to contain a data type. Using a prefix of [type] achieves this... 

 [string]$result = $PingResult.Status

Data types

Notation Data Type
[bool] True / false
[single] Single-precision 32-bit floating point number
[double] Double-precision 64-bit floating point number
[byte] 8-bit unsigned character
[int] 32-bit integer
[long] 64-bit integer
[decimal] 128-bit decimal
[char] Single character
[string] String of characters
[datetime] Date and time
[timespan] Time
[xml] XML object
[array] Array
[wmi] Windows Management Instrumentation (WMI) instance or collection
[wmiclass] WMI class
[adsi] Active Directory Services object
[Boolean] True or False value

Variable Information

As variables tend to be black boxes that can contain anything or nothing, its often necessary to understand more about one...

Variable Type 

$var.GetType()
IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

Variable Properties and Methods 

$var | Get-Member

It's not uncommon for objects provided by API's to provide methods that are in fact objects themselves, you can end up with a lot of information being available once you're capable of drilling into them.

Scope

Variables only fully exist within the scope of the script or function within which they are defined. Within functions local copies of a variable are available, but manipulating them has no effect on the real/master variable in the main scope, to get around this you can use script to enforce using the variable that's in the main script's scope... 

function Local-Add($text) {
    $script:List += $text
}

function Global-Add($text) {
    $script:List += $text
}

$List = @() 
$List += "Text message ONE"
$List.Length                           # List contains one entry
Local-Add "Text message TWO"
$List.Length                           # List still contains one entry
Global-Add "Text message THREE"
$List.Length                           # List now contains two entries

Strings

Basic manipulation tasks can be carried out by using the string object's methods, eg "string".PadRight(10) , see Get-Member -InputObject "Text" for full details.

Concatenation + 

$strAB = $strA + $strB


Interpolation
Interpolation allows variables to be embedded into a string and to be resolved into their actual values. This works between double quotes, but not between single quotes... 

PS E:\> $sub = "replaced"
PS E:\> Write-Output "Variable has been $sub"
Variable has been replaced
PS E:\> Write-Output 'Variable has been $sub'
Variable has been $sub


Search
To search for specific text in a string... 

if (Select-String -InputObject $text -Pattern "StringToFind" -Quiet)
{
        # StringToFind found in $text
}


Match (basic)
To do a basic comparison... 

if ($res.CompareTo("Success")) {
    # Didn't match (CompareTo returns 1 if comparison fails !)
} else {
    # Did match
}


Match (extract)
To extract text that matches a regex... 

$res = [regex]::matches($line, "\d{4}-[A-Za-z]{3}-Week\d{1}.log")
if (-not $res.Count)
{
    # No matches found
} else {
    $res1 = $res.Item(1).Value      # 1st match to regex
}

See Regular Expressions for further info on regex stuff.


Replace
Basic find and replace can be done with the Replace CmdLet, eg to replace "\" with "\\" in the $query variable... 

 $query = $query.Replace("\", "\\")

For proper regular expressions support, use the following syntax 

 $query = [regex]::Replace($query, "search", "replace")


Strip Whitespace

 $string = $string.TrimEnd()

Escape Characters

Text Description
`0 Null
`a Bell/system beep
`b Backspace
`f Form feed
`n New line
`r Carriage return
`t Tab (horizontal)
`v Vertical tab
`' '
`" "

Arrays

$array = @()            # Create blank array
$array += 34            # Add value to end of array

To create an array (table) with column headings, initialise an array, then create a row variable with the column headings and add this row to the array. This is convenient when building a table of data within a loop eg 

$table = @()
foreach ($instance in $set) {
    $row = "" | Select Heading1, Heading2, Heading3
    $row.Heading1 = "something"
    $row.Heading2 = "like"
    $row.Heading3 = "this"
    $table += $row
}

Add rows to an array 

> $array = @()
> $row = "" | Select h1,h2,h3
> $row.h1 = "esx1"
> $row.h2 = "HBA1"
> $row.h3 = "LUN1"
> $array = $array + $row
> $row = "" | Select h1,h2,h3
> $row.h1 = "esx2"
> $row.h2 = "HBA1"
> $row.h3 = "LUN2"
> $array = $array + $row
> $array

h1                                      h2                                      h3
--                                      --                                      --
esx1                                    HBA1                                    LUN1
esx2                                    HBA1                                    LUN2

Select row from array
Using above array as example... 

> if (($array |?{$_.h1 -eq "esx2"})) {"true"} else {"false"}
true
> if (($array |?{$_.h1 -eq "esx3"})) {"true"} else {"false"}
false
> $array |?{$_.h1 -eq "esx2"}

h1                                      h2                                      h3
--                                      --                                      --
esx2                                    HBA1                                    LUN2


> $array |?{$_.h1 -eq "esx2"} | Select -ExpandProperty h2
HBA1

Hashtables

$hash = @{}                                          # Create blank array
$hash["Name"] = "Value"                              # Add value to end of array
$hash.GetEnumerator() | Sort-Object -Property Name   # Sort hashtable

Datetime

The object structure: http://msdn.microsoft.com/en-us/library/system.datetime.aspx

Formatting

To control how a DateTime is displayed you can pass it through Get-Date with the -uFormat option... 

Get-Date $datetime -uFormat "%R hrs, %a %d %b %Y"

Useful formatting examples...

Specifier Example
%R hrs, %a %d %b %Y 07:25 hrs, Fri 24 Dec 2010
%Y-%m-%d 2010-12-24

For the full list of formatting options see http://msdn.microsoft.com/en-us/library/system.globalization.datetimeformatinfo.aspx


Improper (US) Formatting

Be aware that if you live somewhere dates are normally formatted properly (eg not the USA), then Powershell (or the underlying DateTime object type) has a nasty habit of returning a string formatted with day and month swapped around when coming from a script.

If you do a Get-Date it all looks fine, but then you output a DateTime object in a script to some text and its wrong. Add the .ToString() method to the end at it'll sort itself, though quite why when Powershell is converting the object into a string anyway, the object needs to be explicitly told to fix the issue, seems a bit superfluous.

Converters

function ConvertLocalToUnix([datetime]$datetime)
{ 
    ($datetime.ToUniversalTime() - ([datetime]'1/1/1970 00:00:00')).TotalSeconds
}

function ConvertUnixtoLocal($sincepoch)
{
    [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($sincepoch))
}

Environment

Environmental variables can be accessed via $env 

$env:userprofile                  # User profile (eg C:\Users\joeblogs)
dir env:                          # Show all available variables

Macros / Built-in Variables

Variable Description
$_ Either
$? Success/failure of previous statement
$Error Last error - array of recent errors, see $error
$LastExitCode Exit code of the last natively run application
$foreach Enumerator in a foreach loop
$Host Information about the machine being executed on

Conditional Operators

Comparison

Operator Description
-eq Equal to (implied case insensitive)
-ieq Equal to (case insensitive)
-ceq Equal to (case sensitive)
-lt Less than
-gt Greater than
-ge Greater than or Eqaul to
-le Less than or equal to
-ne Not equal to
-match Match (ie string contains) anywhere within string (can be regex)
-notmatch Does not match (ie string contains) (can be regex)
-like Like (ie string is), stricter than match (can be regex)
-notlike Not like (ie string is not) (can be regex)

Logic

Operator Description
-not Not
 ! Not
-and And
-or Or

Credentials

Get-Credential

When running commands that require a connection to a remote machine its useful to be able to store a user/pass combination so that you aren't repeatedly prompted every time you run a command. Create a credential object, then supply that in place of a username in a command 

PS H:\> $cred = Get-Credential

cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
PS H:\> Get-WMIObject -query "SELECT * FROM Win32_OperatingSystem" -credential $cred -computer 159.104.224.167

SystemDirectory : C:\WINDOWS\system32
Organization    : TF
BuildNumber     : 3790
RegisteredUser  : TF
SerialNumber    : 69712-640-3560061-45009
Version         : 5.2.3790

However, this doesn't really help much in a fully scripted situation where you need to supply user and pass in an unattended fashion, for that you also need the help of ConvertTo-SecureString , but if you want to be secure you need to use Store Password Securely

ConvertTo-SecureString

The following example creates a Credential object that can be used for in place of Get_Credential 

$pass = ConvertTo-SecureString $svr.pass -asplaintext -force
$cred = New-Object -typename System.Management.Automation.PSCredential -argumentlist $svr.user,$pass
$wmiobj = Get-WMIObject -query "SELECT * FROM Win32_BIOS" -credential $cred -computer $svr.ip

Store Password Securely

Adapted (a little) from http://bsonposh.com/archives/338 by Brandon This is a two stage process, 1st you have to create a file with your (encrypted) password in (its encrypted by the currently logged in user - so if its going to be used in a scheduled task, make sure the user that will execute the script creates the password file). 

$Credential = Get-Credential
$credential.Password | ConvertFrom-SecureString | Set-Content "Pass.fil"

Then you can use this in a script, the $cred is a standard credential object. 

$pass = Get-Content "Cred.fil" | ConvertTo-SecureString 
$cred = New-Object System.Management.Automation.PsCredential("DOMAIN\user",$pass)

For a complete, but simple user/pass caching system use something like the following... 

# Check for credential files, create if required
if (!(Test-Path $UserFile) -or !(Test-Path $PassFile)) {
    Write-Host "Credential files not found"
    $cred = Get-Credential -Credential ($env:userdomain + "\" + $env:username)
    $cred.UserName | Set-Content $UserFile -Force
    $cred.Password | ConvertFrom-SecureString | Set-Content $PassFile -Force
    Write-Host "Credentials saved"
}
    
# Load password credential from encrypted file
$pass = Get-Content $PassFile | ConvertTo-SecureString
$user = Get-Content $UserFile
$cred = New-Object System.Management.Automation.PsCredential($user, $pass)

...obviously to make the above more useful you'd test that the user/pass combo supplied was correct prior to saving to file.

Logged-In User's Credentials

To use the credentials of the existing logged in session where a CmdLet requires that you specify them use the following... 

$cred = [System.Net.CredentialCache]::DefaultCredentials

Logging

The easiest way to setup logging from with a script is to use the Transcript functionality which will log all output to a transcript file. Note that Write-Host only places line feeds at the end of lines, not carriage returns, therefore Notepad will display such output as one long line. You'll need to use an editor that can handle lines only terminated by LF's (WordPad if you can't install anything, otherwise get something better, eg http://www.scintilla.org/SciTE.html). 

Start-Transcript -Path C:\Users\name\Scripts\script.log -Append -NoClobber
# script
Stop-Transcript

External Processes

One of PowerShell's greatest failings is its inability to run external commands with any predictability, severely limiting tits scope and often forcing you to run small PowerShell scripts from other scripts rather than having one all-in-control PS script. The following example demonstrates how to run any cmd line... 

$cmd = "rrdtool.exe update $rrd $updates"
$proc_res = &$executioncontext.InvokeCommand.NewScriptBlock($cmd)


WMI

Cmdlet for using WMI via PowerShell is Get-WMIObject, for example... 

PS H:\> Get-WMIObject -query "Select * from Win32_OperatingSystem"

SystemDirectory : C:\WINDOWS\system32
Organization    :
BuildNumber     : 2600
RegisteredUser  : TF
SerialNumber    : 76487-OEM-0011903-00102
Version         : 5.1.2600

Further useful examples... 

# Get OS CPU info (address width, speed, FSB etc
$cpu = Get-WMIObject -query "SELECT * FROM Win32_Processor WHERE DeviceID='CPU0'" -credential $cred -computer $svr

# Get local disks
$drives = Get-WMIObject Win32_LogicalDisk -filter "Description = 'Local Fixed Disk'" -credential $cred -computer $svr


Find Classes and Properties

In order to find the correct class use... 

Get-WMIObject -list -credential $cred -computer 159.104.224.167 | Select-String -InputObject {$_.Name} Win32*

To then see all the properties of a class use (if this doesn't work on remote machines (access denied) - it may be due to a known bug in Power Shell v1 whereby Get-WMIObject can't impersonate (or you may just have the wrong credentials)... 

Get-WMIObject Win32_BIOS | Format-List *

TechNet article: Windows PowerShell Best Inventory Tool Ever!

Network

Ping

PS H:\> $objPing = New-Object system.Net.NetworkInformation.Ping
PS H:\> $objPing.Send('127.0.0.1')

Status        : Success
Address       : 127.0.0.1
RoundtripTime : 0
Options       : System.Net.NetworkInformation.PingOptions
Buffer        : {97, 98, 99, 100...}

Name/Address Resolution

IP to Name

  • Be aware, where no name can be found, the call throws an exception. If assigning result to a variable, then it seems to return the local hostname, which is odd.
PS H:\> [System.Net.Dns]::GetHostbyAddress("159.104.31.83")

HostName                                Aliases                                 AddressList
--------                                -------                                 -----------
L-STRUTTS1                              {}                                      {159.104.31.83}

Name to IP 

PS H:\> [System.Net.Dns]::GetHostAddresses("l-strutts1")

Address           : 1394567327
AddressFamily     : InterNetwork
ScopeId           :
IsIPv6Multicast   : False
IsIPv6LinkLocal   : False
IsIPv6SiteLocal   : False
IPAddressToString : 159.104.31.83

WoL Script

This script is largely based on stuff from http://thepowershellguy.com/blogs/posh/archive/2007/04/01/powershell-wake-on-lan-script.aspx 

script-file version
param ([String]$macString = $(throw 'mac address is required'))
$mac = $macString.split(':') | %{ [byte]('0x' + $_) }

if ($mac.Length -ne 6)
{
   throw 'mac address must be 6 hex numbers separated by :'
}

$UDPclient = new-Object System.Net.Sockets.UdpClient
$UDPclient.Connect(([System.Net.IPAddress]::Broadcast),4000)
$packet = [byte[]](,0xFF * 6)
$packet += $mac * 16
write-debug ([bitconverter]::tostring($packet))

[void] $UDPclient.Send($packet, $packet.Length)
[void] $UDPclient.Close()
write-debug "Wake-On-Lan magic packet of length $($packet.Length) sent to $macString"

Excel

Basic example, converting an esxisting CSV file into a XLSX file... 

$INfile = "VMs.csv"
$OUTfile = "VMs.xlsx"

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True

$ExcelBook = $Excel.Workbooks.Add()

$Excel.Cells.Item(1,1) = "VC"
$Excel.Cells.Item(1,2) = "VM Name"
$Excel.Cells.Item(1,3) = "Datastore"

$CSVfile = Import-Csv -Path $INfile

$row = 2

foreach($line in $CSVfile) {
    $Excel.Cells.Item($row,1) = $line.VC
    $Excel.Cells.Item($row,2) = $line.Name
    $Excel.Cells.Item($row,3) = $line.Datastore
    $row += 1
    Write-Host "." -NoNewLine
}

$ExcelBook.SaveAs($OUTfile)
$Excel.Quit()
Remove-Variable -Name Excel
[gc]::Collect()
[gc]::WaitForPendingFinalizers()

Formatting

Example Description
$Excel.Rows.Item(1).Font.Bold = $True Make row 1 bold
$Excel.Rows.Item(1).WrapText = $True Make row 1 wrap text (may affect row height)
$Excel.Rows.Item(1).VerticalAlignment = -4108 Centre (vertically) text
$Excel.Rows.Item(1).HorizontalAlignment = -4108 Centre (horizontally) text
$Excel.Columns.Item(1).columnWidth = 12 Make column 1 have a width of 12
$Excel.Cells.EntireColumn.AutoFit() Auto-fit all columns
$Excel.Cells.EntireColumn.AutoFilter() Auto-filter all columns
$Excel.Columns.Borders.LineStyle = 1 Give all cells a simple border

To freeze panes... 

[void]$Excel.Cells.Item(2,3).Select()           # Select the appropriate cell to freeze around
$Excel.ActiveWindow.FreezePanes = $True         # Freeze

Conditional Formatting

http://robertrobelo.wordpress.com/2010/10/07/excels-conditional-formatting-and-powershell/#comments

FTP

Things to watch out for...

  • KeepAlive's - Its generally safer to disable keep alives, this causes the FTP session to be dropped after each request. This is less efficient, but leads to more reliable results. If requests don't get completed properly the .NET API gets into a stuck state whereby new FTP requests appear to time-out (though no request actually goes to the FTP server.
  • Inconsistent Results - Results from IIS and non-IIS FTP servers can look different, for example a directory listing on an IIS FTP server results in a basic/raw text result, where as from a non-IIS FTP server this results in HTML rendered text

Useful links

Directory Listing

$site = "ftp://ftp-srv/logfiles"
$user = "Anonymous"
$pass = "Hello"

Write-Host "Get FTP site dir listing..." -nonewline

# Do directory listing
$FTPreq = [System.Net.FtpWebRequest]::Create($site)
$FTPreq.Timeout = 30000                             # msec (default is infinite)
$FTPreq.ReadWriteTimeout = 10000                    # msec (default is 300,000 - 5 mins)
$FTPreq.KeepAlive = $false                          # (default is enabled)
$FTPreq.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
$FTPreq.Method = [System.Net.WebRequestMethods+FTP]::ListDirectory

try
{
    $FTPres = $FTPreq.GetResponse()
}
catch
{
   Write-Host "FAILED: $_"
   Exit
}

Write-Host $FTPres.StatusCode -nonewline
Write-Host $FTPres.StatusDescription 

$list = Receive-Stream $FTPres.GetResponseStream()
$FTPres.Close()

Get

$site = "ftp://ftp-srv/logfiles"
$file = "activity1.log"
$user = "Anonymous"
$pass = "Hello"

Write-Host "Download $file " -nonewline
    
$FTPreq = [System.Net.FtpWebRequest]::Create("$site\$file")
$FTPreq.Timeout = 15000                             # msec (defult is infinite)
$FTPreq.ReadWriteTimeout = 10000                    # msec (defult is 300,000 - 5 mins)
$FTPreq.KeepAlive = $false                          # (default is enabled)
$FTPreq.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
$FTPreq.UseBinary = $true 
$FTPreq.Method = [System.Net.WebRequestMethods+FTP]::DownloadFile
    
try
{
    $FTPres = $FTPreq.GetResponse()
}
catch
{
   Write-Host "FAILED: $_"
   Exit
}
$dest = "$DestDir\$file"
    
Write-Host $FTPres.StatusDescription "Write to $DestDir\$file"
$FTPstream = $FTPres.GetResponseStream()
try
{
    $dest = New-Object IO.FileStream ("$DestDir\$file",[IO.FileMode]::Create)
}
catch
    Write-Host "FAILED: $_"
    $FTPstream.Close()
    $FTPres.Close()
    Exit
}
        
[byte[]]$buffer = New-Object byte[] 1024
$read = 0
do
{
    $read=$FTPstream.Read($buffer,0,1024)
    $dest.Write($buffer,0,$read)
}
while ($read -ne 0)
{
    $dest.Close()
}
$FTPstream.Close()
$FTPres.Close()

MySQL

Connect

Make sure you have the .NET connector installed 1st - http://dev.mysql.com/downloads/connector/net/ 

function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { 
  # Load MySQL .NET Connector Objects 
  [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") 

  # Open Connection 
  $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" 
  $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
  $conn.Open() 
  return $conn 
} 

function DisconnectMySQL($conn) {
  $conn.Close()
}

# So, for example...

# Connection Variables 
$user = 'myuser' 
$pass = 'mypass' 
$database = 'mydatabase' 
$MySQLHost = 'database.server.com' 

# Connect to MySQL Database 
$conn = ConnectMySQL $user $pass $MySQLHost $database

Improved connect function with error catcher... 

function ConnectMySQL([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) { 
    # Load MySQL .NET Connector Objects 
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") 
 
    # Open Connection 
    $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" 
    try {
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
        $conn.Open()
    } catch [System.Management.Automation.PSArgumentException] {
        Log "Unable to connect to MySQL server, do you have the MySQL connector installed..?"
        Log $_
        Exit
    } catch {
        Log "Unable to connect to MySQL server..."
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        exit
    }
    Log "Connected to MySQL database $MySQLHost\$database"

    return $conn 
}

Commands

All database operations are done through methods of the MySqlCommand object, the two methods of main interest are...

  • ExecuteNonQuery - Used for queries that don't return any real information, such as an INSERT, UPDATE, or DELETE.
  • ExecuteReader - Used for normal queries that return multiple values. Results need to be received into MySqlDataReader object.
  • ExecuteScalar - Used for normal queries that return a single. The result needs to be received into a variable.

Non-Query 

function ExecuteMySQLNonQuery($conn, [string]$query) { 
  $command = $conn.CreateCommand()                  # Create command object
  $command.CommandText = $query                     # Load query into object
  $RowsInserted = $command.ExecuteNonQuery()        # Execute command
  $command.Dispose()                                # Dispose of command object
  if ($RowsInserted) { 
    return $RowInserted 
  } else { 
    return $false 
  } 
} 

# So, to insert records into a table 
$query = "INSERT INTO test (id, name, age) VALUES (1, 'Joe', 33)" 
$Rows = ExecuteMySQLNonQuery $conn $query 
Write-Host $Rows " inserted into database"

Reader Query In theory, this should work, but it doesn't seem to for me. There's something wrong with the while ($results.Read()), in that you end up displaying the last row returned by the SQL query multiple times. Suspect its due to the way that a Reader object only seems to hold a result temporarily. 

$query = "SELECT * FROM subnets;"
$cmd = $connMySQL.CreateCommand() 
$cmd.CommandText = $query 
$results = $cmd.ExecuteReader() 
$cmd.Dispose() 
while ($results.Read()) {
  for ($i= 0; $i -lt $reader.FieldCount; $i++) {
      write-output $reader.GetValue($i).ToString()
  }
}

Instead, this approach seems to work more reliably. By loading the data into a dataset, it becomes available for offline manipulation and isn't reliant on the database once the data is loaded in. 

function ExecuteMySQLQuery([string]$query) { 
  # NonQuery - Insert/Update/Delete query where no return data is required
  $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connMySQL)    # Create SQL command
  $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)      # Create data adapter from query command
  $dataSet = New-Object System.Data.DataSet                                    # Create dataset
  $dataAdapter.Fill($dataSet, "data")                                          # Fill dataset from data adapter, with name "data"              
  $cmd.Dispose()
  return $dataSet.Tables["data"]                                               # Returns an array of results
}

# So, to produce a table of results from a query...
$query = "SELECT * FROM subnets;"
$result = ExecuteMySQLQuery $query
Write-Host "Found" ($result.Length) "rows..."
$result | Format-Table

Scalar Query 

function ExecuteMySQLScalar([string]$query) {
    # Scalar - Select etc query where a single value of return data is expected
    $cmd = $SQLconn.CreateCommand()                                             # Create command object
    $cmd.CommandText = $query                                                   # Load query into object
    $cmd.ExecuteScalar()                                                        # Execute command
}

Other 

$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)

SMTP / Email Sending

http://msdn.microsoft.com/en-us/library/system.net.mail.smtpclient.aspx#

Emails can be send very simply if no attachment is needed.... 

$smtp = New-Object Net.Mail.SmtpClient -arg $smtpServer 
$smtp.Send($emailFrom,$emailRcpt,$emailSubject,$msgBody)
  • $emailRcpt - Multiple email addresses need to be comma seperated

With Attachments

$smtp = New-Object Net.Mail.SmtpClient -arg $smtpServer 
$msg = New-Object Net.Mail.MailMessage
$attach = New-Object Net.Mail.Attachment($OutputFile)          # See note below
    
$msg.From = $emailFrom
$msg.To.Add($emailRcpt)
$msg.Subject = $emailSubject
$msg.Body = $msgBody
$msg.Attachments.Add($attach)
    
$smtp.Send($msg)
$attach.Dispose()
  • $OutputFile - Will normally need to be a full path as the script needn't be executing where your script is, assuming attachment is in same directory as script use the following...
    • ((Get-Location -PSProvider FileSystem).ProviderPath) + "\" + $OutputFile

With SMTP Authentication

As above, but additionally create a credential object and link it to the SMTP Client object, so... 

$cred = new-object System.net.networkCredential
$cred.domain = "the domain you want"
$cred.userName = "username"
$cred.password = "password"
$smtp.credentials = $cred

With Embedded HTML

As above, but you need to set the IsBodyHTML option for the message, so... 

$msg.IsBodyHTML = $true

Exceptions and Error Handling

To control how a script behaves as a result of an exception, modify the $ErrorActionPreference variable, if required. In the default continue mode, an error will output to the screen, then the script will continue. By outputting the error to the screen PowerShell CmdLets consider the exception to have been handled! If you want the opportunity to catch or trap you must append -ErrorAction:Stop to the CmdLet you expect might fail.

Value Effect
Continue [Default] Outputs error, but keeps processing
SilentlyContinue No output and it keeps going
Inquire Prompt user for action
Stop Outputs error and halts processing

$error

Provides a list of recent errors experienced - which can be invaluable for properly identifying and investigating errors. The object is the same as found in a pipeline when an exception has occurred.

Property Description Example (VC login error)
$error[1].Exception.GetType().FullName Error class for specific error [VMware.VimAutomation.ViCore.Types.V1.ErrorHandling.InvalidLogin]
$error[1].Exception.Message User friendly error message 16/11/2010 10:32:52 Connect-VIServer Login failed due to a bad username or password.
$error[1].ErrorDetails.Message Shorter friendly error message Login failed due to a bad username or password
$error[1].CategoryInfo.Reason Short error message InvalidLogin
$error[1].InvocationInfo Invocation info (what triggered exception) Various info including script command

Basic Error Handler

If you know where the error is likely to occur, then just place an error catcher immediately after it. This doesn't stop the exception appearing on the console, but does allow you to take some action as a result. 

if (-not $?) {
    # Handle error here
  }

Try...Catch

Used to catch an exception in a script block where an exception may be likely. Stops the exception being shown on the console and gives you a chance to do something about it (as long as you've set $ErrorActionPreference to Stop or used -ErrorAction:Stop). 

try
{
    # Something in which an exception is likely
}
catch
{
    Write-Host "FAILED: $_"
    Exit
}

The error returned by the CmdLet can be found in $_ , so this can be tested to ensure the error is as expected (just because you though a command might fail, doesn't mean it failed in the way you expected). For example, $_ will contain the bold bit of the following error. 

Get-Log : Cannot validate argument on parameter 'StartLineNum'. The 0 argument is less than the minimum allowed range of 1. Supply an argument that is greater than 1 and then try the command again.
At C:\Users\simonstrutt\Documents\Scripts\ESX-LogTail.ps1:20 char:57
+     $ESXLog = Get-Log $logKey -VMHost $ESX -StartLineNum <<<<  $LineNo
    + CategoryInfo          : InvalidData: (:) [Get-Log], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,VMware.VimAutomation.Commands.GetLog

This can be elaborated on to make the catch handling more specific, by making the catch block executed depend on the error class. The error class can be determined by making causing the error to be thrown, in which case the class can be found at $_.Exception.GetType().FullName . 

try {
   $proxy = New-WebServiceProxy -uri $endpoint -cred $credential -ErrorAction:Stop
} catch [System.Net.WebException] {
    Write-Host "ERROR: Unable to connect to SOAP server"
    Write-Host $_
} catch {
    Write-Host "ERROR: Unexpected error"
    Write-Host $_
    Write-Host $_.Exception.Message
    Write-Host $_.Exception.GetType().FullName
}

Trap

Used to capture any unhanded exception that occurs anywhere. I tend to consider this a last resort catch-all, though really it depends on the nature of your script. For example if your script is reliant on a connection to a server that can go down, you can design a trap to recover from that specific occurrence rather than having to put a Try...Catch around every operation that could fail.

The key to an effective trap is allowing for the fact that anything might go wrong, therefore you have to set your traps up to handle only specific cases and in all likelihood stop on anything else. 

trap {
    # Handle the exception
    Continue
}
Retrieved from ‘http://vwiki.co.uk/index.php?title=Power_Shell&oldid=1175’