Powershell to pull data from .MBSA files (Microsoft Baseline Security Analyzer)

[tri_94]

Hi there

 

I've created reports from MBSA, and converted them to xml files.

 

I would like to collect all the data and create an excel spreadsheet

 

I can do this for some and have ran into a problem that not all the reports are the same think I've got a way around that part now but also I can't seem to get all the information from the reports.

Below is my PowerShell code and example xml which I can't seem to pull data from fields:

CmdExec role

Domain Controller Test

Folder Permissions

Guest Account

Password Policy

Registry Permissions

Service Accounts

SQL Server/MSDE Security Mode

SSIS Roles

Sysadmin role members

Sysadmins

Sysdtslog

 

also some of these fields are repeated so how's the best way to catch all of that data?

 

Thanks

 

 

$Excel = New-Object -comobject Excel.Application   
$Excel.visible = $True    
$Workbook = $Excel.Workbooks.Add()   
$Info = $Workbook.Worksheets.Item(1)   
 
# Create our column headers   
$Info.Cells.Item(1,1) = "Server name"  
$Info.Cells.Item(1,2) = "Local Account Password Test"
$Info.Cells.Item(1,3) = "Security Updates"
$Info.Cells.Item(1,4) = "Windows Version"
$Info.Cells.Item(1,5) = "File System"
$Info.Cells.Item(1,6) = "Password Expiration"
$Info.Cells.Item(1,7) = "Guest Account"
$Info.Cells.Item(1,8) = "Autologon"
$Info.Cells.Item(1,9) = "Restrict Anonymous"
$Info.Cells.Item(1,10) = "IE Zones"
$Info.Cells.Item(1,11) = "IE Enhanced Security Configuration for Administrators"
$Info.Cells.Item(1,12) = "IE Enhanced Security Configuration for Non-Administrators"
$Info.Cells.Item(1,13) = "Auditing"
$Info.Cells.Item(1,14) = "Shares"
$Info.Cells.Item(1,15) = "Administrators"
$Info.Cells.Item(1,16) = "Macro Security"
$Info.Cells.Item(1,17) = "Services"
$Info.Cells.Item(1,18) = "Windows Firewall"
$Info.Cells.Item(1,19) = "Automatic Updates"
$Info.Cells.Item(1,20) = "Incomplete Updates"
$Info.Cells.Item(1,21) = "SQL Server/MSDE Status"
$Info.Cells.Item(1,22) = "IIS Status"


# Add a little formatting   
$Style = $Info.UsedRange   
$Style.Interior.ColorIndex = 19   
$Style.Font.ColorIndex = 11   
$Style.Font.Bold = $True  
$Style.Columns.AutoFit()


 
$intRow = 2   
 

# grab our XML files  

$files = Get-ChildItem -path "D:\Test\Servers"
cd "D:\test\Servers"

