|
|
| (112 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| == Useful Sites ==
| | #REDIRECT [[:Category:PowerShell]] |
| Subject specific useful links are listed in the sections below
| |
| * [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!
| |
| | |
| == 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...
| |
| * <code> [string]$result = $PingResult.Status </code>
| |
| | |
| {|cellpadding="1" cellspacing="0" border="1"
| |
| |- style="background-color:#bbddff;"
| |
| ! Notation !! Data Type
| |
| |-
| |
| |<code> [datetime] </code> || Date or time
| |
| |-
| |
| |<code> [string] </code> || String of characters
| |
| |-
| |
| |<code> [char] </code> || Single character
| |
| |-
| |
| |<code> [double] </code> || Double-precision floating number
| |
| |-
| |
| |<code> [single] </code> || Single-precision floating number
| |
| |-
| |
| |<code> [int] </code> || 32-bit integer
| |
| |-
| |
| |<code> [wmi] </code> || Windows Management Instrumentation (WMI) instance or collection
| |
| |-
| |
| |<code> [adsi] </code> || Active Directory Services object
| |
| |-
| |
| |<code> [wmiclass] </code> || WMI class
| |
| |-
| |
| |<code> [Boolean] </code> || 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
| |
| | |
| <pre>
| |
| 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
| |
| </pre>
| |
| | |
| | |
| == WMI ==
| |
| | |
| Cmdlet for using WMI via PowerShell is '''Get-WMIObject''', which has an alias of '''gwmi''', for example...
| |
| | |
| <pre>
| |
| 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
| |
| </pre>
| |
| | |
| | |
| === Find Classes and Properties ===
| |
| | |
| In order to find the correct class use...
| |
| <pre>
| |
| Get-WMIObject -list -credential $cred -computer 159.104.224.167 | Select-String -InputObject {$_.Name} Win32*
| |
| </pre>
| |
| | |
| 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)...
| |
| <pre>
| |
| Get-WMIObject Win32_BIOS | Format-List *
| |
| </pre>
| |
| | |
| [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. 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
| |
| <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 function seems to work perfectly<ref name=suite101.com>{{cite web |url=http://database-programming.suite101.com/article.cfm/connecting_to_mysql_from_powershell |title=Connecting to MySQL from PowerShell |publisher=Mark Alexander Bain |year=2009 |accessdate=2010-01-07}}</ref>. I suspect it could be simplified, but it does the job I want, returning a table of results.
| |
| <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]]
| |