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