Difference between revisions of "Power Shell"

Jump to navigation Jump to search

Power Shell (view source)

Revision as of 10:09, 18 February 2011

12,379 bytes removed ,  10:09, 18 February 2011
Offloaded stuff to "PowerShell Examples"
(→‎Useful Sites: Added "Check for publisher's certificate revocation" bit)
(Offloaded stuff to "PowerShell Examples")
Line 1: Line 1:
* See also [[CIM via PowerShell]]
See also...
* '''[[PowerShell Examples]]'''
* [[CIM via PowerShell]]


== Getting Started ==
== Getting Started ==
Line 593: Line 595:
</source>
</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>
=== Formatting ===
{|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
|-
| <code> $Excel.Columns.Borders.LineStyle = 1 </code>            || Give all cells a simple border
|}
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>
=== 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
* [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 ===
Make sure you have the .NET connector installed 1st - http://dev.mysql.com/downloads/connector/net/
<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>
Improved connect function with error catcher...
<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"
    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
}
</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'''
<source lang="powershell">
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
}
</source>
'''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>
* '''<code> $emailRcpt </code>''' - Multiple email addresses need to be comma seperated
=== 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>
=== With SMTP Authentication ===
As above, but additionally create a credential object and link it to the SMTP Client object, so...
<source lang="powershell">
$cred = new-object System.net.networkCredential
$cred.domain = "the domain you want"
$cred.userName = "username"
$cred.password = "password"
$smtp.credentials = $cred
</source>
=== With Embedded HTML ===
As above, but you need to set the <code> IsBodyHTML </code> option for the message, so...
<source lang="powershell">
$msg.IsBodyHTML = $true
</source>


== Exceptions and Error Handling ==
== Exceptions and Error Handling ==
Line 1,035: Line 692:
[[Category:PowerShell]]
[[Category:PowerShell]]
[[Category:WMI]]
[[Category:WMI]]
[[Category:MySQL]]
Retrieved from ‘https://vwiki.co.uk/index.php?title=Special:MobileDiff/1181’

Navigation menu