SQL queries

There aren't that much written about using PowerShell as a channel for T-SQL, available on the web, so I thought I might give it a shot.

The scripts below allows you to query a SQL Server (SELECT statements) and use the returned data, as well as perform queries that doesn't return data (INSERT, UPDATE, DBCC *, etc.).
The way these functions are written are in no way ideal, but rather the minimum amount of scripting required to solve needs I've had in certain scripting scenarios. The scripts below include a limited ability to return errors.


The way to use the functions can be described this way:

1. Use New-SQLconnection to establish a connection to a SQL server.
2. Execute Query-SQL or NonQuery-SQL to perform a query against the server.
3. Use Close-SQLquery to close the query.
4. repeat 2. and 3. as many times as needed.
5. Use Remove-SQLconnection to close the connection.

It is only possible to have one open query (ie. not Close-SQLquery'ed) at a
time, but you can have as many SQLconnections as you need.


First we need the ability to create a connection to a SQL server:

Usage:
PS> $conn = New-SQLconnection "Server\Instance" "Database"
Check the state property on the returned conenction to determine if the connection was successfully established:
PS> if ($conn.state -eq "closed") {# Connection failed - handle error}

function New-SQLconnection {
    Param ([string]$server,
        [string]$database = "master",
        [string]$connectionName = $ScriptName)
    if (test-path variable:\conn) {
        $conn.close()
    } else {
        $conn = new-object ('System.Data.SqlClient.SqlConnection')
    }
    $connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName"
    $conn.ConnectionString = $connString
    $conn.StatisticsEnabled = $true
    $conn.Open()
    $conn
}

Then we should be able to perform a query:

Usage:
PS> Query-SQL "SELECT @@version" $conn
If the query returns zero rows "$data -eq $null" will evaluate to true.
If the query cannot be completed the returned object will be of type [int]

function Query-SQL {
    Param ($query, $conn, [int]$CommandTimeout = 30)
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = $CommandTimeout
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $Rset = $sqlCmd.ExecuteReader()
    if ($? -eq $false) {
        Close-SQLquery $rset
        [int]$rset = -1
    }
    if ($Rset.hasrows -eq $false) {
        Close-SQLquery $rset
        [string]$rset = ""
    }
    ,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}

Execute a SQL statement, where the information returned in on a "x rows affected" level:
If the query returns zero rows "$data -eq $null" will evaluate to true.
If the query cannot be completed the returned object will be of type [int]
Use "if ($data -is [int]) {# handle error}"
The value -1 is returned if a statement that doesnt' return a "x rows affected" is executed
The value -2 is returned if an error occurs.

Usage:
PS> NonQuery-SQL "SELECT @@version" $conn

function NonQuery-SQL {
    Param ($query, $conn, $CommandTimeout = 30)
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandTimeout = $CommandTimeout
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $RowsAffected = $sqlCmd.ExecuteNonQuery()
    if ($? -eq $false) {
        $RowsAffected = -2
    }
    $RowsAffected
}

Close down an existing query (should be performed after every query):

Usage:
PS> Close-SQL $data

function Close-SQLquery {
    Param ($query)
    $query.close()
    $query = $null
}

And finally close an established connection to a SQL Server:

Usage:
PS> Remove-SQLconnection $conn

function Remove-SQLconnection {
    Param ($connection)
    $connection.close()
    $connection = $null
}


Here is a complete example of how to use the functions above to do a simple query and create a new database:

$ScriptName = $myInvocation.MyCommand.Name
$Server = "MyServer\MyInstance"

$myConn = New-SQLconnection $Server "master"
if ($x.state -eq "closed") {"Failed to establish a connection";EXIT}
Write-Host "First Query" -back magenta
$query = "SELECT fileid, filename, size FROM sysfiles"
$data = Query-SQL $query $myConn
if ($data.gettype() -eq [int]) {"Failed to query SQL server";EXIT}
if ($data.gettype() -eq [string]) {"Zero rows returned";EXIT}
while($data.Read()){
    Write-Host $data.GetValue(0) -back yellow
    Write-Host $data.GetString($data.GetOrdinal("filename")) -back yellow
    Write-Host $data.GetValue(2) -back yellow
}

Close-SQLquery $data

Write-Host "Second Query" -backgroundcolor magenta
$query = "SELECT TOP 5 name, description, backup_start_date FROM msdb.dbo.backupset"
$data = Query-SQL $query $myConn
if ($data.gettype() -eq [int]) {"Failed to query SQL server";EXIT}
if ($data.gettype() -eq [string]) {"Zero rows returned";EXIT}
while($data.Read()){
    Write-Host $data.GetValue(2) -back red
    Write-Host $data.GetValue(1) -back red
    Write-Host $data.GetValue(0) -back red
}

Close-SQLquery $data

Write-Host "First NonQuery" -back magenta
$query = "CREATE DATABASE [MyFirstTestDB] "
$data = NonQuery-SQL $query $myConn
if ($data -eq -2) {
    Write-Host "Run away! Something has gone wrong!!!"
} else {
    Write-Host "$data Rows Affected"
}

Remove-SQLconnection $myConn

A small bug in SMO

I recently encountered what appears to be a small bug in SMO:

When accessing the SqlServer.Management.Smo.Wmi.ManagedComputer namespace using a 32bit application (ie. Powershell.exe) from a 64-bit platform (HW & OS), SMO won't return anything for the "ServerInstances" property. If a 64bit application is used everything works just fine.

Try the following script from the 32, and 64 bit versions of PowerShell (repalce MyServer with the name of your SQL server):

32bit: %SystemRoot%\sysWOW64\WindowsPowerShell\v1.0\powershell.exe
- or -
64bit: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe

[reflection.assembly]::Load("Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
$mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" MyServer
$mc

Result when working the correct way:

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services : {MSFTESQL$SQL01, MSFTESQL$SQL02, MSFTESQL$SQL03, MSFTESQL$SQL04...}
ClientProtocols : {np, sm, tcp, via}
ServerInstances : {SQL01, SQL02, SQL03, SQL04...}
ServerAliases : {}
Urn : ManagedComputer[@Name='MyServer']
Name : MyServer
Properties : {}
UserData :
State : Existing

Result when NOT working working the correct way:

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services : {MSFTESQL$SQL01, MSFTESQL$SQL02, MSFTESQL$SQL03, MSFTESQL$SQL04...}
ClientProtocols : {np, sm, tcp, via}
ServerInstances : {}
ServerAliases : {}
Urn : ManagedComputer[@Name='MyServer']
Name : MyServer
Properties : {}
UserData :
State : Existing

NMEA GPS & 22 lines of PowerShell

Inspired by the python article "GPS + Google Maps Mash-up in 42 lines of code", I decided to create something similar in PowerShell:

function Nmea2dec {
    Param ([double]$degrees, $o)
    $deg = [math]::floor($degrees/100.0)
    $frac = (($degrees/100.0) - $deg)/0.6
    $ret = $deg + $frac
    if ($o -eq "S" -or $o -eq "W") {$ret = $ret * (-1)}
    return $ret
}

$port = new-Object System.IO.Ports.SerialPort COM1,4800,None,8,one
$port.open()
$line = ""
while (-not ($line -match ".GPRMC.*")) {
    $line = $port.readline()
}
$splitline = $line.split(",")
$latitude = Nmea2dec $splitline[3] $splitline[4]
$longtitude = Nmea2dec $splitline[5] $splitline[6]
$url = "http://maps.google.com/maps?ie=UTF8&ll=$latitude,$longtitude&q=$latitude,$longtitude&z=16&t=h"
$ie = New-Object -ComObject InternetExplorer.Application
$ie.visible=$true
$ie.navigate2("$url")

If you want to play around with the script above, you'll of course need a serial GPS reciever, or you could just use the following line of data:
$GPRMC,182700.000,A,5541.8761,N,01232.0247,E,0.12,52.50,190708,,,A*55

I think I'll continue to play around with GPS data and Google Maps for a while - look out for PS scripts!

Locked file detection

As previously stated here the Blog, I spend quite some time doing automation of SQL Server maintenance.
One challenge has been to detect if a given SQL Server was currently locking a specific device file (backup file). To solve this problem I came up with this small function:

function TestFileLock {
    ## Attempts to open a file and trap the resulting error if the file is already open/locked
    param ([string]$filePath )
    $filelocked = $false
    $fileInfo = New-Object System.IO.FileInfo $filePath
    trap {
        Set-Variable -name locked -value $true -scope 1
        continue
    }
    $fileStream = $fileInfo.Open( [System.IO.FileMode]::OpenOrCreate, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None )
    if ($fileStream) {
        $fileStream.Close()
    }
    $obj = New-Object Object
    $obj | Add-Member Noteproperty FilePath -value $filePath
    $obj | Add-Member Noteproperty IsLocked -value $filelocked
    $obj
}

Usage:
PS> TestFileLock "c:\pagefile.sys"
PS> TestFileLock "\\Server01\backup\MyInstance05\devDATABASE_00A1.bak"

Obtain external IP

Recently I had to obtain the external IP of a number of PC - ie. the address the PCs appear to use on the internet (in my case, the external IP address of their internet gateway/router).

Here is the small function I ended up using:

## Function to retrieve external IP address.
## the external address is retrieved from the
## title header of the webpage "www.myip.dk"

function Get-ExternalIP {
    $source = "http://www.myip.dk"
    $client = new-object System.Net.WebClient
    $webpage = $client.downloadString($source)
    $lines = $webpage.split("`n")
    foreach ($line in $lines) {
        if ($line.contains("</title>")) {
            $ip = $line.replace("    <title>Your IP address is: ", "").replace("</title>","")
        }
    }
    $obj = New-Object Object
    $obj | Add-Member Noteproperty externalIP -value $ip
    $obj
}

Invoke the function in the following way:

$myip = Get-ExternalIP
$myip.externalip

Make PowerShell start much faster

Jeffrey Snover recently wrote the following on the PowerShell Blog:

Speeding Up PowerShell Startup

I talked about this before but a number of people have missed it so here it is under a better title.

In V1, we had a problem which caused our assemblies to not get ngen'ed during installation. If you don't know what "ngen" is, don't worry - you don't need to. All you need to know is that we didn't do the step that makes things go fast on your machine. The instructions for how to fix this are HERE.

Source: http://blogs.msdn.com/powershell/archive/2008/07/11/speeding-up-powershell-startup.aspx

Script:
Set-Alias ngen @(
dir (join-path ${env:\windir} "Microsoft.NET\Framework") ngen.exe -recurse |
sort -descending lastwritetime
)[0].fullName
[appdomain]::currentdomain.getassemblies() | %{ngen $_.location}

My personal advice would be for everyone to try out the "trick" - on some of my systems running the script reduced PowerShell startup time by 75% or more!

List failed SQL agent Jobs

This function is used to extract a list of jobs that didn't complete successfully on last execution.
The function takes a single parameter - the SQL server (instance) to query.
This script can be useful in getting a daily overview - especially if wrapped in a loop and run against all servers.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo");
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum");
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

function GetFailedAgentJobs {
    param ([string]$servername="127.0.0.1")
    $srv = new-object('Microsoft.SqlServer.Management.Smo.server') $servername
    $jobs = $srv.jobserver.jobs | where-object {$_.isenabled}
    foreach ($job in $jobs) {
        [int]$outcome = 0
        [string]$output = ""
        ## Did the job fail completely?
        if ($job.LastRunOutcome -ne "Succeeded") {
            $outcome++
            $output = $output + " Job failed (" + $job.name + ")" + " Result: " + $job.LastRunOutcome
        }
        ## Did any of the steps fail?
        foreach ($step in $job.jobsteps) {
            if ($step.LastRunOutcome -ne "Succeeded"){
                $outcome++
                $output = $output + " Step failed (" + $step.name + ")" + " Result: " + $step.LastRunOutcome + " -- "
            }
        }
        if ($outcome -gt 0)    {
            $obj = New-Object Object
            $obj | Add-Member Noteproperty name -value $job.name
            $obj | Add-Member Noteproperty lastrundate -value $job.lastrundate
            $obj | Add-Member Noteproperty lastrunoutcome -value $output
            $obj
        }
    }
}

Read groupmembers from AD

A friend of mine asked how he could read from his local AD, in order to create a script for verifying group membership against a saved list. Here is a small piece of code that helped him:

$users = [adsi]"LDAP://cn=groupName,ou=ouName,dc=MyDomain,dc=MyDomain2"

foreach ($user in $users.Member) {
    Write-Host (([adsi]"LDAP://$user").displayname)
}

New-RemoteProcess

It is possible to initiate a process on a remote machine through PowerShell, but only through WMI.
Unfortunately there are certain limitations to this functionality in WMI:
1.) The process will never be visible to a user logged on to the remote server.
2.) The process will only be initialized - no return values are passed back through WMI.

Limitation #2 can be circumvented by directing the output to a file, and then accessing the file after the process has completed.

Here is a quick example:

Function New-RemoteProcess {
## The function returns two values.
## A returncode (0 = success) and the ProcessID on the target server.
    Param ($target, $command, $path="c:\")
    $p = [WMIClass]"\\$target\ROOT\CIMV2:Win32_Process"
    $inParams = $p.psbase.GetMethodParameters("Create")
    $inparams.commandline = $command
    $inparams.currentdirectory = $path
    $result = $p.psbase.invokemethod("Create", $inparams, $null)
    Write-Output $result.ReturnValue
    Write-Output $result.ProcessID
}

New-RemoteProcess "MyServer.something.com" "Calc.exe"

Creating arrays

In PowerShell an array can be contructed in a number of ways:

$a = 1, 2, 3, 4, 5
$a = 1..5

The "1..5" notation builds a range of numbers starting with 1 and ending with 5. It is also possible to create a decrementing list:

$a = 5..1
$a
5
4
3
2
1

As a server/network/database administrator one will often have to deal with servers/databases/tables/etc. that share the same name, except for some sort of numbering scheme:

WebServer001_HQ
WebServer002_HQ
..
WebServer064_HQ


If the above range is needed in PowerShell, it can easily be constructed:

1..64 | ForEach-Object{"WebServer{0:000}" -f $_ + "_HQ"}


What if there are "holes" in the number ranges? Well, ranges can easily be constructed using
"+". Lets make a server list including servers numbered from 4 to 30, but say the servers numbered 19-21 have been decommissioned, and two servers additional servers have been added, numbered 117 and 666:

4..18 + 22..30 + 117,666 | ForEach-Object{"WebServer{0:000}" -f $_ + "_HQ"}

WebServer004_HQ
WebServer005_HQ
WebServer006_HQ
WebServer007_HQ
WebServer008_HQ
WebServer009_HQ
WebServer010_HQ
WebServer011_HQ
WebServer012_HQ
WebServer013_HQ
WebServer014_HQ
WebServer015_HQ
WebServer016_HQ
WebServer017_HQ
WebServer018_HQ
WebServer022_HQ
WebServer023_HQ
WebServer024_HQ
WebServer025_HQ
WebServer026_HQ
WebServer027_HQ
WebServer028_HQ
WebServer029_HQ
WebServer030_HQ
WebServer117_HQ
WebServer666_HQ

PowerShell, WMI and SQL Server

I was recently asked "how can we determine the specific SKU of an installed SQL Server - without being able to execute T-SQL?". My answer:

The following one-liner lists various advanced properties of a SQL installation:

Get-WmiObject sqlserviceadvancedproperty -namespace "root\Microsoft\SqlServer\ComputerManagement" -computername | Select-Object -Property PropertyName, PropertyNumValue, PropertyStrValue

Google Chart API & PowerShell

Have you heard of Google's "Chart API"?
No?
Well, the Google Chart API lets you dynamically generate charts. Using only a browser. But why use a browser when you can use PowerShell instead? :-)
Click here to learn more about Google Chart API.

I had been playing around with Google Chart API for some time, when I decided to create a small PowerShell script to obtain a pie chart - sort of a poor mans replacement for Microsoft Excel or PowerGadgets:

Lets start by writing a small function to download and display an image. This will be a very basic function, so the only input in the function will be an URL.

function DownloadAndShowImage ($url) {
    $localfilename = ".\chart.png"
    $webClient = new-object System.Net.WebClient
    $webClient.Headers.Add("user-agent", "PowerShell Badass Script v666")
    $Webclient.DownloadFile($url, $localfilename)
    Invoke-Item $localfilename
}

Next, we need a function to create a valid Google Chart API url. Input parameters will be two arrays (data values and text), the desired size of the chart and an option to create a 3D pie chart insted of a plain 2D one:

function simpleEncoding ($valueArray, $labelArray, $size, [switch] $chart3D) {
    $simpleEncoding = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
    if ($chart3D) {$chartType = "p3"} else {$chartType ="p"}
    $total = 0
    foreach ($value in $valueArray) {
        $total = $total + $value
    }
    for ($i = 0;$i -lt $valueArray.length;$i++) {
        $relativeValue = ($valueArray[$i] / $total)*62
        $relativeValue = [math]::round($relativeValue)
        $encodingValue = $simpleEncoding[$relativeValue]
        $chartData = $chartData + "" + $encodingValue
    }    
    $chartLabel = [string]::join("|",$labelArray)
    Write-Output "http://chart.apis.google.com/chart?cht=$chartType&chd=s:$chartdata&chs=$size&chl=$chartLabel"
}

That's it! We are now ready to create a small Chart. Let's try it out:

$values = 100,11,40,9
$text = "Hans","Jane","Rose","Simon"
$url = simpleEncoding $values $text "320x150" -Chart3D
DownloadAndShowImage $url

The above should result in your local .png viewer showing something like this:

How about something a little more advanced?
Lets try it - but first we need a small support function:

function GetProcessArray() {
    $ListOfProcs = Get-Process | Sort-Object CPU -desc | Select-Object CPU, ProcessName -First 15
    for ($i = 0;$i -lt $ListOfProcs.length ;$i++) {
        $ProcName = $ProcName + "," + $ListOfProcs[$i].ProcessName
        $ProcUsage = $ProcUsage + "," + $ListOfProcs[$i].CPU
    }
    Write-Output (($ProcName.trimStart(",")).split(","), ($ProcUsage.trimStart(",")).split(","))
}

Now we are ready to create a more detailed graph, this time of the top 15 processes counted by CPU seconds consumed.

$data = GetProcessArray
$url = simpleEncoding $data[1] $data[0] "700x350"
DownloadAndShowImage $url

The result should look like this:

DBCC check through ADO.NET/PS

Here is a small script I use to run DBCC against various databases in my environment:

$ScriptName = $myInvocation.MyCommand.Name
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")
$ConnString = "Server=Servername\Instance;Integrated Security=SSPI;Database=DatabaseName;Application Name=$ScriptName"
$MasterConn = new-object ('System.Data.SqlClient.SqlConnection') $ConnString
$MasterCmd = new-object System.Data.SqlClient.SqlCommand
$MasterCmd.Connection = $MasterConn
$SqlDBCC = "DBCC CHECKDB(master) WITH TABLERESULTS"
$MasterCmd.CommandText = $SqlDBCC
$MasterConn.Open()
$Rset = $MasterCmd.ExecuteReader()
If ($Rset.HasRows -eq $true) {
    While ($Rset.Read()) {
        $line = $Rset["MessageText"]
        If ($Rset["Level"] -gt 10) {
            Write-Host $line -backgroundcolor Yellow -foregroundcolor Red
        } else {
            Write-Host $line
        }
    }
    $Rset.Close()
}
$MasterConn.Close()

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

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

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

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 :-(

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.

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"

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