Power Shell: Difference between revisions

From vwiki
Jump to navigation Jump to search
(→‎Formating: Added Auto-filter)
m (Typo fix)
 
(51 intermediate revisions by the same user not shown)
Line 1: Line 1:
* See also [[CIM via PowerShell]]
#REDIRECT [[:Category:PowerShell]]
 
== Getting Started ==
=== Useful Sites ===
Subject specific useful links are listed in the sections below, the following provide links to installers and general documentation
* [http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx Windows PowerShell V1]
* [http://support.microsoft.com/kb/968929 Windows PowerShell V2]
* http://powershell.com/cs/ - Good all-round help
* http://technet.microsoft.com/en-us/library/bb978526.aspx - TechNet!
 
=== 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).
<source lang="powershell"> Set-ExecutionPolicy RemoteSigned </source>
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)...
<source lang="powershell"> Set-ExecutionPolicy Bypass </source>
 
=== Installed Version ===
To check the main installed version use the following command...
<source lang="powershell"> get-host | select version </source>.
However, if you might have installed something other than the normal RTM or GA release version you'll need to the registry key <code>HKLM\Software\Microsoft\PowerShell\1</code>, which will have the following values of interest...
{|cellpadding="1" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
! Value                !! Data                                !! Meaning
|-
|<code> Install </code> || <code>1</code>                      || Installed (not version number)
|-
|<code> PID </code>    || <code>89383-100-0001260-04309</code> || RTM (Release to Manufacturing)
|-
|<code> PID </code>    || <code>89393-100-0001260-00301</code> || RC2 (Release Candidate 2)
|}
For more info on release version acronyms, see [http://en.wikipedia.org/wiki/Software_release_life_cycle Software Release Life Cycle]
 
=== Help Commands ===
<source lang="powershell">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)</source>
 
