Collect WWN

Due to recent works on migrating a large number of servers and SAN LUNs, I threw together this small script to collect WWN numbers (see WWN on Wikipedia) from servers. Maybe it can be useful for someone else?

Save the lines below as wwn.ps1 :

## WWN version 1.1
## Script to determine HBA WWNs on remote servers (Win 2003+)
## Usage: .\wwn.ps1 servername.domain.com

Param ($servername)

$data = Get-WmiObject -namespace "root\wmi" -class MSFC_FibrePortNPIVAttributes -computer $servername

$data | select WWPN | foreach {[array]::Reverse($_.WWPN); [BitConverter]::ToUInt64($_.WWPN, 0).ToString("X") }

Secunia PSI & Powershell

I been using Secunia's Personal Software Inspector since it was in beta. This is a free product that I highly recommend. It is currently in version 2.0.

For those of you who don't know PSI:
The Secunia PSI is a FREE security tool designed to detect vulnerable and out-dated programs and plug-ins which expose your PC to attacks. Attacks exploiting vulnerable programs and plug-ins are rarely blocked by traditional anti-virus and are therefore increasingly "popular" among criminals.
You can read about it here: Secunia PSI

The following PowerShell script uses the new Secunia PSI API to collect data about the status of a single machine.
The script is something I came up with this evening, as a sort of test for the API. I will leave it at an exercise to the reader to extend it to include several machine, produce nice html output and whatever else you can com up with :-)

$tokenID = "123" ##enter your tokenID here
$token = "xxXXxxXXBmlfgMThZj" ##enter your own token here"
$url = "https://psi.secunia.com/API/?version=1.0&type=scan_result&id=$tokenID&token=$token&feed_format=1"
$wc = new-object system.net.webclient
[xml]$result = $wc.DownloadString($url)

$obj = New-Object Object
$obj | Add-Member Noteproperty lastFullScan -value $result.output.systemOverview.lastFullScan."#cdata-section"
$obj | Add-Member Noteproperty numInsecure -value $result.output.systemOverview.numInsecure."#cdata-section"
$obj | Add-Member Noteproperty numEOL -value $result.output.systemOverview.numEOL."#cdata-section"
$obj | Add-Member Noteproperty numPatched -value $result.output.systemOverview.numPatched."#cdata-section"
$obj | Add-Member Noteproperty numTotal -value $result.output.systemOverview.numTotal."#cdata-section"
$overview = $obj

$programlist = foreach ($program in $result.output.scanResults.program) {
        $obj = New-Object Object
        $obj | Add-Member Noteproperty productname -value $program.productname."#cdata-section"
        $obj | Add-Member Noteproperty version -value $program.version."#cdata-section"
        $obj | Add-Member Noteproperty stateNumber -value $program.stateNumber."#cdata-section"
        $obj | Add-Member Noteproperty lastScanOfProgram -value $program.lastScanOfProgram."#cdata-section"
        $obj | Add-Member Noteproperty secuniaAdvisoryID -value $program.secuniaAdvisoryID."#cdata-section"
        $obj | Add-Member Noteproperty secuniaAdvisoryCriticality -value $program.secuniaAdvisoryCriticality."#cdata-section"
        $obj | Add-Member Noteproperty secuniaProductPage -value $program.secuniaProductPage."#cdata-section"
        $obj | Add-Member Noteproperty vendorProductPage -value $program.vendorProductPage."#cdata-section"
        $obj | Add-Member Noteproperty is64bit -value $program.is64bit."#cdata-section"
        $obj | Add-Member Noteproperty paths -value $program.paths."#cdata-section"
        $obj
}

Write-Host "$('#'*20) Overview $('#'*20)" -fore cyan
$overview | ft -au

Write-Host "$('#'*20) End-Of-Life Programs $('#'*20)" -fore yellow
$programlist | Where {$_.stateNumber -eq 0} | Select productname, version, statenumber, lastscanofprogram | sort statenumber, productname | ft -au

Write-Host "$('#'*20) Insecure Programs $('#'*20)" -fore red
$programlist | Where {$_.stateNumber -eq 1} | Select productname, version, statenumber, lastscanofprogram | sort statenumber, productname | ft -au

