Extracting MBSA XML Data using Powershell

Question

I'm trying to extract various information from Microsoft Security Baseline Analyser 2 results and helpfully found this script: http://fatbeards.blogspot.com/2009/01/powershell-mbsa-logs.html, however I also need a tally of CRITICAL updates, not just all updates as the script currently does.

Critical updates are denoted with Severity="4" as shown below, however I have not managed to get a count working from this so far. The column is created in Excel, but the field is not being populated. Any help would be much appreciated.

Below is a snippet of the raw XML data I am working with:

<SecScan ID="0" DisplayName="xxxx" Machine="xxxx" Date="2009-10-02 11:15:37" LDate="02/10/2009 11:15" Domain="DOMAIN" IP="192.168.34.24" Grade="2" HotfixDataVersion="" MbsaToolVersion="2.1.2104.0" IsWorkgroup="False" SUSServer="" HFFlags="20" SecurityUpdatesScanDone="True" WUSSource="Microsoft Update">

[ Text Truncated ]

<Check ID="500" Grade="5" Type="5" Cat="1" Rank="1" Name="SQL Server Security Updates" URL1="Help/Check5311.html" URL2="Help/Check5311fix.html" GroupID="0a4c6c73-8887-4d7f-9cbe-d08fa8fa9d1e" GroupName="SQL Server"><Advice>No security updates are missing.</Advice><Detail><UpdateData ID="MS06-061" GUID="07609d43-d518-4e77-856e-d1b316d1b8a8" BulletinID="MS06-061" KBID="925673" Type="1" IsInstalled="true" Severity="4" RestartRequired="false"><Title>MSXML 6.0 RTM Security Update  (925673)</Title><References><BulletinURL>http://www.microsoft.com/technet/security/bulletin/MS06-061.mspx&lt;/BulletinURL&gt;&lt;InformationURL&gt;http://support.microsoft.com/kb/925673&lt;/InformationURL&gt;&lt;DownloadURL&gt;http://www.download.windowsupdate.com/msdownload/update/v3-19990518/cabpool/msxml6-kb925673-enu-x86_571e99946aa6674ee6a70cf5801682ec323c7ae0.exe&lt;/DownloadURL&gt;&lt;/References&gt;&lt;OtherIDs&gt;&lt;OtherID Type="CVE">CVE-2006-4685</OtherID><OtherID Type="CVE">CVE-2006-4686</OtherID></OtherIDs></UpdateData>

[ Text Truncated ]

And this is the code thus far. Bold text are my additions.

$Path = "C:\mbsalog"
$files = get-Childitem $Path | where{$_.Extension -match "mbsa"}

# Get Excel ready
$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) = "SDK Components Security Updates"  
$Info.Cells.Item(1,3) = "SQL Server Security Updates"  
$Info.Cells.Item(1,4) = "Windows Security Updates"  
$Info.Cells.Item(1,5) = "BizTalk Server Security Updates"  
$Info.Cells.Item(1,6) = "Exchange Security Updates"  
$Info.Cells.Item(1,7) = "Office Security Updates"

$Info.Cells.Item(1,8) = "Critical Win Updates"

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

$intRow = 2

# iterate over each .mbsa file
foreach ($file in $files)
{
 [XML]$ScanResult = Get-Content $file
 $Scanned = $ScanResult.SecScan.Check | select Name, Advice

$CritUp = $ScanResult.SecScan.Check.UpdateData | select Severity

 $Server = $ScanResult.SecScan.Machine
  foreach($Scan in $Scanned)
 {
  # if Advice doesn't start with a numeric value then set it equal to 0
  if( $Scan.Advice -match '^(?<Cnt>[0-9]*)'){$Advice=$matches.cnt} else{$Advice=0}

if( $Scan.CritUp -match "4"){$CritUp=$matches.cnt} else{$CritUp=0}

 $Style.Cells.Item($intRow, 1) = $Server

  switch ($Scan.Name) 
  {
SDK Components Security Updates{$Style.Cells.Item($intRow, 2) = $Advice;break}
SQL Server Security Updates {$Style.Cells.Item($intRow, 3) = $Advice;break}
Windows Security Updates {$Style.Cells.Item($intRow, 4) = $Advice;break}
BizTalk Server Security Updates{$Style.Cells.Item($intRow, 5) = $Advice;break}
Exchange Security Updates{$Style.Cells.Item($intRow, 6) = $Advice;break}
Office Security Updates  {$Style.Cells.Item($intRow, 7) = $Advice;break}

Critical Win Updates {$Style.Cells.Item($intRow, 8) = $CritUp;break} }

 }
 $intRow = $intRow + 1

}

Thanks in advance.

Answer 1

Regarding this line:

$CritUp = $ScanResult.SecScan.Check.UpdateData | select Severity

Isn't the Update data under the Detail node i.e.:

$CritUp = $ScanResult.SecScan.Check.Detail.UpdateData | select Severity

This "if statement" in your script has a problem:

if( $Scan.CritUp -match "4"){$CritUp=$matches.cnt} else{$CritUp=0}

I'm not sure how you got from $CritUp to $Scan.CritUp. Anyway, since you are trying to count the number of severity 4 items I don't think the way you were doing it is the best approach. A little bit of XPath goes a long way in this scenario e.g.:

$XPathExpr = "/SecScan/Check/Detail/UpdateData[@Severity = '4']"
$CritUp = ($ScanResult.SelectNodes($XPathExpr)).Count