Collecting computer/server inventory

A while back I wrote this handy little function for collecting inventory from the computers in my household. Somehow I never got around to posting it on my blog, for which I apologize ;-)

The function uses WMI to collect the following information from computers running windows:

Hostname and description
Serial number
Operations system
Uptime
Hardware: Mainboard, BIOS version, CPU, ram size and speed, local disks, GPU, NIC names and speed.

PowerShell function:


Function Get-Inventory {
    Param (
        [array]$computerName = '.'
    )
    $currentDate = (Get-Date).ToShortDateString()
    Foreach ($c in $computerName) {
        Try {
            $computerSystem = Get-WmiObject -ClassName Win32_ComputerSystem -Computername $c -Property Name, Domain -ErrorAction Stop
            $fqdn = "$($computerSystem.Name).$($computerSystem.Domain)"
            $mainboard = Get-WmiObject -ClassName Win32_Baseboard -Computername $c
            $bios = Get-WmiObject -ClassName Win32_BIOS -Computername $c
            $cpu = Get-WmiObject -ClassName Win32_Processor -Computername $c
            $ram = Get-WmiObject -ClassName Win32_PhysicalMemory -Computername $c -Property Capacity, Speed
            $ramSize = $ram | Measure-Object Capacity -sum | Select-Object -ExpandProperty sum
            $ramSize = "$([math]::round($ramSize / 1gb +0.5, 0)) GB"
            $ramSpeedMin = $ram | Measure-Object Speed -min | Select-Object -ExpandProperty minimum
            $ramSpeedMax = $ram | Measure-Object Speed -max | Select-Object -ExpandProperty maximum
            If ($ramSpeedMin -eq $ramSpeedMax) {
                $ramSpeed = "($ramSpeedMin MHz)"
            } Else {
                $ramSpeed = "($ramSpeedMin - $ramSpeedMax MHz)"
            }
            $ramInfo = "$ramSize $ramSpeed"
            $gpu = Get-WmiObject -ClassName Win32_VideoController -Computername $c
            $disks = Get-WmiObject -ClassName Win32_DiskDrive -computername $c
            $diskArray = foreach ($disk in $disks) {
                $size = [math]::round($disk.size / 1000000000,0)
                $name = $disk.caption
                "$name ($size GB)"
            }
            $nics = Get-WmiObject -ClassName Win32_NetworkAdapter -ComputerName $c -Filter 'Manufacturer != "Microsoft" or Netenabled = "true"' -Property Description, Speed
            $nicArray = Foreach ($nic in $nics) {
                If ($nic.Speed -ge 1000000000) {
                    $nicSpeed = "$($nic.Speed / 1000000000) Gb/s"
                } Elseif ($nic.Speed -gt 0) {
                    $nicSpeed = "$($nic.Speed / 1000000) Mb/s"
                } Else {
                    $nicSpeed = "Speed Unknown"
                }
                "$($nic.Description) ($nicSpeed)"
            }
            $os = Get-WmiObject Win32_operatingsystem -Computername $c
            $uptime = (Get-Date) - [Management.ManagementDateTimeConverter]::ToDateTime($os.LastBootUpTime)
            $uptimeString = "$($uptime.days) days, $($uptime.hours):$($uptime.minutes):$($uptime.seconds)"
            ## Create Output
            $obj = New-Object Object
            $obj | Add-Member Noteproperty Computername -value $fqdn
            $obj | Add-Member Noteproperty Description -value $os.description
            $obj | Add-Member Noteproperty CollectDate -value $currentDate
            $obj | Add-Member Noteproperty SerialNumber -value $bios.SerialNumber
            $obj | Add-Member Noteproperty Motherboard -value "$($mainboard.manufacturer) ($($mainboard.product))"
            $obj | Add-Member Noteproperty BIOSversion -value $bios.SMBIOSBIOSVersion
            $obj | Add-Member Noteproperty CPU -value $($cpu.name -replace ' {2,}','')
            $obj | Add-Member Noteproperty Memory -value $ramInfo
            $obj | Add-Member Noteproperty GPU -value $gpu.caption
            $obj | Add-Member Noteproperty HDD -value ($diskArray -join ', ')
            $obj | Add-Member Noteproperty NIC -value ($nicArray -join ', ')
            $obj | Add-Member Noteproperty OS -value "$($os.caption)$($os.csdversion) ($($os.osarchitecture))"
            $obj | Add-Member Noteproperty Uptime -value $uptimeString
            $obj
        } Catch {
            Write-Host "Connection to $c failed" -Foreground red
        }
    }
}

Examples of how to use the function against a single computer:

Get-Inventory myComputer

Several computers:

Get-Inventory myComputer1, myComputer2, myServerA

Getting the output in gridview:

Get-Inventory myComputer1, myComputer2, myServerA | Out-GridView

Saving the output to a .csv file for easy opening in excel:

Get-Inventory myComputer1, myComputer2, myServerA | ConvertTo-Csv -Delimiter ';' -NoTypeInformation | Out-File -FilePath inventory.csv -Encoding utf8 -Append

Executing tasks in parallel in PowerShell

Let's pretend that we need to write a script that can test to see if 10 different webpages are available.
While this could be accomplished by retrieving the webpages one at a time, it does take some time to do this. Often something like 0.5 to 2 seconds for each page.

Now imagine that you needed to test 50 pages or 100 - that would take quite a while...
 
The following function "Run-Threaded" allows the same unit of work (scriptblock) to be executed against several targets in parallel.


Function Run-Threaded {
    Param ($scriptBlock, $targets, $throttleLimit = 4)
    ## Initial setup of runspace with default sessionState/host
    $sessionState = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
    $runspacePool = [runspacefactory]::CreateRunspacePool(1, $throttleLimit, $sessionState, $host)
    $runspacePool.Open()
    ## create empty array to keep track of the threads
    $threads = @()
    ## Prepare the threads, one for each target
    $handles = foreach ($target in $targets) {
        $powershell = [powershell]::Create().AddScript($scriptBlock).AddArgument($target)
        $powershell.RunspacePool = $runspacePool
        $powershell.BeginInvoke()
        $threads += $powershell
    }
    ## Throttling - make sure no more than $throttleLimit threads run at once
    $output = $null
    do {
        $i = 0
        $finished = $true
        foreach ($handle in $handles) {
            if ($handle -ne $null) {
                if ($handle.IsCompleted) {
                    $result = $threads[$i].EndInvoke($handle)
                    $threads[$i].Dispose()
                    $handles[$i] = $null
                    $output += $result
                } else {
                    $finished = $false
                }
            }
            $i++
        }
        if (-not $finished) { Start-Sleep -Milliseconds 150 }
    } until ($finished)
    $output
    ## cleanup
    $runspacePool.Close()
}

The following small piece of code ($myScriptBlock), tries to download whatever is located at he address http://$target.
As output it then creates a PowerShell Object, that contains the URL, the download time in seconds and the size of the page downloaded in bytes.



$myScriptBlock = {
    Param ($target)
    $downloadTime = (Measure-Command {$webPage = (new-object net.webclient).DownloadString("http://$target")}).totalSeconds
    $downloadSize = $webpage.length
    $obj = New-Object Object
$obj | Add-Member NoteProperty URL -value $target
    $obj | Add-Member NoteProperty 'DownloadTimeInSeconds' -value $downloadTime
    $obj | Add-Member Noteproperty 'SizeOfPageDownloadedInBytes' -value $downloadSize
    $obj
}

Using the few lines of code below, together with the scriptblock and funtion above, it is possible to execute the dowload of all the pages listed in the array $targets in parallel, and retrieve and display the results, as well as the elapsed time:


$targets = "www.microsoft.com", "www.cnn.com", "www.ebay.com", "www.slashdot.org", "www.eb.dk", "www.bbc.com", "www.apple.com", "www.bing.com", "www.lego.com", "www.htc.com", "www.samsung.com", "www.megabloks.com"
$time = (Measure-Command {$result = Run-Threaded -scriptBlock $myScriptBlock -targets $targets 20}).totalSeconds
$result | Format-Table -AutoSize