Write-Host "$('#'*20) Patched Programs $('#'*20)" -fore cyan
$programlist | Where {$_.stateNumber -eq 2} | Select productname, version, statenumber, lastscanofprogram | sort statenumber, productname | ft -au


The output of the script is a overview containing the time of the last scan, number of insecure, end-of-lif and patched programs detected, as well as a listing of all the programs.

SQL Server Product Key - SS 2005

In order to extract the Product key from SQL Server 2005, change the following two lines in the previous post:

    $regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup"
    $regValue1 = "DigitalProductId"

To:

    $regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\90\ProductID"
    $regValue1 = "DigitalProductID77591"

SQL Server Product Key

A while back I wrote a small script to retrieve the Windows license key. Since then I've been asked several times how to retrieve the product key of an SQL Server.
So today I threw together this small script to do just that

function Get-SQLserverKey {
    ## function to retrieve the license key of a SQL 2008 Server.
    ## by Jakob Bindslet (jakob@bindslet.dk)
    param ($targets = ".")
    $hklm = 2147483650
    $regPath = "SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup"
    $regValue1 = "DigitalProductId"
    $regValue2 = "PatchLevel"
    $regValue3 = "Edition"
    Foreach ($target in $targets) {
        $productKey = $null
        $win32os = $null
        $wmi = [WMIClass]"\\$target\root\default:stdRegProv"
        $data = $wmi.GetBinaryValue($hklm,$regPath,$regValue1)
        [string]$SQLver = $wmi.GetstringValue($hklm,$regPath,$regValue2).svalue
        [string]$SQLedition = $wmi.GetstringValue($hklm,$regPath,$regValue3).svalue
        $binArray = ($data.uValue)[52..66]
        $charsArray = "B","C","D","F","G","H","J","K","M","P","Q","R","T","V","W","X","Y","2","3","4","6","7","8","9"
        ## decrypt base24 encoded binary data
        For ($i = 24; $i -ge 0; $i--) {
            $k = 0
            For ($j = 14; $j -ge 0; $j--) {
                $k = $k * 256 -bxor $binArray[$j]
                $binArray[$j] = [math]::truncate($k / 24)
                $k = $k % 24
         }
            $productKey = $charsArray[$k] + $productKey
            If (($i % 5 -eq 0) -and ($i -ne 0)) {
                $productKey = "-" + $productKey
            }
        }
        $win32os = Get-WmiObject Win32_OperatingSystem -computer $target
        $obj = New-Object Object
        $obj | Add-Member Noteproperty Computer -value $target
        $obj | Add-Member Noteproperty OSCaption -value $win32os.Caption
        $obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture
        $obj | Add-Member Noteproperty SQLver -value $SQLver
        $obj | Add-Member Noteproperty SQLedition -value $SQLedition
        $obj | Add-Member Noteproperty ProductKey -value $productkey
        $obj
    }
}



Use the function to retrieve the Product Key from the local PC:

Get-SQLserverKey

Or to retrieve the Product Key from one or more PCs (locally or remotely):

Get-SQLserverKey "pc1", "pc2", "server999", "server777"

Remeber that the output from the function is a standard PowerShell object, so you can pipe into sort-object, format-table or mayby ConvertTo-HTML ...

SQLmonkeys

A friend and colleague of mine has just launched a new site - go to www.sqlmonkeys.com to have a look.
I'm not sure what contest will be added to the site, but I guess it will mainly be about SQL ... and monkeys ...

SQL: Poor man's audit

Most DBAs have had to examine unknown database/instances in order to evaluate their configuration.

I recently had to examine a few SQL servers for a client in order to offer advice on improvement - mainly in the area of "best practice configuration". This is a (simplified) version of a script I wrote in order to do a quick survey of misconfigurations. Please excuse the simple script - it is more of an exercise in SMO than in SQL or PowerShell.

