Extract KB number into it's own column

PowerShell Help
1

Hello everyone!
Let me start with saying I am currently trying to learn more advanced PowerShell scripting and have been taking a few courses on MVA and reading many forums. Typically I try to leverage the built-in help system to find my answer by using Get-Member and such for properties / objects and their syntax. However, I am in need for this fairly simple script sooner than later for some auditing procedures I need to take care of. I had found most of this script below on one of the many forums I frequent, but cannot remember which. The script does have all of the information I am looking for, and believe that the objects it calls on has the property I am looking for. I just haven’t been able to figure out how to extract just the KB number ( KB123456789 ) itself and create it’s own column. I have slightly modified the original code and cleaned it up to make it easier for me to understand and posted it below. Again, I know it provides it to me in the Title section, but I need it to be on it’s own as well. Any guidance would be greatly appreciated!

$Session = New-Object -ComObject "Microsoft.Update.Session";
$Searcher = $Session.CreateUpdateSearcher();
$historyCount = $Searcher.GetTotalHistoryCount();
$Searcher.QueryHistory(0, $historyCount) | 
Select-Object Date,
@{name="Operation"; 
    expression={
        switch($_.operation){
            1 {"Installation"}; 
            2 {"Uninstallation"}; 
            3 {"Other"}
                             }
                }
  }, 
@{name="Status"; 
    expression={
        switch($_.resultcode){
            1 {"In Progress"}; 
            2 {"Succeeded"}; 
            3 {"Succeeded With Errors"}; 
            4 {"Failed"}; 
            5 {"Aborted"} 
                              }
                }
  }, 
Title, 
Description
2

There are a few ways to go about it, but I think the most straightforward way would be with a bit of regex:

$Searcher.QueryHistory(0, $historyCount) | 
    Select-Object Date, @{
        Name       = "Operation"
        Expression = {
            switch ($_.Operation) {
                1 {"Installation"}
                2 {"Uninstallation"}
                3 {"Other"}
            }
        }
    }, @{
        Name       = "Status"
        Expression = {
            switch ($_.resultcode) {
                1 {"In Progress"} 
                2 {"Succeeded"}
                3 {"Succeeded With Errors"}
                4 {"Failed"}
                5 {"Aborted"}
            }
        }
    }, @{
        Name       = "KB"
        Expression = {
            if ($_.Title -match '(kb\d+)') {
                $matches[0]
            }
            else {
                $null
            }
        }
    }, Title, Description

If you’re not familiar with how PS handles regex, there are a few ways it can be done. In this case, I’m using the regex -match operator to determine if anything in the title contains a KB number pattern (the letters KB, followed by one or more digits immediately after).

This will return either $true or $false. If it returns $false, I assign $null because there is no KB number to put for this property. If it’s $true, the automatic variable $matches will be populated with all matches that register for the last used regex pattern.

If you run into anything funky like multiple KB numbers being defined, you may want to replace $matches[0] with $matches in order to have PS put them all in the same property, as they will all be captured by the regex operation.

Minor note: PowerShell doesn’t require semicolons as line endings. They are optionally available to prematurely end a line (to put two code lines on a single line) but are not necessary for the end of every line.

I would recommend avoiding them for the most part. They’re mostly harmless like this, but I have seen one or two odd cases with them which seem to suggest they don’t quite behave properly in all cases when used in this manner.

3

Hello Joel!

Thank you for that quick reply yesterday. I had not replied as I had to get it into the rest of what I was working on and try it out. It works! Just the way I was looking for and I greatly appreciate your help. I had no idea about the Regex, nor how it works. I appreciate that you took the time to describe it as I am trying to actually learn the ins and outs of what can be done with PowerShell and would rather that than be told the answer. To clarify, a new line is enter unless its in an open bracket then?

I am sure this part is simple, but trying to think of a way to interject the PC name into it's own column as well? It is not in any of the items that I queried that I can tell. This is the last part that I am aware of that I actually need to put in here for the immediate need. However; I am also working on possibly having this use a txt file to query computers in that list and put them all into one database, or preferably putting each PC into its own sheet. I know this is complex and more of a "it would be nice to have" type of thing, but if you have any pointers or even a good reference point for that it would be great.

Thank you again!
Daniel

4

Hmm, well that depends. In the majority of cases, an Enter/Return (new line) will register as a new code line. The exceptions are basically pretty much just things you’d kind of expect to continue. If you look at a line and go “well, the ending character seems to indicate that more is to come”, then it’ll probably behave that way. This is more effective as a demonstration, I feel:

# for arrays, commas continue lines
$array = 1, 2, 3,
    4, 5, 6

# array subexpression operator does too, thanks to its use of parentheses; each new line is a new array element
$array2 = @(
    1
    2
    3
)

# Pipelines do this as well!
Get-ChildItem -Path C:\Users\ |
    Select-Object -Property FullName, LastWriteTime |
    Format-List

I prefer to indent ‘partial lines’ like this for clarity’s sake, but that’s a personal preference; I just find it makes it easier to follow.

You’ll often see people using the ` character to continue lines, as it effectively ‘escapes’ the line break, causing PS to treat it basically like a regular space character. Avoid this. You’ll generally only give yourself a headache tracing it back to these hard-to-spot little guys when you make a small apparently harmless edit and it breaks half of your code, because you have too few, or too many, or left one in the wrong place. The most common use case for this is in doing function parameters, and you’ll see long functions broken up like this:

# Don't do this:
Get-ADUser -Filter "*" `
    -Properties Name, SamAccountName, ProxyAddresses `
    -SearchBase "OU=Group1,DC=Domain,DC=COM" `
    -ErrorAction Stop

# Instead, use what's called 'splatting' (see: Get-Help about_Splatting for details)
$Parameters = @{
    Filter      = "*"
    Properties  = 'Name', 'SamAccountName', 'ProxyAddresses'
    SearchBase  = "OU=Group1,DC=Domain,DC=COM"
    ErrorAction = 'Stop'
}
# Note that instead of calling the variable with $, we've substituted @
Get-ADUser @Parameters

In the example in my previous post, the reason we’re able to continue the Select-Object statement for so many lines is because it accepts hashtable input for calculated properties to add to objects as we did. Hashtables have both single-line and multi-line forms:

$oneliner = @{Property1 = "Value1"; Property2 = "Value2"}

$Multiline = @{
    Property1 = "Value1"
    Property2 = "Value2"
}

Because we’re using the multiline form of the hashtable, everything between the braces as long as it’s valid syntax is “just part of the hashtable” – effectively, PS doesn’t care that the extra lines are there, because we’re defining the hashtable in a valid way.

It’s not the easiest thing to explain, but in a lot of cases… yeah. If something has a multiline constructor like a hashtable or the array subexpression operator @(), you can cram a bunch of lines into a single statement. I’ve found it pretty useful for keeping some denser scripts readable.

5

Good evening Joel,
Again, I want to thank you for your assistance and your explanations so that I learn for future. I really appreciate it and it is very helpful. The way you described it and the examples were very clear. I never knew what the tilde did and never even looked it up, but I can definitely see how that would and could be a nightmare in your code. Its amazing what one of the smallest characters can do in damage. SO much to learn and absolutely 0 time to do so :).

Again,
Thank you!

6

You’re welcome, of course. We’ve all got lots to learn, and I’m also always feeling like I’ll never learn everything there is to find – I guess in the end we’ll end up knowing what we need to know, at each point in time. :slight_smile: