Tuesday, September 16, 2008

Read The Transactionlog of SQL Server from PowerShell

Yes, the header is correct. Thanks to Quest Software, the ability to read individual transactions from the transaction log of SQL Server has arrived!

If you want to try it out, I've written this very brief quick start guide:

Start by downloading the trial version of Quest's LiteSpeed product: www.quest.com (products -> all products -> LiteSpeed)

Install the LiteSpeed trial you've just downloaded.

Start PowerShell

Register the SnapIn:
C:\Winnt\Microsoft.NET\Framework\v2.0.50727\installutil.exe Quest.LogReader.SqlServer.PSSnapIn.dll

Verify that the SnapIn has been registered:
Get-PSSnapin -registered

Add the SnapIn to the current PowerShell session (remember to add this to your profil if you so desire):
add-pssnapin Quest.LogReader.SqlServer

Also register the data formatting information:

Update-FormatData -AppendPath 'C:\Program Files\Quest Software\LiteSpeed\SQL Server\Quest.LogReader.SqlServer.Format.ps1xml'

You are now ready to plays around with the new commands offered by the SnapIn. To get a list of the new commands you can either consult the .chm file in the LiteSpeed folder, or just use Get-Help:

Get-Command *-QLRSql*

If you want the quick path to playing around with a transaction log file, you can use the following miniguide:

Install the Log Reader server components on your SQL server:

Set-QLRSqlSrvAgent -Install -Loginmode Windows

Lets try to look at the transaction log for the Master database:

$log = New-QLRSqlReader -Online . Master Windows
Get-QLRSqlRecord $log

If the above returns too much data, try this (you may want to modify the date a bit):

Get-QLRSqlRecord $log -After '16-09-2008 12:00:00'

Please note the the help text included for the new Commandlets contains a number of errors - If you use the examples, you will encounter a number of syntax errors, but most a easily recognized and corrected.

Friday, August 22, 2008

Call for inspiration: PowerShell + SQL

I was wondering if anyone out there has any good suggestions for SQL related PowerShell automation that just needs doing?

Any suggestions are welcome - if I can find the time, I might even try to create a solution.

This blog is now listed at PowerShellComunity.org

Sunday, August 17, 2008

SQL configuration through SMO

If you administer a large number of SQL servers, you might for some reason, have a server that doesn't quite align to your standards.
In order to avoid this I find it very nice to be able to extract various configuration settings from my servers.
Building on my post from yesterday, I'll post at small script I've been expanding on for some time (the script below is a rather basic version). The script uses SMO to query an SQL server/instance for various configuration settings.
The script requires the SMO functions from my post yesterday to be loaded.