$sumTime = ($result | Measure-Object -Sum DownloadTimeInSeconds).sum
Write-Host "Total runtime: $time seconds" -fore green
Write-Host "Sum of runtime: $sumTime seconds" -fore cyan

The output should look something like this:

URL               DownloadTimeInSeconds SizeOfPageDownloadedInBytes
---               --------------------- ---------------------------
www.microsoft.com             0,3908426                        1020
www.apple.com                 0,1885553                       11248
www.bbc.com                   0,4792315                      114846
www.htc.com                   0,4955355                       10127
www.samsung.com               0,3469335                       71112
www.eb.dk                     1,0516336                      263063
www.megabloks.com             0,6676591                       38997
www.bing.com                  1,0628155                       30023
www.lego.com                  0,9899592                       31447
www.cnn.com                   2,2071444                      118118
www.ebay.com                   2,750856                       93185
www.slashdot.org              3,4376653                      100297

Total runtime: 3.8353139 seconds
Sum of runtime: 14.0688315 seconds 

The code example can easily be adapted to collect the status from 100 databases or 1000 servers, in very little time. However, please note that this happens at the expense of CPU and RAM usage.

An alternative to the function above, would be to use the Start/sSop/Get/Wait-Job Cmdlets, native to PowerShell. Unfortunately those Cmdlets are sometimes too slow and too resource heavy, but they do offer a simple interface and additional possibilities.

Windows Process Affinity

Yesterday I had a new challenge: to change the Processor Affinity through PowerShell.

Processor Affinity in another term for "what cores can my process use". By limiting a CPU hungry process to just use some of the available cores, it is possible to let other processes run more smoothly.

This can easily be accomplished by right-clicking a process in TaskManager, but can it be done by a script?

The answer is ... Yes.

The result was  this small PowerShell function:

Function Set-Affinity {

                             ## id can be either a PID or a processname

                             ## affinity is a bitmask, with the first core being 1, the next 2, then 4, 8, 16 etc.

                             ## To use core number 1,2 and 4 the affinity must be set to 7 (1 + 2 + 4)

                             Param (

                                                          $id = $pid,

                                                          $affinity = 1

                             )

                             If (($id.gettype()).name -eq "int32") {

                                                          $processes = Get-Process -id $id

                             } ElseIf (($id.gettype()).name -eq "String") {

                                                          $processes = Get-Process -name "$id"

                             }                          

                             foreach ($p in $processes) {

                                                          $p | Add-Member Noteproperty OldProcessorAffinity -value $p.ProcessorAffinity

                                                          $p.ProcessorAffinity = $affinity

                                                          $p | Select Id, ProcessName, CPU, OldProcessorAffinity, ProcessorAffinity

                             }

}  

Example (setting the affinity of all "PowerShell" processes to 1 - ie. 1 core maximum):

Set-Affinity PowerShell 1
           

Get-LocalDB

My first attempt at creating LocalDB specific PowerShell code, is a function to extract a list of LocalDB instances. This is not very exciting code, PowerShell-wise, but I wanted to share it anyways:

 Function Get-LocalDB {
    Param()
    $ldb_version = Invoke-Expression "sqllocaldb v"
    if ($ldb_version -notlike "*(11.0*)") {
        throw "Unsupported version of SQL LocalDB : $ldb_version"
    }
    [array]$ldb_instances = Invoke-Expression "sqllocaldb i"
    foreach ($ldb_instance in $ldb_instances) {
        $ldb_instanceDetails = Invoke-Expression "sqllocaldb i '$ldb_instance'"
        $obj = New-Object Object
        foreach ($line in $ldb_instanceDetails) {
            switch -wildcard ($line) {
                "Name:*"                {$obj | Add-Member Noteproperty Name -value $line.padright(21).remove(0,20)}
                "Version:*"                {$obj | Add-Member Noteproperty Version -value $line.padright(21).remove(0,20)}
                "Shared name:*"            {$obj | Add-Member Noteproperty SharedName -value $line.padright(21).remove(0,20)}
                "Owner:*"                {$obj | Add-Member Noteproperty Owner -value $line.padright(21).remove(0,20)}
                "Auto-create:*"            {$obj | Add-Member Noteproperty AutoCreate -value $line.padright(21).remove(0,20)}
                "State:*"                {$obj | Add-Member Noteproperty State -value $line.padright(21).remove(0,20)}
                "Last start time:*"        {$obj | Add-Member Noteproperty LastStartTime -value $line.padright(21).remove(0,20)}
                "Instance pipe name:*"    {$obj | Add-Member Noteproperty InstancePipeName -value $line.padright(21).remove(0,20)}
                Default                 {}
            }
        }
        $obj
    }
}

