Before I get to today’s topic, a bit of follow-up from the last installment:
In part 3, in order to determine the latest release of the target installer package from Github’s API, I used this line of code:
$AvailableVersion = ((Invoke-WebRequest "https://api.github.com/repos/PowerShell/PowerShell/releases/latest").Content | ConvertFrom-Json).tag_name
Reader @johncwelch commented that it would be easier (and I’ll also admit, easier to read) if I had abstracted the API results steps in the final script, as I had outlined over the course of building it. Something like this:
$APIResult = Invoke-WebRequest "https://api.github.com/repos/PowerShell/PowerShell/releases/latest"
$theJSON = ConvertFrom-Json -InputObject $APIResult
$AvailableVersion = $theJSON.tag_name
This also has the advantage of each variable-setting operation being something that can be tested in turn. This allows for error checks or breakpoints in the script, so that execution can be stopped more elegantly in case the data isn’t appearing in the format you expected, if at all. In an alternate universe, the Zach Snyder extended cut of part 3 did break that piece into multiple operations, but it was edited by the studio for pacing issues.
The only advantage of my approach is that it’s all on one line. As a bonus, I was able to use pipes and parentheses to make it look more impressively complicated. To paraphrase a certain Doctor, on bow ties: I like one-liners. One-liners are cool.
Also, like a certain Doctor’s time and space conveyance, a good one-liner is bigger on the inside. I struggle with peripheral neuropathy in my hands brought on by years spent ignoring the symptoms of carpal tunnel. Which is, ironically, a long-winded way to say I prefer anything that saves me time and keystrokes. Less typing = good.
And now, part 4,
In Which PowerShell Is Sent To Work In The Mines
Something most admins spend a considerable portion of their time on, regardless of the platforms they manage, is reporting. To questions like, “Which machines have version X of Application Y installed?” the answer is often “I’ll run a report.”
For some queries, your management platform or MDM will have ready-made reports, or scripts to run that will gather the information for you. Those reports may even be in a “human readable” format. Unfortunately, that usually translates into “admin readable”, and doesn’t always mean “customer friendly”.
In my experience, customers want spreadsheets. They prefer easily digestible charts and graphs, usually with lines that go up and to the right. And as far as career-advancing skills go, the ability to synthesize and present complex information in a way that is easily understood by laypeople will take you a long way.
This column, however, isn’t intended to help with your communications skills – there are other articles I can point you to, though. Instead, I can show you how to leverage PowerShell to mine those long, complex reports for data and convert them to a digestible spreadsheet format. With some practice and a few additional lessons in Excel or Google Sheets, you should be able to improve the quality of the reports you send to customers. Even if the data won’t always make them smile, at least they won’t be frowning because they can’t make sense of a wall of indecipherable text.
Mother Did It Have To Be So High?
Take, for example, the above request about installed software. While that can be obtained in a number of ways, let’s assume the results are insufficient or the format is overly opaque for our purposes. The output from the command “system_profiler” is nicely formatted for skimming to a relevant data point on a single Mac. As is, it doesn’t scale well for seeing trends among even a dozen machines, let alone a fleet of hundreds or more.
But it’s a start.
% system_profiler SPApplicationsDataType
Applications:
App Store:
Version: 3.0
Obtained from: Apple
Last Modified: 3/18/23, 12:34 AM
Kind: Universal
Signed by: Software Signing, Apple Code Signing Certification Authority, Apple Root CA
Location: /System/Applications/App Store.app
[...]
The “SPApplicationsDataType” report spits out all the relevant information you would want to track down outdated versions of software, rogue installs of unapproved apps, or just to keep an inventory of what’s installed where. “system_profiler” can also provide that data in XML or JSON formats.
% system_profiler SPApplicationsDataType -json
{
"SPApplicationsDataType" : [
{
"_name" : "App Store",
"arch_kind" : "arch_arm_i64",
"lastModified" : "2023-03-18T04:34:36Z",
"obtained_from" : "apple",
"path" : "/System/Applications/App Store.app",
"signed_by" : [
"Software Signing",
"Apple Code Signing Certification Authority",
"Apple Root CA"
],
"version" : "3.0"
},
[…]
As we saw in Part 3, PowerShell is fairly adept at ingesting and parsing JSON data, something that is increasingly important since JSON has rapidly become the format of choice for exchanging data between apps and APIs. It’s also something that’s fairly difficult to do in bash without installing and employing additional tools. Tools like “jq” have their own syntax that you need to learn (https://stedolan.github.io/jq/manual/v1.6/). And if you’re going to install and learn a new utility just to work with JSON data, why not install and learn PowerShell?
Firing up “pwsh”, we can explore the “system_profiler” output:
PS > $InstalledAppsJSON = system_profiler SPApplicationsDataType -json
PS > ConvertFrom-Json -InputObject "$InstalledAppsJSON"
SPApplicationsDataType
----------------------
{@{_name=App Store; arch_kind=arch_arm_i64; lastModified=3/18/2023 4:34:36 AM; obtained_from=apple; path=/System…
I’ve set the variable “$InstalledAppsJSON” to the complete output of the “system_profiler” command. Until it’s converted to a PowerShell object with “ConvertFrom-Json” that data exists as just a long text string. To facilitate a deeper dive, we can set a new variable for that converted object data in one of two ways:
PS > $InstalledApps = ConvertFrom-Json "$InstalledAppsJSON"
or
PS > ConvertFrom-Json "$InstalledAppsJSON" -OutVariable "$InstalledApps"
One may be more or less “correct” depending on who you ask, but both generate the same result. You may also notice that “-InputObject” is left off in both commands. Most commands assume that the first argument you provide that isn’t a parameter (such as “-OutVariable”) is the intended input. One of the complaints I’ve seen about PowerShell from those coming to it from bash is PowerShell’s verbosity. You can infer my feelings on that subject from the introduction to today’s article, vis a vis one-liners.
Because of the way “system_profiler” formats its output, each of the applications exist within the root “SPApplicationsDataType”. Now that our data has been objectified, we can see the full details by expanding that root.
PS>$InstalledApps.SPApplicationsDataType
_name : App Store
arch_kind : arch_arm_i64
lastModified : 3/18/2023 4:34:36 AM
obtained_from : apple
path : /System/Applications/App Store.app
signed_by : {Software Signing, Apple Code Signing Certification Authority, Apple Root CA}
version : 3.0
[…]
As you can see, it’s a… list. Of all the same stuff we listed before. In a slightly different format. What was the point of all the JSON conversion steps, again?
We’ll get there. First, we should shove that collection of objects into another variable, so we can make the rest of our code a little less verbose. Less typing = good.
PS > $AppsList = $InstalledApps.SPApplicationsDataType
Do As I Say, Not As I Do
I think my favorite operator in PowerShell is “ForEach-Object”. It does what it says on the tin, “for each object in a collection, do this thing.” As with most operators, you can either pipe the results of a command that generates a list of objects to it, or declare a collection as the “-InputObject” like so:
PS > $AppsList | ForEach-Object {$_}
is the same as
PS > ForEach-Object -InputObject $AppsList {$_}
The “thing to do” is contained inside braces to become what is referred to as a “script block”. Script blocks live somewhere between the bash concepts of subshells and functions. We’ll be exploring them in more detail in future posts. For now, just be aware that multiple statements can be stacked inside the braces, and that the special “pipeline” variable “$_” refers to the individual object being operated on by the script block. In other languages, you might use an alias like “this” or a reference like “$1”.
In the above example, each item in “$AppsList” is passed to the script block, which is simply returning the entire object. This exercise is somewhat pointless, except to illustrate how the pipeline variable works. If we wanted to return just the path to each installed application, that’s in the “path” property of each object. Treat the pipeline variable as an alias to the object, and tack the property on the end, thus
PS > ForEach-Object -InputObject $AppsList {$_.path}
/System/Applications/App Store.app
/System/Applications/Automator.app
/System/Applications/Books.app
/System/Applications/Calculator.app
/System/Applications/Mail.app
[...]
This might be handy for finding applications installed in nonstandard locations. Every now and then, for instance, I find a client machine has “Google Chrome.app” sitting on a user’s desktop. The path to that app, or others that are outside of “/System/Applications” would stand out to someone skimming the full list. The full list, though, is several pages long, and not sorted by anything intelligible. Actions inside “ForEach-Object” apply to each object, so it won’t help you narrow down your list, at least not without some additional steps.
There are perfectly reasonable ways to sort and filter the output by adding conditional logic to the script block, but I’ll save those for another time. Instead, allow me to introduce you to another of my favorite PowerShell operators, “Where-Object”. By the light of the full moon, the were-object transforms… ahem.
Where am I going? Have I gone too far?
If you’re familiar with SQL databases, this step will be much like querying a database table. We can select specific objects from a collection by filtering the results on various criteria. It’s a simple matter to output the array of items and pipe it into the “select…where” equivalent “Where-Object”. The syntax for selecting items from an array even looks like the SQL clause “SELECT * FROM Table WHERE Property=Value;”.
PS > $AppsList | Where-Object -Property "obtained_from" -eq -Value "Apple"
This will produce a subset of the items from the installed apps list, where the “obtained_from” property is the string “apple”. The operator “-eq” is, as you might have guessed, the equivalent of “=“, and short for “is equal to”. Also note, the comparison is not case-sensitive. For that, you can use the “-ceq” operator: “Case-Sensitive Equal”. There are a slew of other operators for the purposes of comparison. “-gt” and “-lt” are “greater-than” and “less-than”, respectively, and so on.
The above can be shortened to
PS > $AppsList | Where "obtained_from" -eq "Apple"
and expressed in slightly different notation thus:
PS > $AppsList | ? {$_.obtained_from -eq "Apple"}
“?” and “Where” are both acceptable shorthand for “Where-Object”. Note the braces in the second version, indicating a script block.
The statement inside the script block translates to “this object’s property ‘obtained_from’ has a value equal to ‘apple’.” If that statement evaluates as true, then the object passes along to the output. As a result, you’ll see a filtered list containing just those Apple apps extracted from the complete list.
As for the previous example of any app located in a path that doesn’t start with “/System/Applications”, you may want to use the operand “-Like” which will match string values using the wildcard “*”. More complex comparisons can be performed with “-Match” which allows for regular expressions.
PS > $AppsList | Where-Object {$_.path -Like "/System/Applications/*"}
But it looks like that gives us the opposite of what we’re looking for – apps that are in the proper place. To invert the selection, use “-notlike” instead:
PS > $AppsList | Where-Object {$_.path -notlike "/System/Applications/*"}
And filters can stack inside the script block, glued together with boolean operators “-and”, “-or”, “-not”, and “-xor”:
PS > $AppsList | Where-Object {$_.path -notlike "/System/Applications/*" -and $_.path -notlike "/Applications/*"}
The option to combine comparators is only available in the script block variation of the “Where-Object” syntax, which makes it possible to build complicated filters like this example, surfacing any app not in the usual folder, as well as those that are from an unknown source.
PS > $AppsList | Where-Object {($_.path -notlike "/System/Applications/*" -and $_.path -notlike "/Applications/*") -or $_.obtained_from -eq "unknown"}
If you’re following closely, you’ll have noticed the output of our commands changed from only the specific property we were after, to getting entire objects, albeit only those that matched our criteria. Can we combine those concepts, and pull a list of just the path of each app that matches our filter? Of course. I wouldn’t have led you down this merry path, only to reveal it was a dead end. Or would I?
No. I wouldn’t.
What A Long, Strange Trip It’s Been
Let’s look now for any application the system knows about that exists inside a user’s home folder, (typically in “/Users/“) and put that result into a new variable, “$UserApps”.
PS > $UserApps = $AppsList | Where-Object {$_.path -like "/Users/*"}
With that filtered list, we can display just the paths to those problematic apps.
PS > $UserApps | ForEach-Object {$_.path}
And you know I had to try and fit it on one line:
PS > ($AppsList | Where-Object {$_.path -like "/Users/*"}) | ForEach-Object {$_.path}
But, you may be asking, how does all this get us any closer to the original goal of this article, namely, customer-friendly reports? We’re getting there, I promise. So far, I’ve shown you how to translate a JSON blob into an array of discrete objects, filter the array and return only the object we’re looking for, and even just return the specific property of those filtered objects. To build a report based on this application data, we’ll need more than just one property at a time.
If we needed to do some kind of operation or transformation on the objects in the list, we could expand on the script block in the “ForEach-Object” command. But, as we’re just looking for the values of certain properties, a different operator should simplify things. Replace the self-explanatory “ForEach-Object” command with the somewhat confusingly named “Select-Object”.
“Select-Object” may sound like it’s filtering your list, like “Where-Object” does, and it can do that, but not in the same way. It will select objects, plural, based on sorting rules (more on that in a moment) but not on any condition or property of the object. It will allow us to output specific properties of the objects it selects, though. It can also be used to add new properties to existing objects, and to create new objects. As verbose as PowerShell is, its designers probably stopped short of naming a command “Select-Objects-And-Do-A-Bunch-Of-Other-Things”. Calling it “Object-Multitool” probably would have caused some confusion as well.
To clear up some of the confusion I sense you are feeling, start with the previous command, and swap out “ForEach-Object” with “Select-Object” and direct it to display the “_name” and path of each app.
PS > $UserApps | Select-Object -Property "_name", "path"
Now we’re getting somewhere. By default, that output should appear in two columns, nicely formatted for readability. After the “-Property” parameter, we can specify as many properties as are in the object, and in whatever order we like. Are you starting to get an inkling of where this is headed?
PS > $UserApps | Select-Object -Property "_name", "version", "path"
You’ll probably notice that the list isn’t in any particular order aside from what “system_profiler” spit out about 2,000 words ago. Maybe the most recently installed items are last? Hard to tell. Let’s fix that by taking “$UserApps” to see the Sorting Hat. (Hufflepuff, by the way)
PS > $UserApps | Sort-Object "_name" | Select-Object -Property "_name", "version", "path"
“Sort-Object” as you may have already guessed, sorts a list or collection by their values of a specific property. By default, the order is ascending, but that can be reversed with the “-Descending” option. Multiple properties can be used for the sort order, and the number of results can also be limited to the “-Top” or “-Bottom” of the list. In this instance, the top 20.
PS > $UserApps | Sort-Object "_name","version" -Top 20 | Select-Object -Property "_name", "version", "path"
Now that things are getting interesting, let’s fire up Visual Studio Code and put some of this into a script.
#!/usr/local/bin/pwsh
$InstalledAppsJSON = system_profiler SPApplicationsDataType -json
$InstalledApps = ConvertFrom-Json "$InstalledAppsJSON"
$AppsList = $InstalledApps.SPApplicationsDataType
$UserApps = $AppsList | Where-Object {$_.path -like "/Users/*"}
$UserApps | Sort-Object "_name" | Select-Object -Property "_name", "version", "path"
Save this as something like “UserAppsReport.ps1” and run it, and you should be greeted by a nicely sorted list, with multiple columns.
Now, to export that in a usable format for collecting into a report. Customers like spreadsheets, which means Excel or Sheets. Both will happily open a CSV formatted text file, and it’s easy to write data to a text file. If you were so inclined, you could tediously create a script to output the text in a format that included the parameters you wanted for each app, one per line, separated by commas and wrapped in quotes. You’d also have to contend with edge cases like app names with special characters, or missing properties that would break your formatting. If only there were a simple command to export a CSV, like “Export-CSV”.
Oh, wait. There is.
Our sorted list can be exported by piping it to the “Export-Csv” command. Optional parameters will allow you to set the delimiting character to something other than comma, specify text encoding, whether or not to overwrite existing files, and so on. The only requirement is a path to write the CSV file to.
$SortedApps = $UserApps | Sort-Object "_name" | Select-Object -Property "_name", "version", "path"
$SortedApps | Export-Csv -Path "./UserAppsReport.csv"
If you’re deploying a script like this to remote devices, you’ll need to work out where to write your CSV to, ensure it’s writable by the user or agent running the script, and so on. Then, it will depend on your management platform of choice for your options to collect those reports. It could also be run remotely and spit the text back to your console for collecting. I leave that as an exercise for the reader.
However you collect a pile of CSV reports from each of your managed Macs, compiling them all into a single spreadsheet is another task that’s easily handled by “ForEach-Object”. This time, for each of the CSV files sitting in a folder named “reports”, we’ll append them to a single report file to be opened in your spreadsheet app of choice.
In bash, normally I would do something like
% cat reports/*.csv > FullReport.csv
The command “cat” being short for “concatenate” which, for historically vague reasons, is used to read the contents of a file. With the “>” redirect, those contents are concatenated together (Oh! I get it!) into the new file “FullReport.csv”. The PowerShell equivalent of “cat” is “Get-Content” which gets the contents of a text file.
If you recall from part 2 the way to get a list of files is “Get-ChildItem” followed by the path to the folder you want to list, files being the children of folders. It also allows the use of wildcards to select only the files, for instance, with names that have “.csv” extensions.
PS > Get-ChildItem ./reports/*.csv
Directory: PowerShell/reports
UnixMode User Group LastWriteTime Size Name
-------- ---- ----- ------------- ---- ----
-rw-r--r-- option8 staff 4/7/2023 11:56 2956 UserAppsReport1.csv
-rw-r--r-- option8 staff 4/7/2023 11:56 2956 UserAppsReport2.csv
-rw-r--r-- option8 staff 4/7/2023 11:56 2956 UserAppsReport3.csv
-rw-r--r-- option8 staff 4/7/2023 11:56 2956 UserAppsReport4.csv
-rw-r--r-- option8 staff 4/7/2023 11:56 2956 UserAppsReport5.csv
-rw-r--r-- option8 staff 4/7/2023 11:56 2956 UserAppsReport6.csv
-rw-r--r-- option8 staff 4/7/2023 11:56 2956 UserAppsReport7.csv
And, if you’ve been paying attention today, you’ll know where this is going.
PS > Get-ChildItem ./reports/*.csv | ForEach-Object {Get-Content $_}
If that worked, you probably saw several pages of formatted text go flying by on your screen. That’s a good sign, but we really need all of that to go into a file, not to the terminal. To dump the output to a file, you could use the “>” redirect, but I’d like to avoid polluting our verbose and semantic PowerShell script with obscure and terse bashisms. We’ll pipe that content to “Out-File” to output it to a file.
PS > Get-ChildItem ./reports/*.csv | ForEach-Object {Get-Content $_} | Out-File ./FullReport.csv
Voila! You can now open “FullReport.csv” in Excel (or Google Sheets, or Numbers, or any other application that can read a CSV), and see a collated spreadsheet of all the applications installed on your client machines that are in a user’s home directory instead of the default Applications folder locations. With a little tweaking, these same basic steps can be used not just to pull reports, but work with various sources of raw data, which you can collate, sort, slice, and dice to your heart’s content.
Or better yet, hand the whole shebang off to someone who enjoys working with spreadsheets. Because to transform this into something you can present to customers, you’ll need to learn to use pivot tables. And that’s outside the scope of this article.
4 Comments
