PowerShell and XML

After my last post, I thought it might be a good idea to spend a little more time explaining the XML capabilities of PowerShell. I had 2 people ask for clarification on it so I obviously didn't give it the attention it deserved! Following are a few examples of what can be done with the XML type in PowerShell.

The file that we will be using is from a Microsoft Baseline Security Analysis. To follow along with the examples, it would be beneficial if you had a similar file. You can use this sample. It looks something like this.

The first thing we need to do is explicitly cast it as an XML file:
[XML]$ScanResult = Get-Content 'c:\sampleMBSA.xml'

Now that we have the XML file, lets see what we can do with it.

1. Get the Server (Machine) name:
   PS> $File.SecScan.Machine
   PS> Test-VP01
   
2. Get the IP address:
   PS> $file.SecScan.IP
   PS> 12.345.67.890
   
3. What 3 areas were scanned:
   PS> $file.SecScan.Check Select Name
   Name
   Office Security Updates
   SDK Components Security Updates
   Windows Security Updates
   
4. See the complete list of updates (installed or not) for Office Security Updates:
   Note that we had 3 areas that were scanned, we are looking at the first one, so we start with [0] index.
   PS> $file.SecScan.Check[0].Detail.UpdateData Select Title
   Title
   Office 2003 Service Pack 2 for Proofing Tools
   Security Update for Office 2003 (KB920813)
   Security Update for Excel 2003 (KB925257)
   Security Update for Word 2003 (KB923094)
   Visio 2003 Service Pack 2
   Security Update for Office 2003 (KB924424)
   ....
   
5. See the complete list of updates that need installed for Office Security Updates:
   PS> $file.SecScan.Check[0].Detail.UpdateData `
   > Where{$_.IsInstalled -eq $false} `
   > Select Title
   Office 2003 Service Pack 2
   Security Update for Office 2003 (KB954478)
   Security Update for Office 2003 (KB923272)
   Security Update for PowerPoint 2003 (KB9230
   Security Update for Microsoft Office Excel
   Security Update for Office 2003 (KB936048)
   Security Update for Excel 2003 (KB940602)
   Outlook Live 2003 Service Pack 2
   Project 2003 Service Pack 2
   Security Update for Office 2003 (KB924424)
   ...
   
6. See the list of critical updates that need to be installed for Office Security Updates:
   PS> $file.SecScan.Check[0].Detail.UpdateData `
   > where{$_.IsInstalled -eq $false -and $_.Severity -eq 4} `
   > select Title, @{name="Severity";Expression={"Critical"}}
   Severity Title
   Critical Security Update for Access Snapshot Viewer 2003 (KB955439)
   Critical Security Update for Microsoft Office Outlook 2003 (KB945432)

Hopefully, this clarifies some of possibilities with XML and PowerShell.

Enjoy!

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