PowerShell: Get Security Updates List From Microsoft By Month/Product/KB/CVE With API

I am a regular at my local PowerShell user group. I keenly look forward to our monthly meetings. We share a lot amongst ourselves. It is a small but close-knit group. Recently, one of the members, Julie came up with a coding challenge for the group. This blog post is about the challenge. My solution uses some neat HashTable tricks. Hopefully, it keeps the code concise yet clear and comprehensive. Please comment if you feel otherwise ;-). I used this trick in an earlier post too.

The HashTable trick

Basically, the trick is this: when you add an element to a HashTable using a new key, it adds the item. When you add an element with an existing key, it updates the existing item within it. Containment checks are not necessary, making the code simple!

The coding challenge:

Julie did a great job of describing the challenge and I will try to state what she sent in a Word document as best as I can.

Requirements

Create a .csv file similar to DesiredOutput.xlsx (screenshot below)

Desired output format (click for larger image)

Step 1

Get your own API Key, you will be requested to sign in with your own account

https://portal.msrc.microsoft.com/en-us/developer

Register for developer API access (click for larger image)

Step 2

Copy your API key into the code challenge file (replacing the XXXX with your newly generated API key [Line 40] of sample code that was provided)

Step 3

Send the code back to me when you are finished.  I will gather all the scripts and evaluate them before the next meeting on Sept 7th

Reference

https://blogs.technet.microsoft.com/yurikasensei/2017/04/13/get-started-with-security-update-guide-new-portal-for-security-updates/

The challenge – simplified:

The challenge came down to converting column data in multiple rows to be a CSV list on the same column but on a single row by either product, and/or KB and/or CVE. Let me try to explain with examples.

Once you get your own API key, you should be able to use my function and the RAW output of the function looks like what is shown below:

Raw output returned by API (click for larger image)

As soon as you see the data and the desired output (at the top of this post), you realize that CVE and Products are grouped by KB. In the raw data, they are all in a separate row. To add to the complexity,, the raw output has multiple KB’s associated with it on each product line. The CVE’s can also be associated with multiple products.

The raw output returned from the API has to be massaged to created the desired output from the top of this post.

Understanding the challenges

The first problem to tackle is to get a good understanding of the raw output from the API and the relationship between the various data columns. Grouping and converting row data into a CSV list on a column is much simpler once this understanding is gained. I looked at the data and noted these items that I noticed with the data (also in the actual code as comments).

Facts about raw data in $reportData

1. A single product can have multiple KB’s associated with it
2. A single KB could be associated with multiple CVE’s
3. A single raw row could have single or multiple KB’s
4. A CVE could be associated with multiple products/KB’s
5. For a single KB and product combination, “Severity, Impact, Restart required” could all be different. Eg: 3191828
6. Each raw row has
   FullProductName – SingleValue
   KBArticle – Hashtable (EMPTY! in some cases)
   CVE – SingleValue
   Severity – SingleValue
   Impact – SingleValue
   RestartRequired – Array (count matches Superdedence) but all values will be the same
   Supercedence – Array (count matches RestartRequired) but each array value is distinct
   CvssScoreSet – HashTable

Given the above, depending on the what you want to look at the data by,
“Severity, Impact, RestartRequired” may be approximations (first or last occurrence)

The solution in theory

Once I understood the data well, I realized that the raw data had to be flattened out to expand collections (like KB) at the row level into their own row so that everything has a single value in each row. Then, the grouping is easy.

It made more sense to allow grouping not just by KB but by other columns like Product or CVE. The Group-Object works fine for most cases but since there will be duplicates after the data is grouped, it makes it easier to just do it with HashTables.

Before you use the code

Before you can use the code, please make sure you have the following setup

• Make sure you are running PowerShell 5 (or later, if you read it in the future)
• Register for and get the developer API key need for API access
• Ensure Install-Module will work with your setup (proxy/firewall etc)

The code:

As I noted, the code allows you get the security updates and group it by one of these for a given month/year

• KB
• Product
• CVE
• optionally allows RAW output as returned by API

Below is the code comments for the code (since WordPress has issues with PowerShell block comments) followed by the code itself. Please make sure you block comment the comments.

NOTE: You do need your own API key (parameter) to use the code. Key used below is not the actual one. Please see the screenshot at the top on how to get your own key.

