James O'Neill's Blog

November 15, 2018

Putting Out-Printer back into PowerShell 6.1

Filed under: Powershell — jamesone111 @ 11:10 pm

One of the things long term PowerShell folk have to get on top of is the move from Windows PowerShell (up to V5.1) to PowerShell Core (V6 and beyond). PowerShell Core uses .NET core which is a subset that is available cross platform. Having a “Subset” means we pay a price for getting PowerShell on Linux, things in Windows-PowerShell which used parts not in the subset went missing from PowerShell 6 on Windows. When PowerShell 6.1 shipped the release notes said

On Windows, the .NET team shipped the Windows Compatibility Pack for .NET Core, a set of assemblies that add a number of removed APIs back to .NET Core on Windows.
We’ve added the Windows Compatibility Pack to PowerShell Core 6.1 release so that any modules or scripts that use these APIs can rely on them being available.

When they say “a number of”, I don’t know how big the number is, but I suspect it is a rather bigger and more exciting number than this quite modest statement suggests. The team blog says 6.1 gives Compatibility with 1900+ existing cmdlets in Windows 10 and Windows Server 2019, though they don’t give a breakdown of what didn’t work before, and what still doesn’t work.

But one command which is still listed as missing is Out-Printer. Sending output to paper might cause some people to think “How quaint”  but the command is still useful, not least because “Send to One note” and “Print to PDF” give a quick way of getting things into a file. In Windows PowerShell Out-Printer is in the Microsoft.PowerShell.Utility system module, but it has gone in PowerShell core. So I thought I would try to put it back. The result is named 6Print and you can install it from the PowerShell gallery (Install-Module 6print). It only works with the Windows version of PowerShell – .NET core on Linux doesn’t seem to have printing support. I’ve added some extra things to the original, you can now specify:

  • -PaperSize and –Landscape, -TopMargin, –BottonMargin, –LeftMargin and –RightMargin to set-up the page
  • -FontName and –FontSize, to get the print looking the way you want.
  • -PrintFileName  (e.g to specify the name of a PDF you are printing to)
  • -Path and -ImagePath although you would normally pipe input into the command (or pass the input as –inputObject) you can also specify a text file with -Path or a BMP, GIF, JEPG, PNG, TIFF file with –ImagePath

As well as –Name, –Printer or –PrinterName to select the printer (a little argument completer will help you fill in the name).

I may try to get this added to the main PowerShell project when it has had some testing. Because so many more things now work you can load the CIM cmdlets for print management with Import-Module -SkipEditionCheck PrintManagement.

it will install on PowerShell 5.1 if you want the extra options.

Advertisements

July 30, 2018

On PowerShell Parameters

Filed under: Powershell — jamesone111 @ 2:53 pm

When I talk about rules for good, reusable, PowerShell I often say “Parameters should be flexible …… so should constants.

The second half of that is a reminder that the first step from something quickly hacked together, towards something sharable is moving some key assignment statements to the top of the script, then putting param( ) around them and commas between them. Doing that means the  = whatever  part is setting the default for a parameter which can be changed at runtime. 

Good parameters allow the user to pipe input into commands, to provide an object or a name which allows the object to be fetched, they support multiple targets from one command (e.g. Get the contents of multiple files) and they help intellisense to suggest values to the user (validationSets, enum types and argument completers all help with that). I thought I did a good job with parameters most of the time – until someone commenting on work I’d contributed to Doug Finke’s ImportExcel  module showed me I wasn’t being as flexible as I should be, and how I had developed a bad habit.

The first thing to mention is that PowerShell is different to most other languages when it comes to labelling parameters with a type. In other places doing that means “this must be an X”; but if you write this in PowerShell:
Param (
   $p
  [int]$h,
  [boolean]$b,
  [EnumType]$e
)

It means “Try to make h an int, try to make b a boolean… don’t bother trying to P into anything”  and passing –h “Hello”  doesn’t cause a  “Type Mismatch Error” which other languages would throw, but PowerShell says ‘Cannot convert value "Hello" to type "System.Int32"’

in that example, none of the parameters is mandatory, and if none is specified PowerShell tries to convert the empty values: the Integer parameter – becomes zero, the boolean becomes false, and an enum type, will fail silently. This means we can’t tell from the value of $h or $b if the user wanted to change things to zero and false or they wanted things to be left as they are. We can use [Nullable[Boolean]] and [Nullable[Int]] and then code must allow for three states – the following will run code that we don’t want to be run when $b is null.
if ($b) {do something}
else    {do something different}

it needs to be something like  
# $b can be true, false or null
if     ($b) {do something}
elseif ($null –ne $b) {do something different}

I don’t like using Boolean parameters: when something is a “Do or Do not” choice like “Append” or “Force”* we would never specify –Append $false or –Force $false – so typing “True” is redundant.
The function in question sets formatting so I have
Param (            
  [int]$height,
  [switch]$bold,
  [switch]$italic,
  [switch]$underline,
  [EnumType]$alignment 
)
if ($bold)      {$row.bold      = $true}
if ($italic)    {$row.italic    = $true}
if ($underline) {$row.underline = $true}

This where my bad habit creeps in … at first sight there is nothing wrong with carrying on like this… 
if ($alignment) {$row.alignment = $alignment}
if ($height)    {$row.height    = $height   }

I test this by setting alignment to bottom and height to 20: everything works, and the code sets off into the world.   
Then the person who was testing my code said “I can’t set the height to zero” . My test can’t differentiate between “blank” and “zero”. Not allowing height to be zero might be OK, but there was worse to come: alignment is an Enum type
Top    = 0
Center = 1
Bottom = 2

etc.

Because “Top” is zero it is treated as false , so the code above works except when “top” is chosen. I need to use better tests.
The new test solved the next problem: my tester said “I can’t remove bold” . Of course, I had seen bold as “Do , or Do not. There is no un-do.”; because the main task of the code is to create new Excel sheets it will setting bold etc… almost exclusively.  And “Almost” is a nuisance.

I don’t want to change these parameters to Booleans because (a) it will break a lot of existing things and (b) it feels wrong to make everyone add “  $true” because a few sometimes use “ $false”. The  parameter list is already overcrowded so I don’t want to add -noBold –noUnderline and so on ; I’d need to figure out what to do about –bold and –notbold being specified together.  The least-inelegant solution I could come up with was based on a little used feature of switches…

Switch parameters are used without a value, if specified they are treated as true. But very early in my PowerShell career, I had a function which took a couple of switches which needed to be passed on to another command. I asked some kind soul (I forget who) how to do this and they said call the second command with –SecondSwitch:$FirstSwitch (in fact you can write any PowerShell parameter with a colon between the name and the value, instead of the conventional space) . So –bold:$false is valid, and   -bold still turns bold on.  But checking the value $bold will return false if the parameter was omitted or set to false explicitly.

So now I have 3 cases where I need to ask “was this parameter specified, or has it defaulted to being…”; and that’s what $PSBoundParameters is for – it’s a dictionary with the names and values that were passed into the command. Not values set as a parameter default, not parameters changed as the function proceeds; bound parameters. So I changed my code to this

if ($PSBoundParameters.ContainsKey('Bold')    ) {$row.Bold      = [boolean]$bold}
if ($PSBoundParameters.ContainsKey('Height')  ) {$row.Height    = $Height       }
if ($PSBoundParameters.ContainsKey(Alignment')) {$row.Alignment = $Alignment    }

So now if the parameters are given a value, whether it is false, zero, or an empty string, the property will be set. There is one last thing to do, and this is why I said it was the least inelegant solution, because –switch:$false is a rarely-used syntax, it’s reasonable to assume people won’t expect that to be the way to say “remove bold” so the parameter help needs to be updated to read “Make text bold; use -Bold:$false to remove bold”.

* If “Do or Do not” sounds familiar, Yoda would tell you that using the –Force switch is something you can not do in a try{}/ catch{} construct.

July 29, 2018

Windows Indexing not indexing properly ? Try this.

Filed under: Desktop Productivity — jamesone111 @ 10:54 am

A few weeks ago now my Surface Book died. It powered off in the middle of something; reluctantly powered on again and eventually went off and no known trick would get it to come back on; back it went and after a short delay a replacement (MK I) surface book arrived. A battery report showed the batteries were on their first charge cycle and it looks brand new. Nice.
A lot of my work is sync’d to one drive, and  quietly made its own way back but music and nearly  200GB of files in my Pictures directory, and a few other files aren’t. So they had to be copied back from my external hard disk. Everything seemed good, but a few funnies started to appear; the groove music app thought most of my music was on an unknown album by an unknown artist. Grouping pictures by tag didn’t work. Searches for pictures and documents didn’t find them even though when I picked through the directories they were there. It all pointed to something wrong with the index. So off to indexing options, and click Advanced and then Reset and wait for the index to chomp through all those files and … no change. 

imageSearch on-line and everything says “Re-build the index”; yes, thanks, done that – in fact I’d done it more than once. I’ve enough experience of the index to know that resetting it is usually the answer (“Wait” is sometimes the answer too, reset is good for the impatient). Some things say check that the directory you want is on the the list of directories to index. And yes, users is on the list and all the files are under there.
I’d put the problem to one side when I happened to click the advanced button on the properties of a directory, and there is an option which I had long forgotten:
“Allow files in this folder to have contents indexed”

Ah … now … what if copying files back from the hard disk had cleared that attribute ? So uncheck it, click OK, and Apply, and choose “Apply changes to this folder only”. Then go back, check the box , click OK, and this time say “Apply changes to this folder, subfolders and files” now force a re-index, searches work. Reset groove (from Windows Settings, Apps) and let it rediscover and artists and albums are back. So if the full text meta-data inside the file (as opposed Dates, size and file name) aren’t being indexed, this is worth a try .

May 31, 2018

More tricks with PowerShell and Excel

Filed under: Office,Powershell — jamesone111 @ 6:25 am

I’ve already written about Doug Finke’s ImportExcel module – for example, this post from last year covers

  • Basic exporting (use where-object to reduce the number of rows , select-object to remove columns that aren’t needed)
  • Using -ClearSheet to remove old data, –Autosize to get the column-widths right, setting titles, freezing panes and applying filters, creating tables
  • Setting formats and conditional format      
  • In this post I want to round up a few other things I commonly use.

    Any custom work after the export means asking Export-Excel to pass through the unsaved Excel Package object like this

    $xl = Get-WmiObject -Class win32_logicaldisk | select -Property DeviceId,VolumeName, Size,Freespace |
               Export-Excel -Path "$env:computerName.xlsx" -WorkSheetname Volumes –PassThru

    Then we can set about making modifications to the sheet. I can keep referring to it via the Excel package object, but it’s easier to use a variable. 
    $Sheet = $xl.Workbook.Worksheets["Volumes"]

    Then I can start applying formatting, or adding extra information to the file
    Set-Format -WorkSheet $sheet -Range "C:D" -NumberFormat "0,000"
    Set-Column -Worksheet $sheet -Column 5
    -Heading "PercentageFree" -Value {"=D$row/C$row"} -NumberFormat "0%" 

    I talked about Set-column in another post. Sometimes though, the data isn’t a natural row or column and the only way to do things is by “Poking” individual cells, like this

        
    $sheet.Cells["G2"].value = "Collected on"
    $sheet.Cells["G3"].value = [datetime]::Today
    $sheet.Cells["G3"].Style.Numberformat.Format =
     "mm-dd-yy"
    $sheet.Cells.AutoFitColumns()
    Close-ExcelPackage $xl –Show

    Sharp-eyed readers will see that the date format appears to be “Least-significant-in-the-middle” which is only used by one country – and not the one where I live. It turns out Excel tokenizes some formatsthis MSDN page explains and describes “number formats whose formatCode value is implied rather than explicitly saved in the file….. [some] can be interpreted differently, depending on the UI language”. In other words if you write “mm-dd-yy” or “m/d/yy h:mm” it will be translated into the local date or date time format. When Export-Excel encounters a date/time value it uses the second of these; and yes, the first one does use hyphens and the second does use slashes. My to-do list includes adding an argument completer for Set-Format so that it proposes these formats.

    Since the columns change their widths during these steps I only auto-size them when I’ve finished setting their data and formats. So now I have the first page in the audit workbook for my computer

    image

    Of course there times when we don’t want a book per computer with each aspect on it’s own sheet, but we want book for each aspect with a page per computer.
    If we want to copy a sheet from one workbook to another, we could read the data and write it back out like this

    Import-Excel -Path "$env:COMPUTERNAME.xlsx" -WorksheetName "volumes" | 
         Export-Excel
    -Path "volumes.xlsx" -WorkSheetname $env:COMPUTERNAME

    but this strips off all the formatting and loses the formulas  – however the Workbook object offers a better way, we can get the Excel package for an existing file with
    $xl1 = Open-ExcelPackage -path "$env:COMPUTERNAME.xlsx"

    and create a new file and get the Package object for it with 
    $xl2 = Export-Excel -Path "volumes.xlsx" -PassThru

    (if the file exists we can use Open-ExcelPackage). The worksheets collection has an add method which allows you to specify an existing sheet as the basis of the new one, so we can call that, remove the default sheet that export created, and close the files (saving and loading in Excel, or not, as required) 

    $newSheet = $xl2.Workbook.Worksheets.Add($env:COMPUTERNAME, ($xl1.Workbook.Worksheets["Volumes"]))
    $xl2.Workbook.Worksheets.Delete("Sheet1")
    Close-ExcelPackage $xl2 -show
    Close-ExcelPackage $xl1 –NoSave

    The new workbook looks the same (formatting has been preserved -  although I have found it doesn’t like conditional formatting) but the file name and sheet name have switched places.

    image

    Recently I’ve found that I want the equivalent of selecting “Transpose” in Excel’s paste-special dialog- take an object with many properties and instead of exporting it so it runs over many columns in making a two-column list of Property name and value
    For example
    $x = Get-WmiObject win32_computersystem  | Select-Object -Property Caption,Domain,Manufacturer,
                                Model, TotalPhysicalMemory, NumberOfProcessors, NumberOfLogicalProcessors

    $x.psobject.Properties | Select-Object -Property name,value |
        Export-Excel -Path "$env:COMPUTERNAME.xlsx" -WorkSheetname General -NoHeader -AutoSize –Show

    imagec

    When I do this i a real script I use the –passthru swtich and apply some formatting

    $ws    = $excel.Workbook.Worksheets["General"]
    $ws.Column(1).Width                     =  64
    $ws.Column(1).Style.VerticalAlignment   = "Center"
    $ws.Column(2).Width                     =  128
    $ws.Column(2).Style.HorizontalAlignment = "Left"
    $ws.Column(2).Style.WrapText            = $true

    Of course I could use Set-Format instead but sometimes the natural way is to refer to use .Cells[]  , .Row() or .Column().

    May 14, 2018

    A couple of easy boosts for PowerShell performance.

    Filed under: Powershell — jamesone111 @ 10:55 am

    At the recent PowerShell and Dev-ops summit I met Joshua King and went to his session – Whip Your Scripts into Shape: Optimizing PowerShell for Speed – (an area where I overestimated my knowledge) and it’s made me think about some other issues.  If you find this post interesting it’s a fair bet you’ll enjoy watching Joshua’s talk. There are a few of things to say before looking at a performance optimization which I added to my knowledge this week.

  • Because scripts can take longer to write than to run, we need to know when it is worth optimizing for speed. After all, if cut we the time from pressing return to the reappearance of the prompt from 1/2 second to 1/4 or even to 1/1000th second our reaction time is such that we don’t do the next thing we’re going to do any sooner. On the other hand if something takes 5 minutes to run (which might be the same command being called many times inside a script), giving minutes back is usable time.
  • Execution time varies with input – it often goes up with the square of the number of items being processed.  (Typically when the operation is in the form “For every item, look at [some subset of] all items”). So you might process 1,000 rows of data in half a second … but then someone takes your code and complains that their data take 5 minutes to process, because they’re working with many more rows. Knowing if you should optimize here isn’t straightforward  – most of the time doesn’t matter, but when it matters at all, it matters a lot.  You can discover if performance tails off badly at 10,000 or 1,000,000 rows but it isn’t easy to predict how many of any given size there will be and whether optimizing performance is time is well spent . If the problem happens at scale, then you might run sub-tasks in parallel (especially if each runs on a different computer), or change the way of working – for example this piece on hash tables is about avoiding the “look at every item” problem.
  • No one writes code to be slow. But the fast way might require something which is longer and/or harder to understand. If we want to write scripts which are reusable we might prefer tidy-but-slower over fast-but-incomprehensible. (All other things being equal we’d love the elegance of something tidy and fast, but a lot of us aren’t going to let the pursuit of that prevent us going home). 
    Something like $SetA | where {$_ –notIn $setB}  is easy to understand but if the sets are big enough it might need billions of comparisons, the work which gave rise to the hash tables piece  cut the number from billions to under a million (and meant that we could run the script multiple times per hour instead of once or twice in a day, so we could test it properly for the first time). But it takes a lot more to understand how it works.
  • One area from Joshua’s talk where the performance could be improved without adding complexity was reducing or eliminating the hit from using Pipelines; usually this doesn’t matter – in fact the convenience of being able to construct a bespoke command by piping cmdlets together was compelling before it was named “PowerShell”.  Consider these two scripts which time how long it takes to increment a counter a million times.

    $i  = 0 ; $j = 1..1000000 ;
    $sw = [System.Diagnostics.Stopwatch]::StartNew() ;
    $J | foreach {$i++ }  ;
    $sw.Stop() ; $sw.Elapsed.TotalMilliseconds

    $i  = 0 ; $j = 1..1000000 ;
    $sw = [System.Diagnostics.Stopwatch]::StartNew() ;
    foreach ($a in $j) {$i++ }  ;
    $sw.Stop() ; $sw.Elapsed.TotalMilliseconds

     The only thing which is different is the foreach – is it the alias for ForEach-Object, or is it a foreach statement . The logic hasn’t changed, and readability is pretty much the same; you might expect them to take roughly the same time to run … but they don’t: on my machine, using the statement is about 6 times faster than piping to the cmdlet.
    This is doing unrealistically simple work; replacing the two “ForEach” lines with

    $j | where {$_ % 486331 -eq 0}
    and
    $j.where(  {$_ % 486331 -eq 0} )

    does something more significant for each item and I find the pipeline version takes 3 times as long! And the performance improvement remains if the output of the .where() goes into a pipeline. I’ve written in the past that sometimes very long pipelines can be made easier to read by breaking them up (even though I have a dislike storing intermediate results), and it turns out we also can boost performance by doing that.

    Recently I found another change : if I define a function

    Function CanDivide {
    Param ($Dividend)
        $Dividend % 486331 -eq 0
    }
    and repeat the previous test with the command as
    $j.where( {CanDivide $_ } )

    People will separate roughly 50:50 into those who find the new version easier to understand, and those who say “I have to look somewhere else to see what ‘can divide’ does”. But is it faster or slower and by how much ? It’s worth verifying this for yourself, but my test said the function call makes the command slower by a factor of 6 or 7 times.  If a function is small, and/or is only called from one place, and/or is called many times to complete a piece of work then it may be better to ‘flatten’ the script. I’m in the “I don’t want to look somewhere else” camp so my bias is towards flattening code, but – like reducing the amount of piping – it might feel wrong for other people. It can make the difference between “fast enough”, and “not fast enough” without major changes to the logic.

    January 8, 2018

    Using the Surface Dial with Adobe LightRoom.

    Filed under: Photography — jamesone111 @ 4:54 pm

    When the surface dial came out the, Wired ran a story You Might not need Microsoft’s surface dial, but you’ll want it.  That sums it up for me, I have the Surface book and the “pen” is a great “brush” for editing photos. It also to plays the surface book’s range of form factors – “folded over” tablet mode with an external keyboard and mouse, it is similar to having a Wacom Cintiq tablet . The dial promises to be the ideal companion for the pen – except that Adobe didn’t seem to be in any hurry to add dial support to Photoshop and Lightroom. Then late in 2017 Adobe added dial support as a ‘technology preview’ in Photoshop… And Santa brought me a surface dial.

    The phrase “No can be told what it is – you have to see it for yourself” from The Matrix feels like it is only one step away from the the Wired article.  Which calls it “Microsoft’s coolest input device ever”  and says “The gadget, which … twists like a doorknob, is a peripheral, like a mouse and keyboard. Except it’s not like those things at all.”  It’s not very like a door knob, more like main knob on a hi-fi system or the single do-everything controller you find in some cars find to control the trip-computer, navigation and sound – after a few seconds use it becomes obvious.  The dial has 4 actions – long press, short press twist-left, twist right, and it “tocks” to tell you it has done something. A long press pulls up a menu which depends the active application – this menu lets you choose which function the twist and short press will perform. There are some generic windows functions available – undo/redo, zoom, scroll up & down, and system volume control. Things that support the Pinch-zoom or two-finger scroll gestures or [ctrl]+[z]  / [ctrl]+[y] should work with the out of the box functions – if there is a music player running which supports the “next track / previous track” those functions light up – and when controlling music a short press acts as play/pause.

    The main thing about the dial is that it doesn’t replace the mouse and if you have both hands on the keyboard you probably wouldn’t take one off to use the dial – but when you have the pen in your right hand and you want to change what it is doing, having your left hand on the dial really works, so for Photoshop it controls brush size , opacity and so on. 

    After a short time with it you start to think “It should do this” . In LightRoom you don’t get the brush controls that you have in Photoshop, the pen isn’t great for working slider controls and pictures scroll left and right not up and and down: fortunately there is an answer – in Windows Settings, under Devices, the Wheel option lets you configure these things for yourself – the one restriction is that this sends key strokes to the application so I still don’t have a way to do the things which need  (for example) ALT + Click. But for the three examples above it’s pretty easy. Here’s how the finished result looks

    image

    So on the first page of wheel settings you select your app, and then you configure the tools that are available from the dial,  as you can see I’ve set up three. [1] is “Select” which goes right or left (with no shift/ctrl/alt keys) for twist and D (Develop) for click. Sliders do [+] or [-] for twist, and “,” – the short cut for “cycle round basic sliders” – for click, and finally there is brush size

    image

    You can see when I do a long click I get these options as 1,2 and 3 with the name in the middle, and I get volume control, scroll, zoom , and undo/redo as well. It’s not as elegant as the way it works in Photoshop, but it works well enough.  To control the brush I size I set-up the following:

    image

    It’s all fairly easy, provided that there is a key combination for what you want to do. I’d like to be able to say for the brush clicking the dial is Alt + Left-Mouse-Click (“clone from here” in Adobe) or assign the side button on the surface Pen to Alt + Left-Mouse-Click instead of Right-Mouse-Click. But for now this will do just fine.

    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

    clip_image002

    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

    clip_image004

    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

    clip_image006

    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

    clip_image008

    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

    clip_image010

    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.

    December 11, 2017

    Using the Import Excel module part 2: putting data into .XLSx files

    Filed under: Office,Powershell — jamesone111 @ 3:55 pm

    This is third of a series of posts on Excel and PowerShell – the first on getting parts of an Excel file out as images wasn’t particularly tied to the ImportExcel Module, but the last one, this one and next one are.  I started with the Import Command – which seemed logical given the name of the module; the Export command is more complicated, because we may want to control the layout and formatting of the data, add titles, include pivot tables and draw charts;. so I have split it into two posts. At its simplest the command looks like this :

    Get-Process | Export-Excel -Path .\demo.xlsx -Show

    This gets a list of processes, and exports them to an Excel file; the -Show switch tells the command to try to open the file using Excel after saving it. I should be clear here that import and export don’t need Excel to be installed and one of the main uses is to get things into Excel format with all the extras like calculations, formatting and charts on a computer where you don’t want to install desktop apps; so –Show won’t work in those environments.  If no –WorksheetName parameter is give the command will use “Sheet1”.

    Each process object has 67 properties and in the example above they would all become columns in the worksheet, we can make things more compact and efficient by using Select-Object in the command to filter down to just the things we need:

    Get-Process | Select-Object -Property Name,WS,CPU,Description,StartTime |
    Export-Excel -Path .\demo.xls -Show
     

    Failed exporting worksheet 'Sheet1' to 'demo.xls':
    Exception calling ".ctor" with "1" argument(s):
    "The process cannot access the file 'demo.xls' because it is being used by another process."

    This often happens when you look at the file and go back to change the command and forget to close it – we can either close the file from Excel, or use the -KillExcel switch in Export‑Excel – from now on I’ll use data from a variable

    $mydata = Get-Process | Select-Object -Property Name, WS, CPU, Description, Company, StartTime
    $mydata | Export-Excel -KillExcel -Path .\demo.xlsx -Show

    This works, but Export-Excel modifies the existing file and doesn’t remove the old data – it takes the properties of the first item that is piped into it and makes them column headings, and writes each item as a row in the spreadsheet with those properties. (If different items have different properties there is a function Update-FirstObjectProperties to ensure the first row has every property used in any row). If we are re-writing an existing sheet, and the new data doesn’t completely cover the old we may be left with “ghost” data. To ensure this doesn’t happen, we can use the ‑ClearSheet option

    $mydata | Export-Excel -KillExcel -Path .\demo.xlsx -ClearSheet -Show

    clip_image002

    Sometimes you don’t want to clear the sheet but to add to the end of it, and one of the first changes I gave Doug for the module was to support a –Append switch, swiftly followed by a change to make sure that the command wasn’t trying to clear and append to the same sheet.

    We could make this a nicer spreadsheet – we could make it clear the column headings look like headings, and even make them filters, we can also size the columns to fit…

    $mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet `
                 -BoldTopRow
    -AutoSize -Title "My Processes" -TitleBold -TitleSize 20 -FreezePane 3 -AutoFilter -Show

    clip_image004

    The screen shot above shows the headings are now in bold and the columns have been auto sized to fit. A title has been added in bold, 20-point type; and the panes have been frozen above row 3. (There are options for freezing the top row or the left column or both, as well as the option used here –FreezePane row [column]) and filtering has been turned on.

    Another way to present tabular data nicely is to use the -Table option

    $mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" -ClearSheet –BoldTopRow    -AutoSize `
           -TableName table -TableStyle Medium6 -FreezeTopRow -show

    clip_image006

    “Medium6” is the default table style but there are plenty of others to choose from, and intellisense will suggest them

    clip_image008

    Sometimes it is helpful NOT to show the sheet immediately, and one of the first things I wanted to add to the module was the ability to pass on an object representing the current state of the workbook to a further command, which makes the following possible:

    $xl = $mydata | Export-Excel -Path .\demo.xlsx -KillExcel -WorkSheetname "Processes" `
        
    -ClearSheet -AutoSize -AutoFilter -BoldTopRow –FreezeTopRow -PassThru

    $ws = $xl.Workbook.Worksheets["Processes"]

    Set-Format -WorkSheet $ws -Range "b:b" -NumberFormat "#,###"   -AutoFit
    Set-Format -WorkSheet $ws -Range "C:C" -NumberFormat "#,##0.00" -AutoFit
    Set-Format -WorkSheet $ws -Range "F:F" -NumberFormat "dd MMMM HH:mm:ss" -AutoFit

    The first line creates a spreadsheet much like the ones above, and passes on the Excel Package object which provides the reference to the workbook and in turn to the worksheets inside it.
    The example selected three columns from the worksheet and applied different formatting to each. The module even supports conditional formatting, for example we could add these lines into the sequence above

    Add-ConditionalFormatting -WorkSheet $ws -Range "c2:c1000" -DataBarColor Blue
    Add-ConditionalFormatting -WorkSheet $ws -Range "b2:B1000" -RuleType GreaterThan
    `
               
    -ConditionValue '104857600'  -ForeGroundColor "Red" -Bold

    The first draws data bars so we can see at glance what is using CPU time and the second makes anything using over 100MB of memory stand out.

    Finally, a call to Export-Excel will normally apply changes to the workbook and save the file, but there don’t need to any changes – if you pass it a package object and don’t specify passthrough it will save your work, so “Save and Open in Excel” is done like this once we have put the data in a formatted it the way we want.

    Export-Excel -ExcelPackage $xl -WorkSheetname "Processes" -Show

    clip_image002[1]

    In the next post I’ll look at charts and Pivots, and the quick way to get SQL data into Excel

    December 5, 2017

    Using the Import-Excel module: Part 1 Importing

    Filed under: Office,Powershell — jamesone111 @ 9:15 am

    The “EEPLus” project provides .NET classes to read and write XLSx files without the need to use the Excel object model or even have Excel installed on the computer (XLSx files, like the other  Office Open XML Format are actually .ZIP format files, containing XML files describing different aspects of the document – they were designed to make that sort of thing easier than the “binary” formats which went before.)   Doug Finke, who is well known in the PowerShell community, used EEPlus to build a PowerShell module named ImportExcel which is on GitHub and can be downloaded from the PowerShell gallery (by running Install-Module ImportExcel on PowerShell 5 or PS4 with the Package Management addition installed) As of version 4.0.4 his module contains some of my contributions. This post is to act as an introduction to the Export parts of the module that I contributed to; there are some additional scripts bundled into the module which do require Excel itself but the core Import / Export functions do not. This gives a useful way to get data on a server into Excel format, or to provide users with a work book to enter data in an easy to use way and process that data on the server – without needing to install Microsoft Office or translate to and from formats like .CSV.

    The Import-Excel command reads data from a worksheet in an XLSx file. By default, it assumes the data has headers and starts with the first header in Cell A1 and the first row of data in row 2. It will skip columns which don’t have a header and but will include empty rows. If no worksheet name is specified it will use the first one in the work book, so at its simplest the command looks like :
    Import-Excel -Path .\demo.xlsx  

    It’s possible that the worksheet isn’t the first sheet in the workbook and/or has a title above the data, so we can specify the start point explicitly
    Import-Excel -Path .\demo.xlsx -WorkSheetname winners -StartRow 2  

    We can say the first row does not contain headers and either have each property (column) named P1, P2, P3 etc, by using the ‑NoHeader switch or specify header names with the -HeaderName parameter like this

    Import-Excel -Path .\demo.xlsx -StartRow 3 -HeaderName “Name”,"How Many"

    The module also provides a ConvertFrom-ExcelSheet command which takes -Encoding and -Delimiter parameters and sends the data to Export-CSV with those parameters, and a ConvertFrom-ExcelToSQLInsert command which turns each row into a SQL statement: this command in turn uses a command ConvertFrom-ExcelData, which calls Import-Excel and then runs a script block which takes two parameters PropertyNames and Record.

    Because this script block can do more than convert data, I added an alias “Use-ExcelData” which is now  part of the module and can be used like this
    Use-ExcelData -Path .\NewUsers.xlsx -HeaderRow 2 -scriptBlock $sb

    If I define the script block as below, each column becomes a parameter for the New-AdUser command which is run for each row

    $sb = {
      param($propertyNames, $record)
      $propertyNames | foreach-object -Begin {$h = @{} }  -Process {
          if ($null -ne $record.$_) {$h[$_] = $record.$_}
      } -end {New-AdUser @h -verbose}
    }

    The script block gets a list of property names and a row of data: the script block gets called for each row and creates a hash table, adds an entry for each property and finally Splats the parameters into a command. It can be any command in the end block provided that the column names in Excel match its Parameters , I’m sure you can come up with your own use cases.

    November 25, 2017

    Getting parts of Excel files as images.

    Filed under: Office,Powershell — jamesone111 @ 7:54 pm

    I feel old when I realise its more than two decades since I learnt about the object models in Word, Excel and even Microsoft project and how to control them from other applications. Although my preferred tool is now PowerShell rather than Access’s version of Visual basic, the idea that “it’s all in there somewhere” means I’ll go and do stuff inside Excel from time to time…

    One of the things I needed to do recently was to get performance data into a spreadsheet with charts – which the export part of Doug Finke’s ImportExcel module handles very nicely. But we had a request to display the charts on a web page without the need to open an Excel file, so it was time to have a look around in Excel’s [very hierarchical] object model.

    An Excel.Application contains
    …. Workbooks which contain
    …. …. Worksheets which contain
    …. …. …. Chartobjects each of which contains
    …. …. …. …. A Chart which has
    …. …. …. …. …. An Export Method

    It seems I can get what I need if I get an Excel application object, load the workbook, work through the sheets, find each chart, decide a name to save it as and call its export method. The PowerShell to do that looks like this

    $OutputType    = "JPG"
    $excelApp      = New-Object -ComObject "Excel.Application"
    $excelWorkBook = $excelApp.Workbooks.Open($path)
    foreach ($excelWorkSheet in $excelWorkBook.Worksheets) {
      foreach ($excelchart in $excelWorkSheet.ChartObjects([System.Type]::Missing)) {
        $excelApp.Goto($excelchart.TopLeftCell,$true)
        $imagePath = Join-Path -Path $Destination -ChildPath ($excelWorkSheet.Name +
                            "_" + ($excelchart.Chart.ChartTitle.Text + ".$OutputType"))
        $excelchart.Chart.Export($imagePath, $OutputType, $false)    
      }
    }
    $excelApp.Quit()

    A couple of things to note – the export method can output a PNG, JPG or GIF file and in the final version of this code, $OutputType is passed as a parameter (like $Path and $Destination  I’ve got into the habit of capitalizing parameter names, and starting normal variables with lowercase letters). There’s a slightly odd way of selecting ‘all charts’ and if the chart isn’t selected before exporting it doesn’t export properly.

    I sent Doug a this which he added to his module (along with some other additions I’d been meaning to send him for over a year!). Shortly afterwards he sent me a message 
    Hello again. Someone asked me about png files from Excel. They generate a sheet, do conditional formatting and then they want to save is as a png and send that instead of the xlsx…

    Back at Excel’s object model… there isn’t an Export method which applies to a range of cells or a whole worksheet – the SaveAs method doesn’t have the option to save a sheet (or part of one) as an image. Which left me asking “how would I do this manually?” I’d copy what I needed and paste it into something which can save it. From version 5 PowerShell has a Get-Clipboard cmdlet which can handle image data. (Earlier versions let you access the clipboard via the .net objects but images were painful). The Excel object model will allow a selection to be copied, so a single script can load the workbook, make a selection, copy it, receive it from the clipboard as an image and save the image.

    $Format = [system.Drawing.Imaging.ImageFormat]::Jpeg
    $xlApp  = New-Object -ComObject "Excel.Application"
    $xlWbk  = $xlApp.Workbooks.Open($Path)
    $xlWbk.Worksheets($WorkSheetname).Select()
    $xlWbk.ActiveSheet.Range($Range).Select() | Out-Null
    $xlApp.Selection.Copy() | Out-Null
    $image = Get-Clipboard -Format Image
    $image.Save($Destination, $Format)

    In practice $Path, $Worksheetname, $Range, $Format and $Destination are all parameters. And the whole thing is wrapped in a function Convert-XlRangeToImage
    Excel puts up a warning that there is a lot of data in the clipboard on exit and to stop that I copy a single cell before exiting.

    $xlWbk.ActiveSheet.Range("a1").Select() | Out-Null
    $xlApp.Selection.Copy() | Out-Null
    $xlApp.Quit()

    The Select and Copy methods return TRUE if they succeed so I send those to Null. The whole thing combines with Doug’s module like this

    $excelPackage = $myData | Export-Excel -Path $Path -WorkSheetname $workSheetname
    $workSheet    = $excelPackage.Workbook.Worksheets[$workSheetname]
    $range        = $workSheet.Dimension.Address
    #      << apply formatting >>
    Export-Excel -ExcelPackage $excelPackage -WorkSheetname $workSheetname
    Convert-XlRangeToImage -Path $Path -WorkSheetname $workSheetname -Range $range –Destination "$pwd\temp.png" –Show

    I sent the new function over to Doug and starting with version 4.0.8 it’s part of the downloadable module

    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        {
    <#
    .Synopsis
        Runs an instance of the current program As Administrator
    #>

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

    June 16, 2017

    More on writing clear scripts: Write-output and return … good or bad ?

    Filed under: Powershell — jamesone111 @ 11:26 am

    My last post talked about writing understandable scripts and I read a piece entitled Let’s kill Write-Output by Mark Krauss (actually I found it because Thomas Lee Tweeted it with “And sort out return too”).

    So let’s start with one practicality. You can’t remove a command which has been in a language for 10 years unless you are prepared for a lot of pain making people re-write scripts. It’s alias “echo” was put there for people who come from other scripting languages, and start by asking “How do I print to the console”. But if removing it altogether is impractical, we can advise people to avoid it, write rules to catch it in the script analyser and so on. Should we ? And when is a good idea to use it ?

    Mark points out he’s not talking about Write-host, which should be kept for limited scenarios: if you want the user to see it by default, but it isn’t part of the output then that’s a job for Write-host, for example with my Get-SQL command   $result = Get-SQL $sqlQuery writes “42 rows returned” to the console but the output saved into $result is the 42 rows of data. Mark gives an example:    
    Write-Output "PowerShell Processes:"
    Get-Process -Name PowerShell

    and says it is better written as  
    "PowerShell Processes:"
    Get-Process -Name PowerShell

    And this is actually a case where Write-host should be used … why ? Let’s turn that into a function.
    Function Get-psProc {
      "PowerShell Processes:"
      Get-Process -Name"*PowerShell*"
    }

    Looks fine doesn’t it ? But it outputs two different types of object into the pipeline. All is well if we run Get-psProc on its own, but if we run
     Get-psProc | ConvertTo-Csv 
    It returns
    #TYPE System.String
    "Length"
    "21" 

    The next command in the pipeline saw that the first object was a string and that determined its behaviour. “PowerShell processes” is decoration you want the user to see but isn’t part of the output. That earlier post on understandable scripts came from a talk about writing good code and the one of the biggest problems I find in other peoples code is fixation with printing to the screen.  That leads to things like the next example – which is meant to read a file and say how many lines there are and their average length.

    $measurement = cat $path | measure -average Length
    echo ("Lines read    : {0}"    -f $measurement.Count  )
    echo ("Average length: {0:n0}" -f $measurement.Average)

    This runs and it does the job the author intended but I’d suggest they might be new to PowerShell and haven’t yet learnt that Output is not the same as “stuff for a user to read” (as in the previous example) , and they feel their output must be printed for reading. Someone more experienced with PowerShell might just write:
    cat $path| measure -average Length
    If they aren’t bothered about the labels,  or if the labels really matter
    cat $path | measure -average Length | select @{n="Lines Read";e={$_.count}}, @{n="Average Length";e={[math]::Round($_.Average,2)}}

    If this is something we use a lot, we might change the aliases to cmdlet names, specify parameter names and save it for later use. And it is re-usable, for example if we want to do something when there are more than x lines in the file, where the previous version can only return text with the number of lines embedded in it.  Resisting the urge to print everything is beneficial and that gets rid of a lot of uses of Write-output (or echo).

    Mark’s post has 3 beefs with Write-Output.

    1. Performance. It is slower but rarely noticeably so, so I’d discount this.
    2. Security / Predictability – Write-Output can be redefined, and that allows for something malign or just buggy. True, but it also allows you to redefine it for logging debugging and so on. So you could use a proxy Write-output for testing and the standard one in production. So this is not exclusively bad
    3. The false sense of security. He says that explicitly returning stuff is held to be better than implicit return, which implies
      Write-Output $result
            is better than just   $result            
      But no-one says you should write    cat $path | Write-Output it’s obviously redundant, but when you don’t isn’t that implying output ?

    My take on the last point is piping output into write-output (or Out-Default) is a tautology “Here’s some output, take it and output it”. It’s making things longer but not clearer. If using write-output does make things clearer then it is a sign the script is hard to read and at least needs some comments, and possibly some redesign. Joel Bennett sums up the false sense of security part in a sentencewhile some people like it because it highlights the spots where you intentionally output something — other people argue it’s presence distracts you from the fact that other lines could output.”  [Thanks Joel, that would have taken me a paragraph!]

    This is where Thomas’ comment about return comes in. Return tells PowerShell to bail out of a function, and there are many good reasons for doing that, it also has a two in one syntax :  return $result is the same as
    $result
    return

    When I linked to Joel above he also asks the question whether, as the last lines of a function, this
    $output = $temp + (Get-Thing $temp)
    return $output

    is better or worse than
    $output = $temp + (Get-Thing $temp)
    $output

    Not many people would add return to the second example – it’s redundant.  But if you store the final output in a variable there is some logic to using return (or Write-output) to send it back. But is it making things any clearer to store the result in a variable ? or it just as easy to read the following.  
    $temp + (Get-Thing $temp)

    As with Write-output, sometimes using return $result makes things clearer and sometimes it’s a habit from other programming languages where functions return results in a single  place so multiple parts must be gathered and then returned. Here’s something which combines the results of 3 queries and returns them

    $result =  (Get-SQL $sqlQuery1)
    $result += (Get-SQL $sqlQuery2)
    $result +  (Get-SQL $sqlQuery3)

    So the first line assigns an array of database rows to a variable the second appends more rows and the third returns these rows together with the results of a third query.  You need to look at  the operator in each line to figure out which sends to the pipeline. Arguably this it is clearer to replace the last line with this:

    $result += (Get-SQL $sqlQuery3)
    return $result

    When there are 3 or 4 lines between introducing $result and putting it into the pipeline this is OK. But lets say there are 50 lines of script between storing the results of the first query and appending the results of the second.  Has the script been made clearer by storing a partial result … or would you see something being appended to $result and look further up the script for where it was originally set and anywhere it was changed ? This example does nothing with the combined segments (like sorting them) we’re just following an old habit of only outputting in one place. Not outputting anything until we have everything can mean it takes a lot longer to run the script – we could have processed all the results from the first query while waiting for the second to run. I would dispense with the variable entirely and use

    Get-SQL $sqlQuery1
    Get-SQL $sqlQuery2
    Get-SQL $sqlQuery3

    If there is a lot of script between each I’d then use a #region around the lines which lead up to each query being run
    #region build query and return rows for x
    #etc etc
    Get-SQL $sqlQuery1
    #endregion 

    so when I collapse the outlining regions in my editor I see
    #region build query and return rows for x
    #region build query and return rows for y
    #region build query and return rows for z

    Which gives me a very good sense of what the script is doing at a high level and then I can drill into the regions if I need to. If I do need to do something to the combined set of rows (like sorting) then my collapsed code might become
    #region build query for x and keep rows for sorting later
    #region build query for y and keep rows for sorting later
    #region build query for z and keep rows for sorting later
    #region return sorted and de-duplicated results of x,y and Z

    Both outlines give a sense of where there should be output and where any output might be a bug.

    In conclusion. 
    When you see Lots of echo / write-output commands that’s usually a bad sign – it’s usually an indication of too many formatted strings going into the pipeline, but Write-Output is not automatically bad when used sparingly – and used properly return isn’t bad either. But if you find yourself adding either for clarity it should make you ask “Is there a better way”.  


    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.   

    March 13, 2017

    Improving PowerShell performance with hash tables.

    Filed under: Powershell — jamesone111 @ 1:07 pm

    Often the tasks which we do with PowerShell scripts aren’t very sensitive to performance – unless we are sitting drumming our fingers on the desk waiting for it to complete there isn’t a lot of value in making it faster.   When I wrote about start-Parallel, I showed that some things only become viable if they can be run reasonably quickly; but you might assume that scripts which run as scheduled tasks can take an extra minute if they need to . 

    imageThat is not always the case.  I’ve been working with a client who has over 100,000 Active directory users enabled for Lync (and obviously they have a lot more AD objects than that). They want to set Lync’s policies based on membership of groups and users are not just placed directly into the policy groups but nested via other groups. If users have a policy but aren’t in the associated group, the policy needs to be removed. There’s a pretty easy Venn diagram for what we need to do.

    If you’ve worked with LDAP queries against AD, you may know how to find the nested members of the group using(memberOf:1.2.840.113556.1.4.1941:=<<Group DN>>).
    The Lync/Skype for Business cmdlets won’t combine an LDAP filter for AD group membership and non-AD property filter for policy – the user objects returned actually contain more than a dozen different policy properties, but for simplicity I’m just going to use ‘policy’ here – as pseduo-code the natural way to find users and change policy – which someone else had already written – looks like this
    Get-CSuser –ldapfiler "(   memberOf <<nested group>> )" | where-object {$_.policy –ne $PolicyName} | Grant-Policy $PolicyName
    Get-CSuser –ldapfiler "( ! memberOf <<nested group>>) " | where-object {$_.policy –eq $PolicyName} | Grant-Policy $null

    (Very late in the process I found there was a way to check Lync / Skype policies from AD but it wouldn’t have changed what follows). 
    You can see that we are going to get every user, those in the group in the first line and those out of it in the second. These “fan out” queries against AD can be slow – MSDN has a warning “Some such queries on subtrees may be more processor intensive, such as chasing links with a high fan-out; that is, listing all the groups that a user is a member of.”   Getting the two sets of data was taking over an hour.  But so what? This is a script which runs once a week, during quiet hours, provided it can run in the window it is given all will be well.  Unfortunately, because I was changing a production script, I had to show that the correct users are selected, the correct changes made and the right information written to a log. While developing a script which will eventually run as scheduled task, testing requires we run it interactively, step through it, check it, polish it, run it again, and a script with multiple segments which run for over an hour is, effectively, untestable (which is why I came to be changing the script in the first place!).

    I found I could unpack the nested groups with a script a lot more quickly than using the “natural” 1.2.840.113556.1.4.1941 method; though it feels wrong to do so. I couldn’t find any ready-made code to do the unpack operation – any search for expanding groups comes back to using the OID method which reinforces the idea. 

    I can also get all 100,000 Lync users – it takes a few minutes, but provided it is only done once per session it is workable, (I stored the users in a global variable if it was present I didn’t re-fetch them) 

    So: I had a variable $users with users and a variable $members which contains all the members of the group; I just had to work out who is each one but not in both. But I had a new problem. Some of the groups contain tens of thousands of users. Lets assume half the users have the policy and half don’t. If I run
    $users.where{$_.policy -ne $PolicyName -and $members -contains $_.DistinguishedName}
    and
    $users.where{$_.policy -eq $PolicyName -and $members -notcontains $_.DistinguishedName}
    the -contains operation is going to have a LOT of work to do: if everybody has been given the right policy none of the 50,000 users without it are in the group but we have to look at all 50,000 group-members to be sure – 2,500,000,000 string comparisons. For the 50,000 users who do have the policy, on average [Not]contains has to look at half the group  members before finding a match so that’s 1,250,000,000 comparisons. 3.75 Billion comparisons for each policy  means it is still too slow for testing. Then I had a flash of inspiration, something which might work.

    I learnt about Hash tables as a computer science undergraduate, and – as the on-line help puts it  – they are “very efficient for finding and retrieving data”. This can be read as they lead to neat code (which has been their attraction for me in the past) or as they minimize CPU use, which is what I need here.  Microsoft also call hash tables  “associative arrays” and often the boundary between a set of key-value pairs (a dictionary) and a “true” hash table is blurred – with a “true” hash tables the location of data in memory is based on the key value – so an item can be found without scanning the whole list. Some ways to do fast finds make tables slow to build. Things I’d never considered with PowerShell hash tables might turn out to be important at this scale. So I built a hash table to return a user’s policy given their DN:
    $users | ForEach-Object -Begin {$hash=@{}} -Process {$hash[$_.distinguishedName] = "" + $_.policy}

    About 100,000 users were processed in under 4 seconds, which was a relief; and the right policy came back for $hash[“cn=bob…”] – it looked instant compared with a couple of seconds with $users.where({$_.distinguishedName –eq “cn=bob…”}).policy

    This hash table will return one of 3 things. If Bob isn’t set-up for Lync/Skype for Business I will get NULL; if Bob has no policy I will get an empty string (that’s why I add the policy to an empty string – it also forces the policy object to be a string), and if he has a policy I get the policy name. So it was time to see how many users have the right policy (that’s the magenta bit in the middle of the Venn diagram above)
    $members.where({$hash[$_] -like $policyname}).count

    I’d found ~10,000 members in one of the policy groups, and reckoned if I could get the “find time” down from 20 minutes to 20 seconds that would be OK and … fanfare … it took 0.34 seconds. If we can check can look up 10,000 items in a 100,000 item table in under a second, these must be proper hash tables. I can have the .where() method evaluate
    {$hash[$_] –eq $Null} for the AD users who aren’t Lync users or
    {$hash[$_] –notin @($null,$policyName) } for users who need the policy to be set. 
    It works just as well the other way around for setting up the hash table to return “True” for all members of the AD group; non-members will return null, so we can use that to quickly find users with the policy set but who are not members of the group. 

    $members | ForEach-Object -Begin {$MemberHash=@{}} -Process {$MemberHash[$_] = "" + $true}
    $users.where({$_.policy -like $policyname -and -not $memberhash[$_.distinguishedName]}).count

    Applying this to all the different policies slashed the time to do everything in the script from several hours down to a a handful of minutes.  So I could test thoroughly before the ahead of putting the script into production.

    January 29, 2017

    Sharing GetSQL

    Filed under: Databases / SQL,Powershell — jamesone111 @ 8:03 pm

    I’ve uploaded a tool named “GetSQL” to the PowerShell Gallery
    Three of my last four posts (and one upcoming one) are about sharing stuff which I have been using for a long time and GetSQL goes back several years – every now and then I add something to it, so it seems like it might never be finished, but eventually I decided that it was fit to be shared.

    When I talk about good PowerShell habits, one of the themes is avoiding massive “do-everything” functions; it’s also good minimize the number of dependencies on the wider system state – for example by avoiding global variables. Sometimes it is necessary to put these guidelines to one side –  the module exposes a single command, Get-SQL which uses argument completers (extra script to fill in parameter values). There was a time when you needed to get Jason Shirks TabExpansion Plus Plus to do anything with argument completers but, if you’re running a current version of PowerShell, Register-ArgumentCompleter is now a built in cmdlet. PowerShell itself helps complete parameter names – and Get-SQL is the only place I’ve made heavy use of parameter sets to strip out parameters which don’t apply (and had to overcome some strange behaviour along the way); having completers to fill in the values for the names of connections, databases, tables and column names dramatically speeds up composing commands – not least by removing some silly typos.

    One thing about Get- commands in PowerShell is that if the PowerShell parser sees a name which doesn’t match a command where a command-name should be it tries putting Get- in front of the name so History runs Get-History, and SQL runs Get-SQL. That’s one of the reasons why the command didn’t begin life as “Invoke-SQL” and instead became a single command. I also wanted to be able to run lots of commands against the same database without having to reconnect each time. So the connection objects that are used are global variables, which survive from one call to the next – I can run
    SQL “select id, name from customers where name like ‘%smith’ ”
    SQL “Select * from orders where customerID = 1234”

    without needing to make and break connections each time – note, these are shortened versions of the command which could be written out in full as: 
    Get-SQL –SQL “Select * from orders where customerID = 1234”
    The first time Get-SQL runs it needs to make a connection, so as well as –SQL it has a –Connection parameter (and extra switches to simplify connections to SQL server, Access and Excel). Once I realized that I needed to talk to multiple databases, I started naming the sessions and creating aliases from which the session  can be inferred (I described how here) so after running
    Get-SQL -Session f1 -Excel  -Connection C:\Users\James\OneDrive\Public\F1\f1Results.xlsx
    (which creates a session named F1 with an Excel file containing results of formula one races) I can dump the contents of a table with
    f1 –Table “[races]”

    Initially, I wanted to either run a chunk of SQL –like the first example (and I added a –Paste switch to bring in SQL from the Windows Clipboard), or to get the whole of a table (like the example above with a –Table parameter), or see what tables had be defined and their structure so I added –ShowTables and –Describe.  The first argument completer I wrote used the “show tables” functionality to get a list of tables and fill in the name for the –Table or –Describe parameters. Sticking to the one function per command rule one would mean writing “Connect-SQL”, “Invoke-RawSQL” “Invoke-SQLSelect”, and “Get-SQLTable” as separate commands, but it just felt right to be able to allow
    Get-SQL -Session f1 -Excel  -Connection C:\Users\James\OneDrive\Public\F1\f1Results.xlsx –showtables

    It also just felt right to allow the end of a SQL statement to be appended to what the command line had built giving a command like the following one – the output of a query can, obviously, be passed through a PowerShell Where-Object command but makes much more sense to filter before sending the data back.
    f1 –Table “[races]” “where season = 1977”

    “where season = 1977” is the –SQL parameter and –Table “[races]” builds “Select * from [Races]” and the two get concatenated to
    “Select * from [Races] where season = 1977”.
    With argument completers for the table name, it makes sense to fill column names in as well, so there is a –Where parameter with a completer which sees the table name and fills in the possible columns. So the same query results from this:
    f1 –Table “[races]”   -where “season”   “= 1977”  
    I took it a stage further and made the comparison operators work like they do in where-object, allowing
    f1 –Table “[races]” -where “season” -eq 1977

    The script will sort out wrapping things in quotes, changing the normal * into SQL’s % for wildcards and so on. With select working nicely (I added –Select to choose columns, –OrderBy, –Distinct and –GroupBy ) I moved on to insert, update (set) and Delete functionality. You can see what I meant when I said I keep adding bits and it might never be “finished”.

    Sometimes I just use the module as a quick way to add a SQL query to something I’m working on: need to query a Lync/Skype backend database ? There’s Get-SQL. Line-of-business database ? There’s Get-SQL. Need to poke round in Adobe Lightroom’s SQL-Lite database? I do that with with Get-SQL -  though in both cases its simple query building is left behind, it just goes back to its roots of running a lump of SQL which was created in some other tool.  The easiest way to get data into Excel is usually the EXPORT part of Doug Finke’s excellent import-excel and sometimes it is easier to do it with insert or update queries using Get-SQL. The list goes on … when your hammer seems like a good one, you find an awful lot of nails.

    December 6, 2016

    Do the job 100 times faster with Parallel Processing in PowerShell

    Filed under: Powershell — jamesone111 @ 11:12 pm
    Tags: ,

    It’s a slightly click-baity title, but I explain below where the 100 times number comes from below. The module is on the PowerShell gallery and you can install it with  Install-Module -Name Start-parallel

    Some of the tasks we need to do in PowerShell involve firing off many similar requests and waiting for their answers – for example getting status from lots computers on a network. It might take several seconds to do each one – maybe longer if the machines don’t respond. Doing them one after the other could take ages. If I want to ping all 255 addresses on on my home subnet, most machines will be missing and it will take 3 seconds to time out for each of the 200+ inactive addresses. Even if I try only one ping for each address it’s going to take 10-12 minutes, and for >99% of that time my computer will be waiting for a response. Running them in parallel could speed things up massively.
    Incidentally the data which comes back from ping isn’t ideal, and I prefer this to the Test-Connection cmdlet.
    Function QuickPing {
        param ($LastByte)
        $P = New-Object -TypeName "System.Net.NetworkInformation.Ping"
        $P.Send("192.168.0.$LastByte") | where status -eq success | select address, roundTripTime
    }

    PowerShell allows you to start multiple processes using Jobs and there are places where Jobs work well. But it only takes a moment to see the flaw in jobs: if you run
    Get-Process *powershell*
    Start-Job -ScriptBlock {1..5 | foreach {start-sleep -Seconds 1 ; $_ } }
    Get-Process *powershell*

    You see that the job creates a new instance of PowerShell … doing that for a single ping is horribly inefficient – jobs are better suited to tasks where run time is much longer than the set up time AND where we don’t want run lots concurrently. In fact I’ve found creating large numbers of jobs tends to crash the PowerShell ISE; so my first attempts at parallelism involved tracking the number of jobs running and keeping to a maximum – starting new jobs only as others finished. It worked but in the process I read this by Boe Prox and this by Ryan Witschger which led me to a better way: RunSpaces and the RunSpace factory.
    MSDN defines a RunSpace as “the operating environment where the command pipeline of the PowerShell object is invoked”; and says that the PowerShell object allows applications that programmatically use Windows PowerShell to create pipelines of commands, invoke them and access the results. The factory can create single RunSpaces, or a pool of RunSpaces. So a program (or script) can get a PowerShell object which says “Run this, with these named parameters and these unnamed arguments. Run it asynchronously (i.e. start it and don’t wait for it complete, give me some signal when it is done), and in an a space from this pool.” If there are more things wanting to run than there are RunSpaces, the pool handles queuing for us. 

    Thus the idea for Start-Parallel was born.  I wanted to be able to do this
    Get-ListOfComputers | Start-Parallel Get-ComputerSettings.ps1
    or this  
    1..255 | Start-Parallel -Command QuickPing -MaxThreads 500
    or even pipe PS objects or hash tables in to provide multiple parameters a same command

    -MaxThreads in the second example says create a pool where 500 pings can be in progress, so every QuickPing can be running at the same time (performance monitor shows a spike of threads). So how long does it take to do 255 pings now? 240 inactive addresses taking 3 seconds each gave me ~720 seconds and the version above runs in a little under 7, so a that’s 100 fold speed increase!  This is pretty consistent with what I’ve found with polling servers over the couple of years I’ve been playing with Start-Parallel – things that would take a morning or an afternoon run in a couple of minutes. 

    You can install it from the PowerShell Gallery. Some tips

    • Get-ListOfComputers | Start-Parallel Get-ComputerSettings.ps1 
      works better than
      $x = Get-ListOfComputers ; Start-Parallel -InputObject $x -Command Get-ComputerSettings.ps1
      if Get-ListOfComputers is slow, we will probably have the results for the first computer(s) before we have been told the last one on the list to check.    
    • Don’t hit the same same service with many requests in parallel – unless you want to mount a denial of service attack.   
    • Remember that RunSpaces don’t share anything – the parallel RunSpaces won’t load your profile, or inherit anything from the session which launches them. And there is no guarantee that every module out there always behaves as expected if run in multiple RunSpaces simultaneously. In particular if, “QuickPing” is defined in a the same PS1 file which runs Start-Parallel, then Start-Parallel is defined in the global scope and can’t see QuickPing in the script scope. The work round for this is to use  
      Start-Parallel –scriptblock ${Function:\QuickPing}
    • Some commands by their nature specify a computer. For others it is easier to define a script block inside another script block (or a function) which takes a computer name as a parameter and runs
      Invoke-Command –ComputerName $computer –scriptblock $InnerScriptBlock
    • I don’t recommend running Start-Parallel inside itself, but based on very limited testing it does appear to work.

    You can install it by running Install-Module -Name Start-parallel

     

    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 { 
    [CmdletBinding(DefaultParameterSetName="PS1")]
    param (  [parameter(Position=0, ValueFromPipeLine=$true)]
             $A
             [parameter(ParameterSetName="PS2")]
             [parameter(ParameterSetName="PS3")]
             [parameter(ParameterSetName="PS4")]
             $B,
             [parameter(ParameterSetName="PS3", Mandatory)]
             $C,
             [parameter(ParameterSetName="PS4", Mandatory)]
             $D
    )
    $PSCmdlet.ParameterSetName
    }

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

    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
    PS2
    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 
    PS3

    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 
    PS1
    > 1 | test  -b 2 -c 3
    PS3

    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)]
      $C,
      [parameter(ParameterSetName="PS4", Mandatory,ValueFromPipelineByPropertyName=$true)]
      $D

    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

    November 19, 2016

    Format-XML on the PowerShell Gallery

    Filed under: Powershell — jamesone111 @ 8:08 pm
    Tags: , ,

    In the last post, I spoke about those bits of PowerShell we carry around and never think to share. Ages ago I wrote a function named “Format-XML” which “pretty prints” XML with nice indents. I’ve passed it on to a few people over the years -  it’s been included as a “helper” in various modules – but I hadn’t published it on its own.

    I’ve got that nagging feeling  I should be crediting someone for providing the original bits but I’ve long since lost track of who. In Britain people sometimes talk about “Trigger’s broom” which classicists tend to call the Ship of Theseus – if you change a part it’s still the same thing, right? But after every part has been changed? That’s even more true of the “SU” script which will be the subject of a future post but in that case I’ve kept track of its origins.

    Whatever… Format-XML is on the PowerShell gallery – you can click Show under FileList on that page to look at the code, or use PowerShell Get (see the Gallery homepage for details) to install it, using Install-Script -Name format-xml the licence is chosen to all you to incorporate it into anything you want with no strings attached.

    Then you can run to load it with .  format-xml.ps1 – that leading “.” matters … and run it with
    Format-XML $MyXML
    or $MyXML | Format-XML
    Where $MyXML is either any of

    • An XML object
    • Some text in XML format
    • The name of a file which contains XML, or
    • A file object where the file contains XML

    Incidentally, if you have stuff to share on the PowerShell gallery the sign-up process is quick, and the PowerShell Get module has a Update-ScriptFileInfo command to set the necessary metadata and then Publish-Script puts the script into the gallery – it couldn’t be easier.  

    November 13, 2016

    One of those “everyday” patterns in PowerShell –splitting a list

    Filed under: Powershell — jamesone111 @ 9:16 pm

    For a PowerShell enthusiast, the gig I’ve been on for the last few weeks is one of those “Glass Half Full/Glass Half Empty” situations: the people I’m working with could do a lot more with PowerShell than they are (half empty) but that’s an opportunity to make things better (half full). A pattern which I take for granted took on practically life-changing powers for a couple of my team this week…. 

    We had to move some  … lets just say “things”, my teammates know they run Move-Thing Name  Destination but they had been mailed several lists with maybe 100 things to move in each one. Running 100 command lines is going to be a chore.  So I gave them this
    @"
    PASTE
    YOUR
    LIST
    HERE
    "@
       -Split  "\s*[\r\n]+\s*"  | ForEach-Object { Move-thing $_ "Destination"}

    Text which is wrapped in @"<newline> and <newline> "@ is technically called a "here string" but to most people it is just a way to have a multiline string.  So pasting a list of items between the quotes is trivial, but the next bit looks like some magic spell …
    PowerShell’s –split operator takes regular expressions and splits the text where it finds them (and throws matching bit away). in Regex \r is carriage Return, and \n is New line and [\r\n] is “either return or newline”, so [\r\n]+ means "at least one of the line break characters, but any number in any order." And I usually use –split this way, but here we found the lists often included spaces and tabs – adding \s* at the beginning and end adds “preceded by / followed by  any number of white space characters – even zero”

    So the multiline string is now a bunch of discrete items. The command we want to run doesn’t always need to be in a foreach {} – text piped in to many commands becomes the right parameter like this 
    @"
    List
    "@
       -Split  "\s*[\r\n]+\s*"  | Get-Thing | Format-Table
    But for  a foreach {} will always work even if it is cumbersome.

    I think lots of us have these ready made patterns – as much as anything this post is a call to think about ones you might share. it was nice to pass this one on and hear the boss’s surprise when one of junior guys told him everything was done.

    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 THE COMMANDS WE NEED
    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'  } 

        $ExecutionContext.SessionState.Applications.Clear()
        $ExecutionContext.SessionState.Scripts.Clear()

        $RemoteServer =  [System.Management.Automation.Runspaces.InitialSessionState]::CreateRestricted(
                                         
    [System.Management.Automation.SessionCapabilities]::RemoteServer)
        $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 {
    <#
    .Synopsis
        Restarts the Print Spooler service on the current Computer
    .Example
        Restart-Spooler
        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"
    }
    #endregion
    #Set the language mode
    if (-not $psise) {$ExecutionContext.SessionState.LanguageMode = [System.Management.Automation.PSLanguageMode]::NoLanguage}

    Logging
    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

    $ExecutionContext.SessionState.Applications.Clear()
    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:

    Clear-Host   
    Exit-PSSession
    Get-Command  
    Get-FormatData
    Get-Help     
    Measure-Object
    Out-Default  
    Select-Object

    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 `
                                    -ShowSecurityDescriptorUI
    -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.

    Next Page »

    Create a free website or blog at WordPress.com.