function GetSQLconfig {
    Param ([string]$serverinstance)
    if ($serverinstance -match "\\") {
        $server = $serverinstance.split('\')[0]
        $instance = $serverinstance.split('\')[1]
    } else {
        $server = $serverinstance
        $instance = "MSSQLSERVER"
    $SMOconn = New-SMOconnection $serverinstance
    if ($SMOconn.connectioncontext.isopen -eq $false) {"Failed to establish a SMO connection to: $server\$instance"}
    $port = 0
    $obj = New-Object Object
    $obj | Add-Member Noteproperty Build -value $SMOconn.information.VersionString
    $obj | Add-Member Noteproperty Edition -value $SMOconn.Information.Edition
    $obj | Add-Member Noteproperty Platform -value $SMOconn.Information.Platform
    $obj | Add-Member Noteproperty Collation -value $SMOconn.Information.Collation
    $obj | Add-Member Noteproperty Productlevel -value $SMOconn.Information.Productlevel
    $obj | Add-Member Noteproperty IsClustered -value $SMOconn.Information.IsClustered
    $obj | Add-Member Noteproperty FulltextInstalled -value $SMOconn.Information.IsFullTextInstalled
    $obj | Add-Member Noteproperty LoginMode -value $SMOconn.Settings.LoginMode    
    $obj | Add-Member Noteproperty Auditlevel -value $SMOconn.Settings.Auditlevel
    $obj | Add-Member Noteproperty NumberOfLogFiles -value $SMOconn.Settings.NumberOfLogFiles
    $obj | Add-Member Noteproperty MaxServermemory -value $SMOconn.Configuration.MaxServerMemory.RunValue
    if ($SMOconn.information.VersionString.split(".")[0] -gt 8) {
        $mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $server
        $port = $mc.ServerInstances["$instance"].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].value
    } else {
        $DMOconn = New-Object -comobject "SQLDMO.SQLServer"
        $DMOconn.loginsecure = $true
        $DMOconn.logintimeout = 3
        if ($DMOconn.versionstring -ne $NULL) {
            $port = $DMOconn.Registry.tcpport
        } else {
            $port = -2
    if ($port -lt 1) { $port = -2 }
    $obj | Add-Member Noteproperty Port -value $port
    Remove-SMOconnection "SMOconn"

Usage: GetSQLconfig MyServer\MyInstance

The above function can easily be wrapped in a loop over several servers:

function MultiGetSQLconfig {
    param ($servers)
    Foreach ($server in $MyServers) {
        GetSQLconfig $server

$myServers = "Server1", "Server2", "Server3", "Server4"
$result = multiGetSQLconfig $myServers

The output isn't that good looking, but since the output is just another object, we can use all the normal PowerShell object cmdlets to format the output:

$result | Sort-Object Build | Format-Table -autosize

The above script has been testet against SQL2000, SQL2005 and SQL2008. But remember that in order to access SQL2008, the machine running PowerShell needs a new (SQL2008 compatible) version of SMO.

Saturday, August 16, 2008

SMO example

As mentioned before, I spend quite some time creating scripts in PowerShell for managing SQL server. Here is an example of a couple of SMO functions I have found very useful.

In the future, I'll try to post some examples that uses the functions below

We'll start by loading the correct assembly:

[void][reflection.assembly]::Load("Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

Or the more general "LoadWithPartialName" - that also works if you have another SMO version:


Then create a function to establish a SMO connection to an SQL instance. The function below creates a smo connection, with a user defined timeout, and applicationname. Remember that it is always recommended to give meaningful names to any connectionto SQL - it can make troubleshooting much easier.

function New-SMOconnection {
    Param ($server, $applicationName= "PowerShell SMO", [int]$commandTimeout = 5)
    Write-Debug "Function: New-SMOconnection $server $connectionname $commandtimeout"
    if (test-path variable:\conn) {
    } else {
        $conn = new-object('Microsoft.SqlServer.Management.Smo.Server') $server
    $conn.connectioncontext.applicationName = $applicationName
    $conn.connectioncontext.connectTimeout = $commandTimeout

And a small function to remove a SMO connection once you are done with it:

function Remove-SMOconnection {
    Param ($connection)
    Write-Debug "Function: Remove-SMOconnection $connection"
    Remove-Variable -Name $connection -Scope 1

The above functions can be used in the following manner:

1.) Establish a SMO connection, remembering to check for errors:

$SMOconn = New-SMOconnection "MyTestServer\SQLinstance0001" "JBs Secret PowerShell App"
if ($SMOconn.connectioncontext.isopen -eq $false) {# handle error}

You now a an open SMO connection to your SQL server. If you would like to retrieve information about what version/build you SQL server is, you could use the following:


Or retrieve a list of database names:

Foreach ($database in $SMOconn.databases) { $database.name }

Remember that "Get-Member is you friend!" - you can use Get-Member (or its alias, "gm") to explore the possibilities of SMO:

$SMOconn | gm

Once you are done, remember to close the SMO connection:

Remove-SMOconnection SMOconn

Friday, August 15, 2008


The current Sort-Object cmdlet in PowerShell lacks "random" functionality.
Here is a small function that allows youto sort randomly:

function sort-random {
    process {
        [array]$x = $x + $_
    end {
        $x | sort-object {(new-object Random).next()}


1..100 | Sort-Random
- or -
Get-Process | Sort-Random

Thursday, July 24, 2008

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:

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

Then we should be able to perform a query:

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.

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

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

PS> Close-SQL $data

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

And finally close an established connection to a SQL Server:

PS> Remove-SQLconnection $conn

function Remove-SQLconnection {
    Param ($connection)
    $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}
    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}
    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

Wednesday, July 23, 2008

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=, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
$mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" MyServer

Result when working the correct way:

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
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
ClientProtocols : {np, sm, tcp, via}
ServerInstances : {}
ServerAliases : {}
Urn : ManagedComputer[@Name='MyServer']
Name : MyServer
Properties : {}
UserData :
State : Existing

Sunday, July 20, 2008

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

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:

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

Wednesday, July 16, 2008

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
    $fileStream = $fileInfo.Open( [System.IO.FileMode]::OpenOrCreate, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None )
    if ($fileStream) {
    $obj = New-Object Object
    $obj | Add-Member Noteproperty FilePath -value $filePath
    $obj | Add-Member Noteproperty IsLocked -value $filelocked

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

Saturday, July 12, 2008

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

Invoke the function in the following way:

$myip = Get-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

Set-Alias ngen @(
dir (join-path ${env:\windir} "Microsoft.NET\Framework") ngen.exe -recurse |
sort -descending lastwritetime
[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!

Wednesday, May 07, 2008

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.


function GetFailedAgentJobs {
    param ([string]$servername="")
    $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") {
            $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"){
                $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

Monday, April 14, 2008

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)


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

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:


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


Thursday, January 31, 2008

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

Friday, January 25, 2008

Google Chart API & PowerShell

Have you heard of Google's "Chart API"?
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:

Thursday, January 24, 2008

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
$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
$MasterCmd.CommandText = $SqlDBCC
$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