Before we rty it out, lets create a couple of extra LocalDB instances, using the sqllocaldb.exe tool:

sqllocaldb create "MySecretInstance"

sqllocaldb create "Some Things are Fishy #3"

Then start the first one:

sqllocaldb start "MySecretInstance"

Calling the function, and piping the output into a format-table -autoformat, now results in the following:

Get-LocalDB | ft -au

 Name                     Version     SharedName Owner       AutoCreate State   LastStartTime       InstancePipeName
----                     -------     ---------- -----       ---------- -----   -------------       ----------------
MySecretInstance         11.0.2318.0            Ares\Jagoop No         Running 26-07-2012 13:51:06 np:\\.\pipe\LOCALDB#66BC3610\tsql\query
Some Things are Fishy #3 11.0.2318.0            Ares\Jagoop No         Stopped 26-07-2012 13:49:21
v11.0                    11.0.2318.0            Ares\Jagoop Yes        Running 26-07-2012 11:22:11 np:\\.\pipe\LOCALDB#822B23E5\tsql\query

SQL Server 2012 LocalDB

I've been playing around with the new LocalDB edition of SQL Server 2012 (read about it here).

In short LocalDB is an edition of SQL Server 2012, comparable in features with express edition, but running as a user process not a service. LocalDB is targeted developers, but I could think of a few other uses for it.


LocalDB automatically creates an instance called "v11.0" - the full name of this instance is "(localDB)\v11.0". Connecting to this instance from PowerShell can be a bit of a challenge, since LocalDB is only fully supported by DOTNET 4.0.3 and above. This means that PowerShell can't work with it "out-of-the-box", except through named pipes (but who really uses those anymore? :-) )

Trying to use QuerySQL (a script I put on the blog back in 2009) will only result in an error, as the connection cannot be made.


QuerySQL "(localDB)\v11.0" "SELECT * FROM sysfiles"

As mentioned before, this can be solved by the use of named pipes. To discover current name* of the pipe, use the following command:

SQLlocalDB i "v11.0"

The last line in the output contains the current name* of the named pipe, which can then be used for the QuerySQL:

QuerySQL "np:\\.\pipe\LOCALDB#822B23E5\tsql\query" "SELECT * FROM sysfiles"

* Please not that the named pipe does not have a static name. Restart the computer, and the name will change.


Another way of making PowerShell and LocalDB play nice is to make PowerShell aware of DOTNET 4.0.3. This can be done by creating a file called “powershell.exe.config” in the C:\Windows\System32\WindowsPowerShell\v1.0 . The file should contain the following:

<?xml version="1.0"?> 

<configuration> 

    <startup useLegacyV2RuntimeActivationPolicy="true"> 

        <supportedRuntime version="v4.0.30319"/> 

        <supportedRuntime version="v2.0.50727"/> 

    </startup> 

</configuration> 

Be aware that this not an officially supported way of using PowerShell, so it might break other stuff ...

After starting a new PowerShell, the earlier command should work just fine:

QuerySQL "(localDB)\v11.0" "SELECT * FROM sysfiles"

Start remote process through WMI

There are many ways to execute a process remotely on a Windows based computer. It is possible use Remote Desktop (RDP/MSTSC) to log onto the desktop of the computer, but this is slow and not easily automated.
You can use the excellent  Sysinternals tools PSEXEC, provided by Microsoft or you can use PowerShell 2.0's support for WinRM.