## **************************************************************
## * Script Name: SQLpoormansaudit.ps1
## * Version: 1.0
## * Developed by:Jakob Bindslet
## * Contact: jakob@bindslet.dk
## * ----------------------------------------------------------
## * Usage: *
## * SQLpoormansaudit.ps1 "Server\InstanceName"
## * ----------------------------------------------------------
## * Description:
## * Script to perform an very quick audit of SQL
## * 2005+ instance.*
## * Tests for collation mismatch between DB & DBMS, allocated
## * memory, AutoClose/shrink, membership of fixed server roles
## * and other stuff
## * Can easily be extended to check for other parameters
## **************************************************************

Param ($instance)
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$smo = new-object('Microsoft.SqlServer.Management.Smo.Server') $instance
$memSrv = $smo.information.physicalMemory
$memMin = $smo.configuration.minServerMemory.runValue
$memMax = $smo.configuration.maxServerMemory.runValue
$version = $smo.versionmajor
$cpuSrv = $smo.information.Processors
$maxParallel = $smo.configuration.maxDegreeOfParallelism.runvalue

## DBMS
Write-Host "Testing instance $($instance.toUpper()) [DBMS Level]" -fore "yellow"
Write-Host "Collation:                  " -noNewLine
Write-Host "$($smo.collation)" -fore "green"
Write-Host "Authentication:             " -noNewLine
Write-Host "$($smo.loginMode)" -fore $(if ($smo.loginMode -eq "Integrated") {"green"} else {"red"})
Write-Host "Login audit:                " -noNewLine
Write-Host "$($smo.auditLevel)" -fore $(if ($smo.auditLevel -eq "All") {"green"} else {"red"})
Write-Host "Max number of logfiles:     " -noNewLine
Write-Host "$($smo.numberOfLogFiles)" -fore $(if ($smo.numberOfLogFiles -gt 30) {"green"} elseif ($smo.numberOfLogFiles -gt 0) {"yellow"} else {"red"})
Write-Host "Server RAM available:       " -noNewLine
Write-Host "$memSrv MB" -fore "green"
Write-Host "SQL memory Min:             " -noNewLine
Write-Host "$memMin" -fore $(if ($memMin -eq 0) {"yellow"} else {"green"})
Write-Host "SQL memory Max:             " -noNewLine
Write-Host "$memMax ($([math]::round($memMax/$memSrv*100,1))% of server total)" -fore $(if ($memMax -gt 2000000) {"red"} else {"green"})
foreach ($role in $smo.roles) {
    if ($($role.enumserverrolemembers().count) -gt 1) {
        Write-Host "# of $($role.name)s: `t    " -noNewLine
        Write-Host "$($role.enumserverrolemembers().count)" -fore "yellow"
    }
}

## DATABASES
Write-Host "`nTesting Databases:" -fore "yellow"
$databases = $smo.databases | where {$_.id -gt 4}
Foreach ($db in $databases) {
    Write-Host "`nDatabase: " -noNewLine
    Write-Host "$($db.name.toUpper())" -fore "green"
    if ($db.autoClose -ne $false) {
        Write-Host "Auto Close:                 " -noNewLine; Write-Host $db.autoClose -fore "red"
        }
    if ($db.autoShrink -ne $false) {
        Write-Host "Auto Shrink:                " -noNewLine; Write-Host $db.autoShrink -fore "red"
    }
    if ($db.autoCreateStatisticsEnabled -ne $true) {
        Write-Host "Auto Create Statistics:     " -noNewLine; Write-Host $db.autoCreateStatisticsEnabled -fore "red"
    }
    if ($db.autoUpdateStatisticsEnabled -ne $true) {
        Write-Host "Auto Update Statistics:     " -noNewLine; Write-Host $db.autoUpdateStatisticsEnabled -fore "red"
    }
    if ($db.status -ne "Normal") {
        Write-Host "Status:                     " -noNewLine; Write-Host $db.status -fore "red"
    }
    if ($db.owner -ne "sa") {
        Write-Host "Owner:                      " -noNewLine; Write-Host $db.owner -fore "red"
    }
    if ($db.pageVerify -ne "Checksum") {
        Write-Host "Pageverify:                 " -noNewLine; Write-Host $db.pageVerify -fore "red"
    }    
    if ($db.collation -ne $smo.collation) {
        Write-Host "Collation:                  " -noNewLine; Write-Host $db.collation -fore "red"
    }    
    if ($($db.CompatibilityLevel.toString().replace('Version','')) -ne $version * 10) {
        Write-Host "Compatibility Level         " -noNewLine; Write-Host $db.CompatibilityLevel -fore "red"
    }    
    if (((get-date) - $db.LastBackupDate).days -gt 1) {
        Write-Host "Last full backup:           " -noNewLine; Write-Host $db.LastBackupDate -fore "red"
    }
}

