Friday, December 21, 2007

Send-SMTPmail update

Version 1.2 of my Send-SMTPmail cmdlet:

# PowerShell 1.0 script to send email through SMTP
# by Jakob Bindslet (jakob@bindslet.dk), 21. december 2007
# Current Script version 1.1
#
#NAME
#    Send-SMTPmail
#
#SYNOPSIS
#    Sends an email by use of an SMTP server
#
#SYNTAX
#    Send-SMTPmail -to -from -subject -body
#    [-smtpserver ] [-port ] [-attachment ] [-html]
#    [-cc ] [-bcc ] [-alert] [-timeout ]
#
#PARAMETERS
#    No specific order of the parameters are required:
#        -to
#        -from
#        -subject
#        -body
#    Optional arguments are:
#    -smtpserver
#        Address of the smtp server to be used. This is only optional if a
#        default    smtpserver has been specified
#    -port
#        port number of the smtp server, used only if it differs from the
#        standard port 25.
#    -attachment
#        Complete path to file to attach, ie. "c:\boot.ini"
#    -cc
#        Recipient(s) to be included as "carbon copy" on the email.
#    -bcc
#        Recipient(s) to be included as "blind carbon copy" on the email.
#    -timeout
#        Sets the timeout in miliseconds, for the send mail operation.
#        This includes the time it take to upload any attachments, so
#        be careful of setting this too low if using attached file.
#    -alert
#        The email will be treatet as an alert (the standard sharepoint
#        alert icon will be used in outlook).
#    -html
#        Indicates that the email will be in HTML format. The HTML
#        content itself should be specified in the -body parameter.
#
#NOTES
#
# -------------------------- EXAMPLE 1 --------------------------
# C:\PS>send-SMTPmail -to hans@contoso.msft -from theBoss@contoso.msft
#    -subject "Congratulations!" -smtpserver "smtp.contoso.msft"
#    -body "you'll get one extra week of vacation year"
#    -bcc "theMiddleBoss@contoso.msft"

function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) {
    if ($smtpserver -eq $null) {$smtpserver = "smtp.myserver.com"}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
    if ($attachment -ne $null) {
        $attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }
    $mailer.send($msg)
}

Thursday, December 20, 2007

A cmdlet-like email script

I'm currently working on a database monitoring, datacollection and automation project using PowerShell. In this project I often find m self in need of sending a status or warning email when some event occurs - For a while I've just used code much like what I posted here on my blog back in december 2006, but last night I spend a bit of time writing up a cmdlet like "Send-SMTPmail" function once and for all.
Please feel free to comment the script, its functionality and implementation.

# PowerShell 1.0 script to send email through SMTP
# by Jakob Bindslet (jakob@bindslet.dk), 20. december 2007
# Current Script version 1.0
#
#NAME
# Send-SMTPmail
#
#SYNOPSIS
# Sends an email by use of an SMTP server
#
#SYNTAX
# Send-SMTPmail -to -from -subject -body
# [-smtpserver ] [-port ] [-attachment ] [-html "yes"]
# [-cc ] [-bcc ] [-alert "yes"] [-timeout ]
#
#PARAMETERS
# No specific order of the parameters are required:
# -to
# -from
# -subject
# -body
# Optional arguments are:
# -smtpserver
# Address of the smtp server to be used. This is only optional if a
# default smtpserver has been specified
# -port
# port number of the smtp server, used only if it differs from the
# standard port 25.
# -attachment
# Complete path to file to attach, ie. "c:\boot.ini"
# -html "yes"
# Indicates that the email will be in HTML format. The HTML
# content itself should be specified in the -body parameter.
# -cc
# Recipient(s) to be included as "carbon copy" on the email.
# -bcc
# Recipient(s) to be included as "blind carbon copy" on the email.
# -alert "yes"
# The email will be treatet as an alert (the standard sharepoint
# alert icon will be used in outlook).
# -timeout
# Sets the timeout in miliseconds, for the send mail operation.
# This includes the time it take to upload any attachments, so
# be careful of setting this too low if using attached file.
#
#NOTES
#
# -------------------------- EXAMPLE 1 --------------------------
# C:\PS>send-SMTPmail -to hans@contoso.msft -from theBoss@contoso.msft
# -subject "Congratulations!" -smtpserver "smtp.contoso.msft"
# -body "You'll get one extra week of vacation year"
# -bcc "theMiddleBoss@contoso.msft"

