Searching Microsoft security bulletins

I remember that we could tick an option to show only security bulletins that were not superseded by a new one on this page: http://technet.microsoft.com/en-us/security/bulletin/default.aspx

Now, there’s an option to ‘Show most recent updates only’, whatever that means. Wait, now Microsoft provides also an Excel file that contains all the information.

We can download, save as CSV file and import it in PowerShell as an object 🙂

            
#Requires -Version 3.0            
            
$xlsxfile = [system.IO.Path]::GetTempFileName()            
try {            
    Invoke-WebRequest -Uri 'http://go.microsoft.com/fwlink/?LinkID=245778' -ErrorAction Stop  -OutFile $xlsxfile            
} catch {            
    Write-Warning -Message "Failed to download file because $($_.Exception.Message)"            
    break            
}            
            
            
if ($xlsxfile -and (Get-Item $xlsxfile).Length -gt 0) {            
                
    $ExcelObj = New-Object -ComObject "Excel.Application"            
            
    # Open the downloaded xlsx file            
    $WBook = $ExcelObj.Workbooks.Open($xlsxfile,3)            
            
    # Load type            
    # $xlFileFormat = "Microsoft.Office.Interop.Excel.XlFileFormat" -as [type]             
    # $xlFileFormat::xlCSV -eq 6            
            
    # Create derived csv file name            
    $csvfile = Join-Path -Path (Split-Path (Get-Item -Path $xlsxfile)) -ChildPath ((Get-Item -Path $xlsxfile).BaseName +  ".csv")            
            
    # Save as CSV # http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat%28v=office.14%29.aspx            
    $WBook.SaveAs($csvfile,6)            
            
    # Close the workbook            
    $WBook.Close($false)            
            
    # Close Excel            
    $ExcelObj.Quit()            
                 
    # Force any remaining Excel process related to our COM object to stop            
     Get-WmiObject -Class Win32_Process | Where-Object {             
     $_.Commandline -match 'EXCEL.EXE" /automation -Embedding' } |             
     Select-Object -Property CommandLine,ProcessId | ForEach-Object -Process {             
         try {             
            Stop-Process -Id  $_.ProcessId -ErrorAction Stop            
         } catch {            
            Write-Warning -Message "Failed to stop Excel because $($_.Exception.Message)"            
         }            
     }            
}            
            
            
# There are duplicate words in the header, so we cannot use it as header as Import-CSV returns an error            
$header = 'Date Posted','Bulletin ID','Bulletin KB','Severity','Impact',            
'Title','Affected Product','Component KB','Affected Component','Impact2','Severity2','Supersedes','Reboot','CVEs'            
            
# By default, Get-content sends 1 line by 1 line through the pipeline # -ReadCount 1            
$content =  Get-Content  -Path $csvfile            
            
# The first two columns header name contains a carriage return in their cell name            
            
# To count the total number of lines we just do            
$totallines = ($content | Measure-Object).Count            
            
# Create a new file name            
$csvfile2 = Join-Path -Path (Split-Path (Get-Item -Path $csvfile)) -ChildPath ((Get-Item -Path $csvfile).BaseName +  "2.csv")            
            
# Output in this new CSV file all the lines except the first 3 lines that represent the header            
($content)[3..$totallines] | Out-File -FilePath $csvfile2            
            
# Get the text separator found in the file            
$separator = ( $content[1] -split '"')[1]            
            
# Ready to import            
$allBulletins = Import-Csv -Path $csvfile2 -Delimiter $separator -Header $header  # | Out-GridView             

Now, we’ve got every security bulletins Microsoft ever published stored as an array of objects in PowerShell

To demonstrate what can be done with these objects, I’ve got 2 examples:

First let’s count how many unique CVEs ever affected Windows 7 x64 SP1

$allW7SP1 = ($allBulletins | Where-Object {            
    $_.'Affected Product' -match "Windows 7 for x64-based Systems Service Pack 1"            
} )             
            
$allW7SP1CVEs = $allW7SP1  | ForEach-Object -Process {            
    if ($_.CVEs -match ',') {            
        $_.CVEs -split "," | ForEach-Object -Process {            
            $_.ToString()            
        }            
    } else {            
        $_.CVEs            
    }            
}             
$allW7SP1CVEs | Group-Object -NoElement | Sort-Object -Descending -Property Count            
            
'Total of unique CVEs: {0}' -f ($allW7SP1CVEs | Sort-Object -Unique).Count

My second example is about how to the list of security bulletins by KB number that apply to Windows 7 x64 SP1 and deal with the supersedence info available as a property of each object.

To be able to filter bulletins, we first need to create an array of unique KB numbers that were superseded, those that we’ll later on exclude from all the results. In other words, I’ll display only KB that do not match these superseded KB numbers.

# Select supersedence info and create an array of unique KB
$SupersededKBs  = @()
($allBulletins | ? {$_.'Affected Product' -match "Windows 7 for x64-based Systems Service Pack 1"} ) |  
Where-Object Supersedes | ForEach-Object -Process {
    if ($_.Supersedes -match ",") {
        $_.Supersedes -split "," | ForEach-Object -Process {
            $_.ToString() | % { ([regex]'(?<BulletinID>MS\d{2}\-\d{3})\[(?<KB>\d{6,8})\]').Matches($_).Groups[-1].Value}
        }
    } else {
        $_.Supersedes | % { ([regex]'(?<BulletinID>MS\d{2}\-\d{3})\[(?<KB>\d{6,8})\]').Matches($_).Groups[-1].Value}
    }
} | Sort-object -Unique | ForEach-Object -Process  {
    $SupersededKBs += $_
}

# Get bulletins that were not superseded
($allBulletins | ? {$_.'Affected Product' -match "Windows 7 for x64-based Systems Service Pack 1"} ) | % {
    if ($_.'Component KB' -notin $SupersededKBs) {
        $_
    }

} | Select-Object -Property @{n='PublishDate';e={Get-Date -Date $_.'Date Posted' -Format 'yyyy-MM-dd' }},
'Bulletin ID','Component KB','Affected Component',CVEs | Sort-Object -Property "PublishDate" -Descending |
Format-Table -AutoSize

There’s actually a third way to query MS security bulletins: by using the webservice associated the page http://technet.microsoft.com/en-us/security/bulletin/default.aspx

$MSBulletins = New-WebServiceProxy -Uri 'http://technet.microsoft.com/sto/services/BulletinSearch.asmx'

To discover methods associated with the web service, we can do:

$MSBulletins | Get-Member -MemberType Methods            
            
$MSBulletins.GetBulletins

Although, there’s a webpage to test the web service on: http://technet.microsoft.com/sto/services/BulletinSearch.asmx?op=GetBulletins
and there’s a XML description of it on : http://technet.microsoft.com/sto/services/BulletinSearch.asmx?WSDL, I cannot figure out what string should be passed to the ‘locale’ parameter.
I can see a web request with ‘en-us’ as locale (see image below), but it doesn’t work with the powershell web service object and when invoking its GetBulletins method.

We can list all products and their associated ID like this:

$MSBulletins.GetProducts(1)

Even making it fail, doesn’t help (me) much:

Any help would be appreciated.

Advertisements

6 thoughts on “Searching Microsoft security bulletins

  1. Hello Guys i tryed to use that Script but i only get two neraly empty Excel files, 3 Folders with the names Excel8.0, notesFF9334, VBE and 2 other files which i cant read. Can someone give me a hint ?. If could get the same Output like on the upper Pictures i would be so happy.

    Sorry for my shitti English and pls Keep calm i am new by using Powershell.

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