PowerShell v2 Launch Party

PowerShell v2 finally launches October 22nd: http://powerscripting.wordpress.com/2009/10/16/powershell-v2-virtual-launch-party/

Virtual Windows & PowerShell

I'm playing around whit Windows 7 and the Virtual Windows feature at the momemt.

Naturally I've tried to manipulate Virtual Windows PC through PowerShell. At the moment the possibilities are apparently limited to using COM - with a namespace similar to Virtual PC/Virtual Server:

### Virtual Windows (Virtual PC) com object ###
$vpc = new-object -com VirtualPC.Application
$vms = $vps.VirtualMachines

Next try out the following commands:

$vms.items(1).save()
$vms.items(1).state
$vms.items(1).startup()

Or maybe just play a bit with Get-Member. If I get the time to play around more, I'll do another post on Virtual Windows.

Getting informationon installed CPUs, Cores and Sockets

A friend and former colleague asked me about a way to obtain rather detailed information on the type and number of CPUs installed in a server, as well as the number of cores present.
This is the function I came up with.

Please note that this function is incapable of coping nicely with limitations implemented using /NUMPROC in boot.ini
The function should work on Windows 2000 Server and above.


function GetCPUinfo {
    param ([array]$servernames = ".")
    foreach ($servername in $servernames) {
        [array]$wmiinfo = Get-WmiObject Win32_Processor -computer $servername
        $cpu = ($wmiinfo[0].name) -replace ' +', ' '
        $description = $wmiinfo[0].description
        $cores = ( $wmiinfo | Select SocketDesignation | Measure-Object ).count
        $sockets = ( $wmiinfo | Select SocketDesignation -unique | Measure-Object ).count
        Switch ($wmiinfo[0].architecture) {
            0 { $arch = "x86" }
            1 { $arch = "MIPS" }
            2 { $arch = "Alpha" }
            3 { $arch = "PowerPC" }
            6 { $arch = "Itanium" }
            9 { $arch = "x64" }
        }
        $manfg = $wmiinfo[0].manufacturer
        $obj = New-Object Object
        $obj | Add-Member Noteproperty Servername -value $servername
        $obj | Add-Member Noteproperty CPU -value $cpu
        $obj | Add-Member Noteproperty Description -value $description
        $obj | Add-Member Noteproperty Sockets -value $sockets
        $obj | Add-Member Noteproperty Cores -value $cores
        $obj | Add-Member Noteproperty Architecture -value $arch
        $obj | Add-Member Noteproperty Manufacturer -value $manfg
        $obj
    }
}

The function is invoked with a list of servers as the single parameter. If no parameter is specified, the local server "." is used.

$result = GetCPUinfo server1, server2, server3

The output can then be piped info a Format-Table or similar.

$result | format-table -auto

The result could look something like this:

Servername CPU                                   Description                         Sockets Cores Architecture Manufacturer
---------- ---                                   -----------                         ------- ----- ------------ ------------
server1    Intel(R) Xeon(TM) CPU 2.80GHz         EM64T Family 15 Model 4 Stepping 8        4    16 x64          GenuineIntel
server2    Intel(R) Core(TM)2 CPU 6400 @ 2.13GHz EM64T Family 6 Model 15 Stepping 6        1     2 x64          GenuineIntel
server3    AMD Opteron(tm) Processor 285         AMD64 Family 15 Model 33 Stepping 2       2     2 x64          AuthenticAMD
server4    Intel(R) Xeon(R) CPU E7340 @ 2.40GHz  EM64T Family 6 Model 15 Stepping 8        2     2 x64          GenuineIntel
server5    Intel(R) Xeon(R) CPU E7340 @ 2.40GHz  EM64T Family 6 Model 15 Stepping 11       4    16 x64          GenuineIntel
server6    Intel(R) Xeon(TM) CPU 2.80GHz         x86 Family 15 Model 4 Stepping 8          1     1 x86          GenuineIntel
server7    Pentium III Tualatin                  x86 Family 6 Model 11 Stepping 1          2     2 x86          GenuineIntel