function Send-SMTPmail() {
## Setting defaults
$to = $null; $from = $null; $from = $null; $subject = $null; $body = $null;
$attachment = $null; $html = $null; $cc = $null; $bcc = $null; $port = $null
$alert = $null; $timeout = $null

$smtpserver = "smtp.myserver.com"
$LastArg = $args.length -1
for ($i = 0; $i -le $LastArg; $i += 2) {
if ($args[$i].substring(0,1) -eq "-") {
Write-Host "param" $args[$i] "value" $args[$i+1] ##debug line, can be removed
switch ($args[$i]) {
-smtpserver {$smtpserver = $args[$i+1]}
-to {$to = $args[$i+1]}
-from {$from = $args[$i+1]}
-subject {$subject = $args[$i+1]}
-body {$body = $args[$i+1]}
-attachment {$attachment = $args[$i+1]}
-html {$html = "yes"}
-cc {$cc = $args[$i+1]}
-bcc {$bcc = $args[$i+1]}
-port {$port = $args[$i+1]}
-alert {$alert = "yes"}
-timeout {$timeout = $args[$i+1]}
}
}
}
$mailer = new-object Net.Mail.SMTPclient($smtpserver)
if ($port -ne $null) {$mailer.port = $port}
if ($timeout -ne $null) {$mailer.timeout = $timeout}
$msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
if ($html -eq "yes") {$msg.IsBodyHTML = $true}
if ($cc -ne $null) {$msg.cc.add($cc)}
if ($bcc -ne $null) {$msg.bcc.add($bcc)}
if ($alert -eq "yes") {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
if ($attachment -ne $null) {
$attachment = new-object Net.Mail.Attachment($attachment)
$msg.attachments.add($attachment)
}
$mailer.send($msg)
}

Thursday, December 13, 2007

Script to extract disk space usage through WMI

This script can be used to extract information regarding sapce usage (actually free disk space in percent) from one or more servers or cluster. Since the script uses Win32_Volume, it can also recognize mountpoints.

$outData = @("")
$server = $args[0]
$dataFromServer = Get-WmiObject Win32_Volume -ComputerName $server | Select-Object SystemName,Label,Name,DriveLetter,DriveType,Capacity,Freespace

foreach ($currline in $dataFromServer) {
    if ((-not $currline.name.StartsWith("\\")) -and ($currline.Drivetype -ne 5)) {
        [float]$tempfloat = ($currline.Freespace / 1000000) / ($currline.Capacity / 1000000)
        $temppercent = [math]::round(($tempfloat * 100),2)
        add-member -InputObject $currline -MemberType NoteProperty -name FreePercent -value "$temppercent %"
        $outData = $outData + $currline
    }
}

$outData | Select-Object SystemName,Label,Name,Capacity,FreePercent | sort-object -property FreePercent | format-table -autosize

Friday, November 16, 2007

PowerShell support in SQL Server 2008

Latest news from IT-Forum/TechEd 2007 Barcelona, is that the upcomming CTP 5 release of MS SQL Server 2008 won't come with integrated PowerShell - this feature won't be available until CTP 6 of SQL Server 2008 :-(

Tuesday, November 06, 2007

Powershell V2 Approaching

If you haven't noticed already, Jeffrey Snover has announced the immediate release of PowerShell V2 CTP.

The PowerShell V2 CTP is not for everyone. You should read this PowerShell Team blog entry ( http://blogs.msdn.com/powershell/archive/2007/11/02/ctp-watch-this-space.aspx ) to find out what it is and what it isn't and then make an informed decision before installing the CTP.

Wednesday, October 10, 2007

ClusterSize

I recently had to complete a survey of the actual clustersizes used on a bunch of SQL Servers. In order to obtain this information, I came up with the small script below.
The script will list all drives (including drives without driveletters assigned, only mountpoints):

As noted by a reader the WMI class "Win32_Volume", is only available on Windows 2003 Server & Windows 2008 Server.

$servers = "myServer1.domain.x", "myServer2.domain.y"
$accData = $null
foreach ($server in $servers) {
    $dataFromServer = Get-WmiObject Win32_Volume -ComputerName $server | Select-Object SystemName,Name,DriveLetter,DriveType,Blocksize,Filesystem
    $accData = $accData + $dataFromServer
}

$accData | ConvertTo-Html | Set-Content "VolumeList.html"

Thursday, August 30, 2007

Formatting through the -f parameter

Formatting in PowerShell is typically achieved through the use of .NET's format strings. You might have seen cryptic statements like:

"{0,-8:P1}" -f 1.75

The above format can be explained as:

"{argument index[,alignment][:formatString zeros]}"

Note that both the alignment and the formatString are optional.

argument index is used when formatting more than one argument.

##### Argument Index
"{0,8}{1,10}" -f "John", "Doe"        # " John Doe"
"{0,8}{1,10}" -f "Albert", "Smith"    # " Albert Smith"

alignment indicates the minimal length of the string.
Positive valus are right justified and padded with spaces on the left if the string size is shorter than the value.

Negative values are left justified and padded with spaces on the right if the string size is shorter than the value.

##### Alignment

"{0,10}" -f "orange"     # " orange"
"{0,-10}" -f "orange"    # "orange "

format string is used mainly for numeric formatting.

##### C - Currency, the number represents the number of trailing zeroes
"{0:C3}" -f 25        # "$25.000"
"{0:C3}" -f -25        # "($25.000)"

##### D - Decimal, the number represents the number of leading zeroes
"{0:D5}" -f 25        # "00025"
"{0:D5}" -f -25        # "-00025"

##### E - Exponential, the number represents the number of trailing zeroes
"{0:E5}" -f 25        # "2.50000E+E001"
"{0:E5}" -f -25        # "-2.50000E+E001"

##### N - Number, the number represents the number of trailing zeroes
"{0:N5}" -f 25        # "25.00000"
"{0:N5}" -f -25        # "-25.00000"

##### P - Percentage, the number represents the number of leading zeroes
"{0:P3}" -f 25        # "2,500.000%"
"{0:P3}" -f -25        # "-2,500.000%"

##### X - Hexadecimal, the number represents the number of leading zeroes
"{0:X5}" -f 25        # "00019"
"{0:X5}" -f -25        # "FFFFFFE7"


Here are a few examples. Try them yourself.

"{0,9:P2}{1,20}" -f 0.09,"Standard loan"
"{0,9:P2}{1,20}" -f 0.25,"High interest loan"
"{0,9:P2}{1,20}" -f 3.5,"Mafia loan"

"{0,10:N3}{1,10:N3}{2,10:N3}{3,10:N3}" -f 0.2, 0.3, 0.45, 0.91
"{0,10:N3}{1,10:N3}{2,10:N3}{3,10:N3}" -f 1.1, 3, 2.5, 91.1
"{0,10:N3}{1,10:N3}{2,10:N3}{3,10:N3}" -f 25, 300, 211.5, 450

Wednesday, August 29, 2007

Is my drive fragmented?

Marco Shaw commented on my post ragarding fragmentation of files. Unfortunately WMI doesn't expose the fragmentation of specific files (at least I can't find it). But Marco's approach is useful in many situations.

Here is a small example on how to retrieve and display the fragmentation staus from a series of servers:

$allservers = "server1.", "server2"

Write-Host -foregroundcolor Yellow "Server         Drive Label         ClusterSize AverageFragmentsPerFile"
Write-Host -foregroundcolor Yellow "-------------- ----- ------------ ----------- -----------------------"

foreach ($server in $allservers) {
    $allvolumes = Get-Wmiobject -class Win32_Volume -Computername $server
    foreach ($volume in $allvolumes) {
        if ($volume.drivetype -eq 3) {
            $output = "{0,-15} {1,-6} {2,-15} {3,8} {4,15}" -f $server, $volume.DriveLetter, $volume.Label, $volume.DefragAnalysis().DefragAnalysis.ClusterSize, $volume.DefragAnalysis().DefragAnalysis.AverageFragmentsPerFile
            Write-Host -foregroundcolor White $output
        }
    }
}

Wednesday, August 22, 2007

A few nice variables

Obtaining the name of the current script: $myInvocation.MyCommand.Name
Path to Powershell executable: $PSHome
Get environment variables: $env:username or $env:logonserver
List all available environment variables: dir env: (note that no $ is included)
Show last commandline entered: $line
Current Path: $pwd

Thursday, August 09, 2007

Reading directly from SQL Server

A small example of reading a value from an SQL Server. In this case the maximum memory allowed for the SQL Server Instance.

$adOpenStatic = 3

$adLockOptimistic = 3
$timeout = 3

function GetMaxMemory {
    ## returns single string
    ## returns -1 if memory is unlimited
    ## returns -2 if no connection to the desired DB can be obtained
    ## arg0 = datasource (eg. server1\instance01)
    $objConnection = New-Object -comobject ADODB.Connection
    $objRecordset = New-Object -comobject ADODB.Recordset
    $query = "SELECT value FROM [master].[dbo].[sysconfigures] WHERE config = 1544"
    $objConnection.Set_ConnectionString("Provider=SQLOLEDB; Data Source=" + $args[0] + "; Initial Catalog=master; Integrated Security=SSPI")
    $objConnection.Set_ConnectionTimeout($timeout)
    $objConnection.Open()
    if ($objConnection.State -eq "1") {
        $objRecordset.Open($query, $objConnection, $adOpenStatic, $adLockOptimistic)
            $objRecordset.MoveFirst()
            $temp0 = ($objRecordset.Fields.Item("value").Value)
            if ($temp0 -eq 2147483647) {
                $temp0 = -1
            } else {
                $temp0 = $temp0 * 1mb
            }
        [long]$temp1 = $temp0
        Write-Output $temp1
        $objRecordset.Close()
        $objConnection.Close()
    } else {
        Write-output -2
    }
}

Usage:

GetMaxMemory servername\isntancename

Thursday, January 25, 2007

Is my file fragmented?

Small function to determine if a file is fragmented - for instance to analyzed an .ldf/.mdf file.
The function below also demonstrates one way to invoke an external command/tool (in this case "contig.exe" from www.sysinternals.com).
The function requires contig.exe to be in the local path.

function getFileFragments {
    $filename = $args[0]
    $contigOutput = contig -a $filename
    foreach ($line in $contigOutput) {
        if ($line -match "Average fragmentation") {
            $splitline = $line.split(' ')
            $x = $splitline.count
            Write-Host $splitline[$x -2]
        }
    }
}

Usage:

getFileFragments "c:\boot.ini"

Tuesday, January 02, 2007

Test connection to server

Small function to test connectivity to a server.

function test-connection {
    $pingtest = ping $args[0] -n 1
        if ($pingtest -match 'TTL=') {
        Write-Output $true
    } else {
        Write-host $false
    }
}

Usage:

test-connection MyServer01.domain.com