Difference between revisions of "Power Shell"

From vwiki
Jump to navigation Jump to search
m (Minor re-org)
(→‎MySQL: Reworked)
Line 165: Line 165:


== MySQL ==
== MySQL ==
=== Connect ===
<pre>
<pre>
function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {  
function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {  
   # Load MySQL .NET Connector Objects  
   # Load MySQL .NET Connector Objects  
   [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")  
   [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")  
Line 175: Line 176:
   $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)  
   $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)  
   $conn.Open()  
   $conn.Open()  
  $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
   return $conn  
   return $conn  
}  
}  


function WriteMySQLQuery($conn, [string]$query) {  
function DisconnectMySQL($conn) {
   $command = $conn.CreateCommand()  
   $conn.Close()
  $command.CommandText = $query
}
  $RowsInserted = $command.ExecuteNonQuery()
 
  $command.Dispose()
# So, for example...
  if ($RowsInserted) {
    return $RowInserted
  } else {
    return $false
  }
}


# setup vars
# Connection Variables
$user = 'myuser'  
$user = 'myuser'  
$pass = 'mypass'  
$pass = 'mypass'  
Line 200: Line 194:
$conn = ConnectMySQL $user $pass $MySQLHost $database  
$conn = ConnectMySQL $user $pass $MySQLHost $database  


# Read all the records from table  
</pre>
$query = 'INSERT INTO test (id,name,age) VALUES ("1","Joe","33")'
 
=== 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 = WriteMySQLQuery $conn $query  
$Rows = WriteMySQLQuery $conn $query  
Write-Host $Rows " inserted into database"  
Write-Host $Rows " inserted into database"  
</pre>
</pre>


'''Reader Query'''
'''Scalar Query'''
'''Other'''
<pre>
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
</pre>


== Exceptions and Error Handling ==
== Exceptions and Error Handling ==

Revision as of 14:47, 7 January 2010

Useful Sites

Subject specific useful links are listed in the sections below

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

  • [string]$result = $PingResult.Status
Notation Data Type
[datetime] Date or time
[string] String of characters
[char] Single character
[double] Double-precision floating number
[single] Single-precision floating number
[int] 32-bit integer
[wmi] Windows Management Instrumentation (WMI) instance or collection
[adsi] Active Directory Services object
[wmiclass] WMI class
[Boolean] True or False value


Credentials

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


WMI

Cmdlet for using WMI via PowerShell is Get-WMIObject, which has an alias of gwmi, 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

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


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 (doesn't work on remote machines (access denied) - this is a known bug in Power Shell v1)... 

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

MySQL

Connect

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

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 = WriteMySQLQuery $conn $query 
Write-Host $Rows " inserted into database"

Reader Query

Scalar Query

Other 

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

Exceptions and Error Handling

To control how a script behaves as a result of an exception, modify the $ErrorActionPreference variable, if required.

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

if (-not $?) {
    # Handle error here
  }
Retrieved from ‘https://vwiki.co.uk/index.php?title=Power_Shell&oldid=623’