PS
Please let me know if you find any limitations or has suggestions for improvement.

Testing WinRAR 3.90 beta

I recently had to evaluate the preformance of the Rarsofts new beta of WinRAR.
WinRAR is a powerful GUI and CLI archiever that handles many archieve formats.
In order to test the preformance I installed WinRAR 3.80 as well as WinRAR 3.90beta1 (in both 32 and 64-bit versions).

Here is a light version of the PowerShell script I came up with (please adjust file paths and $source according to your own need):

Function Test-Rar {
    param ($winrar = "c:\Program Files (x86)\WinRAR\Rar.exe",
        $source = "d:\psref351.pdf",
        $target = "c:\test.rar",
        $testruns = 1)
    $version = & $winrar
    Write-Host "Test using: $winrar" -fore "green"
    Write-Host $version[1] -fore "green"
    for ($i = 1; $i -le $testruns; $i++){
        if (Test-Path $target) { del $target }
        $time = Measure-Command {& $winrar a $target $source}
        $sourcesize = (Get-ChildItem $source).length
        $targetsize = (Get-ChildItem $target).length
        $obj = New-Object Object
        $obj | Add-Member Noteproperty Testrun -value $i
        $obj | Add-Member Noteproperty TimeInSeconds -value ([math]::round(($time.totalseconds), 2))
        $obj | Add-Member Noteproperty SourceByteSize -value $sourcesize
        $obj | Add-Member Noteproperty TargetByteSize -value $targetsize
        $obj | Add-Member Noteproperty CompressionPercent -value ([math]::round( (1-($targetsize / $sourcesize)) * 100,2))
        $obj
    }
}

The above function can be use in the following way:

Test-Rar -source "d:\psref351.pdf" -target "d:\test.rar"


However, I needed to run tests with several versions of WinRAR and compress rather large files. So I needed a small function to repeat the same test multipel times with different versions of WinRAR.

Function MultiTest {
    Param ($rarversions = "c:\Program Files (x86)\WinRAR\Rar.exe")
    foreach ($rarversion in $rarversions) {
        $result = Test-RAR -testruns 5 -winrar $rarversion
        $result
        $stat = $result | Measure-Object -Property TimeInSeconds -min -max -average
        $stat | Add-Member Noteproperty Executable -value $rarversion
        $stat
    }
}

Here is how to use the function:

$rarversions = "c:\Program Files (x86)\WinRAR\Rar.exe", "D:\wrar39b1\rar.exe", "D:\winrar-x64-39b1\rar.exe"
$result = MultiTest $rarversions
$result | Select Executable, Count, Average, Maximum, Minimum | ft -au


While this is not a preview of the new WinRAR version, my results indicate a decrease in the time required to create a .rar file in the 20-30% range. Most impressive indeed!

WebServer investigated

I recently spend some time playing around with creating a bunch of scripts to monitor changes in a web page. While doing this I needed to determine what kind of webserver a given site was hosted on. This is a somewhat simplified version of my solution:

function Get-WebServer {
    param ($target)
    if (-not ($target -like "http://*")) {
        $target = "http://" + $target
    }
    $wr = [System.Net.WebRequest]::Create($target)
    $wr.UserAgent = "InterwebExploder 10.0 RC1 - 'Titanic'"
    $header = $wr.GetResponse()
    Write-host "$target"
    $header | select Server, LastModified, ProtocolVersion, ResponseUri | Format-List
}

Please note that proxies and various other issues can ruin the function.
To use the function, simply call it with the name of the target url:

Get-WebServer "www.blogspot.com"

CLI Menu in PowerShell

There are a lot of WPF-based PowerShell menu functions and demos available in various places, but for some reason I feel that PowerShell really needs a good, ol' fashion CLI menu.

So, here is a quick, sort of hack'ish CLI menu for PowerShell (dont worry, I just made up the individual menu options - can you tell I just reread BOFH recently?).

