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

PowerShell Help
1

Hello everyone,
I’m struggling to make this script work now with the new API. I’m not sure what is the API key now.

The powershell script returns what expected but not working for updates newer than 2017 because API key is not anymore used by Microsoft.

#
   # .EXAMPLE
        #------------
        #Group by KB
        #------------
        $rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2022-Jun' `
                    -APIKey '5b3115e7-b271-4c53-8d34-bcc17015e03e' `
                    -ResultType 'CVEByKB'
 
        $rslt | ogv
 
  #  .EXAMPLE
        #------------
        #Group by Product
        #------------
        $rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2022-Jun' `
                    -APIKey '5b3115e7-b271-4c53-8d34-bcc17015e03e' `
                    -ResultType 'CVEByProduct'
 
        $rslt | ogv
 
 #   .EXAMPLE
        #------------
        #Group by CVE
        #------------
        $rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2022-Jun' `
                    -APIKey '5b3115e7-b271-4c53-8d34-bcc17015e03e' `
                    -ResultType 'KBByCVE'
 
        $rslt | ogv
 
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
    }
}
$rslt = Get-SecurityUpdate `
                    -MonthOfInterest '2022-Jun' `
                    -APIKey '5b3115e7-b271-4c53-8d34-bcc17015e03e' `
                    -ResultType 'RAW'
 
$rslt | ogv```

Source of this script is here: https://sqljana.wordpress.com/2017/08/31/powershell-get-security-updates-list-from-microsoft-by-monthproductkbcve-with-api/ 

Does anyone use this kind of setup? For every new CVE I would like to get a list of all KBs of each OS.
2

Hi, welcome to the forum :wave:

According to this article, no API key is required.

As a quick test, I ran the code below and it pulled the data without any problems so there doesn’t seem to be a problem with not providing an API key, and according to the article linked above, if you do provide one, it’s ignored.

Install-Module MSRCSecurityUpdates -Force
Import-Module MsrcSecurityUpdates
$monthOfInterest = '2022-Jun'
Get-MsrcCvrfDocument -ID $monthOfInterest -Verbose | 
    Get-MsrcSecurityBulletinHtml -Verbose | 
        Out-File E:\Temp\Files\MSRCJuneUpdates.html

The source you link is five years old so if you have to use that specific script, I suggest contacting the author for support.

1 Like
3

Yes, this works but not showing the KBs only for each CVE in a CSV

4

Sure, but you stated in your original post that you thought it was a problem with the API key. That’s not the case.

The script is returning data, even when the API key is commented out. However, it appears to be failing to process all of the rows.
We don’t, as a general rule, troubleshoot or fix scripts that have been found online. If you are having problems with the script, you should contact the author for assistance.

1 Like
5

Since you don’t say what you’re actually trying to achieve (desired result/output), I just played around with the data and produced output as I would want it. Feel free to use this to build exactly what you’re looking for.


$homepath = "c:\temp"
$filename = "$homepath\MS_Monthly_CVE.csv"
$filename_raw = "$homepath\MS_Monthly_Raw.csv"

if(-not (Get-Module -Name MsrcSecurityUpdates -ListAvailable)){
    Install-Module -Name MsrcSecurityUpdates -Force -Scope CurrentUser
    Import-Module -Name MsrcSecurityUpdates -Verbose
}

# generates the correct date format for Get-MsrcCvrfDocument to pull the current patch
function getMonthOfInterest {
    $basedate = (get-date -day 12).Date
    $patchtues = $basedate.AddDays(2 - [int]$basedate.DayOfWeek)
    $patchtues = $patchtues.AddHours(20)

    if ( ((get-date).Date) -lt $patchtues) {
        $patchmonth = (get-date -format "yyyy-MMM")
        return $patchmonth
    }
    else {
        $patchmonth = $basedate.ToString("yyyy-MMM")
        return $patchmonth
    }
}

$monthOfInterest = getMonthOfInterest

Write-Verbose "Downloading $monthOfInterest rollup patch information from Microsoft" -Verbose

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

Write-Verbose "Sorting records with multiple KBArticle IDs to process seperately" -Verbose

$multiple,$single = $reportdata.where({@($_.kbarticle.id).count -gt 1},5)

Write-Verbose "Extracting property names" -Verbose

$properties = $multiple | Select-Object -First 1 | ForEach-Object {
    $_.psobject.properties.name -notmatch 'kbarticle'
}

$selectprop = @{name="KBArticle";e={$kb.ID}},
              @{name="KBUrl";e={$kb.Url}},
              @{name="KBSubType";e={$kb.SubType}} +
              $properties

Write-Verbose "Converting each record with multiple KBArticle IDs into distinct objects" -Verbose

$grouped = $multiple + $single | ForEach-Object{
    foreach($kb in $_.kbarticle){
        $_ | Select-Object $selectprop
    }
} | Group-Object -Property cve,kbarticle

Write-Verbose "Grouping records with common CVE and KBArticle ID" -Verbose

$newprop = 'KBUrl', 'KBSubType' + $properties -notmatch 'CVE'

Write-Verbose "Creating calculated properties" -Verbose

$calculatedprop = foreach($propname in $newprop){
    @{n=$propname;e={($_.group.$propname | Select-Object -Unique | Where-Object {$_}) -join '; '}.GetNewClosure()}
}

$combinedprop = @{n='CVE';e={$record.group[0].cve}},@{n='KBArticle';e={$record.group[0].KBArticle}} + $calculatedprop

Write-Verbose "Creating new custom objects" -Verbose

$output = foreach($record in $grouped){
    $record | Select-Object $combinedprop
}

Write-Verbose "Please review the data and press enter to finish and create reports" -Verbose

# view the data visually
$output | Out-GridView

$null = Read-Host

Write-Verbose "Exporting raw and processed CSV data" -Verbose

$output | Export-Csv $filename -NoTypeInformation -Force
$reportdata | Export-Csv $filename_raw -NoTypeInformation -Force
2 Likes
6

Thank you, looks ok. Still need a bit of tunning for my needs, but it helps a lot.