Power Shell: Difference between revisions

From vwiki
Jump to navigation Jump to search
m (→‎Variables: Added to variable types)
m (→‎Variables: Added interpolation)
Line 1: Line 1:
== Useful Sites ==
== Variables ==
Subject specific useful links are listed in the sections below
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).
* [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!


== Installed Version ==
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>.
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]


== Variables ==
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...
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>  
<source lang="powershell"> [string]$result = $PingResult.Status </source>  


Line 63: Line 44:
|}
|}


== 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


=== Strings ===
'''Concatenation +'''
<source lang="powershell">
<source lang="powershell">
PS H:\> $cred = Get-Credential
$strAB = $strA + $strB
 
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>
</source>


=== ConvertTo-SecureString ===
'''Interpolation'''
* http://technet.microsoft.com/en-us/library/dd819512.aspx
* Converts encrypted standard strings to secure strings
 
== WMI ==
 
Cmdlet for using WMI via PowerShell is '''Get-WMIObject''', which has an alias of '''gwmi''', for example...


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">
<source lang="powershell">
PS H:\> Get-WMIObject -query "Select * from Win32_OperatingSystem"
PS E:\> $sub = "replaced"
 
PS E:\> Write-Output "Variable has been $sub"
SystemDirectory : C:\WINDOWS\system32
Variable has been replaced
Organization    :
PS E:\> Write-Output 'Variable has been $sub'
BuildNumber    : 2600
Variable has been $sub
RegisteredUser  : TF
SerialNumber    : 76487-OEM-0011903-00102
Version        : 5.1.2600
 
PS H:\> Get-WMIObject -query "SELECT * FROM Win32_UTCTime"
 
__GENUS          : 2
__CLASS          : Win32_UTCTime
__SUPERCLASS    : Win32_CurrentTime
__DYNASTY        : Win32_CurrentTime
__RELPATH        : Win32_UTCTime=@
__PROPERTY_COUNT : 10
__DERIVATION    : {Win32_CurrentTime}
__SERVER        : L-0STRUTTS1
__NAMESPACE      : root\cimv2
__PATH          : \\L-0STRUTTS1\root\cimv2:Win32_UTCTime=@
Day              : 27
DayOfWeek        : 4
Hour            : 10
Milliseconds    :
Minute          : 0
Month            : 8
Quarter          : 3
Second          : 1
WeekInMonth      : 5
Year            : 2009
 
PS H:\> Get-WMIObject -query "Select * from Win32_OperatingSystem" -credential sysmgr -computer ukpgemon2
 
SystemDirectory : C:\WINDOWS\system32
Organization    : Thomson Financial
BuildNumber    : 3790
RegisteredUser  : Dell Image Rev 1
SerialNumber    : 69712-640-3560061-45321
Version        : 5.2.3790
 
PS H:\> Get-WMIObject -query "Select * from Win32_userAccount WHERE Domain = 'OCM-WCS1' AND Name = 'pptp_ypos'" -credential administrator -computer 159.104.224.167
AccountType : 512
Caption    : OCM-WCS1\pptp_ypos
Domain      : OCM-WCS1
SID        : S-1-5-21-2453442427-2400495818-1845097998-1073
FullName    : Ypos-Consulting GmbH
Name        : pptp_ypos
</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>
</source>
To then see all the properties of a class use (doesn't work on remote machines (access denied) - this is a known bug in Power Shell v1)...
<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>
== MySQL ==
=== Connect ===
<pre>
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
</pre>
=== 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'''
<pre>
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"
</pre>
'''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.
<pre>
$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()
  }
}
</pre>
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.
<pre>
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
  $recordCount = $dataAdapter.Fill($dataSet, "data")                          # Fill dataset from data adapter, with name "data"             
  $cmd.Dispose()
  return $dataSet.Tables["data"]
}
# So, to produce a table of results from a query...
$query = "SELECT * FROM subnets;"
ExecuteMySQLQuery $query | Format-Table
'''Scalar Query'''
'''Other'''
<pre>
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
</pre>
== 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 Catcher ===
If you know where the error is likely to occur, then just place an error catcher immediately after it...
<pre>
if (-not $?) {
    # Handle error here
  }
</pre>
[[Category:PowerShell]]
[[Category:WMI]]
[[Category:MySQL]]

Revision as of 12:22, 22 January 2010

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}</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... 

 [string]$result = $PingResult.Status
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 or 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


Strings

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
Retrieved from ‘http://vwiki.co.uk/index.php?title=Power_Shell&oldid=676’