Use the up and down arrows to navigate the menu, and press ENTER to select.

function DrawMenu {
    ## supportfunction to the Menu function below
    param ($menuItems, $menuPosition, $menuTitel)
    $fcolor = $host.UI.RawUI.ForegroundColor
    $bcolor = $host.UI.RawUI.BackgroundColor
    $l = $menuItems.length + 1
    cls
    $menuwidth = $menuTitel.length + 4
    Write-Host "`t" -NoNewLine
    Write-Host ("*" * $menuwidth) -fore $fcolor -back $bcolor
    Write-Host "`t" -NoNewLine
    Write-Host "* $menuTitel *" -fore $fcolor -back $bcolor
    Write-Host "`t" -NoNewLine
    Write-Host ("*" * $menuwidth) -fore $fcolor -back $bcolor
    Write-Host ""
    Write-debug "L: $l MenuItems: $menuItems MenuPosition: $menuposition"
    for ($i = 0; $i -le $l;$i++) {
        Write-Host "`t" -NoNewLine
        if ($i -eq $menuPosition) {
            Write-Host "$($menuItems[$i])" -fore $bcolor -back $fcolor
        } else {
            Write-Host "$($menuItems[$i])" -fore $fcolor -back $bcolor
        }
    }
}

function Menu {
    ## Generate a small "DOS-like" menu.
    ## Choose a menuitem using up and down arrows, select by pressing ENTER
    param ([array]$menuItems, $menuTitel = "MENU")
    $vkeycode = 0
    $pos = 0
    DrawMenu $menuItems $pos $menuTitel
    While ($vkeycode -ne 13) {
        $press = $host.ui.rawui.readkey("NoEcho,IncludeKeyDown")
        $vkeycode = $press.virtualkeycode
        Write-host "$($press.character)" -NoNewLine
        If ($vkeycode -eq 38) {$pos--}
        If ($vkeycode -eq 40) {$pos++}
        if ($pos -lt 0) {$pos = 0}
        if ($pos -ge $menuItems.length) {$pos = $menuItems.length -1}
        DrawMenu $menuItems $pos $menuTitel
    }
    Write-Output $($menuItems[$pos])
}

Example:

$bad = "Format c:","Send spam to boss","Truncate database *","Randomize user password","Download dilbert","Hack local AD"
$selection = Menu $bad "WHAT DO YOU WANNA DO?"
Write-Host "YOU SELECTED : $selection ... DONE!`n"

Another Example:


