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)
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
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
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:
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
- A single product can have multiple KB’s associated with it
- A single KB could be associated with multiple CVE’s
- A single raw row could have single or multiple KB’s
- A CVE could be associated with multiple products/KB’s
- For a single KB and product combination, “Severity, Impact, Restart required” could all be different. Eg: 3191828
- 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:
Security Updates – By Product
$rslt = Get-SecurityUpdate `
-MonthOfInterest '2017-Jun' `
-APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
-ResultType 'CVEByProduct'
$rslt | ogv
The output:
Security Updates – By KB
$rslt = Get-SecurityUpdate `
-MonthOfInterest '2017-Jun' `
-APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
-ResultType 'CVEByKB'
$rslt | ogv
The output:
Security Updates – By CVE
$rslt = Get-SecurityUpdate `
-MonthOfInterest '2017-Jun' `
-APIKey '16ce02bef3d9475699b9cc3f0bed1234' `
-ResultType 'KBByCVE'
$rslt | ogv
The output:
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.
SQL Jana 
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?
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)
Hi. Thanks for the script, works really well. I was wondering if there was a way we could return the date of release for the KB? The MonthOfInterest returns the month, however I need the actual release date for the KB.
Thank you
Hi IK. Thank you. Right now, where I am at, I am unable to review the output to see if a specific date is available. I will do so at a later time. Do you see the date in any of the output? If it is available, it should be easy enough to separate out. If there are no specific dates, one of these other functions might have some information but I am not sure:
https://github.com/Microsoft/MSRC-Microsoft-Security-Updates-API/tree/master/src/MsrcSecurityUpdates/Public
Tonsco Dabe has updates to the above code and has hosted it on GitHub. Thanks @Tonsco (Twitter)
GitHub:
https://github.com/meta-l/MSSecurityUpdates
This was working great until this month, now I this:
Unable to get security update data. Error in step: “[Get-SecurityUpdate]: Loop through raw data to make lists – CVE’s by product, KB’s by product, Products by KB’s etc]”
Index operation failed; the array index evaluated to null.
At D:\cron\PatchAdams\Get-SecurityUpdate.ps1:174 char:9
+ Throw “Unable to get security update data. Error in step: `”{ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (Unable to get s…luated to null.:String) [], RuntimeException
+ FullyQualifiedErrorId : Unable to get security update data. Error in step: “[Get-SecurityUpdate]: Loop through raw data to make lists – CVE’s by product, KB’s by product, Products b
y KB’s etc]”
Index operation failed; the array index evaluated to null.
Any ideas?