Databases
StackOverflow2013
Postsdbo
POExtracting MBSA XML Data using Powershell
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExtracting MBSA XML Data using Powershell
    text
    copied!<p>I'm trying to extract various information from Microsoft Security Baseline Analyser 2 results and helpfully found this script: <a href="http://fatbeards.blogspot.com/2009/01/powershell-mbsa-logs.html" rel="nofollow noreferrer">http://fatbeards.blogspot.com/2009/01/powershell-mbsa-logs.html</a>, however I also need a tally of CRITICAL updates, not just all updates as the script currently does.</p> <p>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.</p> <p>Below is a snippet of the raw XML data I am working with:</p> <pre><code>&lt;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"&gt; [ Text Truncated ] &lt;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"&gt;&lt;Advice&gt;No security updates are missing.&lt;/Advice&gt;&lt;Detail&gt;&lt;UpdateData ID="MS06-061" GUID="07609d43-d518-4e77-856e-d1b316d1b8a8" BulletinID="MS06-061" KBID="925673" Type="1" IsInstalled="true" Severity="4" RestartRequired="false"&gt;&lt;Title&gt;MSXML 6.0 RTM Security Update (925673)&lt;/Title&gt;&lt;References&gt;&lt;BulletinURL&gt;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"&gt;CVE-2006-4685&lt;/OtherID&gt;&lt;OtherID Type="CVE"&gt;CVE-2006-4686&lt;/OtherID&gt;&lt;/OtherIDs&gt;&lt;/UpdateData&gt; [ Text Truncated ] </code></pre> <p>And this is the code thus far. Bold text are my additions.</p> <pre><code>$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" </code></pre> <p><b>$Info.Cells.Item(1,8) = "Critical Win Updates"</b> </p> <pre><code># 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 </code></pre> <p><b> $CritUp = $ScanResult.SecScan.Check.UpdateData | select Severity</b></p> <pre><code> $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 '^(?&lt;Cnt&gt;[0-9]*)'){$Advice=$matches.cnt} else{$Advice=0} </code></pre> <p><b> if( $Scan.CritUp -match "4"){$CritUp=$matches.cnt} else{$CritUp=0}</b></p> <pre><code> $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} </code></pre> <p><b> Critical Win Updates {$Style.Cells.Item($intRow, 8) = $CritUp;break} </b> }</p> <pre><code> } $intRow = $intRow + 1 } </code></pre> <p>Thanks in advance.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload