PowerShell Examples

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

• 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

$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

Scheduling

Script to run a function at a specific interval throughout the day

$start = Get-Date

# Scheduler stuff...
$DoSingleRun = 0            # Ignore scheduling and just run once
$IntervalMins = 15          # Should fit into an hour exactly
$End = "18:50"              # Time of day that script should cease (should be just after last required run time)
$CheckThrottle = 250        # Idle throttle / time check interval (msec)

# Include library files
. .\lib\Standard.ps1

# Scheduled functions =================================================================================

function Do-Stuff {
    # This is where the business of the script goes
}

function Log-Perf {
    # Keeps track of script resource usage
    Log ("Perf CPU(sec): " + [Math]::Round($proc.cpu, 0) + ", Paged Mem (MB): " + [Math]::Round(($proc.pm/1024), 0) + ", WrkSet Mem (MB): " +  Math]::Round(($proc.ws/1024), 0))
}

# MAIN SCRIPT ====================================================================================

# Initial prep -----------------------------------------------------------------------------------

if ($DoSingleRun) {
    Start-Transcript -Path check-test.log -Append
} else {
    Rename-Item -Path check.log -NewName check-1.log -Force
    Start-Transcript -Path check.log -Append
}
Log "Started script run at $start" 

# Get process for this script
$proc = [System.Diagnostics.Process]::GetCurrentProcess()

# Check scheduler variables
if ((60 % $IntervalMins) -ne 0) {
    Log "Interval error - $IntervalMins mins doesn't fit into an hour!"
    Exit
}

try {
    $EndTime = Get-Date $End
} catch {
    Log "Invalid end time: $End hrs"
    Log $_.Exception.GetType().FullName
    Log $_.Exception.Message
    Exit
}

# Set dummy last run time (aligned to nice start time), and next run time
$LastRunTime = Get-Date
$offset = ($IntervalMins + ($LastRunTime.Minute % $IntervalMins))
$LastRunTime = $LastRunTime.AddMinutes(-$offset)
$LastRunTime = $LastRunTime.AddSeconds(-$LastRunTime.Second)
$NextRunTime = $LastRunTime.AddMinutes($IntervalMins)

#Write-Host "Offset is       " $offset
Log ("Last runtime is " + $LastRunTime)
Log ("Next runtime is " + $NextRunTime)
Log ("Script cease at " + $EndTime)

# Do any once-off initialisation here, 
#  eg connect to a MySQL database, open an "I'm alive" TCP port, load credentials


# Main loop -------------------------------------------------------------------------------------

if ($DoSingleRun) {
    Log ("Doing single run...")
    Perform-ESX-Check
} else {
    While (1) {
        if ($NextRunTime -lt (Get-Date)) {
            Log-Perf
            Log ("Starting run at " + (Get-Date))
            Do-Stuff
            Log ("Completed run at " + (Get-Date))
            $LastRunTime = $LastRunTime.AddMinutes($IntervalMins)
            $NextRunTime = $LastRunTime.AddMinutes($IntervalMins)
            if ($NextRunTime -lt (Get-Date)) {
                Log "WARNING: Next run is going to be late!"
            }
        }
        if ($EndTime -lt (Get-Date)) {
            Log ("Script ending at " + (Get-Date))
            Break
        }
        Start-Sleep -Milliseconds $CheckThrottle
    }
}

# Do any final once-off completion stuff here
# - EG disconnect from database, send "I'm finished" email

Stop-Transcript