Saturday, January 10, 2009

PowerShell & MBSA logs

Was recently asked if I could iterate over a directory containing *.MBSA files from a scheduled run of the Microsoft Baseline Security Analyzer. These *.MBSA files are XML files that contain some basic machine information as well as the number of security and service packs that are required to get the machine to a secure state. The GUI shows the information as follows: The specifics of the request that I received were to grab the count of the following updates per machine (circled in the picture above):
  • SDK Components Security Updates
  • SQL Server Security Updates
  • Windows Security Updates
  • BizTalk Server Security Updates
  • Exchange Security Updates
  • Office Security Updates

What a great opportunity to use PowerShell's XML capabilities! The following script iterates over each file in the directory, parses the file and looks for the count. This information is dynamically entered into an Excel spreadsheet.

  1. # grab our XML files   
  2. $files = Get-ChildItem -path 'C:\SecurityScans'    
  3.   
  4. # Get Excel ready   
  5. $Excel = New-Object -comobject Excel.Application   
  6. $Excel.visible = $True    
  7. $Workbook = $Excel.Workbooks.Add()   
  8. $Info = $Workbook.Worksheets.Item(1)   
  9.   
  10. # Create our column headers   
  11. $Info.Cells.Item(1,1) = "Server name"  
  12. $Info.Cells.Item(1,2) = "SDK Components Security Updates"  
  13. $Info.Cells.Item(1,3) = "SQL Server Security Updates"  
  14. $Info.Cells.Item(1,4) = "Windows Security Updates"  
  15. $Info.Cells.Item(1,5) = "BizTalk Server Security Updates"  
  16. $Info.Cells.Item(1,6) = "Exchange Security Updates"  
  17. $Info.Cells.Item(1,7) = "Office Security Updates"  
  18.   
  19. # Add a little formatting   
  20. $Style = $Info.UsedRange   
  21. $Style.Interior.ColorIndex = 19   
  22. $Style.Font.ColorIndex = 11   
  23. $Style.Font.Bold = $True  
  24.   
  25. $intRow = 2   
  26.   
  27. # iterate over each .mbsa file   
  28. foreach ($file in $files)   
  29. {   
  30.     [XML]$ScanResult = Get-Content $file  
  31.     $Scanned = $ScanResult.SecScan.Check | select Name, Advice   
  32.     $Server = $ScanResult.SecScan.Machine   
  33.     foreach($Scan in $Scanned)   
  34.     {   
  35.         # if Advice doesn't start with a numeric value then set it equal to 0   
  36.         if$Scan.Advice -match '^(?<Cnt>[0-9]*)'){$Advice=$matches.cnt}    else{$Advice=0}   
  37.            
  38.         $Style.Cells.Item($intRow, 1) = $Server  
  39.            
  40.         switch ($Scan.Name)    
  41.         {   
  42.             "SDK Components Security Updates"   {$Style.Cells.Item($intRow, 2) = $Advice;break}   
  43.             "SQL Server Security Updates"       {$Style.Cells.Item($intRow, 3) = $Advice;break}   
  44.             "Windows Security Updates"          {$Style.Cells.Item($intRow, 4) = $Advice;break}   
  45.             "BizTalk Server Security Updates"   {$Style.Cells.Item($intRow, 5) = $Advice;break}   
  46.             "Exchange Security Updates"         {$Style.Cells.Item($intRow, 6) = $Advice;break}   
  47.             "Office Security Updates"           {$Style.Cells.Item($intRow, 7) = $Advice;break}   
  48.         }   
  49.   
  50.     }   
  51.     $intRow = $intRow + 1   
  52. }  

The result is a nicely formatted Excel spreadsheet that has the total number of updates.

This script hopefully demonstrates a how to use few different PowerShell features:

  • Creating a COM object
  • Using Regular Expressions
  • Using [XML] type

Enjoy

4 comments:

Mark said...

Thanks, this is a big help. I was wondering though how hard it would be to add an extra column that shows the number of critical updates?

I notice that Critical updates are all Severity="4" under UpdateData. Just can't quite seem to get the syntax right myself!

Wes Stahler said...

Thanks Mark. Here is a quick stab at getting the right info.
[xml]$a = Get-Content 'c:\temp\securityscans\OSUMC - CVM-MSFP-P01 (9-29-2009 1-56 PM).mbsa'

$a.SecScan.Check[20].Detail.UpdateData | where {$_.severity -eq 4 -and $_.IsInstalled -eq $false}

JPL said...

Hi Wes,

I´m trying to run the script but I keep getting the following error:

Get-Content : Access to the path 'D:\SecurityScans\Config' is denied.
At D:\SecurityScans\mbsa_excel.ps1:30 char:35
+ [XML]$ScanResult = Get-Content <<<< $file
+ CategoryInfo : PermissionDenied: (D:\SecurityScans\Config:String) [Get-Content], UnauthorizedAccessException
+ FullyQualifiedErrorId : GetContentReaderUnauthorizedAccessError,Microsoft.PowerShell.Commands.GetContentCommand

I´ve checked the folder security and even set to Everyone/Full Control but I can´t find a solution. The script starts to run and fill the Excel spreadsheet with server names but no security data is filled.

Any help is appreciated.

Thanks,

JP

Anonymous said...

@ JPL:
i had the same error. just change line 2 from
$files = Get-ChildItem -path 'C:\SecurityScans'
to
$files = Get-ChildItem -path 'C:\SecurityScans\*.mbsa'

Thats all!