While all of the options above might be good in any specific situation, I would like to share an alternative : using WMI to initiate a remote process.

The small function below, allows you to initiate a process on a remote computer, and - should you want to - wait for its completion. The function does not allow you to return the result of the process, but it is possible to direct the output to a local file, and access that file afterwards through some other mean.

Function NewProcessWMI {
    Param (
        $target = ".",
        $command = "dir",
        [switch]$WaitForCompletion
    )
    $cmdResult = ([WmiClass]"\\$target\ROOT\CIMV2:Win32_Process").create($command)
    Switch ($cmdresult.returnvalue) {
        0 {$resultTxt = "Successful"}
        2 {$resultTxt = "Access denied"}
        3 {$resultTxt = "Insufficient privilege"}
        8 {$resultTxt = "Unknown failure"}
        9 {$resultTxt = "Path not found"}
        21 {$resultTxt = "Invalid parameter"}
        default {$resultTxt = "Unhandled error"}
    }
    $processId = $cmdresult.processId
    $processStatus = "unknown"
    if ($WaitForCompletion) {
        $wait = $true
        While ($wait) {
            Start-Sleep -Milliseconds 250
            $test = Get-WmiObject -query "select * from Win32_Process Where ProcessId='$processId'"
            if ((Measure-Object -InputObject $test).count -eq 0) {
                $wait = $false
            }
        }
        $processStatus = "completed"
    }
    $obj = New-Object Object
    $obj | Add-Member Noteproperty Target -value $target
    $obj | Add-Member Noteproperty Command -value $command
    $obj | Add-Member Noteproperty Result -value $($cmdresult.returnvalue)
    $obj | Add-Member Noteproperty ProcessStart -value $resultTxt
    $obj | Add-Member Noteproperty ProcessId -value $processId
    $obj | Add-Member Noteproperty ProcessStatus -value $processStatus
    $obj
}

The function can be used as follows:


NewProcessWMI "." "cmd /c ipconfig" -WaitForCompletion | ft -au
NewProcessWMI "." "cmd /c dir c:\users\*.* /s" -WaitForCompletion | ft –au


To direct the output to a file:

NewProcessWMI "." "cmd /c ipconfig > C:\ipconfigResult.txt" -WaitForCompletion | ft -au


You must have some way of reading the C:\ipconfigResults.txt afterwards, as this script (and WMI in general) doesn't provide such capabilities.

SQL Server Product Key - update

In the past, I've posted a few scripts to extract the Windows and/or SQL Server license keys.
Since these posts have been some of the most popular* posts on my blog, I've decided to update those scripts, and make them more compatible with newer version of Windows and SQL Server.

* I've received more than 100 emails and comments about just those scripts.

The script below has been tested against the following versions:
          Windows Server 2003
          Windows Server 2008
          Windows Server 2008 R2
          Windows Server 2012 RC
          SQL Server 2005
          SQL Server 2008
          SQL Server 2008 R2
          SQL Server 2012


Function SQLbuild2name {
    Param ($build)   
    Switch -wildcard ($build) {
        "8.0*"    { $name = "SQL Server 2000"}
        "9.0*"    { $name = "SQL Server 2005"}
        "10.0*"    { $name = "SQL Server 2008"}
        "10.5*"    { $name = "SQL Server 2008R2"}
        "11.0*"    { $name = "SQL Server 2012"}
        Default    { $name = "Unknown version"}
    }
    $name
}