#
    .SYNOPSIS
        Get details of products affected by a CVRF document

    .DESCRIPTION
       CVRF documents next products into several places, including:
       -Vulnerabilities
       -Threats
       -Remediations
       -Product Tree
       This function gathers the details for each product identified in a CVRF document.

    .PARAMETER
        MonthofInterest - In the format 2017-Jun
    .PARAMETER
        APIKey - The unique API key acquired from MS to do web calls
    .PARAMETER
        ResultType - What should the data be grouped by

    .EXAMPLE
        #------------
        #Get RAW output (no grouping)
        #------------
        $rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2017-Jun' `
                    -APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
                    -ResultType 'RAW'

        $rslt | ogv

    .EXAMPLE
        #------------
        #Group by KB
        #------------
        $rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2017-Jun' `
                    -APIKey '16ce02bef3d9475699b9cc3f0bed1235' `
                    -ResultType 'CVEByKB'

        $rslt | ogv

    .EXAMPLE
        #------------
        #Group by Product
        #------------
        $rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2017-Jun' `
                    -APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
                    -ResultType 'CVEByProduct'

        $rslt | ogv

    .EXAMPLE
        #------------
        #Group by CVE
        #------------
        $rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2017-Jun' `
                    -APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
                    -ResultType 'KBByCVE'

        $rslt | ogv

.NOTES
    Created for the PowerShell Code Challenge of PowerShell User Group - Charlotte

Version History
    v1.0  - Aug 04, 2017. Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]

.LINK
    sqljana.wordpress.com
    https://blogs.technet.microsoft.com/yurikasensei/2017/04/13/get-started-with-security-update-guide-new-portal-for-security-updates/
#

The actual code…(only code comments are above)

