When last we met, dear Reader, I made an offhand remark about spreadsheets and pivot tables, a throwaway joke about something a lot of people don’t understand. But it got me thinking. You’ve probably never worked much with spreadsheets, and why would you? You work in IT, not accounting.
So today, we’ll take a slight diversion into using PowerShell to replicate some advanced spreadsheets functions. It might come in handy down the road, and besides, I couldn’t resist beating an already dead joke.
So, to begin with, whiskey tango foxtrot is a pivot table, anyway?
I was told there would be no math
Bear with me for just a moment, and all will be made clear.
Think back to high school math. Look past the bad hair and shortsighted fashion choices, and see if you can remember a day where you were introduced to the terms “integral” and “derivative”. For me, that was one of the last days that math made any sense, and not much that I learned after has stuck. I certainly haven’t used any advanced calculus in everyday life. I fix computers and write silly articles about them. I’m not an engineer.
But I still remember that acceleration is the derivative of velocity, and velocity is the derivative of distance. On a graph with Time along the X axis and Distance along the Y, the velocity of a coconut-laden swallow can be determined by finding the slope of a line drawn between any two points. That is to say, starting with information in one dimension (distance), you can transform it and derive additional dimensions of information from it (velocity, acceleration).
Pivot tables do that. Kind of. In a way. It will make more sense later, I promise.
At no time will my hands leave my body.
For our exercise today, instead of distance and time data, we’ll be working with an example report of the version of Chrome installed on the Macs in a fictional client network. The questions we want answered are:
* Which versions of Chrome are installed on the network?
* How many different versions of Chrome are installed on the network?
* How many Macs are running old versions that need to be patched?
* Who needs to be harassed to get their Mac patched?
We’re going to do all of this without opening a spreadsheet.
PowerShell’s power comes in part from add-ons, installable modules that extend its core functionality. Which is possibly why, I just realized, the open source PowerShell platform is called PowerShell Core. Modules, along with additional functions, sample scripts, and both user-created and first-party content, can be found in a number of repositories online. The largest official repo, and the one used by PowerShell by default when looking for new modules, is PowerShell Gallery (https://www.powershellgallery.com).
Depending on how you acquired and installed PowerShell in the first place, PowerShell Gallery may not already be configured as a module source. To check that, open “pwsh” and run “Get-PSRepository”.
PS > Get-PSRepository
Name InstallationPolicy SourceLocation
---- ------------------ --------------
PSGallery Untrusted https://www.powershellgallery.com/api/v2That looks promising.
Or, you might see a message that looks like this, instead of the one above:
WARNING: Unable to find module repositories.Which means “PSGallery” needs to be properly registered. Since PSGallery is the default repository, it should be as simple as confirming that you want that to be registered as a module source when searching for new or updated content.
PS > Register-PSRepository -Default Now we can find the module we’re after for today’s exercise:“ConsoleGuiTools”.
The Grid. A digital frontier.
On Windows, there is a delightful function called “Out-GridView” that opens a new window to display data, allows for a user to highlight and select objects, and generally interact with a custom GUI. With the move to a common, cross-platform codebase, some of that functionality was ported to PowerShell Core, but not all of it. Then, in 2019 a cross-platform module called “GraphicalTools” was briefly available that brought the grid view functionality to Mac and Linux. I say “briefly”, because about a year later development stopped on that module while the PowerShell UI elements were all rewritten or ported to a new framework called “MAUI” (Multi-platform Application User Interface).
It’s been a crazy few years since then, as you might have heard. Suffice it to say, there’s still no “Out-GridView” on macOS. Well, there is, but it’s the broken version still knocking around from 2020, deprecated and unmaintained. Instead of a graphical UI, we will have to get by with a text-based UI. Hence “ConsoleGuiTools”.
With the PSGallery repository enabled, it should be possible to find and install the “ConsoleGuiTools” module.
PS > Find-Module Microsoft.PowerShell.ConsoleGuiTools
Version Name Repository Description
------- ---- ---------- -----------
0.7.2 Microsoft.PowerShell.ConsoleGuiToo… PSGallery Cross-platform Console Gui Tools for PowerShellOnce we know the module is available, it’s just one more step to install it.
PS > Install-Module Microsoft.PowerShell.ConsoleGuiTools
Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its InstallationPolicy value by running the
Set-PSRepository cmdlet. Are you sure you want to install the modules from 'PSGallery'?
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is "N"): You may have noticed above that “PSGallery” was listed above as “untrusted”. It’s probably fine to fix this as the message suggests, by running “set-PSRepository -Name ‘PSGallery’ -InstallationPolicy Trusted”. I actually prefer having an additional confirmation step before I add something to my environment. On the other hand, you may deploy a script somewhere to run non-interactively that requires modules that are not already installed. In that case, you’ll want to set the source for those modules to be trusted, or distribute them alongside your script. That, and building your own modules, are something I’ll cover in a future article.
For the purposes of today’s exercise, go ahead and select “Y” to install “Microsoft.PowerShell.ConsoleGuiTools”. You trust me, right? Once you approve the install, you’ll see a progress indicator appear as the pieces are downloaded. Once it’s done, give this command a try:
PS > ps ax | Out-ConsoleGridViewYou should see your Terminal taken over by a text interface displaying a scrollable listing of running processes. Something like this:
Before you press ESC to close that and return to your “pwsh” session, notice that the bottom row of the window has keyboard shortcuts for selecting items in the list. I imagine that may come in handy later.
Through the magic of television, I’ve already prepared a sample report with some random data to dissect. You can download it from here: https://github.com/Mac-Nerd/PowerShell/tree/main/Part-5
If you’ll recall from last time, the command to read the contents of a text file into PowerShell is “Get-Content”. Once it’s loaded, we can pipe that out to the console grid view.
PS > Get-Content './Chrome Version Sample - Before.csv' | Out-ConsoleGridViewBut that’s just going to show the file as it is, without any additional formatting or interpretation of the CSV data. You should also remember from last time that we were able to easily translate JSON into PowerShell objects. The same goes for CSV formatted text. “ConvertFrom-Csv” should do the trick.
PS > Get-Content './Chrome Version Sample - Before.csv' | ConvertFrom-Csv | Out-ConsoleGridViewUsing the arrow keys, you should find that you’re able to scroll the list, and when you hit tab, the cursor should show up next to “Filter” in the upper left corner. If you start typing, the displayed rows will change to only include the ones that include the text you’ve typed. There’s a lot that can be done with this grid view, and we’ll just scratch the surface today. I encourage you to play around with it the next time you have a task that requires user input.
And now, as promised, we return to pivot tables, and to summarizing our data. Or as in my tortured math metaphor, to derive trends from the individual data points.
This is all a moo point.
The field we’re interested in summarizing contains the Chrome version numbers, as labeled in the CSV’s header row “Version”. To pivot the data on that column means finding all the unique values of the Version field, and to group the rows containing each one together. If you were to do it by hand, you might first sort the data on the Version column, then select each grouping of rows and collapse them somehow. The result is a new table, a “pivot table” with one row each for version 101, 102, 103, and so on. The data in each row then needs to reflect the answer to our third question: how many individual machines have that version installed?
If this were financial data, we might be looking to add up profits in each grouping of stores, based on their region. All the stores in the Southwest region are collected together, and all their profits and losses summed together to get a result for the whole region. And again for the Southeast region stores, and so on. Pivot the data on the Region column, and sum up the Profits columns.
Since all we’re after is a count, there’s no need to do even simple addition. But we will need to fire up Visual Studio Code.
As usual, create a new script and throw the shebang in the first line. Then grab the CSV data like we did in the Terminal.
#!/usr/local/bin/pwsh
# Read CSV data
$CSVData = Get-Content -raw './Chrome Version Sample - Before.csv' | ConvertFrom-CsvSince the data is already in a known format, and all we’re doing is importing a file’s contents into PowerShell objects, it turns out we can skip the two steps for reading then converting. There’s an “Import-CSV” command that will do it in one step, and it has the same optional parameters as “ConvertFrom-CSV”. “Import-CSV” is essentially the convert command, but takes a file as its input instead of a string. Or multiple files, if you specify a wildcard path like “*.csv”.
Since our sample data already has a header row, we don’t need to set “-Header”, and the delimiter is a comma, so “-Delimiter” is also unnecessary, but it’s good to know they are there, when you get a report from your MDM platform and each field is separated by semicolons or something else.
$CSVData = Import-Csv "Chrome Version Sample - Before.csv"“Import-CSV” will create an object for each row, and populate it with properties with names based on the header row. So we’ll end up with 900 or so objects with the following properties:
* Application
* Policy
* Hostname
* UserEmail
* Result
* ExitCode
* LastResult
* Version
* LastSeen
* UUID
Once the data is there, we need to group all the rows and their contents based on the unique values in the “Version” column, or pivot on “Version”. To do that, we’ll start with the “Group-Object” command. This will group objects, based on a common value in the specified object property. In this case, all our objects have a “Version” property, so to create groups of each Version value, we ‘ll do this.
$GroupedRows = $CSVData | Group-Object -Property "Version"The next step to pivoting the data is, um. There’s really no next step. If you output the grouped objects, you’ll see what I mean.
PS > $PivotData
Count Name Group
----- ---- -----
2 100.0.4896 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Ste…
1 101.0.4951 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Oli…
3 102.0.5005 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Zio…
18 103.0.5060 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Mck…
19 104.0.5112 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Mad…
76 105.0.5195 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Rem…
44 106.0.5249 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Abi…
49 107.0.5304 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Seb…
1 108.0.5359 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Dan…
111 109.0.5414 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Bri…
27 110.0.5481 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Ayl…
42 111.0.5563 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Muh…
273 112.0.5615 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Kob…
240 113.0.5672 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Jos…That looks suspiciously like a pivot table. Not only that, but the exact pivot table we were looking for: each version of Chrome has its own row, and the “Count” column provides the number of Macs with that version installed. All done. See you next time! Our finished script:
#!/usr/local/bin/pwsh
# Read CSV data
$CSVData = Import-Csv "Chrome Version Sample - Before.csv"
# Create a pivot table from the "Version" field
$PivotData = $CSVData | Group-Object -Property "Version"But wait. There’s more!
Since I promised you a nicely formatted report, and because I can’t leave well enough alone, let’s keep going.
All that malarkey about “Out-ConsoleGridView” must have been leading up to something, so let’s see what this does for us:
$PivotData | Out-ConsoleGridViewA slight improvement, partly because of the nicer formatting within the Visual Studio Code terminal window (color!). The console grid view is more than merely decorative. It’s interactive, so give it a try and select some rows with the arrow keys and Space. Wouldn’t it be great if that selection could make its way into the script, and you could somehow get a list of the Macs that needed patching?
We’ve covered the concept of a “pipeline variable” in previous posts, and that’s going to come into play here. To get the selected row out of the grid view, I’ll create a variable “$SelectedRow” and output that to the console. And to restrict the output from “ConsoleGridView” to a single object, instead of the default of allowing multiple items, set the “-OutputMode” parameter to “single”.
$SelectedRow = $PivotData | Out-ConsoleGridView -OutputMode Single
$SelectedRowI’ll select the third row, the one with three items. The output will show the result of my selection contains a group of objects, presumably three of them, in a container called “Group”.
Count Name Group
----- ---- -----
3 102.0.5005 {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Zi…But we already knew that, right? To dig into the result, we need to expand that “Group” column. Being within the “$SelectedRow” object, we can obtain that like this:
$SelectedRow.Group
Application : Google Chrome
Policy : Chrome Patching
Hostname : Zions-Mac.local
UserEmail : Zion@example.com
Result : Success
ExitCode : 0
LastResult : Success
Version : 102.0.5005
LastSeen : 04/05/2023
UUID : 3C766FFA
Application : Google Chrome
Policy : Chrome Patching
Hostname : Alaynas-Mac.local
UserEmail : Alayna@example.com
Result : Success
ExitCode : 0
LastResult : Success
Version : 102.0.5005
LastSeen : 04/05/2023
UUID : 788476F6
Application : Google Chrome
Policy : Chrome Patching
Hostname : Devins-Mac.local
UserEmail : Devin@example.com
Result : Success
ExitCode : 0
LastResult : Success
Version : 102.0.5005
LastSeen : 04/05/2023
UUID : 6B68973DBut if all we need are the hostnames to push updates to, or better yet the email addresses of the users we need to nag about running those updates, we can use “ForEach-Object” and a script block to output the “UserEmail” property of each. But it’s easier to just do this:
$SelectedRow.Group.UserEmail
Zion@example.com
Alayna@example.com
Devin@example.comMaybe you want something more practical that you can hand off to another script, or maybe to your client to show which of their employees aren’t following instructions on staying current. Then “ForEach-Object” and script blocks come into play. What do you get if you return multiple values, separated by commas?
$SelectedRow.Group | ForEach-Object { $_.Hostname + "," + $_.UserEmail }
Zions-Mac.local,Zion@example.com
Alaynas-Mac.local,Alayna@example.com
Devins-Mac.local,Devin@example.comWhy, it looks like comma-separated-values, a CSV. How convenient. And you could write those strings of text out to a file with “Out-File” and it would be a valid CSV file. But if we’re going to the trouble of a CSV round-trip, why not do it the PowerShell way? We imported the data with “Import-CSV”. It only stands to reason we can export the “$SelectedRow.Group” values with “Export-Csv”.
$SelectedRow.Group | Export-Csv -Path "./export.csv""export.csv" will look like this:
"Application","Policy","Hostname","UserEmail","Result","ExitCode","LastResult","Version","LastSeen","UUID"
"Google Chrome","Chrome Patching","Zions-Mac.local","Zion@example.com","Success","0","Success","102.0.5005","04/05/2023","3C766FFA"
"Google Chrome","Chrome Patching","Alaynas-Mac.local","Alayna@example.com","Success","0","Success","102.0.5005","04/05/2023","788476F6"
"Google Chrome","Chrome Patching","Devins-Mac.local","Devin@example.com","Success","0","Success","102.0.5005","04/05/2023","6B68973D"You probably don’t need all of those columns, just to be able to name and shame some noncompliant users. We can limit the fields to just the essentials by injecting “Select-Object” before exporting the CSV. This will select only those object properties we specify, and simplify matters somewhat.
$SelectedRow.Group | Select-Object -Property Hostname,UserEmail | Export-Csv -Path "./export.csv"And the resulting “export.csv”
"Hostname","UserEmail"
"Zions-Mac.local","Zion@example.com"
"Alaynas-Mac.local","Alayna@example.com"
"Devins-Mac.local","Devin@example.com"You’re a paleontologist. Dig a little deeper.
Looking at the results of the initial pivot table, there are a lot of stragglers still hanging on to old versions of Chrome. Not only that, but there are more than a dozen different versions represented. This is pretty typical from what I’ve seen in real-world situations. Chrome’s automatic update runs normally, but the update doesn’t actually install unless the browser is restarted. Most people don’t reboot their Macs but once every couple of weeks, if that. And they almost never restart their web browser unless it’s crashed.
Instead of doing a report and export for each individual version of Chrome – 14 in my pseudorandomly generated example data – you’re more likely to want a list of all the Macs running a Chrome version that’s more than two or three versions out of date. You can do that a couple of ways. Instead of limiting selections in the console grid view to a single row, we can change “-OutputMode Single” to “-OutputMode Multiple”. This way you can use the space bar to select as many rows as you like. The rest of the code will work just the same as if the grid view returned one row.
The other option would be to group the rows in the pivot table differently. With this line, we selected unique “Version” fields.
$PivotData = $CSVData | Group-Object -Property "Version"But the “-Property” option will take more than just a single value. For example, adding a second field name will give us a table with two rows for each unique version, one where the “ExitCode” (presumably of the reporting script that was run) was zero, and another where it was one.
$PivotData = $CSVData | Group-Object -Property "Version","ExitCode"Handy, but I think we’re looking for fewer categories, not more. The “-Property” parameter accepts more than just the names of properties, too. We can apply some logic to the pivot groups with a script block. The “Version” property of each object can be checked with “$_.Version” – there’s that pipeline variable again. If we wanted two groups, one where the version is more than three major releases out of date, and one with more recent versions, a simple boolean check of “is the version less than than 110?” would look like this:
$PivotData = $CSVData | Group-Object -Property {$_.Version -lt 110}The resulting "$PivotData" would looks like this:
Count Name Group
----- ---- -----
582 False {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Muhammads-M…
324 True {@{Application=Google Chrome; Policy=Chrome Patching; Hostname=Madeleines-…Two rows, with the result of the code block, either “True” or “False” as the row name. Select the “True” group, and continue on the rest of the script to get your list of 324 misbehaving users. Next week, you’ll need to change “110” to be “111” since Chrome version 114 will have been released by then.
Note: the next section will involve math. It’s very simple math, so there’s no need to be anxious. I didn’t want to simply spring it on you without warning.
The One With The Subtraction
We need to find the highest version number represented in the original data. In the pivot results, the data is nicely sorted, but the CSV is not. To sort a collection of objects, by the values of a property of each object, “Sort-Object -Property”. That will put items with the highest value at the end, so we can get that highest value by picking the last one, and selecting that property with “Select-Object -Last 1 -Property”. Putting all of that together will return an object that contains only the “Version” property.
$MaxVersion = $CSVData | Sort-Object -Property "Version" | Select-Object -Last 1 -Property "Version"
$MaxVersion.Version
113.0.5672But, that’s not an actual number. You can’t do math with that. It has too many dots. You can try it, but you’ll get an error like “Error: "The input string '113.0.5672' was not in a correct format.” Strangely, the “-lt” comparison operator knows that “109.0.5414” is less than 110. Probably because it’s alphabetically before 110, but that doesn’t help much. “113.0.5672” is not a number; it’s a string. We can fix that, though. We know how to manipulate strings.
I can count at least five different ways to get what we’re after, and only because I stopped counting. In this case, we only want the major release number “113”, so we need to split the string at the first dot and discard everything after. The “split()” function will take care of the first part. It will create an array from a string, breaking the string on a delimiting character. In this case “.” Since the result is an array, the first item is designated with “[0]”.
$LastMajorVersion = $MaxVersion.Version.split(".")[0]
113We can do math with that. PowerShell is even smart enough to realize that a string that contains only digits can be treated as a number without having to explicitly set its type to “Int32”. We can swap “($LastMajorVersion - 3)” in for “110” and we’re done with the maths portion of the exam.
The complete script:
#!/usr/local/bin/pwsh
# Read CSV data
$CSVData = Import-Csv "Chrome Version Sample - Before.csv"
# find the highest Chrome version represented in the data
$MaxVersion = $CSVData | Sort-Object -Property "Version" | Select-Object -Last 1
# Get a number from the string in the object's Version property.
$LastMajorVersion = $MaxVersion.Version.split(".")[0]
# Create a pivot table from the "Version" field, only splitting at "current - 3"
$PivotData = $CSVData | Group-Object -Property {$_.Version -lt ($LastMajorVersion - 3)}
# Display the pivot table, select a row to see details
$SelectedRow = $PivotData | Out-ConsoleGridView -OutputMode Multiple -Title "Chrome version older than $($LastMajorVersion - 3)"
# Export the hostname and email addresses
$SelectedRow.Group | Select-Object -Property Hostname,UserEmail | Export-Csv -Path "./export.csv"How you doin’?
Hopefully, today you have picked up a few new terms and concepts, as well as some tricks to use in your PowerShell scripts. The next time you’re chatting with someone about data analysis and spreadsheets (as one does) you should be able to drop the phrase “just pivot the data” and know what you’re talking about. And when they ask, you can just tell them it’s not like it’s advanced calculus or something.
4 Comments