$options = "Dir","Ping", "Ipconfig"
$selection = Menu $options "CHOOSE YOUR COMMAND:"
Switch ($selection) {
    "Dir" {Invoke-Expression "Dir C:\";break}
    "Ping" {Invoke-Expression "Ping 127.0.0.1";break}
    "Ipconfig" {Invoke-Expression "Ipconfig";break}
}

Trapping errors in Powershell

I just stumbled across this sweet little introduction to trapping errors in PowerShell:
http://powershell.com/cs/blogs/tobias/archive/2008/09/29/trapping-errors.aspx

Retrieveing the Windows Product Key

I was recently asked if I knew of a tool capable of retrieving the Product Key from an installed instance of Windows Vista. While there are several such tools available (use google), my solution - naturally - was to write a PowerShell script that can do just that.

The script below is based on information from "The Custodian" on Everything2: http://everything2.com/node/1058074

I've tested the function against Windows XP, Vista x64 and Windows 7 x64, as well as Windows Server 2003 and Windows Home Server. As long at the the user executing the script has sufficient privileges, and no firewall is blocking remote access to the registry/WMI the script appears to work fine.

function Get-WindowsKey {
    ## function to retrieve the Windows Product Key from any PC
    ## by Jakob Bindslet (jakob@bindslet.dk)
    param ($targets = ".")
    $hklm = 2147483650
    $regPath = "Software\Microsoft\Windows NT\CurrentVersion"
    $regValue = "DigitalProductId"
    Foreach ($target in $targets) {
        $productKey = $null
        $win32os = $null
        $wmi = [WMIClass]"\\$target\root\default:stdRegProv"
        $data = $wmi.GetBinaryValue($hklm,$regPath,$regValue)
        $binArray = ($data.uValue)[52..66]
        $charsArray = "B","C","D","F","G","H","J","K","M","P","Q","R","T","V","W","X","Y","2","3","4","6","7","8","9"
        ## decrypt base24 encoded binary data
        For ($i = 24; $i -ge 0; $i--) {
            $k = 0
            For ($j = 14; $j -ge 0; $j--) {
                $k = $k * 256 -bxor $binArray[$j]
                $binArray[$j] = [math]::truncate($k / 24)
                $k = $k % 24
            }
            $productKey = $charsArray[$k] + $productKey
            If (($i % 5 -eq 0) -and ($i -ne 0)) {
                $productKey = "-" + $productKey
            }
        }
        $win32os = Get-WmiObject Win32_OperatingSystem -computer $target
        $obj = New-Object Object
        $obj | Add-Member Noteproperty Computer -value $target
        $obj | Add-Member Noteproperty Caption -value $win32os.Caption
        $obj | Add-Member Noteproperty CSDVersion -value $win32os.CSDVersion
        $obj | Add-Member Noteproperty OSArch -value $win32os.OSArchitecture
        $obj | Add-Member Noteproperty BuildNumber -value $win32os.BuildNumber
        $obj | Add-Member Noteproperty RegisteredTo -value $win32os.RegisteredUser
        $obj | Add-Member Noteproperty ProductID -value $win32os.SerialNumber
        $obj | Add-Member Noteproperty ProductKey -value $productkey
        $obj
    }
}

Use the function to retrieve the Product Key from the local PC:

Get-WindowsKey

Or to retrieve the Product Key from one or more PCs (locally or remotely):

Get-WindowsKey "pc1", "pc2", "server999", "server777"

Remeber that the output from the function is a standard PowerShell object, so you can pipe into sort-object, format-table or mayby ConvertTo-HTML ...

T-SQL query with object-based result

Two small functions that can be used to make an T-SQL query, and have the result returned as a standard PowerShell object. This can be useful if - for some reason - you would like to use Sort-Object or similar on the result afterwards.

function ConnectSQL {
    Param ($server, $query, $database)
    $conn = new-object ('System.Data.SqlClient.SqlConnection')
    $connString = "Server=$server;Integrated Security=SSPI;Database=$database"
    $conn.ConnectionString = $connString
    $conn.Open()
    $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $sqlCmd.CommandText = $query
    $sqlCmd.Connection = $conn
    $Rset = $sqlCmd.ExecuteReader()
    ,$Rset ## The comma is used to create an outer array, which PS strips off automatically when returning the $Rset
}

function QuerySQL {
    Param ($server, $query, $database = "master")
    $data = ConnectSQL $server $query $database
    while ($data.read() -eq $true) {
        $max = $data.FieldCount -1
        $obj = New-Object Object
        For ($i = 0; $i -le $max; $i++) {
            $name = $data.GetName($i)
            $obj | Add-Member Noteproperty $name -value $data.GetValue($i)
     }
     $obj
    }
}

The function is used like this:

QuerySQL "MyServer" "SELECT * FROM sysdatabases"

Or maybe more like this:

$data = QuerySQL "MyServer" "SELECT * FROM sysdatabases"
$data | Format-Table -AutoSize

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.

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.

PS
This blog is now listed at PowerShellComunity.org

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
        $DMOconn.connect("$server\$instance")
        if ($DMOconn.versionstring -ne $NULL) {
            $port = $DMOconn.Registry.tcpport
        } else {
            $port = -2
        }
        $DMOconn.close()
    }
    if ($port -lt 1) { $port = -2 }
    $obj | Add-Member Noteproperty Port -value $port
    $obj
    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
$result

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.

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=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

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

[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")

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) {
        $conn.connectioncontext.disconnect()
    } else {
        $conn = new-object('Microsoft.SqlServer.Management.Smo.Server') $server
    }
    $conn.connectioncontext.applicationName = $applicationName
    $conn.connectioncontext.connectTimeout = $commandTimeout
    $conn.connectioncontext.connect()
    $conn
}

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:

$SMOconn.Information.version

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

Sort-Random

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

Usage:

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