function Get-SecurityUpdate
{
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory=$true,
                Position=0,
                ValueFromPipelineByPropertyName=$true,
                HelpMessage="Enter Year-Month.  Example 2017-May")]
        [ValidateNotNullOrEmpty()]
        [string]
        $MonthOfInterest,

        [Parameter(Mandatory=$true,
                Position=1,
                ValueFromPipelineByPropertyName=$true,
                HelpMessage="Enter your APIKey. Eg: 16ce02bef3d9475699b9cc3f0bed1234")]
        [ValidateNotNullOrEmpty()]
        [string]
        $APIKey,

        [Parameter(Mandatory=$false,
                Position=2,
                ValueFromPipelineByPropertyName=$true,
                HelpMessage="Enter the result grouping nature. Eg: CVEByKB")]
		[ValidateSet('RAW', 'CVEByProduct', 'KBByProduct', 'ProductByKB', 'CVEByKB', 'KBByCVE')]
        [string]
		$ResultType = 'CVEByKB'

    )

    [string] $fn = $MyInvocation.MyCommand
    [string] $stepName = "Begin [$fn]" 

    try
    {    

        $stepName = "[$fn]: Check if MSRCSecurityUpdates exists and install if not"
        #---------------------------------------------------------------
        Write-Verbose $stepName

        if(-not {Get-Module MsrcSecurityUpdates})
        {
            Install-Module MSRCSecurityUpdates -Force
        }

        #Not necessary to import..just referencing a cmdlet in the module should automatically import the module
        #Import-Module MSRCSecurityUpdates -Force

        $stepName = "[$fn]: Set APIKey and download updates for month of interest"
        #---------------------------------------------------------------
        Write-Verbose $stepName

        Set-MSRCApiKey -ApiKey $APIKey -Verbose         

        $reportData = Get-MsrcCvrfDocument -ID  $MonthOfInterest | Get-MsrcCvrfAffectedSoftware 

        $stepName = "[$fn]: Loop through raw data to make lists - CVE's by product, KB's by product, Products by KB's etc"
        #---------------------------------------------------------------
        Write-Verbose $stepName

        #Facts about raw data in $reportData
        #
        # 1) A single product can have multiple KB's associated with it
        # 2) A single KB could be associated with multiple CVE's
        # 3) A single raw row could have single or multiple KB's
        # 4) A CVE could be associated with multiple products/KB's
        # 5) For a single KB and product combination, "Severity, Impact, Restart required" could all be different. Eg: 3191828
        # 6) Each raw row has
        #       FullProductName - SingleValue
        #       KBArticle       - Hashtable (EMPTY! in some cases)
        #       CVE             - SingleValue
        #       Severity        - SingleValue
        #       Impact          - SingleValue
        #       RestartRequired - Array (count matches Superdedence) but all values will be the same
        #       Supercedence    - Array (count matches RestartRequired) but each array value is distinct
        #       CvssScoreSet    - HashTable

        #Given the above,
        #  depending on the what you want to look at the data by,
        #  "Severity, Impact, RestartRequired" may be approximations (first or last occurance)

        #These hashtables will hold specific associations as key and value as csv
        [hashtable]$cveByProductHash = @{}
        [hashtable]$kbByProductHash = @{}
        [hashtable]$productByKBHash = @{}
        [hashtable]$cveByKBHash = @{}
        [hashtable]$kbByCVEHash = @{}
        [hashtable]$productByCVEHash = @{}

        #These hashtables will hold all data values as objects by the keys
        [hashtable]$cveByProductHashData = @{}
        [hashtable]$kbByProductHashData = @{}
        [hashtable]$productByKBHashData = @{}
        [hashtable]$cveByKBHashData = @{}
        [hashtable]$kbByCVEHashData = @{}
        [hashtable]$productByCVEHashData = @{}

        foreach($row in $reportData)
        {
            Write-verbose ('Processing row: ' + $row)

            #There is only one CVE per raw row
            $cveByProductHash[$row.FullProductName] += ($row.CVE + ';')

            #There are multiple KB's per raw row
            foreach($kb in $row.KBArticle)
            {
                $kbByProductHash[$row.FullProductName] += ($kb.ID + ';')
                $productByKBHash[$kb.ID] += ($row.FullProductName + ';')
                $cveByKBHash[$kb.ID] += ($row.CVE + ';')
                $kbByCVEHash[$row.CVE] += ($kb.ID + ';')
                $productByCVEHash[$row.CVE] += ($row.FullProductName + ';')

                #These are the ways in which data can be looked at!
                # (split, select unique and join back to eliminate duplicates)
                #

                #----- By Product --------
                $cveByProductHashData[$row.FullProductName] = [pscustomobject]@{
                                                'ProductName'= $row.FullProductName
                                                'KB' = ((($kbByProductHash[$row.FullProductName]).Split(';') | Select-Object -Unique) -Join ';')
                                                'CVE' = ((($cveByProductHash[$row.FullProductName]).Split(';') | Select-Object -Unique) -Join ';')
                                                'Severity'= $row.severity
                                                'Impact'= $row.impact
                                                }

                $kbByProductHashData[$row.FullProductName] = `
                                                $cveByProductHashData[$row.FullProductName]

                #----- By KB --------
                $productByKBHashData[$kb.ID] = [pscustomobject]@{
                                                'KB'= $kb.ID
                                                'ProductName' = ((($productByKBHash[$kb.ID]).Split(';') | Select-Object -Unique) -Join ';')
                                                'CVE' = ((($cveByKBHash[$kb.ID]).Split(';') | Select-Object -Unique) -Join ';')
                                                'Severity'= $row.severity
                                                'Impact'= $row.impact
                                                }

                $cveByKBHashData[$kb.ID] = `
                                                $productByKBHashData[$kb.ID]

                #----- By CVE --------
                $kbByCVEHashData[$row.CVE] = [pscustomobject]@{
                                                'CVE' = $row.CVE
                                                'KB' = ((($kbByCVEHash[$row.CVE]).Split(';') | Select-Object -Unique) -Join ';')
                                                'ProductName' = ((($productByCVEHash[$row.CVE]).Split(';') | Select-Object -Unique) -Join ';')
                                                'Severity'= $row.severity
                                                'Impact'= $row.impact
                                                }

                $productByCVEHashData[$row.CVE] =
                                                $kbByCVEHashData[$row.CVE]

            }
        }

        #Serve it up the way the caller wants!
        #
        switch ($ResultType)
        {
            'RAW'           {$reportData}
            'CVEByProduct'  {$cveByProductHashData.Values}
            'KBByProduct'   {$kbByProductHashData.Values}
            'ProductByKB'   {$productByKBHashData.Values}
            'CVEByKB'       {$cveByKBHashData.Values}
            'KBByCVE'       {$kbByCVEHashData.Values}
            'ProductByCVE'  {$productByCVEHashData.Values}
        }                

    }
    catch
    {
        [Exception]$ex = $_.Exception
        Throw "Unable to get security update data. Error in step: `"{0}]`" `n{1}" -f `
                        $stepName, $ex.Message
    }
    finally
    {
        #Return value if any
    }
}

Sample output

The usage examples for the code are in the comments section although I will include a few sample output screenshots

Security Updates – RAW output

$rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2017-Jun' `
                    -APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
                    -ResultType 'RAW'

$rslt | ogv

The output:

The raw API output (click for larger image)

Security Updates – By Product

$rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2017-Jun' `
                    -APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
                    -ResultType 'CVEByProduct'

$rslt | ogv

The output:

Grouped by ProductName (click for larger image)

Security Updates – By KB

$rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2017-Jun' `
                    -APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
                    -ResultType 'CVEByKB'

$rslt | ogv

The output:

Grouped by KB (click for larger image)

Security Updates – By CVE

$rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2017-Jun' `
                    -APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
                    -ResultType 'KBByCVE'

$rslt | ogv

The output:

Grouped by CVE (click for larger image)

Conclusion

The code above is not very efficient as it does an unique sort on every iteration of the inner most loop and that can be improved. However, I think it is concise and for the amount of data it deals with, it might be okay.

If you are a security admin, you could do a lot more with this data. There are so many automation possibilities using this data as the source!

Please post to the comments section if you have suggestions or comments.