James O'Neill's Blog

April 10, 2020

Transformers for PowerShell parameters which take secrets.

Filed under: Uncategorized — jamesone111 @ 11:17 am

PowerShell Functions often need to accept secure strings as or PowerShell credentials (which contain secure strings) as parameters.  There are people who will advise avoiding use of secure string completely, which isn’t really practical, but one shouldn’t credit Secure String with magic powers it doesn’t have.

  1. It is encoded so that your account on the current machine can decode it at will – any process running under your account can get to the secret.
  2. Another account, or your account on another machine can’t decode it. The main use is securing something in a file on one machine.
  3. In PowerShell 7 (but not PowerShell Core 6 or Windows PowerShell 5) the ConvertFrom-SecureString cmdlet has an -AsPlainText switch which gets the secret back. With older version the standard process is to create a credential object and then ask for the plaintext “network password”.
  4. It is only securing the credential at rest in the file. At some stage the plain text will be in memory (and malware can find it).

A script which looks like the following is obviously bad

$MyUserName = "James"

$MyPassword = ConvertTo-SecureString -force -asPlainText "NotVerySecret"

$Credential = New-Object PSCredential $myUserName,$MyPassword

Better to do

if (Test-path mycred.xml)  {

       $Credential = Import-Clixml myCred.xml


else {

       $Credential = Get-Credential

       $Credential | export-CliXML my.cred.xml


In the xml file you might see something like this


<S N="UserName">james</S>

<SS N="Password">01000000d08c9ddf011 … 395d4060</SS>


<S> Denotes a normal string and <ss> a long sequence of digits which become a secure string. There are ways use different keys, and DSC uses one of them to encrypt passwords when making putting them in a .MOF file, which service can then decode, but this simple method uses a personal, local key.

Credentials and secure strings are also useful for things like personal access tokens or API Keys where you might prefer not to save the plain text, but the commands which use them expect plain text here’s a simple case

Function Demo {





    "Your API key is $ApiKey"


Because [string] acts as “Convert what you are given to a string”, the function won’t refuse a secure string or a credential – it outputs

Your API key is System.Security.SecureString or Your API key is System.Management.Automation.PSCredential

My usual answer to this that $APIkey shouldn’t be typed and the body of the function should include a check the type of $APIKey and convert it as required; this approach has worked well for me as long as I have been doing PowerShell but there is an option to do it more elegantly.

One Question which came up on GitHub recently went like this:

Some commands take an API key use it as a plain text string so they have a string-typed parameter, but it would be useful to have a parameter attribute which allowed a string declared as a string to accept and convert credential or securestring objects.

PowerShell 5 and upwards can support argument transformers, and and the one below is named “UnSecureString”, it’s declared as an argument transformation attribute, with a single method, transform, which receives the parameter as “Input data” and returns an object. To work on version 5, it converts a credential to a password, and if it is given a secure string, it converts to it to a credential first.

class UnSecureString : System.Management.Automation.ArgumentTransformationAttribute  {

    [object] Transform([System.Management.Automation.EngineIntrinsics]$EngineIntrinsics, [object] $InputData) {

        if ($InputData -is [securestring]) {

            $InputData =  New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList 'Placeholder', $InputData


        if ($InputData -is  [pscredential]) {

            $InputData =  $InputData.GetNetworkCredential().password


        return ($InputData)



With this in place the parameter declaration in the function changes: 

Function Demo {





    "Your API key is $ApiKey"


And now it will take string, secure String, or credential

>demo "api12345key"

Your API key is api12345key

>$ss = ConvertTo-SecureString -AsPlainText "api34567key" -Force

>demo $ss

Your API key is api34567key

>$cred = New-Object pscredential "NoRealName", $ss

>demo $cred

Your API key is api34567key

It’s a debatable whether the parameter should still be typed in this case. In another github discussion, someone said they didn’t like to leave the untyped because on-line help shows parameter types and leaving them as the default, [object] skipped a chance to guide the user to provide the right thing. Since PowerShell lets us specify full paths and relative paths as strings, path info objects, or file info objects to identify a file we want to work on, I’ve always tried to accept make a “-Widget” parameter accept a unique widget ID, a Widget Name (possibly with wildcards), or one or more Object(s), the user shouldn’t need to care and I will write help explaining the three options. Without the help I’d be making the user guess.  The help for this example steers the user towards providing a plain text string




    Demo [[-ApiKey] <string>]

My preference is more towards removing the type and assuming the user will read more of the help, but I don’t think these things are absolutes. As supporting older versions of PowerShell becomes less of an issue, using classes and shifting the “first make sure this is the right kind of thing” code can simplify the function body, which is a plus. 
There is a case for changing the parameter type to secure string and having a transformer which turns plain strings to secure ones. But that can lead to writing

Demo –apikey (convertTo-SecureString ….) ,  
which is the horrible insecure method I showed at the start but more annoying to a user – especially if they look inside the function and see the first thing that happens is the secure string is converted back an placed in a request body. Examples should never show that, but should be based on the secure string coming from a file.

April 2, 2020

Including PowerShell classes in modules: a quick round-up

Filed under: Powershell,Uncategorized — jamesone111 @ 5:47 pm
Tags: , ,

Recently I have been adding PowerShell classes to modules and this post is what I’ve learned about where the class code should be located and how different ways of loading it have odd looking side effects. At some point I’ll make a list of people who should get some credit for educating me, because I would not have discovered all of it on my one.

From the start PowerShell has been able to use the Add-Type command to compile a type written in C# (or visual basic) and load it into the PowerShell session like any other class. Windows PowerShell 5 added the ability to code classes in PowerShell and there’s little if any change version 6 and 7. However there are some quite unusual behaviours when they are used in modules, and which don’t seem well documented. The following applies to the classes written in PowerShell (not the ones loaded with Add-Type)

  1. A PowerShell class is only visible outside its own module if either.

    a. It is loaded in the PSM1 file (not dot sourced into the PSM1) AND the module is loaded with the using module directive. OR

    b. It is loaded from a PS1 file using the ScriptsToProcess section of the PSD1 manifest.

  2. If a module is loaded by a script which specifies Using module, classes from its PSM1 are only visible in the script’s scope (inside a ps1, unless the file is Dot sourced.)

    The same scope issues/rules apply if the class is loaded from ScriptsToProcess and the module is loaded with Import-module

  3. If a class used as a parameter attribute in a function is not visible in the scope where that function is called it cannot be abbreviated by dropping the Attribute from its name.

    In other words, a parameter attribute which has been shortened from [ValidateWibbleAttribute()] to [ValidateWibble ()] may cause failures if the module is loaded in the wrong way. [2] above should make it clear that sooner or later any module will be loaded the wrong way.

Based on this

  1. My view of a .PSM1 file as a small “loader” – which I think is a common one – needs to at least be revised enough to allow for Classes to be included in it.

    I’m still not keen on having everything in the PSM1 and will probably continue to keep functions in their own PS1 files, but functions can be successfully dot sourced into the PSM1 and classes cannot.
  2. Using module X is preferable to Import-Module X, where the module has some requirement which means it won’t run on legacy Windows PowerShell.

    In other words,V4 and earlier are still out in the field – and for modules which work with those versions you might prefer import to using. However V4 won’t import a module with classes so with modules which need V5/6/7 sticking to Import-Module doesn’t gain anything and makes classes invisible

  3. I’m no longer trimming ‘attribute’ off the names of parameter attribute class names when declaring parameters. I’m not sure at the moment if there is any reason to use attribute in the class name (it appears not).

June 22, 2019

Last time I saw this many shells, someone sold them by the sea shore.

Filed under: Azure / Cloud Services,Linux / Open Source,Powershell,Uncategorized — jamesone111 @ 10:04 pm

I’ve been experimenting with lots of different combinations of shells on Windows 10.

imageBASH.  I avoided the Subsystem for Linux on Windows 10 for a while. There are only two steps to set it up – adding the Subsystem, and adding your chosen Linux to it. If the the idea of installing Linux into Windows, but not as a virtual machine, and getting it from the Windows store gives you a headache, you’re not alone, this may help or it make things worse. I go back to the first versions of Windows NT which had a Windows-16 on Windows-32 subsystem (WoW, which was linked to the Virtual Dos Machine – 32-bit Windows 10 can still install these), an OS/2 subsystem, and then a Posix subsystem. Subsystems translated APIs so binaries intended for a different OS could run on NT, but kernel functions (drivers, file-systems, memory management, networking, process scheduling) – remained the responsibility of underlying OS. 25 years on, the Subsystem for Linux arrives in two parts – the Windows bits to support all the different Linuxes , and then distributor-supplied bits to make it look like Ubuntu 18.4 (which is what I have) or Suse or whichever distribution you chose. wslconfig.exe will tell you which distro(s) you have and change the active one. There is a generic launcher wsl.exe which will launch any Linux binary in the subsystem so you can run wsl bash but a Windows executable, bash.exe streamlines the process

imageLinux has a view of Windows’ files (C: is auto-mounted at/mnt/c and the mount command will mount other Windows filesystems including network and removable drives) but there is strongly worded advice not to touch Linux’s files via their location on C: – see here for more details. – Just before publishing this I updated the 1903 release of Windows 10 which adds a proper access which you can see in the screen shot 
Subsystem processes aren’t isolated – although a Linux API call might have a restricted view of the system. For example ps only sees processes in the subsystem but if you start two instances of bash, they’re both in the subsystem they can both see each other and running kill in one will terminate the other. The subsystem can run a Windows binary (like net.exe start which will see Windows services) and pipe its output into an Linux one, like less;  those who prefer some Linux tools get to use them in their management of Windows.
The subsystem isn’t read-only – anything which changes in that filesystem stays changed – since the subsystem starts configured for US Locale,
sudo locale-gen en_GB.UTF-8 and sudo update-locale LANG=en_GB.UTF-8 got me to a British locale. 

Being writable meant I could install PowerShell core for Linux into the subsystem: I just followed the instructions (including running sudo apt-get update and sudo apt-get upgrade powershell to update from 6.1 to 6.2). Now I can test whether things which work in Windows PowerShell (V5), also work with PowerShell Core (V6) on different platforms.  I can tell the Windows Subsystem for Linux to go straight into PowerShell with  wsl pwsh (or wsl pwsh –nologo if I’m at a command line already). Like bash it can start Windows and Linux binaries and the “in-the-Linux-subsystem” limitations still hold. Get-Process asks about processes in the subsystem , not the wider OS. Most PowerShell commands are there; some familiar aliases overlap with Linux commands and most of those have been removed (so | sort will send something to the Linux sort, not to sort-object,  and ps is not the alias for get-process;  kill and CD are exceptions to this rule.). Some common environment variables (Temp, TMP, UserProfile, ComputerName) are not present on Linux, and Windows specific cmdlets, like Get-Service,  don’t exist in the Linux world, and tab expansion switches to Unix style by default but you can set either environment to match the other. My PowerShell Profile soon gained a Set-PsReadlineOption command to give me the tab expansion I expect and it sets a couple of environment variables which I know some of my scripts use.  It’s possible (and tempting) to create some PSDrives which map single letters to places on /mnt, but things like to revert back to the Linux path. After that V6 core is the same on both platforms

PowerShell on Linux has remoting over SSH; it connects to another instance of PowerShell 6 running in what SSH also terms a “subsystem”. Windows PowerShell (up to 5.1) uses WinRM as its transport and PowerShell Core (6) on Windows can use both. For now at least, options like constrained endpoints (and hence “Just Enough Admin”  or JEA), are only in WinRM.
The instructions for setting up OpenSSH are here; I spent a very frustrating time editing the wrong config file – there is one in with the program files, and my brain filtered out the instruction which said edit the sshd_config file in C:\Program Data\ssh. I edited the one in the wrong directory and could make an SSH session into Windows (a useful thing to know to prove Open SSH is accepting connections) but every attempt to create a PowerShell session gave the error
New-PSSession : [localhost] The background process reported an error with the following message: The SSH client session has ended with error message: subsystem request failed on channel 0.
When I (finally) edited the right file I could connect to it from both Windows and Linux versions of PowerShell core with New-PSSession -HostName localhost.  (Using –HostName instead of –Computername tells the command “This is an SSH host, not a WinRM one”). It always amazes me how people, especially but not exclusively those who spend a lot of time with Linux, are willing to re-enter a password again and again and again. I’ve always thought it was axiomatic that a well designed security system granted or refused access to many things without asking the user to re-authenticate for each (or “If I have to enter my password once more, I’ll want the so-called ‘Security architect’ fired”). So within 5 minutes I was itching to get SSH to sign in with a certificate and not demand my password.

image I found some help here, but not all the steps are needed. Running the ssh-keygen.exe utility which comes with OpenSSH builds the necessary files – I let it save the files to the default location and left the passphrase for the file blank, so it was just a case of hitting enter for each prompt. For a trivial environment like this I was able to copy the id_rsa.pub file to a new file named authorized_keys in the same folder, but in a more real world case you’d copy and paste each new public key file into authorized_keys, then I could test a Windows to Windows remoting session over SSH. When that worked I copied the .ssh directory to my home directory in the Subsystem for Linux, and the same command worked again.

imagePowerShell Core V6 is built on .NET core, so some parts of PowerShell 5 have gone missing: there’s no Out-Grid, or Show-Command, No Out-Printer (I wrote a replacement), no WMI commands, no commands to work with the event log, no transactions and no tools to manage the computer’s relationship with AD.  The  Microsoft.* modules provide about 312 commands in V5.1 and about 244 of those are available in V6; but nearly 70 do things which don’t make sense in the Linux world because they work with WinRM/WSMan, Windows security or Windows services. A few things like renaming the computer, stopping and restarting it, or changing the time zone need to be done with native Linux tools. But we have just over 194 core cmdlets on all platforms, and more in pre-installed modules. There was a also a big step forward with compatibility in PowerShell 6.1 and another with 6.2 – there is a support for a lot more of the Windows API, so although some things don’t work in Core a lot more does than at first release. It may be necessary to specify the explicit path to the module (the different versions use either “…\WindowsPowerShell\…” or “..\PowerShell\…” in their paths and Windows tools typically install their modules for Windows PowerShell) or to use Import-Module in V6 with the –SkipEditionCheck switch. Relatively few stubbornly refuse to work, and there is a solution for them: remotely run the commands that otherwise are unavailable – instead of going over SSH this time you use WinRM, (V5 doesn’t support SSH) When I started working with constrained endpoints I found I liked the idea of not needing to install modules everywhere and running their commands remotely instead, once you have a PSSession to the place where the commands exist, you can use Get-Module and Import-Module with a –PsSession switch, to make them available. So we can bridge between versions – “the place where the commands exist” is “another version of PowerShell on the same machine” it’s all the same to remoting. The PowerShell team have announced that the next release uses .Net core 3.0 which should mean the return of Out-Gridview (eventually), and other home brew tools to put GUI interfaces onto PowerShell; that’s enough of a change to  bump the major version number, and they will drop “Core” from the name to try to remove the impression that it is a poor relation on Windows. The PowerShell team have a script to do a side by side install of the preview – or even the daily builds – Thomas Lee wrote it up here. Preview 1 seems to have done the important but invisible work of changing .Net version; new commands will come later; but at the time of writing PowerShell 7 preview has parity with PowerShell Core 6, and the goal is parity with Windows PowerShell 5

There is no ISE in PowerShell 6/7, Visual Studio Code had some real annoyances but pretty well all of them have been fixed for some months now and somewhere I joined the majority who see it as the future. Having a Git client built-in has made collaborating on the ImportExcel module so much easier, and that got me to embrace it . Code wasn’t built specifically for PowerShell which means it will work with whichever version(s) it finds.  
imageThe right of the status bar looks like this and clicking the green bit pulls up a menu where you can swap between versions and test what you are writing in each one. These swaps close one instance of PowerShell and open another so you know you’re in a clean environment (not always true with the ISE); the flip side is you realise it is a clean environment when you want something which was loaded in the shell in the shell I’ve just swapped away from.
VS Code’s architecture of extensions means it can pull all kinds of clever tricks – like remote editing –and the Azure plug in allows an Azure Cloud Shell to be started inside the IDE. imageWhen you use Cloud Shell in a browser it has nice easy ways to transfer files; but you can discover the UNC path to your cloud drive with Get-cloudDrive  then , Get-AzStorageAccount will show you a list of accounts, you can work out the name of the account from the UNC path and you use this as the user name to logon but you also need to know the resource group it is in, and Get-AzStorageAccount shows that. Armed with the name and resource group  Get-AzStorageAccountKey gives you one or more keys which can be used as a password, and you can map a drive letter to the cloud drive.

Surely that’s enough shells for one post … well not quite. People have been getting excited about the new Windows Terminal which is went into preview in the Windows store a few hours before I posted this Before that you needed to enable developer options on Windows and build it for yourself. It needs the 1903 Windows update and with that freshly installed I thought “I’ve also got [full] Visual Studio on this machine, why not build and play with Terminal”. As it turns out I needed to add the latest Windows SDK and several gigabytes of options to Visual Studio (all described on the github page), but with that done it was one git command to download the files, another to get submodules, then open visual studio, select the right section per the instructions and say build me an X64 release, have a coffee … and the app appears. (In the limited time I’ve spent with version in store it looks to be the same as the build-your-own version).

imageIt’s nice, despite being early code (no settings menu, just a json file of settings to change)., It’s the first time time Microsoft have put out a replacement for the host which Window uses for command line tools – shells or otherwise, so you could run ssh, ftp, or a tool like netsh in it.  I’ve yet to find a way to have “as admin” and normal processes running in one instance. It didn’t take long for me to add PowerShell on Linux and PowerShell 7 preview to the default choices (it’s easy to copy/paste/adjust the json – just remember to change the guid when adding an new choice, and you can specify the path to a PNG file to use as an icon).
So, in a single window, I have all the shells, except for 32-bit PowerShell 5, as tabs:  CMD, three different, 64-bit versions of PowerShell on Windows, PowerShell on WSL, BASH on WSL, and PowerShell on Linux in Azure.
I must give a shout out to Scott Hanselman for the last one; I was thinking “there must be a way to do what VS code does” and from his post Scott thought down the same lines a little while before me. He hooked up with others working on it and shared the results. I use a 2 line batch file with title and azshell.exe (I’m not sure when “title” crept into CMD, but I’m fairly sure it wasn’t always there. I’ve used it to keep the tab narrow for CMD: to set the tab names for each of the PowerShell versions I set $Host.UI.RawUI.WindowTitle  which even works with from WSL) [UPDATED 3 Aug. Terminal 0.3 has just been releases with an Azure option which starts the cloud shell, but only in its bash incarnation. AzShell.exe can support a choice of shell by specifying –shell pwsh or –shell bash ] 
So I get 7 Shells, 8 if I added the 32 bit version of PowerShell. Running them in the traditional host would give me 16 possible shells. Add the 32 and 64 bit PowerShell ISEs and VS code with Cloud shell and 3 Versions of local PowerShell, and we’re up to 22. And finally there is Azure cloud shell in a browser, or , if you must, the azure phone app, so I get to an nice round two dozen shells in all without ssh’ing into other machines (yes terminal can run ssh) , using any of the alternate Linux shells with WSL or loading all the options VS code has. “Just type the following command line” is not as simple as it used to be.

March 20, 2019

PowerShell Text wrangling [not just] part 4 of the Graph API series

Filed under: Uncategorized — jamesone111 @ 10:00 am

Almost the first line of PowerShell I ever saw was combining two strings like this
"{0} {1}"  -f $x , $y
And my reaction was “What !! If the syntax to concatenate two strings is so opaque this might not be for me”
[Edit as if to prove the awkwardness of the syntax, the initial post had two errors in such a short fragment. Thanks Doug.]
The –f operator is a wrapper for .NETs [String]::format and it is useful, partly for inserting strings into another string. For example I might define a SQL Statement in one place like this:
"Insert Into Users [GivenName], [Surname], [endDate] Values ('{0}', '{1}','{2}') "
and later I can get a ready-to-run query, using  $sqlInsert -f $first,$last,$end  
Doing this lets me arrange a script with long strings placed away from the logic; I’m less happy with this:  

@"Update Users
Set[endDate] = '{0}'
where {1} = '{2}'
And   {3} = '{4}'
"@ -f $end,$fieldName1,$value1,$fieldName2,$value    
because the string is there, my brain automatically goes back and forth filling in what should be in {0}, {1} and {2}, so I’d prefer to put $first, $Last and $end inside one string, or move the string out of sight. A string format operator is there to apply formatting and going over some downloaded code –f let me change this :
$now = Get-Date
$d = $now.Day.ToString()
if ($d.Length -eq 1) {$d ="0$d"}
$m = $now.month
if ($m.Length -eq 1) {$m ="0$m"}
$y = $now.Year
$logMessage = "Run on $d/$m/$y"  

To this:  
$now = Get-Date
= "Run on {0:d}" –f $now

For years my OneNote notebook has had a page which I lifted from the now-defunct blog of Kathy Kam (which you may still find re-posts of) which explains what the formatting strings are. In this case :d is “local short date” which is better than hard coding a date format; formatting strings used in Excel generally work, but there are some extra single-character formats like :g for general date/time, and :D for long date. If you live somewhere that puts the least significant part of the date in the middle then you might ask ‘Why not use  "Run on $now" ?”    
The 10th day of March 2019 outputs “Run on 03/10/2019 13:48:32” –in most of the world that means “3rd day of October”. But we could use
"Run on $($now.ToString('d'))"
And most people who use PowerShell will have used the $() syntax to evaluate the property of a variable embedded in a string.  But you can put a lot inside $(), this example will give you a list of days:
"Days are $(0..6 | foreach {"`r`n" + $now.AddDays($_).ToString('dddd')})"
The quote marks inside the $() don’t end the string  and  what is being evaluated can run over multiple lines like this
"Days are $(0..6 | foreach {
       "`r`n" +
} )"

Again, there are places where this I have found this technique to be useful, but encountering it in an unfamiliar piece of script means it takes me a few seconds to see that "`r`n" is a string, inside a code block, inside a string, in my script. I might use @" … "@  , which I think was once required for multi-line strings, instead of "…" which certainly works now, but leaves me looking for the closing quote – which isn’t the next quote. If the first part of the string was set and then a loop added days to the string that would be easier to follow. Incidentally when I talk of “an unfamiliar piece of script ” I don’t just mean other peoples work, I include work I did long enough ago that I don’t remember it.

Embedding in a string, concatenating multiple strings, or using –f  might all work, so which one is best in a given situation varies (sometimes it is shortest code that is easiest to understand and other things are clearer spread over a few lines) and the choice often comes down to personal coding style.
When working on my Graph API module I needed to send JSON like this (from the Microsoft Documentation) to create a group :

  "description": "Group with designated owner and members",
  "displayName": "Operations group",
  "groupTypes": [
  "mailEnabled": true,
  "mailNickname": "operations2019",
  "securityEnabled": false,
  "owners@odata.bind": [
  "members@odata.bind": [

This might be done as a large string with embedded variables, and even a couple of embedded for loops like the previous example, or to I could build the text up a few lines at a time. Eventually I settled on doing it like this.
$settings = @{'displayName'     = $Name
              'mailNickname'    = $MailNickName
              'mailEnabled'     = $true
              'securityEnabled' = $false
              'visibility'      = $Visibility.ToLower()
              'groupTypes'      = @('Unified')
if ($Description) {$settings['description']        = $Description  }
if ($Members)     {$settings['members@odata.bind'] = @() + $Members}
if ($Owners)      {$settings['owners@odata.bind']  = @() + $Owners }

$json = ConvertTo-Json $settings
Write-Debug $json
$group = Invoke-RestMethod @webparams -body $json

ConvertTo-Json only processes two levels of hierarchy by default so when the Hash table has more layers it needs the –DEPTH parameter to translate properly. Why do it this way? JSON says ‘here is something (or a collection of things) with a name’, so why say that in PowerShell-speak only to translate it? Partly it’s keeping to the philosophy of only translating into text at last moment; partly it’s getting rid of the mental context-switching – this is script, this is text with script-like bits . Partly it is to make getting things right easier than getting things wrong: if things are built up a few lines at a time,  I need to remember that ‘Unified’ should be quoted, but as a Boolean value ‘false’ should not, I need to track unclosed quotes and brackets, to make sure commas are where they are needed and nowhere else: in short, every edit is a chance to turn valid JSON into something generates a “Bad Request” message – so everywhere I generate JSON I have Write-Debug $Json. But any syntax errors in that hash table will be highlighted as I edit it.
And partly… When it comes to parsing text, I’ve been there and got the T-Shirts; better code than mine is available, built-in with PowerShell; I’d like apply the same logic to creating such text: I want to save as much effort as I can between “I have these parameters/variables” and “this data came back”. That was the thinking behind writing my GetSQL module: I know how to connect to a database and can write fairly sophisticated queries, but why keep writing variations of the same few simple ones, and the connection to send them to the database? SQL statements have the same “context switch” – if I type “–eq” instead of “=” in a query it’s not because I’ve forgotten the SQL I learned decades ago. Get-SQL lets me keep my brain in PowerShell mode and write.  
Get-SQL –Update LogTable –set Progess –value 100 –where ID –eq $currentItem

My perspective – centred on the script that calls the API rather than the API or its transport components – isn’t the only way. Some people prize the skill of handwriting descriptions of things in JSON. A recent project had me using DSC for bare-metal builds (I need need to parse MOF files to construct test scripts, and I could hand crank MOF files, but why go through that pain? ); DSC configuration functions take a configuration data parameter which is a hash holding all the details of all the machines. This was huge. When work started it was natural to create a PowerShell variable holding the data but when it became hundreds of lines I moved that data to its own file but it remained a series of declarations which could be executed – this is the code which did that

Get-ChildItem -Path (Join-Path -Path $scriptPath -ChildPath "*.config.ps1") | ForEach-Object {
    Write-Verbose -Message "Adding config info from $($_.name)"
    ConfigurationData.allNodes += (& $_.FullName )

– there was no decision to store data as PowerShell declarations it just happened as a accident of how the development unfolded, and there were people working on that project who found JSON easier to read (we could have used any format which supports a hierarchy). So I added something to put files through ConvertFrom-JSon and convert the result from a PSCustomObject to a hash table so they could express the data in the way which seemed natural to them.

Does this mean data should always be shifted out of the script ? Even that answer is “it depends” and is influenced by personal style. The examples which Doug Finke wrote for the ImportExcel module often start like this:

$data = ConvertFrom-Csv @'
Item,Quantity,Price,Total Cost
Baseball Bats,38,159.00,6042.00

Which is simultaneously good and bad. It is placed at the the start of the file not sandwiched between lumps of code, we can see that it is data for later and what the columns are, and it is only one per row of data where JSON would be 6 lines. But csv gives errors a hiding place – a mistyped price, or an extra commas is hard to see. But that doesn’t matter in this case. But we wouldn’t mash together the string being converted from other data… would we ?

December 12, 2017

Using the import Excel Module: Part 3, Pivots and charts, data and calculations

Filed under: Uncategorized — jamesone111 @ 4:43 pm

In the previous post I showed how you could export data to an XLSx file using the Export-Excel command in Doug Finke’s ImportExcel module (Install it from the PowerShell gallery!). The command supports the creation of Pivot tables and Pivot charts. Picking up from where part 2 left off, I can get data about running processes, export them to a worksheet and then set up a pivot table

$mydata = Get-Process | Select-Object -Property Name, WS, CPU, Description, Company, StartTime
$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
   -IncludePivotTable -PivotRows "Company" -PivotData @{"WS"="Sum"} -show


To add a pivot chart the command line becomes
$mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
-IncludePivotTable -PivotRows "Company" -PivotData @{"WS"="Sum"} `
-IncludePivotChart -ChartType Pie -ShowPercent -show


The chart types are also suggested by intellisense, note that some of them don’t support -ShowPercent or ‑ShowCategory options and “bad” combinations will result in an error on opening Excel. Re-creating existing Pivot charts can cause an error as well.

There is an alternative way of creating Pivot tables and charts –which is particularly useful when we want more than one in the same workbook

del .\demo.xlsx

$xl = $mydata | Export-Excel -Path .\demo.xlsx -WorkSheetname "Processes" -PassThru

$Pt1 = New-PivotTableDefinition -PivotTableName "WS"  -PivotData @{"WS" ="Sum"} -SourceWorkSheet "Processes" `
          -PivotRows Company -IncludePivotChart -ChartType ColumnClustered -NoLegend
$Pt2 = New-PivotTableDefinition -PivotTableName "CPU" -PivotData @{"CPU"="Sum"} -SourceWorkSheet "Processes" `
          -PivotRows Company -IncludePivotChart -ChartType ColumnClustered -NoLegend

$xl = Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -PivotTableDefinition $Pt1 -PassThru
Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -PivotTableDefinition $Pt2 -Show


New-PivotTableDefinition builds the table definition as a hash table – we could equally well write a large hash table with multiple pivots defined in it, like this

del .\demo.xlsx

$mydata | Export-Excel -Path .\demo.xlsx -WorkSheetname "Processes" -Show -PivotTableDefinition @{
    "WS" = @{"SourceWorkSheet"   = "Processes"      ;
             "PivotRows"         = "Company"        ;
             "PivotData"         = @{"WS"="Sum"}    ;
             "IncludePivotChart" = $true            ;
             "ChartType"         = "ColumnClustered";
             "NoLegend"          = $true};
   "CPU" = @{"SourceWorkSheet"   = "Processes"      ;
             "PivotRows"         = "Company"        ;
             "PivotData"         = @{"CPU"="Sum"}   ;
             "IncludePivotChart" = $true            ;
             "ChartType"         = "ColumnClustered";
             "NoLegend"          = $true }

Export-Excel allows [non-pivot] charts to be defined and passed as a parameter in similar same way -in the following example we’re going to query a database for a list of the most successful racing drivers, the SQL for the query looks like this:
$Sql = "SELECT TOP 25 WinningDriver, Count(RaceDate) AS Wins
        FROM   races 
        GROUP  BY WinningDriver  
        ORDER  BY count(raceDate) DESC"

Then we define the chart and feed the result of the query into Export-Excel (I’m using my GetSQL module from the PowerShell Gallery for this but there are multiple ways )
$chartDef = New-ExcelChart -Title "Race Wins" -ChartType ColumnClustered
               -XRange WinningDriver -YRange Wins -Width 1500 -NoLegend -Column 3

Get-SQL $Sql | Select-Object -property winningDriver, Wins |
  Export-Excel -path .\demo2.xlsx -AutoSize -AutoNameRange -ExcelChartDefinition $chartDef -Show

The important thing here is that the chart definition refers to named ranges in the spreadsheet – “Winning Driver” and “Wins” and the Export-Excel command is run with –AutoNameRanges so the first column of is a range named “Winning Driver” and the second “Wins” – you can see in the screen shot “Wins” has been selected in the “Name” box (underneath the File menu) and the data in the Wins column is selected. The chart doesn’t need a legend and positioned the right of column 3


I found that he EEPLUS object which Doug uses can insert a Data table object directly into a worksheet, which should be more efficient, and it also saves using a select-object command to remove the database housekeeping properties which are in every row of data as I had to do in the example above. It didn’t take much to a command to Doug’s module to put SQL data into a spreadsheet without having to pipe the data into Export-Excel from another command. And I cheated by passing the resulting object through to Export-Excel so that I could use parameters found in Export-Excel and pass the Worksheet object and parameters on and get Export-Excel to finish the job so I write something like this:
Send-SQLDataToExcel -SQL $sql -Session $session -path .\demo2.xlsx -WorkSheetname "Winners" `
        -AutoSize  -AutoNameRange -ExcelChartDefinition $chartDef -Show

In this example I use an existing session with a database – the online help shows you how to use different connection strings with ODBC or the SQL Server native client. 

I also added commands to set values along a row or down a column – for an example we can expand the racing data to not just how many wins, but also how many fastest laps and how many pole positions, export this data and use the -Passthrough switch to get an Excel Package object back
$SQL = "SELECT top 25 DriverName,         Count(RaceDate) as Races ,
                      Count(Win) as Wins, Count(Pole)     as Poles,
                      Count(FastestLap) as Fastlaps
        FROM  Results
        GROUP BY DriverName
        ORDER BY (count(win)) desc"

$Excel = Send-SQLDataToExcel -SQL $sql -Session $session -path .\demo3.xlsx `
            -WorkSheetname "Winners" -AutoSize -AutoNameRange -Passthru

Having done this, we can add columns to calculate the ratios of two pairs of existing columns

$ws = $Excel.Workbook.Worksheets["Winners"]
Set-Row    -Worksheet $ws -Heading "Average"     -Value {"=Average($columnName`2:$columnName$endrow)"}
              -NumberFormat "0.0" -Bold
Set-Column -Worksheet $ws -Heading "WinsToPoles" -Value {"=D$row/C$row"} -Column 6 -AutoSize -AutoNameRange
Set-Column -Worksheet $ws -Heading "WinsToFast"  -Value {"=E$row/C$row"} -Column 7 -AutoSize -AutoNameRange
Set-Format -WorkSheet $ws -Range "F2:G50" -NumberFormat "0.0%"

In the examples above the value parameter is a Script block, when this is evaluated $row and $column are available so if the value is being inserted in row 5, {"=E$row/C$row"} becomes =E5/C5
The script block can use $row , $column (current row and column numbers) $columnName (current column letter), $StartRow/$EndRow $StartColumn/$EndColumn (column and row numbers)

If the value begins with “=” it is treated as a formula rather than a value; we don’t normally want to put in a fixed formula – without the “=” the value inserted down the column or across the row will be a constant

The set-Column command supports range naming, and both commands support formatting – or we can use the ‑PassThru switch and pipe the results of setting the column into Set-Format. There seems to be a bug in the underlying library where applying number formatting to column after formatting a row applies the same formatting to the column and to the row from the previous operation. So, the example above uses a third way to apply the format which is to specify the range of cells in Set-Format.
Finally we can output this data, and make use of the names given to the newly added columns in a new chart.

$chart = New-ExcelChart -NoLegend -ChartType XYScatter -XRange WinsToFast -YRange WinsToPoles `
           -Column 7 -Width 2000 -Height 700

Export-Excel -ExcelPackage $Excel -WorkSheetname "Winners"-Show -AutoSize -AutoNameRange `
         -ExcelChartDefinition $chart


So there you have it; PowerShell objects or SQL data goes in – possibly over multiple sheets; headings and filters get added, panes arranged: extra calculated rows and columns are inserted , and formatting applied, pivot tables and charts inserted – and if Excel itself is available you can export them. No doubt someone will ask before too long if I get the the charts out of Excel and into PowerPoint slides ready for a management meeting … And since the all of this only works with XLSX files, not legacy XLS ones there might me another post soon about reading those files.

July 24, 2017

An extra for my PowerShell profile–Elevate

Filed under: Uncategorized — jamesone111 @ 7:15 pm

More than usual, in the last few days I’ve found myself starting PowerShell or the ISE only to find I wanted a session as administrator : it’s a common enough thing but eventually I said ENOUGH!  I’d seen “-verb runas” to start an executable as administrator , so I added this to my profile.

Function Elevate        {
    Runs an instance of the current program As Administrator

    Start-Process (Get-Process -id $PID).path -verb runas

June 2, 2017

On writing understandable scripts.

Filed under: Uncategorized — jamesone111 @ 7:20 pm


At two conferences recently I gave a talk on “What makes a good PowerShell module”  (revisiting an earlier talk) the psconf.eu guys have posted a video of it and I’ve made the slides available (the version in US used the same slide deck with a different template). .

One of the my points was Prefer the familiar way to the clever way. A lot of us like the brilliant PowerShell one-liner (I belong to the “We don’t need no stinking variables” school and will happily pipe huge chains of commands together). But sometimes breaking it into multiple commands means that when you return it later or someone new picks up what you have done, it is easier to understand what is happening.  There are plenty of other examples, but generally clever might be opaque ; opaque needs comments and somewhere I picked up that what applies to jokes, applies to programming: if you have to explain it, it isn’t that good.

Sometimes, someone doesn’t now the way which is familiar to everyone else, and they throw in something like this example which I used in the talk:
Set-Variable -Scope 1 -Name "variableName" -Value $($variableName +1)
I can’t recall ever using Set-Variable, and why would someone use it to to set a variable to its current value + 1? The key must be in the -scope parameter, –scope 1 means “the parent scope” , most people would write $Script:VariableName ++ or $Global:VariableName ++ When we encounter something like this, unravelling what Set-Variable is doing interrupts the flow of understanding … we have to go back and say “so what was happening when that variable was set …” 

There are lots of cases where there are multiple ways to do something some are easier to understand but aren’t automatically the one we pick: all the following appear to do the same job

"The value is " + $variable
"The value is " + $variable.ToString()
"The value is $variable"
"The value is {0}" -f $variable
"The value is " -replace "$",$variable

You might see .ToString() and say “that’s thinking like a C# programmer” … but if $variable holds a date and the local culture isn’t US the first two examples will produce different results (to string will use local cultural settings) .
If you work a lot with the –f operator , you might use {0:d} to say “insert the first item in ‘short date’ format for the local culture” and naturally write
“File {0} is {1} bytes in size and was changed on {2}” –f $variable.Name,$variable.Length,$variable.LastWriteTime
Because the eye has to jump back and forth along the line to figure out what goes into {0} and then into {1} and so on, this loses on readability compared concatenating the parts with + signs, it also assumes the next person to look at the script has the same familiarity with the –f operator. I can hear old hands saying “Anyone competent with PowerShell should be familiar with –f” but who said the person trying to understand your script meets your definition of competence.   
As someone who does a lot of stuff with regular expressions, I might be tempted by the last one … but replacing the “end of string” marker ($) to as a way of appending excludes people who aren’t happy with regex.  I’m working on something which auto-generates code at the moment and it uses this because the source that it reads doesn’t provide a way of specifying “append”, but has “replace”. I will let it slide in this case but being bothered by it is a sign that I do ask myself “are you showing you’re clever or writing something that can be worked on later”.  Sometimes the only practical way is hard, but if there is a way which takes an extra minute to write and pays back when looking at the code in a few months time.   

November 30, 2016

Powershell Piped Parameter Peculiarities (and a Palliative pattern!)

Filed under: Uncategorized — jamesone111 @ 7:33 am

Writing some notes before sharing a PowerShell module,  I did a quick fact check and rediscovered a hiccup with piped parameters and (eventually) remembered writing a simplified script to show the problem – 3 years ago as it turns out. The script appears below: it has four parameter sets and all it does is tell us which parameter set was selected: There are four parameters: A is in all 4 sets, B is in Sets 2,3 and 4, C is only in 3 and D is only in set 4. I’m not really a fan of parameter sets but they help intellisense to remove choices which don’t apply. 

function test { 
param (  [parameter(Position=0, ValueFromPipeLine=$true)]
         [parameter(ParameterSetName="PS3", Mandatory)]
         [parameter(ParameterSetName="PS4", Mandatory)]

So lets check out what comes back for different parameter combinations
> test  1

No parameters or parameter A only gives the default parameter set. Without parameter C or D it can’t be set 3 or 4, and with no parameter B it isn’t set 2 either.

> test 1 -b 2
Parameters A & B or parameter B only gives parameter set 2, – having parameter B it must be set 2,3 or 4 and but 3 & 4 can be eliminated because C and D are missing. 

> test 1 -b 2 –c 3 

Parameter C means it must be set 3 (and D means it must be set 4) ; so lets try piping the input for parameter A
> 1 | test 
> 1 | test  -b 2 -c 3

So far it’s as we’d expect.  But then something goes wrong.
> 1 | test  -b 2
Parameter set cannot be resolved using the specified named parameters

Eh ? If data is being piped in, PowerShell no longer infers a parameter set from the absent mandatory parameters.  Which seems like a bug. And I thought about it: why would piping something change what you can infer about a parameter not being on the command line? Could it be uncertainty whether values could come from properties the piped object ? I thought I’d try this hunch
   [parameter(ParameterSetName="PS3", Mandatory,ValueFromPipelineByPropertyName=$true)]
  [parameter(ParameterSetName="PS4", Mandatory,ValueFromPipelineByPropertyName=$true)]

This does the trick – though I don’t have a convincing reason why two places not providing the values works better than one – (in fact that initial hunch doesn’t seem to stand up to logic) . This (mostly) solves the problem– there could be some odd results if parameter D was named “length” or “path” or anything else commonly used as a property name. I also found in the “real” function that adding ValueFromPipelineByPropertyName to too many parameters – non mandatory ones – caused PowerShell to think a set had been selected and then complain that one of the mandatory values was missing from the piped object. So just adding it to every parameter isn’t the answer

July 1, 2016

Just enough admin and constrained endpoints. Part 2: Startup scripts

Filed under: Uncategorized — jamesone111 @ 1:36 pm

In part 1 I looked at endpoints and their role in building your own JEA solution, and said applying constraints to end points via a startup script did these things

  • Loads modules
  • Hides cmdlets, aliases and functions from the user.
  • Defines which scripts and external executables may be run
  • Defines proxy functions to wrap commands and modify their functionality
  • Sets the PowerShell language mode, to further limit the commands which can be run in a session, and prevent new ones being defined.

The endpoint is a PowerShell RunSpace running under its own user account (ideally a dedicated account) and applying the constraints means a user connecting to the endpoint can do only a carefully controlled set of things. There are multiple ways to set up an endpoint, I prefer to do it with using a start-up script, and below is the script I used in a recent talk on JEA. It covers all the points and works but being an example the scope is extremely limited :

$Script:AssumedUser  = $PSSenderInfo.UserInfo.Identity.name
if ($Script:AssumedUser) {
Write-EventLog -LogName Application -Source PSRemoteAdmin -EventId 1 -Message "$Script:AssumedUser, Started a remote Session"
Import-Module -Name PrintManagement -Function Get-Printer

#HIDE EVERYTHING. Then show the commands we need and add Minimum functions
if (-not $psise) { 
    Get-Command -CommandType Cmdlet,Filter,Function | ForEach-Object  {$_.Visibility = 'Private' }
    Get-Alias                                       | ForEach-Object  {$_.Visibility = 'Private' }
    #To show multiple commands put the name as a comma separated list 
    Get-Command -Name Get-Printer                   | ForEach-Object  {$_.Visibility = 'Public'  } 


    $RemoteServer =  [System.Management.Automation.Runspaces.InitialSessionState]::CreateRestricted(
    $RemoteServer.Commands.Where{($_.Visibility -eq 'public') -and ($_.CommandType -eq 'Function') } |
ForEach-Object {  Set-Item -path "Function:\$($_.Name)" -Value $_.Definition }

#region Add our functions and business logic
function Restart-Spooler {
    Restarts the Print Spooler service on the current Computer
    Restarts the spooler service, and logs who did it  

    Microsoft.PowerShell.Management\Restart-Service -Name "Spooler"
    Write-EventLog -LogName Application -Source PSRemoteAdmin -EventId 123 -Message "$Script:AssumedUser, restarted the spooler"
#Set the language mode
if (-not $psise) {$ExecutionContext.SessionState.LanguageMode = [System.Management.Automation.PSLanguageMode]::NoLanguage}

Any action taken from the endpoint will appear to be carried out by privileged Run As account, so the script needs to log the name of the user who connects runs commands. So the first few lines of the script get the name of the connected user and log the connection: I set-up PSRemoteAdmin as a source in the event log by running.  
New-EventLog -Source PSRemoteAdmin -LogName application

Then the script moves on to the first bullet point in the list at the start of this post: loading any modules required; for this example, I have loaded PrintManagement. To make doubly sure that I don’t give access to unintended commands, Import-Module is told to load only those that I know I need.

Private functions (and cmdlets and aliases)
The script hides the commands which we don’t want the user to have access to (we’ll assume everything). You can try the following in a fresh PowerShell Session (don’t use one with anything you want to keep!)

function jump {param ($path) Set-Location -Path $path }
(Get-Command set-location).Visibility = "Private"
cd \
This defines jump as a function which calls Set-Location – functionally it is the same as the alias CD; Next we can hide Set-location, and try to use CD but this returns an error
cd : The term 'Set-Location' is not recognized
But Jump \ works: making something private stops the user calling it from the command line but allows it to be called in a Function. To stop the user creating their own functions the script sets the language mode as its final step 

To allow me to test parts of the script, it doesn’t hide anything if it is running in the in the PowerShell ISE, so the blocks which change the available commands are wrapped in  if (-not $psise) {}. Away from the ISE the script hides internal commands first. You might think that Get-Command could return aliases to be hidden, but in practice this causes an error. Once everything has been made Private, the Script takes a list of commands, separated with commas and makes them public again (in my case there is only one command in the list). Note that script can see private commands and make them public, but at the PowerShell prompt you can’t see a private command so you can’t change it back to being public.

Hiding external commands comes next. If you examine $ExecutionContext.SessionState.Applications and $ExecutionContext.SessionState.Scripts you will see that they are both normally set to “*”, they can contain named scripts or applications or be empty. You can try the following in an expendable PowerShell session

ping localhost
ping : The term 'PING.EXE' is not recognized as the name of a cmdlet function, script file, or operable program.
PowerShell found PING.EXE but decided it wasn’t an operable program.  $ExecutionContext.SessionState.Applications.Add("C:\Windows\System32\PING.EXE") will enable ping, but nothing else.

So now the endpoint is looking pretty bare, it only has one available command – Get-Printer. We can’t get a list of commands, or exit the session, and in fact PowerShell looks for “Out-Default” which has also been hidden. This is a little too bare; we need to Add constrained versions of some essential commands;  while to steps to hide commands can be discovered inside PowerShell if you look hard enough, the steps to put in the essential commands need to come from documentation. In the script $RemoteServer gets definitions and creates Proxy functions for:


I’ve got a longer explanation of proxy functions here, the key thing is that if PowerShell has two commands with the same name, Aliases beat Functions, Functions beat Cmdlets, Cmdlets beat external scripts and programs. “Full” Proxy functions create a steppable pipeline to run a native cmdlet, and can add code at the begin stage, at each process stage for piped objects and at the end stage, but it’s possible to create much simpler functions to wrap a cmdlet and change the parameters it takes; either adding some which are used by logic inside the proxy function, removing some or applying extra validation rules. The proxy function PowerShell provides for Select-Object only supports two parameters: property and InputObject, and property only allows 11 pre-named properties. If a user-callable function defined for the endpoint needs to use the “real” Select-Object – it must call it with a fully qualified name: Microsoft.PowerShell.Utility\Select-Object (I tend to forget this, and since I didn’t load these proxies when testing in the ISE, I get reminded with a “bad parameter” error the first time I use the command from the endpoint).  In the same way, if the endpoint manages active directory and it creates a Proxy function for Get-ADUser, anything which needs the Get-ADUser cmdlet should specify the ActiveDirectory module as part of the command name.

By the end of the first if … {} block the basic environment is created. The next region defines functions for additional commands; these will fall mainly into two groups: proxy functions as I’ve just described and functions which I group under the heading of business logic. The end point I was creating had “Initialize-User” which would add a user to AD from a template, give them a mailbox, set their manager and other fields which appear in the directory, give them a phone number, enable them Skype-For-Business with Enterprise voice and set-up Exchange voice mail, all in one command. How many proxy and business logic commands there will be, and how complex they are both depend on the situation; and some commands – like Get-Printer in the example script – might not need to be wrapped in a proxy at all.
For the example I’ve created a Restart-Spooler command. I could have created a Proxy to wrap Restart-Service and only allowed a limited set of services to be restarted. Because I might still do that the function uses the fully qualified name of the hidden Restart-Service cmdlet, and I have also made sure the function writes information to the event log saying what happened. For a larger system I use a 3 digits where the first indicates the type of object impacted (1xx for users , 2xx for mailboxes and so on) and the next two what was done (x01 for Added , x02 for Changed a property).

The final step in the script is to set the language mode. There are four possible language modes Full Language is what we normally see; Constrained language limits calling methods and changing properties to certain allowed .net types, the MATH type isn’t specifically allowed, so [System.Math]::pi will return the value of pi, but [System.Math]::Pow(2,3) causes an error saying you can’t invoke that method, the SessionState type isn’t on the allowed list either so trying to change the language back will say “Property setting is only allowed on core types”. Restricted language doesn’t allow variables to be set and doesn’t allow access to members of an object (i.e. you can look at individual properties, call methods, or access individual members of an array), and certain variables (like $pid) are not accessible. No language stops us even reading variables 

Once the script is saved it is a question of connecting to the end point to test it. In part one I showed setting-up the end point like this
$cred = Get-Credential
Register-PSSessionConfiguration -Name "RemoteAdmin"       -RunAsCredential $cred `
-StartupScript 'C:\Program Files\WindowsPowerShell\EndPoint.ps1'
The start-up script will be read from the given path for each connection, so there is no need to do anything to the Session configuration when the script changes; as soon as the script is saved to the right place I can then get a new session connecting to the “RemoteAdmin” endpoint, and enter the session. Immediately the prompt suggests something isn’t normal:

$s = New-PSSession -ComputerName localhost -ConfigurationName RemoteAdmin
Enter-PSSession $s
[localhost]: PS>

PowerShell has a prompt function, which has been hidden. If I try some commands, I quickly see that the session has been constrained

[localhost]: PS> whoami
The term 'whoami.exe' is not recognized…

[localhost]: PS> $pid
The syntax is not supported by this runspace. This can occur if the runspace is in no-language mode...

[localhost]: PS> dir
The term 'dir' is not recognized ….

However the commands which should be present are present. Get-Command works and shows the others

[localhost]: PS> get-command
CommandType  Name                    Version    Source
-----------  ----                    -------    ------
Function     Exit-PSSession
Function     Get-Command
Function     Get-FormatData
Function     Get-Help
Function     Get-Printer                 1.1    PrintManagement                                                                                        
Function     Measure-Object
Function     Out-Default
Function     Restart-Spooler
Function     Select-Object

We can try the following to show how the Select-object cmdlet has been replaced with a proxy function with reduced functionality:
[localhost]: PS> get-printer | select-object -first 1
A parameter cannot be found that matches parameter name 'first'.

So it looks like all the things which need to be constrained are constrained, if the functions I want to deliver – Get-Printer and Restart-Spooler – if  work properly I can create a module using
Export-PSSession -Session $s -OutputModule 'C:\Program Files\WindowsPowerShell\Modules\remotePrinters' -AllowClobber -force
(I use -force and -allowClobber so that if the module files exist they are overwritten, and if the commands have already been imported they will be recreated.)  
Because PowerShell automatically loads modules (unless $PSModuleAutoloadingPreference tells it not to), saving the module to a folder listed in $psModulePath means a fresh PowerShell session can go straight to using a remote command;  the first command in a new session might look like this

C:\Users\James\Documents\windowsPowershell> restart-spooler
Creating a new session for implicit remoting of "Restart-Spooler" command...
WARNING: Waiting for service 'Print Spooler (Spooler)' to start...

The message about creating a new session comes from code generated by Export-PSSession which ensures there is always a session available to run the remote command. Get-PSSession will show the session and Remove-PSSession will close it. If a fix is made to the endpoint script which doesn’t change the functions which can be called or their parameters, then removing the session and running the command again will get a new session with the new script. The module is a set of proxies for calling the remote commands, so it only needs to change to support modifications to the commands and their parameters. You can edit the module to add enhancements of your own, and I’ve distributed an enhanced module to users rather than making them export their own. 

You might have noticed that the example script includes comment-based help – eventually there will be client-side tests for the script, written in pester, and following the logic I set out in Help=Spec=Test, the test will use any examples provided. When Export-PsSession creates the module, it includes help tags to redirect requests, so running Restart-Spooler –? locally requests help from the remote session; unfortunately requesting help relies on a existing session and won’t create a new one.

June 27, 2016

Technical Debt and the four most dangerous words for any project.

Filed under: Uncategorized — jamesone111 @ 9:15 am

I’ve been thinking about technical debt. I might have been trying to avoid the term when I wrote Don’t swallow the cat, or more likely I hadn’t heard it, but I was certainly describing it – to adapt Wikipedia’s definition it is the future work that arises when something that is easy to implement in the short run is used in preference to the best overall solution”. However it is not confined to software development as Wikipedia suggests.
“Future work” can come from bugs (either known, or yet to be uncovered because of inadequate testing), design kludges which are carried forward, dependencies on out of date software, documentation that was left unwritten… and much more besides.

The cause of technical debt is simple: People won’t say “I (or we) cannot deliver what you want, properly, when you expect it”.
“When you expect it” might be the end of a Scrum Sprint, a promised date or “right now”. We might be dealing with someone who asks so nicely that you can’t say “No” or the powerful ogre to whom you dare not say “No”. Or perhaps admitting “I thought I could deliver, but I was wrong” is too great a loss of face. There are many variations.

I’ve written before about “What you measure is what you get” (WYMIWIG) it’s also a factor. In IT we measure success by what we can see working. Before you ask “How else do you judge success?”, Technical debt is a way to cheat the measurement – things are seen to be working before all the work is done. To stretch the financial parallel, if we collect full payment without delivering in full, our accounts must cover the undelivered part – it is a liability like borrowing or unpaid invoices.

Imagine you have a deadline to deliver a feature. (Feature could be a piece of code, or an infrastructure service however small). Unforeseeable things have got in the way. You know the kind of things: the fires which apparently only you know how to extinguish, people who ask “Can I Borrow You”, but should know they are jeopardizing your ability to meet this deadline, and so on.
Then you find that doing your piece properly means fixing something that’s already in production. But doing that would make you miss the deadline (as it is you’re doing less testing than you’d like and documentation will have to be done after delivery). So you work around the unfixed problem and make the deadline. Well done!
Experience teaches us that making the deadline is rewarded, even if you leave a nasty surprise for whoever comes next – they must make the fix AND unpick your workaround. If they are up against a deadline they will be pushed to increase the debt. You can see how this ends up in a spiral: like all debt, unless it is paid down, it increases in future cycles.

The Quiet Crisis unfolding in Software Development has a warning to beware of high performers, they may excel at the measured things by cutting corners elsewhere. It also says watch out for misleading metrics – only counting “features delivered” means the highest performers may be leaving most problems in their wake. Not a good trait to favour when identifying prospective managers.

Sometimes we can say “We MUST fix this before doing anything else.”, but if that means the whole team (or worse its manager) can’t do the thing that gets rewarded then we learn that trying to complete the task properly can be unpopular, even career limiting. Which isn’t a call to do the wrong thing: some things can be delayed without a bigger cost in the future; and borrowing can open opportunities that refusing to ever take on any debt (technical or otherwise) would deny us. But when the culture doesn’t allow delivery plans to change, even in the face of excessive debt, it’s living beyond its means and debt will become a problem.

We praise delivering on-time and on-budget, but if capacity, deadline and deliverables are all fixed, only quality is variable. Project management methodologies are designed to make sure that all these factors can be varied and give project teams a route to follow if they need to vary by too great a margin. But a lot of work is undertaken without this kind of governance. Capacity is what can be delivered properly in a given time by the combination of people, skills, equipment and so on, each of which has a cost. Increasing headcount is only one way to add capacity, but if you accept adding people to a late project makes it later then it needs to be done early. When me must demonstrate delivery beyond our capacity, it is technical debt that covers the gap.

Forecasting is imprecise, but it is rare to start with plan we don’t have the capacity to deliver. I think another factor causes deadlines which were reasonable to end up creating technical debt.

The book The Phoenix Project has a gathered a lot of fans in the last couple of years, and one of its messages is that Unplanned work is the enemy of planned work. This time management piece separates Deep work (which gives satisfaction and takes thought, energy, time and concentration) from Shallow work (the little stuff). We can do more of value by eliminating shallow work and the Quiet Crisis article urges managers to limit interruptions and give people private workspaces, but some of each day will always be lost to email, helping colleagues and so on.

But Unplanned work is more than workplace noise. Some comes from Scope Creep, which I usually associate with poor specification, but unearthing technical debt expands the scope, forcing us to choose between more debt and late delivery. But if debt is out in the open then the effort to clear it – even partially – can be in-scope from the start.
Major incidents can’t be planned and leave no choice but to stop work and attend to them. But some diversions are neither noise, nor emergency. “Can I Borrow You?” came top in a list of most annoying office phrases and “CIBY” serves as an acronym for a class of diversions which start innocuously. These are the four dangerous words in the title.

The Phoenix Project begins with the protagonist being made CIO and briefed “Anything which takes focus away from Phoenix is unacceptable – that applies to whole company”. For most of the rest of the book things are taking that focus. He gets to contrast IT with manufacturing where a coordinator accepts or declines new work depending on whether it would jeopardize any existing commitments. Near the end he says to the CEO Are we even allowed to say no? Every time I’ve asked you to prioritize or defer work on a project, you’ve bitten my head off. …[we have become] compliant order takers, blindly marching down a doomed path”. And that resonates. Project steering boards (or similarly named committees) can to assign capacity to some projects and disappoint others. Without one – or if it is easy to circumvent – we end up trying to deliver everything and please everyone;  “No” and “What should I drop?” are answers, we don’t want to give especially to those who’ve achieved their positions by appearing to deliver everything, thanks to technical debt.

Generally, strategic tasks don’t compete to consume all available resources. People recognise these should have documents covering

  • What is it meant to do, and for whom? (the specification / high level design)
  • How does it do it? (Low level design, implementation plan, user and admin guides)
  • How do we know it does what it is meant to? (test plan)

But “CIBY” tasks are smaller, tactical things; they often lack specifications: we steal time for them from planned work assuming we’ll get them right first time, but change requests are inevitable. Without a spec, there can be no test plan: yet we make no allowance for fixing bugs. And the work “isn’t worth documenting”, so questions have to come back to the person who worked on it.  These tasks are bound to create technical debt of their own and they jeopardize existing commitments pushing us into more debt.

Optimistic assumptions aren’t confined to CIBY tasks. We assume strategic tasks will stay within their scope: we set completion dates using assumptions about capacity (the progress for each hour worked) and about the number of hours focused on the project each day. Optimism about capacity isn’t a new idea, but I think planning doesn’t allow for shallow / unplanned work – we work to a formula like this:
In project outcomes, debt is a fourth variable and time lost to distracting tasks a fifth. A better formula would look like this

Usually it is the successful projects which get a scope which properly reflects the work needed, stick to it, allocate enough time and capacity and hold on to it. It’s simple in theory, and projects which go off the rails don’t do it in practice, and fail to adjust. The Phoenix Project told how failing to deliver “Phoenix” put the company at risk. After the outburst I quoted above, the CIO proposes putting everything else on hold, and the CEO, who had demanded 100% focus on Phoenix, initially responds “You must be out of your right mind”. Eventually he agrees, Phoenix is saved and the company with it. The book is trying to illustrate many ideas, but one of them boils down to “the best way to get people to deliver what you want is to stop asking them to deliver other things”.

Businesses seem to struggle to set priorities for IT: I can’t claim to be an expert in solving this problem, but the following may be helpful

Understanding the nature of the work. Jeffrey Snover likes to say “To ship is to choose”. A late project must find an acceptable combination of additional cost, overall delay, feature cuts, and technical debt. If you build websites, technical debt is more acceptable than if you build aircraft. If your project is a New Year’s Eve firework display, delivering without some features is an option, delay is not. Some feature delays incur cost, but others don’t.

Tracking all work: Have a view of what is completed, what is in Progress, what is “up next”, and what is waiting to be assigned time. The next few points all relate to tracking.
Work in progress has already consumed effort but we only get credit when it is complete. An increasing number of task in progress may mean people are passing work to other team members faster than their capacity to complete it or new tasks are interrupting existing ones.
All work should have a specification
before it starts. Writing specifications takes time, and “Create specification for X” may be task in itself.
And yes, I do know that technical people generally hate tracking work and writing specifications. 
Make technical debt visible. It’s OK to split an item and categorize part as completed and the rest as something else. Adding the undelivered part to the backlog keeps it as planned work, and also gives partial credit for partial delivery – rather than credit being all or nothing. It means some credit goes to the work of clearing debt.
And I also know technical folk see “fixing old stuff” as a chore, but not counting it just makes matters worse.
Don’t just track planned work. Treat jobs which jumped the queue, that didn’t have a spec or that displaced others like defects in a manufacturing process – keep the score, and try to drive it down to zero. Incidents and “CIBY” jobs might only be recorded as an afterthought but you want see where they are coming from and try to eliminate them at source.

Look for process improvements. if a business is used to lax project management, it will resist attempts to channel all work through a project steering board. Getting stakeholders together in a regular “IT projects meeting” might be easier, but get the key result (managing the flow of work).

And finally Having grown-up conversations with customers.
Businesses should understand the consequences of pushing for delivery to exceed capacity; which means IT (especially those in management) must be able to deliver messages like these.
“For this work to jump the queue, we must justify delaying something else”
“We are not going be able to deliver [everything] on time”, perhaps with a follow up of “We could call it delivered when there is work remaining but … have you heard of technical debt?”

February 26, 2014

Depth of field

Filed under: Uncategorized — jamesone111 @ 7:51 pm

Over the years I have seen a lot written about Depth of Field and recently I’ve seen it explained wrongly but with great passion. So I thought I would post the basic formulae, show how they are derived and explain how they work in practical cases.

So first: a definition. When part of an image is out of focus that’s not an on/off state. There’s massively blurred, a little blurred, and such a small amount of blur it still looks sharply focused: if we magnify the “slightly out of focus” parts enough we can see that they are not sharply focused. Depth of field is a measure of how far either side of the point of focus, appears to be properly focused (even though it is very slightly out of focus), given some assumptions about how much the image is magnified. 


When a lens focuses an image, the lens-to subject distance, D, and lens-to-image distance, d, are related to the focal length with the equation
1/D + 1/d = 1/f

We can rearrange this to derive the distance to the subject (D) in terms of focal length (f) and image distance (d)
D = df/(d-f)

Since d is always further than f, we can write the difference as Δ and replace d with f+Δ. Putting that into the previous equation makes it
D = (f2+Δf)/Δ which re-arranges to
D = (f2/Δ)+f

When Δ is zero the lens is focused at infinity, so if you think of that position as the start point for any lens, to focus nearer to the camera we move lens away from the image by distance of Δ

The formula be rearranged as the “Newtonian form” of the equation
D-f = f2 , therefore
Δ(D-f) = f2 and since Δ = (d-f)
(d-f)(D-f) = f2

We can work out a focus scale for a lens using D = (f2/Δ)+f . Assume we have a 60mm lens, and it moves in or out 1/3mm for each 30 degrees of turn ; 
When the lens is 60mm from the image we can mark ∞ at the 12 O’clock position: Δ = 0 and D= ∞,
if we turn the ∞ mark to the 1 O’clock position (30 degrees) Δ = 1/3 and D= 3600/(1/3) = 10800 + 60 = 10.86 M, so we can write 10.9 in at the new 12 O’clock position
turn the ∞ mark to the 2 O’clock position (60 degrees)  Δ = 2/3 and  D= 3600/(2/3) = 5400 + 60 = 5.46 M , so we can write 5.5 at the latest 12 O’clock position
turn the ∞ mark to the 3 O’clock position (90 degrees)  Δ = 1  and D= 3600 + 60 = 3.66 M, so this time we write 3.7 at 12 O’clock
turn the ∞ mark to the 4 O’clock position (120 degrees) Δ = 4/3 and D= 3600/(4/3) = 2700 + 60 = 2.76 M, so 2.8 goes on the scale at 12 O’clock
turn the ∞ mark to the 5 O’clock position (150 degrees) Δ = 5/3 and D= 3600/(5/3) = 2160 + 60 = 2.22 M
turn the ∞ mark to the 6 O’clock position (180 degrees) Δ = 2 and  D= 3600/2 = 1800 + 60 = 1.86 M so we can 2.2 and 1.9 to the scale to finish the job

And so on. For simplicity of calculation we often consider the extra 60mm insignificant and D ≈(f2/Δ) is usually close enough. It’s also worth noting that the roles of D as subject distance and d as image distance can be swapped – the whole arrangement is symmetrical.

In the diagram above, the blue lines show the lens is focused at a distance D, which has a lens to image distance (d) of (f+Δ) , something further away than D will not come to a sharp focus at the image plane, but some distance in front of it (something nearer than D will come to a focus behind the image plane). Focused rays of light form a cone: if the point of the code is not on the image plane, the rays form a disc which is called “a circle of confusion”. The red lines in the diagram illustrate the case for something at infinity and show how smaller a aperture width (bigger f/ number) leads to a smaller circle of confusion. 
The only factors which determine the size of the circle that is formed are focal length, aperture, and the distance between the lens and the image (i.e. the distance at which the lens is focused) Two set-ups using the same focal length, and same aperture, focused at the same distance will produce the same size circle regardless of the size of the recording medium which captures the image, the size of the image circle produced by the lens or any other factor.

A point at infinity will form an image at a distance f behind the lens (that’s the definition of focal length) and so we know it forms a image Δ in front of the film/sensor in the setup in the diagram.
The red lines form two similar triangles between the lens and the image. The “base” of the large one is w (the aperture width) and its "height" is f.
We normally write aperture as a ratio between width and focal length, e.g. f/2 means the aperture’s width is half the focal length.
So f = aw (where a is the f/ number) , so we can say this triangle has a base of w and a height of w*a

The base of smaller triangle is the circle of confusion from the mis-focused point at infinity.
This circle’s diameter is normally written as c, so using similar triangles the height of the smaller triangle must be its base * a, so:
Δ = c * a

As the lens moves further away from the image, the circle for the point at infinity gets bigger: a small enough circle looks like a point and but there comes a size where we can see it is a circle.
If we know that size we, can calculate the value of Δ as c*a and since we know that D = (f2/Δ) + f, we can define the subject distance when a point at infinity starts to look out of focus as
(f2/ca) + f  .
This distance is known as the hyperfocal distance (H) strictly, H = (f2/ca ) + f,  but it usually accurate enough to write H ≈ f2/ca ;
It later we’ll use a rearrangement of this: since Δ = ca, this simplified form of the equation can be turned into Δ≈f2/H


We can see that we get the same size circle if the image plane is c*a in front of where the image would focus as well as c*a behind it, so we can say
(1) for an subject at distance is D, the lens to image distance is approximately (f2/D)+f  (more accurately it is  (f2/(D-f))+f ) and
(2) the zone of apparent sharp focus runs from anything which would be in focus at (f2/D)+f -ca to anything which would be in focus at (f2/D)+f + ca

This formula is accurate enough for most purposes: but it would be more accurate to say the range runs ((f2/(D-f))+f)*(f+ca)/f  to ((f2/(D-f))+f)*(f-ca)/f because this accounts for  Δ getting slightly bigger as d increases for nearer and nearer subjects.  The error is biggest at short distances with wide apertures.
A  35mm frame with c=0.03 and an aperture of f/32, gives c*a ≈ 1. If we focus a 50mm lens at 1m (f2/D)+f = 52.5,
So the simple form of the formula would say an image formed 51.5-53.5mm behind the lens is in the “in focus zone”. The long form is 51.45- 53.55.
So instead of the dof extending from  1.716M to 0.764m  it actually goes from 1.774 to 0.754m.   
Since we only measure distance to 1 or two significant figures, aperture to 1 or 2 significant figures (and f/22 is really f/23) and focal length to the nearest whole mm (and stated focal length can be inaccurate by 1 or 2 mm) the simple formula gives the the point where most people kind-of feel that the image isn’t really properly focused to enough accuracy.  

It’s also worth noting that the if we have a focus scale like the one out lined above the same distance either side of a focus will give the same Δ, so we can calculate Δ for each aperture mark, and put depth of field scale marks on a lens.

∞    11.    5.5    3.7    2.8    2.2    1.9M 
^ | ^

If we want to work out D.o.F numbers (e.g. to make our own tables) , we know that the lens to image distance for the far point (df ) is (f2/Df)+f  and for the near point, (dn) it is (f2/Dn)+f

therefore,  f2/Df + f = f2/D + f – Δ    (or + Δ for the near point)

we can remove +f from each side and get f2/Df  = f2/D – Δ ;

since Δ = f2/H, we can rewrite this it as f2/Df  = f2/D  – f2/H ;

the f2 terms cancel out so we get  1/Df = 1/D – 1/H , for the far point and for the near point 1/Dn = 1/D + 1/H  ;

We can rewrite these as  1/Df =(H-D)/(H*D), for the far point and for the near point 1/Dn =(H+D)/(H*D) so

Df = HD/(H-D), for the far point and for the near point Dn =HD/(H+D)

These produce an interesting series

Focus Distance (D) Near Point(Dn) Far Point(Df)
H H/2
H/2 H/3 H
H/3 H/4 H/2
H/4 H/5 H/3
H/5 H/6 H/4

In other words, if the focus distance is H/x the near point is H/(x+1) and the far point is H/(x-1).

[These formulae: Dn = H/((H/D)+1)  ,  Df = H/((H/D)-1)  can be re-arranged to Dn = H/((H+d)/D)  ,  Df = H/((H-D)/D) and then to Dn = HD/(H+d), Df = HD/(H-D) – the original formulae ]

This can useful for doing a quick mental d.o.f calculation. A 50mm lens @ f/8 on full frame has a hyperfocal distance of roughly 10m (502/(.03*8) +50 = 10.46M). If I focus at 1M (roughly H/10) the near point is H/11 = 0.90909M and the far point is 1.1111M so I have roughly 9CM in front and 11 CM behind

Earlier I said “As the lens moves further away from the image, the circle gets bigger: a small enough circle looks like a point and but there comes a size where it starts looking like a circle. If we know that size…

How much of the image the circle occupies, determines whether it is judged to be still in focus, or a long way out of focus. So value for c must be proportional to the size of the image, after any cropping has been done.

By convention 35mm film used c=0.03mm and APS-C crop sensor cameras use c=0.02mm. Changing image (sensor) size changes allowable circle size c, and so changes Δ , and so the depth of field scale on a lens designed for one size of of image needs to be adjusted if used on a camera where the image is different size (on an APS-C camera reading the scale for 1 stop wider aperture than actually set will give roughly the right reading).

Size of the circle formed does not depend on image size but allowable circle size does and hyperfocal distance and apparent depth of field change when c changes

Changing sensor size (keeping same position with the same lens and accepting a change of framing).

If we use two different cameras – i.e. use a different circle size – at the same spot, focused on the same place and we use the same focal length and same aperture on both, then the one with the smaller image has less depth of field. It doesn’t matter how we get to the smaller image, whether it is by cropping a big one or starting with a smaller film/sensor size.

We get less D.o.F because c has become smaller, so f2/ca – the hyperfocal distance has moved further away. When you look at f2/ca,  a smaller value of c needs a larger value of a to compensate.

Changing sensor size and focal length (getting the same framing from same position)

If we use two cameras – with different circle size – and use different focal lengths to give the same angle of view, but keep the same aperture then the larger image will have less depth of field because the f and c have gone up by the same factor , but f is squared in the equation. A larger value of a is needed to compensate for f being squared.

So: a 50mm @ f/8 on full frame has the approximate field of view and depth of field of a 35mm @ f/5.6 on APS-C . If that’s we want, the full frame camera needs to use a slower shutter speed or higher ISO to compensate, which have their own side effects.

If we want the depth that comes from the 35mm @ f/32 on APS-C , the 50 might not stop down to f/44 to give the same depth on Full Frame.

But if we use the 50 @ f/1.4 to isolate a subject from the background on full frame the 35 probably doesn’t open up to f/1

Changing focal length and camera position

People often think of perspective as a function of the angle of view of the lens. Strictly that isn’t correct : perspective is a function of the ratios of subject to camera distances. If you have two items the same size with one a meter behind the other and you stand a meter from the nearer one, the far one is 2M away, and will appear 1/2 the size. If you stand 10 meters from the first (and therefore 11 meters from the second), the far object will appear 10/11ths of the size. It doesn’t matter what else is in the frame. But: if you fit a wider lens the natural response is to move closer to the subject : it is that change of viewpoint which causes that the change of perspective. Changing focal length and keeping position constant means the perspective is constant, and the framing changes. Changing focal length and keeping framing constant means a change of position and with it a change of perspective.

If you have two lenses for the same camera and a choice between standing close with a wide angle lens or further away with a telephoto (and accepting the change of perspective for the same framing) we can work out the distances.

Let’s say with the short lens, H is 10 and you stand 5 meters away.

The near point is (10 * 5) / (10+5) = 3.33 : 1.67 meters in front

The far point is (10 * 5) / (10-5) = 10 : 5 meters behind = 6.67 in total

If we double the focal length and stand twice as far away the hyperfocal distance increases 4 fold (if the circle size and aperture don’t change), so we get a d.o.f zone like this

(40*10) / (40+10) = 8 : 2 meters in front

(40*10) / (40-10) = 13.33: 3.33 meters behind =5.33 in total.

Notice the background is more out of focus with the long lens, but there is actually MORE in focus in front of the subject. The wider lens includes more "stuff" in the background and it is sharper – which is why long lenses are thought of as better at isolating a subject from the background.

Changing camera position and sensor size.

If you only have one lens and your choice is to move further away and crop the image (or use a smaller sensor) or come close and use a bigger image what we can calculate that too: keeping the full image / close position as the first case from the previous example we would keep the near point 1.6667 meters in front and a far point 5 meters behind = 6.67 in total

If we use half the sensor width, we halve c and double H, if we double the distances we have doubled every term in the equation.

(20*10) / (20+10) = 6.6667 : 3.33 meters (in front)

(20*10) / (20-10) = 20 : 10 meters (behind) – 13.33Meters in total, so you get twice as much in the zone either side of the image but dropping back and cropping.

June 30, 2012

Using the Windows index to search from PowerShell:Part Two – Helping with user input

Filed under: Uncategorized — jamesone111 @ 10:46 am

Note: this was originally written for the Hey,Scripting guy blog where it appeared as the 26 June 2012 episode. The code is available for download . I have some more index related posts coming up so I wanted to make sure everything was in one place

In part one I developed a working PowerShell function to query the Windows index. It outputs data rows which isn’t the ideal behaviour and I’ll address that in part three; in this part I’ll address another drawback: search terms passed as parameters to the function must be "SQL-Ready". I think that makes for a bad user experience so I’m going to look at the half dozen bits of logic I added to allow my function to process input which is a little more human. Regular expressions are the way to recognize text which must be changed, and I’ll pay particular attention to those as I know I lot of people find them daunting.

Replace * with %

SQL statements use % for wildcard, but selecting files at the command prompt traditionally uses *. It’s a simple matter to replace – but for the need to "escape" the* character, replacing * with % would be as simple as a –replace statement gets:
$Filter = $Filter -replace "\*","%"
For some reason I’m never sure if the camera maker is Canon or Cannon so I’d rather search for Can*… or rather Can%, and that replace operation will turn "CameraManufacturer=Can*" into "CameraManufacturer=Can%". It’s worth noting that –replace is just as happy to process an array of strings in $filter as it is to process one.

Searching for a term across all fields uses "CONTAINS (*,’Stingray’)", and if the -replace operation changes* to % inside a CONTAINS() the result is no longer a valid SQL statement. So the regular expression needs to be a little more sophisticated, using a "negative look behind"
$Filter = $Filter -replace " "(?<!\(\s*)\*","%"

In order to filter out cases like CONTAINS(*… , the new regular expression qualifies "Match on *",with a look behind – "(?<!\(\s*)" – which says "if it isn’t immediately preceded by an opening bracket and any spaces". In regular expression syntax (?= x) says "look ahead for x" and (?<= x) says "Look behind for x" (?!= x) is “look ahead for anything EXCEPT x” and (?<!x) is “look behind for anything EXCEPT x” these will see a lot of use in this function. Here (?<! ) is being used, open bracket needs to be escaped so is written as \( and \s* means 0 or more spaces.

Convert "orphan" search terms into ‘contains’ conditions.

A term that needs to be wrapped as a "CONTAINS" search can be identified by the absence of quote marks, = , < or > signs or the LIKE, CONTAINS or FREETEXT search predicates. When these are present the search term is left alone, otherwise it goes into CONTAINS, like this.
$filter = ($filter | ForEach-Object {
    if  ($_ -match "'|=|<|>|like|contains|freetext") 
    else   {"Contains(*,'$_')"}

Put quotes in if the user omits them.

The next thing I check for is omitted quote marks. I said I wanted to be able to use Can*, and we’ve seen it changed to Can% but the search term needs to be transformed into "CameraManufacturer=’Can%’ ". Here is a –replace operation to do that.
$Filter = $Filter -replace "\s*(=|<|>|like)\s*([^'\d][^\s']*)$",' $1 ''$2'' '
This is a more complex regular expression which takes a few moments to understand

Regular expression




Any spaces (or none)



= or < or > or "Like"



Anything which is NOT a ‘ character
or a digit



Any number of non-quote,
non-space characters (or none)



End of line


Capture the enclosed sections
as matches

$Matches[0]= "=Can%"
$Matches[1]= "="
$Matches[2]= "Can%"

‘ $1 ”$2” ‘0

Replace Matches[0] ("=Can%")
with an expression which uses the
two submatches "=" and "can%".

= ‘Can%’

Note that the expression which is being inserted uses $1 and $2 to mean matches[1] and[2] – if this is wrapped in double quote marks PowerShell will try to evaluate these terms before they get to the regex handler, so the replacement string must be wrapped in single quotes. But the desired replacement text contains single quote marks, so they need to be doubled up.

Replace ‘=’ with ‘like’ for Wildcards

So far, =Can* has become =’Can%’, which is good, but SQL needs "LIKE" instead of "=" to evaluate a wildcard. So the next operation converts "CameraManufacturer = ‘Can%’ "into "CameraManufacturer LIKE ‘Can%’ ":
$Filter = $Filter -replace "\s*=\s*(?='.+%'\s*$)" ," LIKE "

Regular expression




= sign surrounded by any spaces

CameraManufacturer = ‘Can%’


A quote character

CameraManufacturer = Can%’


Any characters (at least one)

CameraManufacturer = ‘Can%’


% character followed by ‘

CameraManufacturer = ‘Can%’


Any spaces (or none)
followed by end of line


Look ahead for the enclosed expression but don’t include it in the match

$Matches[0] = "="
(but only if ‘Can%’ is present)

Provide Aliases

The steps above reconstruct "WHERE" terms to build syntactically correct SQL, but what if I get confused and enter “CameraMaker” instead of “CameraManufacturer” or “Keyword” instead of “Keywords” ? I need Aliases – and they should work anywhere in the SQL statement – not just in the "WHERE" clause but in "ORDER BY" as well.
I defined a hash table (a.k.a. a "dictionary", or an "associative array") near the top of the script to act as a single place to store the aliases with their associated full canonical names, like this:
$PropertyAliases = @{
    Width       = "System.Image.HorizontalSize";
    Height      = "System.Image.VerticalSize";
    Name        = "System.FileName";
    Extension   = "System.FileExtension";
    Keyword     = "System.Keywords";
    CameraMaker = "System.Photo.CameraManufacturer"
Later in the script, once the SQL statement is built, a loop runs through the aliases replacing each with its canonical name:
$PropertyAliases.Keys | ForEach-Object {
    $SQL= $SQL -replace "(?<=\s)$($_)(?=\s*(=|>|<|,|Like))",$PropertyAliases[$_]
A hash table has .Keys and .Values properties which return what is on the left and right of the equals signs respectively. $hashTable.keyName or $hashtable[keyName] will return the value, so $_ will start by taking the value "width", and its replacement will be $PropertyAliases["width"] which is "System.Image.HorizontalSize", on the next pass through the loop, "height" is replaced and so on. To ensure it matches on a field name and not text being searched for, the regular expression stipulates the name must be preceded by a space and followed by "="or "like" and so on.

Regular expression




The literal text "Width"

Width > 1024


A Space


Look behind for the enclosed expression
but don’t include it in the match

$Matches[0] = "Width"
(but only if a leading space is present)


any spaces (or none)


The literal text "Like", or any of the characters comma, equals, greater than or less than

Width > 1024


Look ahead for the enclosed expression
but don’t include it in the match

$Matches[0] = "Width"
(but only if " >" is present)

If the prefix is omitted put the correct one in.

This builds on the ideas we’ve seen already. I want the list of fields and prefixes to be easy to maintain, so just after I define my aliases I define a list of field types
$FieldTypes = "System","Photo","Image","Music","Media","RecordedTv","Search"
For each type I define two variables, a prefix and a fieldslist : the names must be FieldtypePREFIX and FieldTypeFIELDS – the reason for this will become clear shortly but here is what they look like
$SystemPrefix = "System."
$SystemFields = "ItemName|ItemUrl"
$PhotoPrefix  = "System.Photo."
$PhotoFields  = "cameramodel|cameramanufacturer|orientation"
In practice the field lists are much longer – system contains 25 fieldnames not just the two shown here. The lists are written with "|" between the names so they become a regular expression meaning "ItemName or ItemUrl Or …". The following code runs after aliases have been processed
foreach ($type in $FieldTypes) {
   $fields = (get-variable "$($type)Fields").value
   $prefix = (get-variable "$($type)Prefix").value 
   $sql    = $sql -replace "(?<=\s)(?=($Fields)\s*(=|>|<|,|Like))" , $Prefix
I can save repeating code by using Get-Variable in a loop to get $systemFields, $photoFields and so on, and if I want to add one more field, or a whole type I only need to change the variable declarations at the start of the script. The regular expression in the replace works like this:

Regular expression




Look behind for a space
but don’t include it in the match



The literal text "orientation" or "cameramanufacturer"

CameraManufacturer LIKE ‘Can%’


any spaces (or none)



The literal text "Like", or any of the characters comma, equals, greater than or less than

CameraManufacturer LIKE ‘Can%’


Look ahead for the enclosed expression
but don’t include it in the match

$match[0] is the point between the leading space and "CameraManufacturer LIKE" but doesn’t include either.

We get the effect of an "insert" operator by using ‑replace with a regular expression that finds a place in the text but doesn’t select any of it.
This part of the function allows "CameraManufacturer LIKE ‘Can%’" to become "System.Photo CameraManufacturer LIKE ‘Can%’ " in a WHERE clause.
I also wanted "CameraManufacturer" in an ORDER BY clause to become "System.Photo CameraManufacturer". Very sharp-eyed readers may have noticed that I look for a Comma after the fieldname as well as <,>,=, and LIKE. I modified the code which appeared in part one so that when an ORDER BY clause is inserted it is followed by a trailing comma like this:
if ($orderby) { $sql += " ORDER BY " + ($OrderBy -join " , " ) + ","}

the new version will work with this regular expression but the extra comma will cause a SQL error and so it must be removed later.
When I introduced the SQL I said the SELECT statement looks like this:

SELECT System.ItemName, System.ItemUrl,      System.FileExtension, System.FileName, System.FileAttributes, System.FileOwner, 
       System.ItemType, System.ItemTypeText , System.KindText,     System.Kind,     System.MIMEType,       System.Size

Building this clause from the field lists simplifies code maintenance, and as a bonus anything declared in the field lists will be retrieved by the query as well as accepted as input by its short name. The SELECT clause is prepared like this:
if ($First) 
     {$SQL = "SELECT TOP $First "}
else {$SQL = "SELECT "}
foreach ($type in $FieldTypes)
     {$SQL +=((get-variable "$($type)Fields").value -replace "\|",", " ) + ", "}

This replaces the "|" with a comma and puts a comma after each set of fields. This means there is a comma between the last field and the FROM – which allows the regular expression to recognise field names, but it will break the SQL , so it is removed after the prefixes have been inserted (just like the one for ORDER BY).
This might seem inefficient, but when I checked the time it took to run the function and get the results but not output them it was typically about 0.05 seconds (50ms) on my laptop – it takes more time to output the results.
Combining all the bits in this part with the bits in part one turns my 36 line function into about a 60 line one as follows

Function Get-IndexedItem{
Param ( [Alias("Where","Include")][String[]]$Filter ,
$PropertyAliases = @{Width ="System.Image.HorizontalSize"; 
                    Height = "System.Image.VerticalSize"}
$FieldTypes      = "System","Photo"
$PhotoPrefix     = "System.Photo."
$PhotoFields     = "cameramodel|cameramanufacturer|orientation"
$SystemPrefix    = "System."
$SystemFields    = "ItemName|ItemUrl|FileExtension|FileName"
if ($First) 
     {$SQL = "SELECT TOP $First "}
else {$SQL = "SELECT "}
foreach ($type in $FieldTypes)
     {$SQL +=((get-variable "$($type)Fields").value -replace "\|",", ")+", " }
if ($Path -match "\\\\([^\\]+)\\.")
     {$SQL += " FROM $($matches[1]).SYSTEMINDEX WHERE "}
if ($Filter)
     {$Filter = $Filter -replace "\*","%"
      $Filter = $Filter -replace"\s*(=|<|>|like)\s*([^'\d][^\s']*)$",' $1 ''$2'' '
      $Filter = $Filter -replace "\s*=\s*(?='.+%'\s*$)" ," LIKE "
      $Filter = ($Filter | ForEach-Object {
          if ($_ -match "'|=|<|>|like|contains|freetext")
          else {"Contains(*,'$_')"}
      $SQL += $Filter -join " AND "
if ($Path)
    {if ($Path -notmatch "\w{4}:") {$Path = "file:" + $Path}
     $Path = $Path -replace "\\","/"
     if ($SQL -notmatch "WHERE\s$") {$SQL += " AND " }
     if ($Recurse) 
          {$SQL += " SCOPE = '$Path' "}
     else {$SQL += " DIRECTORY = '$Path' "}
if ($SQL -match "WHERE\s*$")
     { Write-warning "You need to specify either a path , or a filter." ; return }
if ($OrderBy) { $SQL += " ORDER BY " + ($OrderBy -join " , " ) + ","}
$PropertyAliases.Keys | ForEach-Object 
     { $SQL= $SQL -replace"(?<=\s)$($_)(?=\s*(=|>|<|,|Like))", $PropertyAliases.$_ }
foreach ($type in $FieldTypes)
{$fields = (get-variable "$($type)Fields").value
     $prefix = (get-variable "$($type)Prefix").value
     $SQL    = $SQL -replace "(?<=\s)(?=($Fields)\s*(=|>|<|,|Like))" , $Prefix
$SQL = $SQL -replace "\s*,\s*FROM\s+" , " FROM "
$SQL = $SQL -replace "\s*,\s*$" , ""
$Provider="Provider=Search.CollatorDSO;"+ "Extended Properties=’Application=Windows’;"
$Adapter = new-object system.data.oledb.oleDBDataadapter -argument $SQL, $Provider
$DS     = new-object system.data.dataset
if ($Adapter.Fill($DS)) { $DS.Tables[0] }

In part 3 I’ll finish the function by turning my attention to output

Using the Windows index to search from PowerShell: Part one: Building a query from user input.

Filed under: Uncategorized — jamesone111 @ 10:43 am

Note: this was originally written for the Hey,Scripting guy blog where it appeared as the 25 June 2012 episode. The code is available for download . I have some more index related posts coming up so I wanted to make sure everything was in one place

I’ve spent some time developing and honing a PowerShell function that gets information from the Windows Index– the technology behind the search that is integrated into explorer in Windows 7 and Vista. The Index can be queried using SQL and my function builds the SQL query from user input, executes it and receives rows of data for all the matching items. In Part three, I’ll look at why rows of data aren’t the best thing for the function to return and what the alternatives might be. Part two will look at making user input easier – I don’t want to make an understanding SQL a prerequisite for using the function. In this part I’m going to explore the query process.

We’ll look at how at how the query is built in a moment, for now please accept that a ready-to-run query stored in the variable $SQL. Then it only takes a few lines of PowerShell to prepare and run the query

$Provider="Provider=Search.CollatorDSO;Extended Properties=’Application=Windows’;"
$adapter = new-object system.data.oledb.oleDBDataadapter -argument $sql, $Provider
$ds      = new-object system.data.dataset
if ($adapter.Fill($ds)) { $ds.Tables[0] }

The data is fetched using oleDBDataAdapter and DataSet objects; the adapter is created specifying a "provider" which says where the data will come from and a SQL statement which says what is being requested. The query is run when the adapter is told to fill the dataset. The .fill() method returns a number, indicating how many data rows were returned by the query – if this is non-zero, my function returns the first table in the dataset. PowerShell sees each data row in the table as a separate object; and these objects have a property for each of the table’s columns, so a search might return something like this:

SYSTEM.ITEMURL : file:C:/Users/James/pictures/DIVE_1771+.JPG
SYSTEM.KIND : {picture}
SYSTEM.MIMETYPE : image/jpeg
SYSTEM.SIZE : 971413

There are lots of fields to choose from, so the list might be longer. The SQL query to produce it looks something like this.

SELECT System.ItemName, System.ItemUrl,        System.FileExtension,
       System.FileName, System.FileAttributes, System.FileOwner, 
       System.ItemType, System.ItemTypeText ,  System.KindText, 
       System.Kind,     System.MIMEType,       System.Size
WHERE  System.Keywords = 'portfolio' AND Contains(*,'stingray')

In the finished version of the function, the SELECT clause has 60 or so fields; the FROM and WHERE clauses might be more complicated than in the example and an ORDER BY clause might be used to sort the data.
The clauses are built using parameters which are declared in my function like this:

Param ( [Alias("Where","Include")][String[]]$Filter ,

In my functions I try to use names already used in PowerShell, so here I use -Filter and -First but I also define aliases for SQL terms like WHERE and TOP. These parameters build into the complete SQL statement, starting with the SELECT clause which uses -First

if ($First) {$SQL = "SELECT TOP $First "}
else        {$SQL = "SELECT "}
$SQL += " System.ItemName, System.ItemUrl " # and the other 58 fields

If the user specifies –First 1 then $SQL will be "SELECT TOP 1 fields"; otherwise it’s just "SELECT fields". After the fields are added to $SQL, the function adds a FROM clause. Windows Search can interrogate remote computers, so if the -path parameter is a UNC name in the form \\computerName\shareName the SQL FROM clause becomes FROM computerName.SYSTEMINDEX otherwise it is FROM SYSTEMINDEX to search the local machine.
A regular expression can recognise a UNC name and pick out the computer name, like this:

if ($Path -match "\\\\([^\\]+)\\.") {
$sql += " FROM $($matches[1]).SYSTEMINDEX WHERE "
else {$sql += " FROM SYSTEMINDEX WHERE "}

The regular expression in the first line of the example breaks down like this

Regular expression




2 \ characters: "\" is the escape character, so each one needs to be written as \\



Any non-\ character, repeated at least once



A \,followed by any character



Capture the section which is enclosed by the brackets as a match

$matches[0] =\\computerName\s
$matches[1] =computerName

I allow the function to take different parts of the WHERE clause as a comma separated list, so that
-filter "System.Keywords = 'portfolio'","Contains(*,'stingray')"
is equivalent to
-filter "System.Keywords = 'portfolio' AND Contains(*,'stingray')"

Adding the filter just needs this:

if ($Filter) { $SQL += $Filter -join " AND "}

The folders searched can be restricted. A "SCOPE" term limits the query to a folder and all of its subfolders, and a "DIRECTORY" term limits it to the folder without subfolders. If the request is going to a remote server the index is smart enough to recognise a UNC path and return just the files which are accessible via that path. If a -Path parameter is specified, the function extends the WHERE clause, and the –Recurse switch determines whether to use SCOPE or DIRECTORY, like this:

if ($Path){
     if ($Path -notmatch "\w{4}:") {
           $Path = "file:" + (resolve-path -path $Path).providerPath
     if ($sql -notmatch "WHERE\s*$") {$sql += " AND " }
     if ($Recurse)                   {$sql += " SCOPE = '$Path' " }
      else                           {$sql += " DIRECTORY = '$Path' "}

In these SQL statements, paths are specified in the form file:c:/users/james which isn’t how we normally write them (and the way I recognise UNC names won’t work if they are written as file://ComputerName/shareName). This is rectified by the first line inside the If ($Path) {} block, which checks for 4 "word" characters followed by a colon. Doing this will prevent ‘File:’ being inserted if any protocol has been specified –the same search syntax works against HTTP:// (though not usually when searching on your workstation), MAPI:// (for Outlook items) and OneIndex14:// (for OneNote items). If a file path has been given I ensure it is an absolute one – the need to support UNC paths forces the use of .ProviderPath here. It turns out there is no need to convert \ characters in the path to /, provided the file: is included.
After taking care of that, the operation -notmatch "WHERE\s*$" sees to it that an "AND" is added if there is anything other than spaces between WHERE and the end of the line (in other words if any conditions specified by –filter have been inserted). If neither -Path nor -filter was specified there will be a dangling WHERE at the end of the SQL statement .Initially I removed this with a ‑Replace but then I decided that I didn’t want the function to respond to a lack of input by returning the whole index so I changed it to write a warning and exit. With the WHERE clause completed, final clause in the SQL statement is ORDER BY, which – like WHERE – joins up a multi-part condition.

if ($sql -match "WHERE\s*$") {
     Write-warning "You need to specify either a path, or a filter."
if ($orderby) { $sql += " ORDER BY " + ($OrderBy -join " , ") }

When the whole function is put together it takes 3 dozen lines of PowerShell to handle the parameters, build and run the query and return the result. Put together they look like this:

Function Get-IndexedItem{
Param ( [Alias("Where","Include")][String[]]$Filter ,
if ($First) {$SQL = "SELECT TOP $First "}
else        {$SQL = "SELECT "}
$SQL += " System.ItemName, System.ItemUrl " # and the other 58 fields
if ($Path -match "\\\\([^\\]+)\\.") {
              $SQL += "FROM $($matches[1]).SYSTEMINDEX WHERE "
else         {$SQL += " FROM SYSTEMINDEX WHERE "}
if ($Filter) {$SQL += $Filter -join " AND "}
if ($Path) {
    if ($Path -notmatch "\w{4}:") {$Path = "file:" + $Path}
    $Path = $Path -replace "\\","/"
    if ($SQL -notmatch "WHERE\s*$") {$SQL += " AND " }
    if ($Recurse)                   {$SQL += " SCOPE = '$Path' " }
    else                            {$SQL += " DIRECTORY = '$Path' "}
if ($SQL -match "WHERE\s*$") {
    Write-Warning "You need to specify either a path or a filter."
if ($OrderBy) { $SQL += " ORDER BY " + ($OrderBy -join " , " ) }
$Provider = "Provider=Search.CollatorDSO;Extended Properties=’Application=Windows’;"
$Adapter  = New-Object system.data.oledb.oleDBDataadapter -argument $SQL, $Provider
$DS       = New-Object system.data.dataset
if ($Adapter.Fill($DS)) { $DS.Tables[0] }

The -Path parameter is more user-friendly as a result of the way I handle it, but I’ve made it a general rule that you shouldn’t expect the user to know too much of the underlying syntax ,and at the moment the function requires too much knowledge of SQL: I don’t want to type

Get-Indexed-Item –Filter "Contains(*,'Stingray')", "System.Photo.CameraManufacturer Like 'Can%'"

and it seems unreasonable to expect anyone else to do so. I came up with this list of things the function should do for me.

  • Don’t require the user to know whether a search term is prefixed with SYSTEM. SYSTEM.DOCUMENT, SYSTEM.IMAGE or SYSTEM.PHOTO. If the prefix is omitted put the correct one in.
  • Even without the prefixes some fieldnames are awkward for example "HorizontalSize" and "VerticalSize" instead of width and height. Provide aliases
  • Literal text in searches needs to be enclosed in single quotes, insert quotes if the user omits them.
  • A free text search over all fields is written as Contains(*,’searchTerm’) , convert "orphan" search terms into contains conditions.
  • SQL uses % (not *) for a wild card – replace* with % in filters to cope with users putting the familiar *
  • SQL requires the LIKE predicate(not =) for wildcards : replace = with like for Wildcards

In Part two, I’ll look at how I do those things.

June 25, 2012

Lonesome George

Filed under: Uncategorized — jamesone111 @ 3:15 pm

At Easter I was in the Galapagos Islands; work had taken me to Ecuador diving in the Galapagos was too good an opportunity to miss. Mainland Ecuador was a country I knew little about and two weeks working in the capital (Quito, just South of the Equator, and 10,000 feet up in the Andes) doesn’t qualify me as an expert. The client there was a good one to work with, and what I saw of the city (a bunch of Taxi rides and a bus tour on the one day we weren’t working) mean I’d go back if asked. Travel wasn’t good and the return flights so bad that I’ve vowed never to fly with Iberia again. Flying to the islands the plane had a problem which meant if it landed it couldn’t take off again so having got within sight of the islands we had to go all the way back to Quito and get another plane. Down at sea level the heat was ferocious, the transportation scary and the insect bites the worst I’ve had. But the diving… different kinds of Sharks (including a close encounter with a group of Hammerheads), Seal Lions, Turtles, Rays (including a Manta encounter on the very first dive which set the tone) – I’d put up with a lot for that. And if some search engine has steered you here, I dived with Scuba Iguana, and if I manage to go back I’ll dive with them again.

The Scuba place was pretty much next door to the Darwin station: home of giant tortoises and a tortoise breading programme. Galapagos comes from the Spanish word for saddle because the shape of the giant tortoise’s shell looked like a traditional saddle. I also learnt that some languages – including Spanish – don’t have distinct words for Tortoise and (Marine) Turtles.  The sex of tortoises is determined by the temperature at which the eggs incubate and the breeding programme gathers eggs, incubates them to get extra females, and looks after the baby tortoises keeping them safe human introduced species (like rats) which feed on eggs and baby tortoises. Each island’s tortoises are different so the eggs and hatchlings are marked up so they go back to right island. But there is no breeding programme for Pinta island (also named Abingdon island by the Royal Navy. According to a story told by Stephen Fry on QI, sailors ate giant tortoises and found them very good.)  A giant Tortoise was found on Pinta; but a search over several years failed to find a second. So he – Lonesome George, was brought to the Darwin station in the 1970s. No one knows for sure how he was then. All efforts to find a mate for him failed: so George lived out his final decades as the only known example of  Geochelone nigra abingdoni  the Pinta Galapagos tortoise.
On Easter Sunday I walked up see George and the giants from other islands who live at the station. George was keeping out of the sun; he shared an enclosure and I wondered what he made of the other species – if somewhere in that ancient reptile brain lurked a memory of others just like him, a template into which the other tortoises didn’t quite fi.

Later in trip I was asked to help with some work on survey being prepared about Hammerhead sharks. I was told they estimated as having a 20% chance of becoming extinct in the next 100 years. This statistic is quite hard to digest: my chances of being extinct in 100 years are close to 100%: so my contribution to the survey was suggest that telling people if things continue as they are the chances of seeing Hammerheads on a dive in 5 years will be X amount less than today. It’s not fair, but we care more about some species than others and I hope there will still be Hammerheads for my children to see in a few years. Sadly they won’t get the chance to see the Pinta Tortoise.

June 22, 2012

Windows Phone 8. Some Known Knowns, and Known Unknowns

Filed under: Uncategorized — jamesone111 @ 10:23 am

Earlier this week Microsoft held its Windows Phone Summit where it made a set of announcements about the next generation of Windows Phone – Windows phone 8 in summary these were

  • Hardware Support for Multi-core processors , Additional Screen resolutions, Removable memory cards, NFC
  • Software The Windows 8 core is now the base OS, Support for native code written in C or C++ (meaning better games), IE 10, new mapping (from Nokia), a speech API which apps can use. Better business-oriented features, VOIP support, a new “Wallet” experience and in-app payments, and a new start screen.

This summit came hot on the heels of the Surface tablet launch, which seemed to be a decision by Microsoft that making hardware for Windows 8 was too important to be left to the hardware makers. The first thing I noted about phone announcement was the lack of a Microsoft branded phone. I’ve said that Microsoft should make phones itself since before the first Windows Mobile devices appeared – when Microsoft was talking about “Stinger” internally; I never really bought any of the reasons given for not doing so. But I’d be astounded if Nokia didn’t demand that Microsoft promise not to make a phone (whether there’s a binding agreement or just an understanding between Messrs Elop and Ballmer we don’t know). Besides Nokia Microsoft has 3 other device makers on-board: HTC have devices for every Microsoft mobile OS since 2000, but also have a slew of devices for Android, Samsung were a launch partner for Phone 7 but since then have done more with Android ; LG were in the line up for the Windows Phone 7 launch and are replaced by Huawei.  What these 3  feel about Nokia mapping technology is a matter of guesswork but depends on branding and licensing terms.

There are some things we think we know, but actually they are things we know that we don’t know.

  • Existing 7.x phones will not run Windows Phone 8 but will get an upgrade to Windows 7.8. I have an HTC Trophy which I bought in November 2010 and it has gone from 7.0 to 7.5 and I’ll be quite happy to get 7.8. on a 2 year old phone. Someone who just bought a Nokia Lumia might not feel quite so pleased.  What will be in 7.8 ? The new start screen has been shown. But will it have IE10 ? Will it have the new mapping and Speech capabilities. The Wallet, In-app-payments ?  This matters because….
  • Programs specifically targeting Windows Phone 8 won’t run on 7. Well doh! Programs targeting Windows 7 don’t run XP. But what programs will need to target the new OS ? Phone 7 programs are .NET programs and since .NET compiles to an intermediate language not to CPU instructions, a program which runs on Windows 8-RT (previous called Windows on ARM) should go straight onto a Windows 8-intel machine (but not vice versa), and Phone 7 programs will run on Phone 8. An intriguing comment at the launch says the Phone 8 emulator runs on Hyper-V; unless Hyper-V has started translating between different CPU instruction sets this means the Emulated phone has an Intel CPU but it doesn’t matter because it is running .NET intermediate language not binary machine code. So how many new programs will be 8-specific ? – Say Skype uses the VOIP support and in-app payments for calling credit. Will users with old phones be stuck with the current version of Skype? Do they get a new version where those features don’t light up. Or do they get the same experience as someone with a new phone. If the only things which are Phone 8 specific are apps which need Multiple cores (or other newly supported hardware) there would never have been any benefit upgrading a single core phone from 7 to 8.  
  • Business support. This covers many possibilities, but what is in and what is out ? Will the encryption send keys back to base as bit-locker does ? Will there be support for Direct-Access ? Corporate wireless security ? Will adding Exchange server details auto-configure the phone to corporate settings (like the corporate app-store) . Will it be possible to block updates ? This list goes on and on.

It’s going to interesting to see what this does for Microsoft and for Nokia’s turn-round.

May 11, 2012

Lies, damn lies and public sector pensions

Filed under: Uncategorized — jamesone111 @ 9:08 am

Every time I hear that “public sector workers”  are protesting about government plans for their pensions –as happened yesterday – I think of two points I made to fellow school governors when the teachers took a day off (with the knock on cost to parents and the economy) these were

  • Does  classroom teachers understand their union wants them to subsidize the head’s pension from theirs ?  (Have the Unions explained to the classroom teachers that is what they are doing
  • Any teacher who is part of this protest has demonstrated they have insufficient grasp of maths to teach the subject (except, perhaps to the reception class.)

This second point is the easier of the two to explain: the pension paid for over your working life is a function of:

  • The salary you earned (which in turn depends on the rate at which your salary grew)
  • What fraction of your salary was paid in to your pension fund. It might be you gave up X% or your employer put in Y% that you never saw or a combination.   
  • How many years you paid in for (when you started paying in, and when you retire)
  • How well your pension fund grew before it paid out
  • How long the pension is expected to pay out for (how long you live after retirement)
  • Annuity rates – the interest earned on the pension fund as it pays out.

In addition, some people receive some pension which wasn’t paid for in this way; some employers (public or private sector) make guarantees to top up either the pension fund so it will buy a given level of annuity or to top-up  pension payments bought with the fund. The total you receive is the combination of what you have paid for directly and the top-up.
Change any factor – for example how long you expect to live – and either what you have paid for changes or the other factors have to change to compensate. Since earnings and rates of return aren’t something we control, living longer means either we have pay for a smaller pension, or we must pay in more, or retire later or some combination of all three. Demanding that the same amount will come out of a pension for longer, without paying more in is a demand for a guaranteed top-up in future – in the case of public sector employees that future top-up comes from future taxes, for private sector it comes from future profits.

It’s easy for those in Government to make pension promises because those promises don’t need to be met for 30 years or more. Teachers whose retirement is imminent would have come into the profession when Wilson, Heath or Callaghan was in Downing Street, and all 3 are dead and buried; so with the exception of Denis Healey are all the chancellors who set budgets while they were in office. It’s temping for governments to save on spending by under-contributing to pensions today, and leave future governments with the shortfall: taken to the extreme governments can turn pensions into a Ponzi scheme – this year’s “Teachers’/Police/whatever pay” bill covers what all current teachers/police officers / whoever are paid for doing the job and all pensions paid to retired ones for doing the job cheaply in the past. Since I am, more-or-less, accusing governments of all colours of committing fraud, I might as well add false accounting to the charge sheet. Let’s say the Government wants to buy a squadron of new aircraft but doesn’t want to raise taxes to pay for them all this year; it borrows money and the future liability that creates is accounted for. If the deal it makes with public sector workers is for a given amount to spend today, and a promise of a given standard of living in retirement ,does it record that promise – that future liability – as part of pay today? Take a wild guess.
This wouldn’t matter – outside accounting circles – if everything was constant. But the length of time between retirement and death has increased and keeps on increasing. For the sake of a simple example: lets assume someone born in 1920, joined the Police after world war II , served for 30 years and retired in 1975 at age 55 expecting to die at 70. Their retirement was half their length of service. Now consider someone born in 1955, who also joined the police at age 25, served for 30 years and retired 2010. Is any one making plans for their Pension to stop in 2025 ? We might reasonably expect this person to live well into their eighties – so we’ve moved from 1 retired officer for every 2 serving, to a 1:1 ratio. I’m not saying that in 1975 the ratio was 1:2 and in 2012 it is 1:1 but that’s the direction of travel. 

I’ve yet to hear a single teacher say their protests about pensions amount to a demand that they should under-fund their retirement as a matter of public policy and their pupils – who will then be tax payers – should make up difference. As one of those whose work generates the funds to pay for the public sector I must choose a combination of lower pension, later retirement, and higher contributions than I was led to expect when I started work 25 years or so ago. And there are people demanding my taxes insulate them from having to do the same; or (if you prefer) demanding a pay rise to cover the gap between what past governments have promised them and what they are actually paying for, or (and this becomes a bit uncomfortable) that government starts telling us what it really costs to have the teachers, nurses, police officers and so on we want. 

But what of my claim that Unions get low paid staff to subsidize the pensions of higher paid colleagues. Lets take two teachers; I’ll call them Alice and Bob, and since this is a simplified example they’ll fit two stereotypes: Alice sticks to working in the class room; and gets a 2% cost of living rise every year. Bob competes for every possible promotion, and gets promoted in alternate years, so he gets a 2% cost of living rise alternating with a 10% rise. Although they both started on the same salary after 9 end-of-year rises, Alice’s pay has gone up by 19.5% and Bob – who must be a head by now – has seen his rise by 74%  
Throughout the 10 years they pay 10% of their salary into their pension fund – to make the sums easy we’ll assume they pay the whole 10% on the last day of the year, and each year their pension fund manager earns them 10% of what was in their pension pot at the end of the previous year. After 10 years Alice has £17,184 in her pension pot, and Bob has £20,390 in his.

Alice (and her fellow class room teachers) are told by the Union Rep that any attempt to change from final salary as the calculation mechanism is an attack on your pension, for her, this is factually wrong. If you are ever told this you need to ask if you are a high flier like Bob or if your career is more like Alice’s. To see why it is wrong (and lets put it down to the Union rep being innumerate , rather than dishonest), lets pretend the pension scheme only has Alice and Bob in it. So the total pot is £37,574 – Alice put in 46% of that money, but of it is shared in the ratio of the final salaries 11,950 : 17,432 ,Alice gets 41% of the pay out. 
You can argue it doesn’t work like that because Alice’s pot (at 1.44 times her final salary) might just cover the percentage of her final salary she has been promised: Bob’s pension pot is only 1.17 times his final salary which will give him a smaller percentage so the government steps in and boosts his pot to be 1.44 time his  final salary just like Alice’s. So Bob gets a golden handshake of nearly £4700 and Alice gets nothing.
Suppose 1.44 years is nowhere near enough and Alice and Bob need 3 years salary to buy a large enough annuity; the government needs to find £18,668 for Alice (108% of her pot), and 31,907 for Bob (156% of his pot). Whichever way you cut and slice if your salary grows quicker than your colleagues you will do better out of final salary than they do. If it grows more slowly you will fare worse.  

Alice Bob
Salary Increase Pension Payment Pension Pot Salary Increase Pension Payment Pension Pot
Year 1   10,000.00 2%               1,000.00     1,000.00   10,000.00 10%               1,000.00     1,000.00
Year 2   10,200.00 2%               1,020.00     2,120.00   11,000.00 2%               1,100.00     2,200.00
Year 3   10,404.00 2%               1,040.40     3,372.40   11,220.00 10%               1,122.00     3,542.00
Year 4   10,612.08 2%               1,061.21     4,770.85   12,342.00 2%               1,234.20     5,130.40
Year 5   10,824.32 2%               1,082.43     6,330.36   12,588.84 10%               1,258.88     6,902.32
Year 6   11,040.81 2%               1,104.08     8,067.48   13,847.72 2%               1,384.77     8,977.33
Year 7   11,261.62 2%               1,126.16   10,000.39   14,124.68 10%               1,412.47   11,287.53
Year 8   11,486.86 2%               1,148.69   12,149.12   15,537.15 2%               1,553.71   13,970.00
Year 9   11,716.59 2%               1,171.66   14,535.69   15,847.89 10%               1,584.79   16,951.79
Year 10   11,950.93               1,195.09  17,184.35   17,432.68               1,743.27   20,390.23
Average Salary   10,949.72   13,394.10
Combined Final Salary   29,383.60 Total Pot  37,574.58
Alice’s share   15,282.37
Bob’s Share   22,292.21
Combined Average Salary   24,343.82 Total Pot   37,574.58
Alice’s share   16,900.85
Bob’s Share   20,673.73

What if the mechanism for calculating were Average salary , not final salary? It doesn’t quite remove gap but gets very close. Instead of £2,000 of Alice’s money going to Bob it’s less than £300. 
A better way to look at this is to say if the amount of money in the combined Pension pot pays £5000 a year in Pensions, do we split it as roughly £2000 to Alice and £3000 to Bob (the rough ratio of their final salaries – each gets about 1/6th of their final salary) or £2250 to Alice and £2750 to Bob (the ratio of their average salaries and each gets about 1/5th of their average).
Whenever average salary is suggested as a basis, union leaders will say that pensions are calculated from a smaller number as if it reduces the amount paid. If the government wanted to take money that way it would be simpler to say “a full pension will in future be a smaller percentage of final salary”. Changing to average-based implies an increase in the percentage paid. 

That perhaps is the final irony. Rank and file Police officers – whose career pay is like Alice’s in the example – marched through London yesterday demanding that their pensions be left alone; you do not need to spend long reading “Inspector Gadget” to realise when you remove the problems created for the Police by politicians most of the problems that are left are created by senior officers whose career pay follows the “Bob” path. Yet the “many” marching were demanding that they continue to subsidize these “few”. As Gadget himself likes to say : you couldn’t make it up.

April 22, 2012

Don’t Swallow the cat. Doing the right thing with software development and other engineering projects.

Filed under: Uncategorized — jamesone111 @ 8:30 pm

In my time at Microsoft I became aware of the saying “communication occurs only between equals.” usually couched in the terms “People would rather lie than deliver bad news to Steve Ballmer”. Replacing unwelcome truths with agreeable dishonesty wasn’t confined to the CEOs direct reports, and certainly isn’t a disease confined to Microsoft. I came across ‘The Hierarchy of Power Semantics’ more than 30 years ago when I didn’t understand what was meant by the title; it was written in the 1960s and if you don’t recognise “In the "beginning was the plan and the specification, and the plan was without form and the specification was void and there was darkness on the face of the implementation team”  see here – language warning for the easily offended.
Wikipedia says the original form of “communication occurs only between equals”  is Accurate communication is possible only in a non-punishing situation. There are those who (consciously or not) use the impossibly of saying “No” to extract more from staff and suppliers; it can produce extraordinary results, but sooner or later it goes horribly wrong. For example the Challenger disaster was caused by the failure of an ‘O’ ring in solid rocket booster made by Morton Thiokol. The engineers responsible for the booster were quite clear that in cold weather the ‘O’ rings were likely to fail with catastrophic results.  NASA asked if a launch was OK after a freezing night and fearing the consequences of saying “No” managers at Morton Thiokol over-ruled the engineers and allowed the disastrous launch to go ahead.  Most people can think of some case where someone made an impossible promise to a customer, because they were afraid to say no.

Several times recently I have heard people say something to the effect that ‘We’re so committed to doing this the wrong way that we can’t change to the right way.”  Once the person saying it was me, which was the genesis of this post. Sometimes, in a software project because saying to someone – even to ourselves – “We’re doing this wrong” is difficult, so we create work rounds. The the odd title of this post comes from a song which was played on the radio a lot when I was a kid.

There was an old lady, who swallowed a fly, I don’t know why she swallowed a fly. I guess she’ll die.
There was an old lady, who swallowed a spider that wriggled and jiggled and ticked inside her. She Swallowed the spider to catch the fly  … I guess she’ll die
There was an old lady, who swallowed a bird. How absurd to swallow a bird. She swallowed the bird to catch the spider … I guess she’ll die
There was an old lady, who swallowed a cat. Fancy that to swallow a cat. She swallowed the cat to catch the bird …  I guess she’ll die
There was an old lady, who swallowed a dog. What a hog to swallow a dog. She swallowed the dog to catch the cat … I guess she’ll die
There was an old lady, who swallowed a horse. She’s dead, of course

In other words each cure needs a further, more extreme cure.  In my case the “fly” was a simple problem I’d inherited. It would take a couple of pages to explain the context, so for simplicity it concerns database tables and the “spider” was to store data de-normalized. If you don’t spend your days working with databases, imagine you have a list of suppliers, and a list of invoices from those suppliers. Normally you would store an ID for the supplier in the invoice table, and look up the name from the supplier table using the ID. For what I was doing it was better to put the supplier name in the invoices table, and ignore the ID. All the invoices for the supplier can be looked up by querying for the name. The same technique applied to products supplied by that supplier: store the supplier name in the product table, look up products by supplier name. This is not because I didn’t know any better, I had database normal forms drummed into me two decades ago. To stick with the metaphor: I know that, under normal circumstances, swallowing spiders is bad, but faced with this specific fly it was demonstrably the best course of action.
At this point someone who could have saved me from my folly pointed out that supplier names had to be editable. I protested that the names don’t change, but Amalgamated Widgets did, in fact, become Universal Widgets. This is an issue because Amalgamated not Universal raised the invoices in the filing cabinet so matching them to invoices in the system requires preserving the name as it was when the invoice was generated. “See, I was right name should be stored” – actually this exception doesn’t show I was right at all, but on I went. On the other hand all of  Amalgamated’s products belong to Universal now. Changing names means doing a cascaded update (update any product with the old company name to the new name when a name changes) the real case has more than just products. If you’re filling in the metaphor you’ve guessed I’d reached the point of figuring out how to swallow a bird. Worse, I could see another problem looming (anyone for Cat ?): changes to products had to be exported to another system, and the list of changes had their own table requiring cascaded updates from the cascaded updates.

One of the great quotes in Macbeth says “I am in blood stepped in so far that should I wade no more, Returning were as tedious as go o’er.” he knows what he’s doing is wrong, but it is as hard to go back (and do right) as it is to go on.  Except it isn’t: the solution is not to swallow another couple more spiders and a fly, the solution is to swallow a bird, then a cat and so on.  The dilemma is that the effort for an additional work-round is smaller than the effort to go back fix the initial problem and unpick all the work-rounds to date – either needs to be done now, and the easy solution is to choose the one which needs the least effort now. The sum of effort required for future work-rounds is greater but we can discount that effort because it isn’t needed now. Only in a non-punishing situation can we tell people that progress must be halted for a time to fix a problem which has been mitigated up to now. Persuading people that such a problem needs to fixed at all isn’t trivial, I heard this quote in a Radio programme a while back

“Each uneventful day that passes reinforces a steadily growing false sense of confidence that everything is alright:
that I, we, my group must be OK because the way we did things today resulted in no adverse consequences.”

In my case the problem is being fixed at the moment, but in how many organisations is it what career limiting move to tell people that something which has had now adverse consequences to date must be fixed? 

February 4, 2012

Customizing PowerShell, Proxy functions and a better Select-String

Filed under: Uncategorized — jamesone111 @ 9:24 pm

I suspect that even regular PowerShell users don’t customize their environment much. By co-incidence, in the last few weeks I’ve made multiple customizations to my systems (my scripts are sync’d over 3 machines, customize one, customize all). Which has given me multiple things to talk about. My last post was about adding persistent history this time I want to look at Proxy Functions …

Select-String is, for my money, one of the best things in PowerShell. It looks through piped text or through files for anything which matches a regular expression (or simple text) and reports back what matched and where with all the detail you could ever need. BUT It has a couple of things wrong with it: it won’t do a recursive search for files, and sometimes the information which comes back is too detailed. I solved both problems with a function I named “WhatHas” which has been part of my profile for ages. I have been using this to search scripts, XML files and saved SQL whenever I need a snippet of code that I can’t remember or because something needs to be changed and I can’t be sure I’ve remembered which files contain it. I use WhatHas dozens (possibly hundreds) of times a week. Because it was a quick hack I didn’t support every option that Select-string has, so if a code snippet spans lines I have go back to the proper Select-String cmdlet and use its -context option to get the lines either side of the match: more often than not I find myself typing dir -recurse {something} | select-String {options}

A while back I saw a couple of presentations on Proxy functions (there’s a good post about them here by Jeffrey Snover): I thought when I saw them that I would need to implement one for real before I could claim to understand them, and after growing tired of jumping back and forth between select-string and WhatHas, I decided it was time to do the job properly creating a proxy function for Select-String and keep whathas as an alias. 

There are 3 bits of background knowledge you need for proxy functions.

  1. Precedence. Aliases beat Functions, Functions beat Cmdlets. Cmdlets beat external scripts and programs. A function named Select-String will be called instead of a cmdlet named Select-String – meaning a function can replace a cmdlet simply by giving it the same name. That is the starting point for a Proxy function.
  2. A command can be invoked as moduleName\CommandName. If I load a function named “Get-Stuff” from my profile.ps1 file for example, it won’t have an associated module name but if I load it as part of a module, or if “Get-Stuff” is a cmdlet it will have a module name.
    Get-Command get-stuff | format-list name, modulename
    will show this information You can try
    > Microsoft.PowerShell.Management\get-childitem
    For yourself. It looks like an invalid file-system path, but remember PowerShell looks for a matching Alias, then a matching Function and a then a matching cmdlet before looking for a file.
  3. Functions can have a process block (which runs for each item passed via the pipeline) a begin block (which runs before the first pass through process, and an end block (which runs after the last item has passed through process.) Cmdlets follow the same structure, although it’s harder to see.

Putting these together A function named Select-String can call the Select-String cmdlet, but it must call it as Microsoft.PowerShell.Utility\Select-String or it will just go round in a loop. In some cases, calling it isn’t quite enough and PowerShell V2 delivered the steppable pipeline which can take a PowerShell command (or set of commands piped together) and allow us to run its begin block , process block , and end block, under the control of an function. So a Proxy function looks like this :
Function Select-String {
  Param  ( Same Parameters as the real Select-String
           Less any I want to prevent people using
           Plus any I want to add
   Begin { My preliminaries
Process { My Per-item code against current item ($_ )

     end { $steppablePipeline.End
           My Clean up code

What would really help would be something produce a function like this template, and fortunately it is built into PowerShell: it does the whole thing in 3 steps: Get the command to be proxied, get the detailed metadata for command and build a Proxy function with the meta data, like this:
  $cmd=Get-command select-string -CommandType cmdlet
  $MetaData = New-Object System.Management.Automation.CommandMetaData ($cmd)

The last command will output the Proxy function body to the console, I piped the result into Clip.exe and pasted the result into a new definition
Function Select-String { }
And I had a proxy function.

At this point it didn’t do anything that the original cmdlet doesn’t do but that was a starting point for customizing.
The auto-generated parameters are be formatted like this
  [Parameter(ParameterSetName='Object', Mandatory=$true, ValueFromPipeline=$true)]

And I removed some of the line breaks to reduce the screen space they use from 53 lines to about half that.
The ProxyCommand creator wraps parameter names in braces just in case something has a space or other breaking character in the name, and I took those out.
Then I added two new switch parameters -Recurse and -BareMatches.

Each of the Begin, Process and End blocks in the function contains a try...catch statement, and in the try part of the begin block the creator puts code to check if the -OutBuffer common parameter is set and if it is, over-rides it (why I’m not sure) – followed by code to create a steppable pipeline, like this:
  $wrappedCmd = $ExecutionContext.InvokeCommand.GetCommand('Select-String',
  $scriptCmd = {& $wrappedCmd @PSBoundParameters }
  $steppablePipeline = $scriptCmd.GetSteppablePipeline($myInvocation.CommandOrigin)

I decided it would be easiest to build up a string and make that into the steppable pipeline . In simplified form
   $wrappedCmd        = "Microsoft.PowerShell.Utility\Select-String " 
  $scriptText        = "$wrappedCmd @PSBoundParameters"
  if ($Recurse)      { $scriptText = "Get-ChildItem @GCI_Params | " + $scriptText }
  if ($BareMatches)  { $scriptText += " | Select-Object –ExpandProperty 'matches' " +
                                      " | Select-Object -ExpandProperty 'value'   " }  
  $scriptCmd         = [scriptblock]::Create($scriptText)  
  $steppablePipeline = $scriptCmd.GetSteppablePipeline($myInvocation.CommandOrigin)

& commandObject works in a scriptblock: the “&” sign says  “run this” and if this is a command object that’s just fine: so the generated code has scriptCmd = {& $wrappedCmd @PSBoundParameters } where $wrappedCmd  is a command object.
but when I first changed the code from using a script block to using a string I put the original object $wrappedCmd inside a string. When the object is inserted into a string, the conversion renders it as the unqualified name of the command – the information about the module is lost, so I produced a script block which would call the function, which would create a script block which would call the function which… is an effective way to cause a crash.

The script above won’t quite work on its own because
(a) I haven’t built up the parameters for Get-Childitem. So if -recurse or –barematches are specified I build up a hash table to hold them, using taking the necessary parameters from what ever was passed, and making sure they aren’t passed on to the Select-String Cmdlet when it is called. I also make sure that a file specification is passed for a recursive search it is moved from the path parameter to the include parameter.
(b) If -recurse or -barematches get passed to the” real” Select-String cmdlet it will throw a “parameter cannot be found” error, so they need to be removed from $psboundParameters.

This means the first part of the block above turns into
  if ($recurse -or $include -or $exclude) {
     $GCI_Params = @{}
     foreach ($key in @("Include","Exclude","Recurse","Path","LiteralPath")) {
          if ($psboundparameters[$key]) {
$GCI_Params[$key] = $psboundparameters[$key]
     # if Path doesn't seem to be a folder it is probably a file spec
     # So if recurse is set, set Include to hold file spec and path to hold current directory
     if ($Recurse -and -not $include -and ((Test-Path -PathType Container $path) -notcontains $true) ) {
        $GCI_Params["Include"] = $path
        $GCI_Params["Path"] = $pwd
   $scriptText = "Get-ChildItem @GCI_Params | "
else { $scriptText = ""}

And the last part is
if ($BareMatches) {
  $scriptText += " | Select-object -expandproperty 'matches' | Select-Object -ExpandProperty 'value' "
$scriptCmd = [scriptblock]::Create($scriptText)
$steppablePipeline = $scriptCmd.GetSteppablePipeline($myInvocation.CommandOrigin)

There’s no need for me to add anything to the process or end blocks, so that’s it – everything Select-String originally did, plus recursion and returning bare matches.

I’ve put the whole file on skydrive here

January 28, 2012

Adding Persistent history to PowerShell

Filed under: Uncategorized — jamesone111 @ 7:19 pm

The Doctor: “You’re not are you? Tell me you’re not Archaeologists”
River Song: “Got a problem with Archaeologists ?”
The Doctor: “I’m a time traveller. I point and laugh at Archaeologists”

I’ve said to several people that my opinion of Linux has changed since I left Microsoft: after all I took a job with Microsoft because I rated their products and stayed there 10 years, I didn’t have much knowledge of Linux at the start of that stay and had no call to develop any during it, so my views I had was based on ignorance and supposition. After months of dealing with it on a daily basis I know how wrong I was. In reality Linux is uglier, more dis-functional and, frankly, retarded than I ever imagined it could be. Neither of our Linux advocates suggest it should be used anywhere but servers (just as well they both have iPhones and Xboxes which are about as far from the Open source ideal as it’s possible to go). But compare piping or tab expansion in PowerShell and Bash and you’re left in no doubt which one was designed 20 years ago. “You can only pipe text ? Really ? How … quaint”.

One of guys was trying to do something with Exchange from the command-line and threw down a gauntlet.
If PowerShell’s so bloody good why hasn’t it got persistent history”
OK. This is something which Bash has got going for it. How much work would it take to fix this ? Being PowerShell the answer is “a few minutes”. Actually the answer is “a lot less time than it takes to write a blog post about it”

First a little side track various people I know have a PowerShell prompt which looks like
[123]  PS C:\users\Fred>
Where 123 was the history ID. Type H (or history, or Get-History) and PowerShell shows you the previous commands, with their history ID, the command Invoke-History <id> (or ihy for short) runs the command.
I’d used PowerShell for ages before I discovered typing #<id>[Tab] inserts the history item into the command line. I kept saying “I’ll do that one day”, and like so many things I didn’t get round to it.
I already use the history ID I have this function in my profile
Function HowLong {
   <# .Synopsis Returns the time taken to run a command
      .Description By default returns the time taken to run the last command
.Parameter ID The history ID of an earlier item.

   param  ( [Parameter(ValueFromPipeLine=$true)]
$id = ($MyInvocation.HistoryId -1)
  process {  foreach ($i in $id) {
                 (get-history $i).endexecutiontime.subtract(
(get-history ($i)).startexecutiontime).totalseconds
Once you know $MyInvocation.HistoryID gives the ID of the current item, it is easy to change the Prompt function to return something which contains it.

At the moment I find I’m jumping back and forth between PowerShell V2, and the CTP of V3 on my laptop
(and I can run PowerShell –Version 2 to launch a V2 version if I see something which I want to check between versions).
So I finally decided I would change the prompt function. This happened about the time I got the “why doesn’t the history get saved” question. Hmmm. Working with history in the Prompt function. Tick, tick, tick.  [Side track 2 In PowerShell the prompt isn’t a constant, it is the result of a function.  To see the function use the command type function:prompt]
So here is the prompt function I now have in my profile.
Function prompt {
  $hid = $myinvocation.historyID
  if ($hid -gt 1) {get-history ($myinvocation.historyID -1 ) |
                      convertto-csv | Select -last 1 >> $logfile
  $(if (test-path variable:/PSDebugContext) { '[DBG]: ' } else { '' }) + 
    "#$([math]::abs($hid)) PS$($PSVersionTable.psversion.major) " + $(Get-Location) + 
    $(if ($nestedpromptlevel -ge 1) { '>>' }) + '> '

The first part is new lines are new: get the history ID and if is greater than 1, get the previous history item, convert from an object to CSV format, discard the CSV header and append it to the file named in $logFile (I know I haven’t set it yet)

The second part is lifted from the prompt function found in the default profile, that reads
"PS $($executionContext.SessionState.Path.CurrentLocation)$('>' * ($nestedPromptLevel + 1)) "
It’s actually one line but I’ve split it at the + signs for ease of reading.
I put the a # sign and the history ID before “PS” – when PowerShell starts the ID is –1 so I make sure it is the absolute value.
After “PS” I put the major version of PowerShell.
I’m particularly pleased with the #ID part in the non-ISE version of PowerShell double clicking on #ID selects it. My mouse is usually close enough to my keyboard that the keypad [Enter] key is within reach of my thumb so if I scroll up to look at something I did earlier, one flickity gesture (double-click, thumb enter, right click [tab]) has the command in the current command line.

So now I’m keeping a log, and all I need to do is to load that log my from Profile. PowerShell has an Add-History command and the on-line help talks about reading in the history from a CSV file so that was easy – I decided I would truncate the log when PowerShell started and also ensure that the file had the CSV header so here’s the reading friendly version of what’s in my profile.

$MaximumHistoryCount = 2048
$Global:logfile = "$env:USERPROFILE\Documents\windowsPowershell\log.csv"
$truncateLogLines = 100
$History = @()
$History += '#TYPE Microsoft.PowerShell.Commands.HistoryInfo'
$History += '"Id","CommandLine","ExecutionStatus","StartExecutionTime","EndExecutionTime"'
if (Test-Path $logfile) {$history += (get-content $LogFile)[-$truncateLogLines..-1] | where {$_ -match '^"\d+"'} }
$history > $logfile
$History | select -Unique  |
 Convertfrom-csv -errorAction SilentlyContinue |
 Add-History -errorAction SilentlyContinue

UPDATE Copying this code into the blog page and splitting the last $history line to fit, something went wrong and the
select -unique went astray. Oops.
It’s there because hitting enter doesn’t advance the History count, or run anything but does cause the prompt function to re-run. Now I’ve had to look it again it occurs to me it would be better to have select –unique in the (get-content $logfile) rather in the Add-history section as this would remove duplicates before truncating.

So … increase the history count, from the default of 64 (writing this I found that in V3 ctp 2 the default is 4096). Set a Global variable to be the path to the log file, and make it obvious what the length is I will truncate the log to.
Then build an array of strings named history. Put the CSV header information into $history, and if the log file exists put up to the truncate limit of lines into $history as well. Write $history back to the log file and pipe it into add history, hide any lines which won’t parse correctly. Incidentally those who like really long lines of PowerShell could recode all lines with $history in them into one line. So a couple of lines in the prompt function and between 3 and 9 lines in the profile depending on how you write them all in it’s less than a dozen lines. This blog post has taken a good couple of hours, and I don’t the code in 10 to 15 minutes.


Oh , and one thing I really like – when I launch PowerShell –Version 2 inside Version 3, it imports the history giving easy access to the commands I just used without needing to cut and paste.

If you’re a Bash user and didn’t storm off in a huff after my initial rudeness I’d like to set a good natured challenge. A non-compiled enhancement to bash I can load automatically which gives it tab expansion on par with PowerShell’s (OK, PowerShell has an unfair advantage completing parameters, so just command names and file names). And in case you wondered about the quote at the top of the post from one of Stephen Moffat’s Dr Who episodes. You see, “I Know PowerShell. I point and laugh at Bash users.”

December 20, 2011

Free NetCmdlets

Filed under: Uncategorized — jamesone111 @ 9:48 pm
I’ve mentioned the NetCmdlets before. Although not perfect if you spend a lot of your life using PowerShell and various network tools they are a big help. They’ve made a bunch of things which would have been longwinded and painful relatively easy. So here is a mail I have just had from PowerShell inside (aka /n software) . I don’t normally hold with pasting mails straight into a blog post, but you’ll see why if you read on: if you click the link it asks you to fill in some details and “A member of our sales team will contact you with your FREE NetCmdlets Workstation License. (Limit one per customer.)”

A Gift For The Holidays: FREE NetCmdlets Workstation License – This Week Only, Tell a Friend!

Help us spread some PowerShell cheer! Tweet, blog, post, email, or just tell a friend and you can both receive a completely free workstation license of NetCmdlets!

NetCmdlets includes powerful cmdlets offering easy access to every major Internet technology, including: SNMP, LDAP, DNS, Syslog, HTTP, WebDav, FTP, SMTP, POP, IMAP, Rexec/RShell, Telnet, SSH, Remoting, and more. Hurry, this offer ends on Christmas day – Happy Holidays!

NetCmdlets Workstation License:

$99.00 FREE

NetCmdlets Server License:
* Special Limited Time Offer *

$349.00 $199.00 [+] Order Now

Hurry, this offer ends on Christmas day – Get your free license now!

Happy Holidays!

Or as we say this side of the Pond Merry Christmas !

Next Page »

Create a free website or blog at WordPress.com.