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!