#iterate over each .mbsa file   
foreach ($file in $files)   
{   
   [xml]$ScanResult = Get-Content $file  
   $Scanned = $ScanResult.SecScan.Check  | select Name, Advice   
   $Server = $ScanResult.SecScan.Machine   
   foreach($Scan in $Scanned)   
   {
   
   
   
   
   
   		$Object = New-Object PSObject
              $Object | add-member Noteproperty $Scanned.name[0] $Scanned.advice[0]
              $Object | add-member Noteproperty $Scanned.name[1] $Scanned.advice[1]
              $Object | add-member Noteproperty $Scanned.name[2] $Scanned.advice[2]
              $Object | add-member Noteproperty $Scanned.name[3] $Scanned.advice[3]
              $Object | add-member Noteproperty $Scanned.name[4] $Scanned.advice[4]
              $Object | add-member Noteproperty $Scanned.name[5] $Scanned.advice[5]
              $Object | add-member Noteproperty $Scanned.name[6] $Scanned.advice[6]
              $Object | add-member Noteproperty $Scanned.name[7] $Scanned.advice[7]
              $Object | add-member Noteproperty $Scanned.name[8] $Scanned.advice[8]
              $Object | add-member Noteproperty $Scanned.name[9] $Scanned.advice[9]
              $Object | add-member Noteproperty $Scanned.name[10] $Scanned.advice[10]
              $Object | add-member Noteproperty $Scanned.name[11] $Scanned.advice[11]
              $Object | add-member Noteproperty $Scanned.name[12] $Scanned.advice[12]
              $Object | add-member Noteproperty $Scanned.name[13] $Scanned.advice[13]
              $Object | add-member Noteproperty $Scanned.name[14] $Scanned.advice[14]
              $Object | add-member Noteproperty $Scanned.name[15] $Scanned.advice[15]
              $Object | add-member Noteproperty $Scanned.name[16] $Scanned.advice[16]
              $Object | add-member Noteproperty $Scanned.name[17] $Scanned.advice[17]
              $Object | add-member Noteproperty $Scanned.name[18] $Scanned.advice[18]
              $Object | add-member Noteproperty $Scanned.name[19] $Scanned.advice[19]
              $Object | add-member Noteproperty $Scanned.name[20] $Scanned.advice[20]
              $Object | add-member Noteproperty $Scanned.name[21] $Scanned.advice[21]
       
   
    
   
   
             
       $Style.Cells.Item($intRow, 1) = $Server  
          
       switch ($Scan.Name)    
       {   
  
           "Local Account Password Test"       {$Style.Cells.Item($intRow, 2) = $Object.'Local Account Password Test';break}
           "Security Updates"                  {$Style.Cells.Item($intRow, 3) = $Object.'Security Updates';break}
           "Windows Version"                   {$Style.Cells.Item($intRow, 4) = $Object.'Windows Version';break}
           "File System"                       {$Style.Cells.Item($intRow, 5) = $Object.'File System';break}
           "Password Expiration"               {$Style.Cells.Item($intRow, 6) = $Object.'Password Expiration';break}
           "Guest Account"                     {$Style.Cells.Item($intRow, 7) = $Object.'Guest Account';break}
           "Autologon"                         {$Style.Cells.Item($intRow, 8) = $Object.Autologon;break}
           "Restrict Anonymous"                {$Style.Cells.Item($intRow, 9) = $Object.'Restrict Anonymous';break}
           "IE Zones"                          {$Style.Cells.Item($intRow, 10) = $Object.'IE Zones';break}
           "IE Enhanced Security Configuration for Administrators"          {$Style.Cells.Item($intRow, 11) = $Object.'IE Enhanced Security Configuration for Administrators';break}
           "IE Enhanced Security Configuration for Non-Administrators"                         {$Style.Cells.Item($intRow, 12) = $Object.'IE Enhanced Security Configuration for Non-Administrators';break}
           "Auditing"                         {$Style.Cells.Item($intRow, 13) = $Object.Auditing;break}
           "Shares"                           {$Style.Cells.Item($intRow, 14) = $Object.Shares;break}
           "Administrators"                   {$Style.Cells.Item($intRow, 15) = $Object.Administrators;break}
           "Macro Security"                   {$Style.Cells.Item($intRow, 16) = $Object.'Macro Security';break}
           "Services"                         {$Style.Cells.Item($intRow, 17) = $Object.Services;break}
           "Windows Firewall"                 {$Style.Cells.Item($intRow, 18) = $Object.'Windows Firewall';break}
           "Automatic Updates"                {$Style.Cells.Item($intRow, 19) = $Object.'Automatic Updates';break}
           "Incomplete Updates"               {$Style.Cells.Item($intRow, 20) = $Object.'Incomplete Updates';break}
           "SQL Server/MSDE Status"           {$Style.Cells.Item($intRow, 21) = $Object.'SQL Server/MSDE Status';break}
           "IIS Status"                       {$Style.Cells.Item($intRow, 22) = $Object.'IIS Status';break}


         
           

       }   
 Remove-Variable object

   }   
   $intRow = $intRow + 1   
}  



# And save it away:
$Path = "D:\test\servers.xlsx"
$Workbook.SaveAs($Path,51)

$excel.Quit()
[system.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Start-Sleep 1
'Excel processes: {0}' -f @(Get-Process excel -ea 0).Count

invoke-item $path