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)

SecurityUpdatesDesiredOutput
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

MSDeveloperAPIAccount
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:

RawSecurityUpdateData
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:

RawSecurityUpdateData
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:

SecurityUpdatesByProduct
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:

SecurityUpdatesByKB
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:

SecurityUpdatesByCVE
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.

Advertisements

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

  1. Great script. Two notes: 1) I had to uncomment the Import-Module line, even though I have PS 5. 2) You are presenting Severity and Impact as a single value, even though these may be different depending on the OS. Shouldn’t these also be joined values from the raw data?

    1. Alan, thank you for the feedback. I am not sure why Import-Module was necessary but I am glad it was left commented there!

      As for Severity/Impact/RestartRequired, as you rightly said, it should be a list and the lists would be different based on the grouping. I recognized that during the analysis and noted that in the code comments but did not expend the effort to do it as it would complicate the code (9 more HashTables would have been required) and distract it from its core functionality.

      This is the code comment I am referring to.
      #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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s