PowerShell Examples

From vwiki
Revision as of 10:09, 18 February 2011 by Sstrutt (talk | contribs) (Initial creation)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Excel

Basic example, converting an esxisting CSV file into a XLSX file... 

$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()

Formatting

Example Description
$Excel.Rows.Item(1).Font.Bold = $True Make row 1 bold
$Excel.Rows.Item(1).WrapText = $True Make row 1 wrap text (may affect row height)
$Excel.Rows.Item(1).VerticalAlignment = -4108 Centre (vertically) text
$Excel.Rows.Item(1).HorizontalAlignment = -4108 Centre (horizontally) text
$Excel.Columns.Item(1).columnWidth = 12 Make column 1 have a width of 12
$Excel.Cells.EntireColumn.AutoFit() Auto-fit all columns
$Excel.Cells.EntireColumn.AutoFilter() Auto-filter all columns
$Excel.Columns.Borders.LineStyle = 1 Give all cells a simple border

To freeze panes... 

[void]$Excel.Cells.Item(2,3).Select()           # Select the appropriate cell to freeze around
$Excel.ActiveWindow.FreezePanes = $True         # Freeze

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

Directory Listing

$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()

Get

$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()


MySQL

Connect

Make sure you have the .NET connector installed 1st - http://dev.mysql.com/downloads/connector/net/ 

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

Improved connect function with error catcher... 

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 
}

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

Reader Query In theory, this should work, but it doesn't seem to for me. There's something wrong with the while ($results.Read()), 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. 

$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()
  }
}

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. 

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

Scalar Query 

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
}

Other 

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


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

$smtp = New-Object Net.Mail.SmtpClient -arg $smtpServer 
$smtp.Send($emailFrom,$emailRcpt,$emailSubject,$msgBody)
  • $emailRcpt - Multiple email addresses need to be comma seperated

With Attachments

$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()
  • $OutputFile - 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...
    • ((Get-Location -PSProvider FileSystem).ProviderPath) + "\" + $OutputFile

With SMTP Authentication

As above, but additionally create a credential object and link it to the SMTP Client object, so... 

$cred = new-object System.net.networkCredential
$cred.domain = "the domain you want"
$cred.userName = "username"
$cred.password = "password"
$smtp.credentials = $cred

With Embedded HTML

As above, but you need to set the IsBodyHTML option for the message, so... 

$msg.IsBodyHTML = $true
Retrieved from ‘http://vwiki.co.uk/index.php?title=PowerShell_Examples&oldid=1182’