Function Get-SQLserverKey {
    ## function to retrieve the Windows Product Key from any PC as well as the SQL Server Product Key
    ## by Jakob Bindslet (jakob@bindslet.dk), last updated 2012-07-14
    Param ($targets = ".")
    $hklm = 2147483650
    $regPath2005 = "SOFTWARE\Microsoft\Microsoft SQL Server\90\ProductID"
    $regPath2005version = "SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion"
    $regPath2008 = "SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup"
    $regPath2012 = "SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup"
    $regValue2005 = "DigitalProductId77591"
    $regValue2008 = "DigitalProductId"
    $regValue2012 = "DigitalProductId"
    $regValueVersion2005 = "CurrentVersion"
    $regValueVersion2008 = "Version"
    $regValueVersion2012 = "Version"
    $SQLinstalled = $false
    Foreach ($target in $targets) {
        $productKey = $null
        $win32os = $null
        $sqlversion = $null
        $wmi = [WMIClass]"\\$target\root\default:stdRegProv"
        $data2005 = $wmi.GetBinaryValue($hklm,$regPath2005,$regValue2005)
        $data2008 = $wmi.GetBinaryValue($hklm,$regPath2008,$regValue2008)
        $data2012 = $wmi.GetBinaryValue($hklm,$regPath2012,$regValue2012)
        $productKey = ""
        if ($data2005.uValue.length -ge 1) {
            $binArray2005 = ($data2005.uValue)[52..66]
            $binArray = $binArray2005
            $sqlversion = $wmi.GetStringValue($hklm,$regPath2005version,$regValueVersion2005).sValue
            $SQLinstalled = $true
        }
        if ($data2008.uValue.length -ge 1) {
            $binArray2008 = ($data2008.uValue)[52..66]
            $binArray = $binArray2008
            $sqlversion = $wmi.GetStringValue($hklm,$regPath2008,$regValueVersion2008).sValue
            $SQLinstalled = $true
        }
        if ($data2012.uValue.length -ge 1) {
            $binArray2012 = ($data2012.uValue)
            $binArray = $binArray2012
            $sqlversion = $wmi.GetStringValue($hklm,$regPath2012,$regValueVersion2012).sValue
            $SQLinstalled = $true
           }

        if ($SQLinstalled) {
            ## decrypt base24 encoded binary data
            $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"
            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
                }
            }
        } else {
            $productKey = "no SQL Server found"
        }
        $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 (Windows)" -value $win32os.SerialNumber
        $obj | Add-Member Noteproperty "SQL Version" -value "$(SQLbuild2name $sqlversion) ($sqlversion)"
        $obj | Add-Member Noteproperty "ProductKey (SQL Server)" -value $productkey
        $obj
    }
}




Usage:


Get-SQLserverKey computername



Or if you just need to target the local computer:

Get-SQLserverKey

Creating a Firewall opening using Powershell

Here is a quick solution to the challenge of creating a firewall opening on a Windows 7 or Windows 2008R2 server (should work on different version from Vista and above too ...).

Several settings are hardcoded, and others only support a limited number of settings (the function only supports TCP and UDP openings), but it can easily be extended if needed.

Function New-FirewallRule {

    Param(

        $name = "unnamed rule",

        $description = "made by PowerShell",

        $port = 666,

        $appName = $null,

        $serviceName = $null,

        [switch]$udp,

        [switch]$allprofiles

    )

    $firewallPolicy = New-Object -ComObject hnetcfg.fwpolicy2

    $firewallRule = New-Object -ComObject HNetCfg.FWRule

    if ($allprofiles) {

        $CurrentProfiles = 7 ## All profiles

    } else {

        $CurrentProfiles = $firewallPolicy.CurrentProfileTypes

    }

    if ($udp) {

        $protocol = 17 ## NET_FW_IP_PROTOCOL_UDP = 17

    } else {

        $protocol = 6 ## NET_FW_IP_PROTOCOL_TCP = 6

    }

    $firewallRule.name = $name

    $firewallRule.description = $description

    $firewallRule.protocol = $protocol

    $firewallRule.enabled = $true

    $firewallRule.localPorts = $port

    $firewallRule.action = 1  ## NET_FW_ACTION_ALLOW

    $firewallRule.grouping = "@firewallapi.dll,-23255"

    $firewallRule.profiles = $CurrentProfiles

    $firewallPolicy.Rules.Add($firewallRule)

}

To use the function to create a couple of SQL Server openings:

New-FirewallRule "SQL Server (1433/TCP)" "Made by Jakob" -Port 1433 -allprofiles

New-FirewallRule "SQL Server (1434/UDP)" "Made by Jakob" -Port 1434 -allprofiles -udp

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!