PowerShell Examples - Revision history

Sstrutt: Added Depreciated template

2012-04-18T13:29:48Z

Added Depreciated template

@@ Line 1: / Line 1: @@
 == Excel ==
 Basic example, converting an esxisting CSV file into a XLSX file...
@@ Line 508: / Line 510: @@
 $SshClient.Dispose()
 </source>

Sstrutt: Update table style

2012-03-03T22:45:22Z

Update table style

← Older revision		Revision as of 22:45, 3 March 2012
Line 34:		Line 34:

	=== Formatting ===		=== Formatting ===
	{\|~~cellpadding~~="~~1" cellspacing="0" border="1~~"		{\|class="vwikitable"
	\|- ~~style="background-color:#bbddff;"~~		\|-
	! Example !! Description		! Example !! Description
	\|-		\|-

Sstrutt: /* MySQL */ Added "Test for NULL"

2012-01-06T14:07:12Z

MySQL: Added "Test for NULL"

@@ Line 301: / Line 301: @@
 $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
 </source>
 == Scheduling ==

Sstrutt: Added "SSH Client"

2011-11-10T09:59:28Z

Added "SSH Client"

Show changes

Sstrutt: /* Scheduling */ Fix typo

2011-02-18T10:18:04Z

Scheduling: Fix typo

← Older revision		Revision as of 10:18, 18 February 2011
Line 353:		Line 353:
	== Scheduling ==		== Scheduling ==
	Script to run a function at a specific interval throughout the day		Script to run a function at a specific interval throughout the day
	<source lang=powershell">		<source lang="powershell">
	$start = Get-Date		$start = Get-Date

Sstrutt: Added "Scheduling" script

2011-02-18T10:17:38Z

Added "Scheduling" script

@@ Line 349: / Line 349: @@
 <source lang="powershell">
 $msg.IsBodyHTML = $true
 </source>
 [[Category:PowerShell]]
 [[Category:MySQL]]

Sstrutt: Initial creation

2011-02-18T10:09:56Z

Initial creation

New page

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

[[Category:PowerShell]]
[[Category:MySQL]]