== Useful One-Liners ==
{|cellpadding="1" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
! Command              !! Description
|-
|<code> Get-Content <file-path> <nowiki>|</nowiki> Out-GridView </code> || Display (log)file in the nice Grid View window 
|-
|<code> (Get-Location -PSProvider FileSystem).ProviderPath </code>      || 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 <code> { } </code> in order to delimit them, eg <code>${varname}</scode>.
 
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...
<source lang="powershell"> [string]$result = $PingResult.Status </source>
 
{|cellpadding="1" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
! Notation                      !! Data Type
|-
| <code> [bool] </code>  || True / false
|-
| <code> [single] </code> || Single-precision 32-bit floating point number
|-
| <code> [double] </code> || Double-precision 64-bit floating point number
|-
| <code> [byte] </code> || 8-bit unsigned character
|-
| <code> [int] </code> || 32-bit integer
|-
| <code> [long] </code> || 64-bit integer
|-
| <code> [decimal] </code> || 128-bit decimal
|-
| <code> [char]  </code> || Single character
|-
| <code> [string] </code> || String of characters
|-
| <code> [datetime] </code>    || Date and time
|-
| <code> [timespan] </code>    || Time
|-
| <code> [xml] </code> || XML object
|-
| <code> [array] </code> || Array
|-
| <code> [wmi] </code> || Windows Management Instrumentation (WMI) instance or collection
|-
| <code> [wmiclass] </code> || WMI class
|-
| <code> [adsi] </code> || Active Directory Services object
|-
| <code> [Boolean] </code> || 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'''
<source lang="powershell">
$var.GetType()
IsPublic IsSerial Name                                    BaseType
-------- -------- ----                                    --------
True    True    Object[]                                System.Array
</source>
 
'''Variable Properties and Methods'''
<source lang="powershell">
Get-Member -InputObject $var
</source>
 
 
=== Strings ===
Basic manipulation tasks can be carried out by using the string object's methods, eg <code> "string".PadRight(10) </code>, see <code> Get-Member -InputObject "Text" </code> for full details.
 
'''Concatenation +'''
<source lang="powershell">
$strAB = $strA + $strB
</source>
 
<br>'''Interpolation''' <br>
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...
<source lang="powershell">
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
</source>
 
<br>'''Search''' <br>
To search for specific text in a string...
<source lang="powershell">
if (Select-String -InputObject $text -Pattern "StringToFind" -Quiet)
{
        # StringToFind found in $text
}
</source>
 
<br>'''Match (basic)'''<br>
To do a basic comparison...
<source lang="powershell">
if ($res.CompareTo("Success")) {
    # Didn't match (CompareTo returns 1 if comparison fails !)
} else {
    # Did match
}
</source>
 
<br>'''Match (extract)'''<br>
To extract text that matches a regex...
<source lang="powershell">
$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
}
</source>
See [[Regular Expressions]] for further info on regex stuff.
 
<br>'''Replace''' <br>
Basic find and replace can be done with the Replace CmdLet, eg to replace "\" with "\\" in the $query variable...
<source lang="powershell"> $query = $query.Replace("\", "\\") </source>
For proper regular expressions support, use the following syntax
<source lang="powershell"> $query = [regex]::Replace($query, "search", "replace") </source>
 
<br>'''Strip Whitespace'''<br>
<source lang="powershell"> $string = $string.TrimEnd() </source>
 
=== Escape Characters ===
{|cellpadding="1" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
!  Text              !! Description
|-
| <code> `0 </code>  || Null
|-
| <code> `a </code>  || Bell/system beep
|-
| <code> `b </code>  || Backspace
|-
| <code> `f </code>  || Form feed
|-
| <code> `n </code>  || New line
|-
| <code> `r </code>  || Carriage return
|-
| <code> `t </code>  || Tab (horizontal)
|-
| <code> `v </code>  || Vertical tab
|-
| <code> `' </code>  || '
|-
| <code> `" </code>  || "
|}
 
=== Arrays ===
<source lang="powershell">
$array = @()            # Create blank array
$array += 34            # Add value to end of array
</source>
 
=== Hashtables ===
<source lang="powershell">
$hash = @{}                                          # Create blank array
$hash["Name"] = "Value"                              # Add value to end of array
$hash.GetEnumerator() | Sort-Object -Property Name  # Sort hashtable
</source>
 
=== Datetime ===
The object structure: http://msdn.microsoft.com/en-us/library/system.datetime.aspx
 
<br>'''Converters'''<br>
<source lang="powershell">
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))
}
</source>
 
=== Environment ===
Environmental variables can be accessed via <code> $env </code>
 
<source lang="powershell">
$env:userprofile                  # User profile (eg C:\Users\joeblogs)
dir env:                          # Show all available variables
</source>
 
=== Macros / Built-in Variables ===
 
{|cellpadding="4" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
! Variable                !! Description
|-
| <code> $_ </code>      || Either
|-
| <code> $? </code>      || Success/failure of previous statement
|-
| <code> $Error </code>  || Last error
|-
| <code> $LastExitCode </code> || Exit code of the last natively run application
|-
| <code> $foreach </code> || Enumerator in a foreach loop
|-
| <code> $Host </code>    || Information about the machine being executed on
|}
 
== Conditional Operators ==
== Comparison ==
{|cellpadding="1" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
!  Operator          !! Description
|-
| <code> -eq </code>  || Equal to (implied case insensitive)
|-
| <code> -ieq </code> || Equal to (case insensitive)
|-
| <code> -ceq </code> || Equal to (case sensitive)
|-
| <code> -lt </code>  || Less than
|-
| <code> -gt </code>  || Greater than
|-
| <code> -ge </code>  || Greater than or Eqaul to
|-
| <code> -le </code>  || Less than or equal to
|-
| <code> -ne </code>  || Not equal to
|-
| <code> -match </code>  || Match (ie string contains) anywhere within string
|-
| <code> -notmatch </code>  || Does not match (ie string contains)
|-
| <code> -like </code>  || Like (ie string is), stricter than match
|-
| <code> -notlike </code>  || Not like (ie string is not)
|}
 
=== Logic ===
{|cellpadding="1" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
!  Operator            !! Description
|-
| <code> -not </code>  || Not
|-
| <code> ! </code>    || Not
|-
| <code> -and </code>  || And
|-
| <code> -or </code>  || 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
 
<source lang="powershell">
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
</source>
 
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 <code> ConvertTo-SecureString </code>, but if you want to be secure you need to use [[Power_Shell#Store Password Securely|Store Password Securely]]
 
=== ConvertTo-SecureString ===
* http://technet.microsoft.com/en-us/library/dd819512.aspx
* Converts encrypted standard strings to secure strings
The following example creates a Credential object that can be used for in place of <code> Get_Credential </code>
<source lang="powershell">
$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
</source>
 
=== 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).
<source lang="powershell">
$Credential = Get-Credential
$credential.Password | ConvertFrom-SecureString | Set-Content "Pass.fil"
</source>
 
Then you can use this in a script, the <code> $cred </code> is a standard credential object.
<source lang="powershell">
$pass = Get-Content "Cred.fil" | ConvertTo-SecureString
$cred = New-Object System.Management.Automation.PsCredential("MPADGLOBAL\simonstrutt",$pass)
</source>
 
== 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 <code> Write-Host </code> 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).
 
<source lang="powershell">
Start-Transcript -Path C:\Users\name\Scripts\script.log -Append -NoClobber
# script
Stop-Transcript
</source>
 
== 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...
<source lang="powershell">
$cmd = "rrdtool.exe update $rrd $updates"
$proc_res = &$executioncontext.InvokeCommand.NewScriptBlock($cmd)
</source>
 
 
== WMI ==
Cmdlet for using WMI via PowerShell is '''Get-WMIObject''', for example...
 
<source lang="powershell">
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
</source>
 
Further useful examples...
<source lang="powershell">
# 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
</source>
 
 
=== Find Classes and Properties ===
 
In order to find the correct class use...
<source lang="powershell">
Get-WMIObject -list -credential $cred -computer 159.104.224.167 | Select-String -InputObject {$_.Name} Win32*
</source>
 
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 <code>Get-WMIObject</code> can't impersonate (or you may just have the wrong credentials)...
<source lang="powershell">
Get-WMIObject Win32_BIOS | Format-List *
</source>
 
[http://technet.microsoft.com/en-gb/magazine/2009.02.windowspowershell.aspx TechNet article: Windows PowerShell Best Inventory Tool Ever!]
 
== Network ==
=== Ping ===
<pre>
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...}
</pre>
 
=== 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.
<pre>
PS H:\> [System.Net.Dns]::GetHostbyAddress("159.104.31.83")
 
HostName                                Aliases                                AddressList
--------                                -------                                -----------
L-STRUTTS1                              {}                                      {159.104.31.83}
</pre>
 
'''Name to IP'''
<pre>
PS H:\> [System.Net.Dns]::GetHostAddresses("l-strutts1")
 
Address          : 1394567327
AddressFamily    : InterNetwork
ScopeId          :
IsIPv6Multicast  : False
IsIPv6LinkLocal  : False
IsIPv6SiteLocal  : False
IPAddressToString : 159.104.31.83
</pre>
 
=== WoL Script ===
This script was sourced from http://thepowershellguy.com/blogs/posh/archive/2007/04/01/powershell-wake-on-lan-script.aspx
<source lang="powershell">
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)
write-debug "Wake-On-Lan magic packet of length $($packet.Length) sent to $macString"
</source>
 
== Excel ==
Basic example, converting an esxisting CSV file into a XLSX file...
<source lang="powershell">
$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()
</source>
 
=== Formating ===
{|cellpadding="1" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
! Example                                                        !! Description
|-
| <code> $Excel.Rows.Item(1).Font.Bold = $True </code>          || Make row 1 bold
|-
| <code> $Excel.Rows.Item(1).WrapText = $True </code>            || Make row 1 wrap text (may affect row height)
|-
| <code> $Excel.Rows.Item(1).VerticalAlignment = -4108  </code>  || Centre (vertically) text
|-
| <code> $Excel.Rows.Item(1).HorizontalAlignment = -4108 </code> || Centre (horizontally) text
|-
| <code> $Excel.Columns.Item(1).columnWidth = 12 </code>        || Make column 1 have a width of 12
|-
| <code> $Excel.Cells.EntireColumn.AutoFit() </code>            || Auto-fit all columns
|-
| <code> $Excel.Cells.EntireColumn.AutoFilter() </code>          || Auto-filter all columns
|}
 
 
To freeze panes...
<source lang="powershell">
[void]$Excel.Cells.Item(2,3).Select()          # Select the appropriate cell to freeze around
$Excel.ActiveWindow.FreezePanes = $True        # Freeze
</source>
 
== 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
* [http://msdn.microsoft.com/en-us/library/system.net.ftpwebrequest.aspx MSDN FtpWebRequest Class]
* Examples I've worked from
** http://rcovelo.blogspot.com/2008/10/powershell-very-simple-ftp-client.html
** http://stackoverflow.com/questions/265339/whats-the-best-way-to-automate-secure-ftp-in-powershell
** http://powershell.com/cs/media/p/804.aspx
 
=== Directory Listing ===
<source lang="powershell">
$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()
</source>
 
=== Get ===
<source lang="powershell">
$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()
</source>
 
== MySQL ==
=== Connect ===
<source lang="powershell">
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
</source>
 
=== Commands ===
All database operations are done through methods of the <code> MySqlCommand </code> 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 <code> MySqlDataReader </code> object.
* '''ExecuteScalar''' - Used for normal queries that return a single.  The result needs to be received into a variable.
 
'''Non-Query'''
<source lang="powershell">
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"
</source>
 
'''Reader Query'''
In theory, this should work, but it doesn't seem to for me.  There's something wrong with the <code>while ($results.Read())</code>, 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.
<source lang="powershell">
$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()
  }
}
</source>
 
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.
<source lang="powershell">
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
</source>
 
'''Scalar Query'''
 
'''Other'''
<source lang="powershell">
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
</source>
 
== 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....
 
<source lang="powershell">
$smtp = New-Object Net.Mail.SmtpClient -arg $smtpServer
$smtp.Send($emailFrom,$emailRcpt,$emailSubject,$msgBody)
</source>
 
=== With Attachments ===
<source lang="powershell">
$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()
</source>
 
* '''<code> $OutputFile </code>''' - 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...
** <code>((Get-Location -PSProvider FileSystem).ProviderPath) + "\" + $OutputFile </code>
 
== Exceptions and Error Handling ==
* http://huddledmasses.org/trap-exception-in-powershell/ - Exception trapping
* http://www.pluralsight.com/community/blogs/keith/archive/2007/01/22/45814.aspx - Error handling
 
To control how a script behaves as a result of an exception, modify the <code> $ErrorActionPreference </code> variable, if required.
 
{|cellpadding="1" cellspacing="0" border="1"
|- style="background-color:#bbddff;"
! 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
|}
 
 
=== 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.
<source lang="powershell">
if (-not $?) {
    # Handle error here
  }
</source>
 
=== 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
<source lang="powershell">
try
{
    # Something in which an exception is likely
}
catch
{
    Write-Host "FAILED: $_"
    Exit
}
</source>
 
The error returned by the CmdLet can be found in <code> $_ </code>, 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, <code> $_ </code> 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
 
[[Category:PowerShell]]
[[Category:WMI]]
[[Category:MySQL]]

Latest revision as of 15:37, 4 October 2016

Redirect to:

Retrieved from ‘http://vwiki.co.uk/index.php?title=Power_Shell&oldid=2647’