RegEX to extract data from MBSA report

[cluM09]

Hello,

I need help with the regular expression to extract the data from strings generated by the MBSA as shown below. What I need is to be able to only extract for information in the following lines from the output as shown below:

| MS08-010 | Missing | Cumulative Security Update for Internet Explorer 7 for Windows Server 2003 (KB944533) | Critical |
| MS08-007 | Missing | Security Update for Windows Server 2003 (KB946026) | Important |
| MS07-067 | Missing | Security Update for Windows Server 2003 (KB944653) | Important |
| MS08-008 | Missing | Security Update for Windows Server 2003 (KB943055) | Moderate |
| MS08-001 | Missing | Security Update for Windows Server 2003 (KB941644) | Important |
| MS08-002 | Missing | Security Update for Windows Server 2003 (KB943485) | Important |
| MS07-061 | Missing | Security Update for Windows Server 2003 (KB943460) | Critical |
| MS07-068 | Missing | Security Update for Windows Server 2003 (KB941569) | Critical |
| MS07-064 | Missing | Security Update for Windows Server 2003 (KB941568) | Critical |

| 942763 | Missing | Update for Windows Server 2003 (KB942763) | |
| 110806 | Missing | Microsoft .NET Framework 2.0 Service Pack 1 (KB110806) | |
| 890830 | Missing | Windows Malicious Software Removal Tool - February 2008 (KB890830) | |

Output data from MBSA:

Computer name: DOMAIN\SERVER1
IP address: 172.16.204.71
Security report name: DOMAIN - SERVER1 (3-1-2008 8-34 PM)
Scan date: 3/1/2008 8:34 PM
Scanned with MBSA version: 2.0.6706.0
Security update catalog: Microsoft Update
Catalog synchronization date:
Security assessment: Severe Risk

Security Updates Scan Results

Issue: SDK Components Security Updates
Score: Check passed
Result: No security updates are missing.

Current Update Compliance

| MS07-028 | Installed | Security Update for CAPICOM (KB931906) | Critical |

Issue: SQL Server Security Updates
Score: Check passed
Result: No security updates are missing.

Current Update Compliance

| MS06-061 | Installed | MSXML 6.0 RTM Security Update (925673) | Critical |

Issue: Windows Security Updates
Score: Check failed (critical)
Result: 9 security updates are missing. 3 service packs or update rollups are missing.

Security Updates

| MS08-010 | Missing | Cumulative Security Update for Internet Explorer 7 for Windows Server 2003 (KB944533) | Critical |
| MS08-007 | Missing | Security Update for Windows Server 2003 (KB946026) | Important |
| MS07-067 | Missing | Security Update for Windows Server 2003 (KB944653) | Important |
| MS08-008 | Missing | Security Update for Windows Server 2003 (KB943055) | Moderate |
| MS08-001 | Missing | Security Update for Windows Server 2003 (KB941644) | Important |
| MS08-002 | Missing | Security Update for Windows Server 2003 (KB943485) | Important |
| MS07-061 | Missing | Security Update for Windows Server 2003 (KB943460) | Critical |
| MS07-068 | Missing | Security Update for Windows Server 2003 (KB941569) | Critical |
| MS07-064 | Missing | Security Update for Windows Server 2003 (KB941568) | Critical |

Update Rollups and Service Packs

| 942763 | Missing | Update for Windows Server 2003 (KB942763) | |
| 110806 | Missing | Microsoft .NET Framework 2.0 Service Pack 1 (KB110806) | |
| 890830 | Missing | Windows Malicious Software Removal Tool - February 2008 (KB890830) | |

Current Update Compliance

| MS07-058 | Installed | Security Update for Windows Server 2003 (KB933729) | Important |
| MS07-035 | Installed | Security Update for Windows Server 2003 (KB935839) | Critical |
| MS07-020 | Installed | Security Update for Windows Server 2003 (KB932168) | Moderate |
| MS07-047 | Installed | Security Update for Windows Server 2003 (KB936782) | Important |
| 914961 | Installed | Windows Server 2003 Service Pack 2 (32-bit x86) | |
| MS07-039 | Installed | Security Update for Windows Server 2003 (KB926122) | Important |
| MS07-043 | Installed | Security Update for Windows Server 2003 (KB921503) | Moderate |
| MS07-040 | Installed | Security Update for Microsoft .NET Framework, Version 2.0 (KB928365) | Critical |
| 940767 | Installed | Windows Internet Explorer 7 for Windows Server 2003 | |
| 933360 | Installed | Update for Windows Server 2003 (KB933360) | |
| 890830 | Installed | Windows Malicious Software Removal Tool - September 2007 (KB890830) | |
| MS07-042 | Installed | Security Update for Microsoft XML Core Services 6.0 and Microsoft XML Core Services 6.0 Service Pack 1

(KB933579) | Critical |
| MS06-078 | Installed | Security Update for Windows Media Player 6.4 (KB925398) | Critical |
| MS07-022 | Installed | Security Update for Windows Server 2003 (KB931784) | Important |
| MS07-056 | Installed | Security Update for Outlook Express for Windows Server 2003 (KB941202) | Critical |
| MS07-040 | Installed | Security Update for Microsoft .NET Framework, Version 1.1 Service Pack 1 (KB933854) | Critical |
| MS07-050 | Installed | Security Update for Internet Explorer 7 for Windows Server 2003 (KB938127) | Critical |
| MS07-012 | Installed | Security Update for Windows Server 2003 (KB924667) | Important |
| MS07-017 | Installed | Security Update for Windows Server 2003 (KB925902) | Critical |
| MS07-042 | Installed | Security Update for Windows Server 2003 (KB936021) | Moderate |
| MS07-057 | Installed | Cumulative Security Update for Internet Explorer 7 for Windows Server 2003 (KB939653) | Moderate |
| MS07-021 | Installed | Security Update for Windows Server 2003 (KB930178) | Critical |
| MS07-034 | Installed | Cumulative Security Update for Outlook Express for Windows Server 2003 (KB929123) | Low |
| MS07-031 | Installed | Security Update for Windows Server 2003 (KB935840) | Important |

It is possible to do extract the data I indicated above?

Thanks.

 

[PHV]

You may simply use the InStr function:
If InStr(yourBuffer, "| Missing |") > 0 Then
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EBGreen]

I just stuck the sample data into a text file and read it from there, but this might get you started down the right path:

Dim oRE : Set oRE = New Regexp

strFileContents = oFSO.OpenTextFile("C:\Temp\test.txt").REadAll()

oRE.Global = True
oRE.Pattern = "(.*\| Missing \|.*)"
WScript.Echo strFileContents

Set colMatches = oRE.Execute(strFileContents)
For Each oMatch In colMatches
WScript.Echo oMatch
Next


[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[cluM09]

EBGreen,

Thank you for the response. It works.

I also tried HPV's suggestion, but the output from it was not as good since the MBSA report contains stange characters. The output from using your regEx code shows a cleaner data output.

Now I need another help to exctract the string within the "|" symbol from output I got from your regEx code.

What would be the regEx pattern to extract the data betwwen the "|" character?

I tried to use the following For...Loop, but the information I got from it was not quite good since the output from the MBSA report contained some strange characters that prevents the standard looping routine from getting the proper data.

Set objList = CreateObject("Scripting.Dictionary")

For Each oMatch In colMatches
arrItems = Split(oMatch,"|")
For Each objItem in Split(oMatch,"|")
strRecord = strRecord & ";" & Trim(objItem)
Next
objList.Add objList.Count, strRecord
Next


For Each objItem in objList.Items
Wscript.Echo Chr(34) & objItem & Chr(34)
Next

The second For... Loop shows some blank space data even with the Trim function.

I think the regEX would be able to rid of the unwanted characters from the report.

Thanks again.

 

[EBGreen]

Look at the documentation of the Regular Expression object and the SubMatches collection of the Match object. I think something like this pattern will do what you want with submatches:

"| (.*) | (.*) | (.*) | (.*) |"

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[cluM09]

EBGreen,

I cannot get the submatches to work since I need to extract each item within the "|" characters.

I tried the following iteration, and it retruned the proper data:

For Each oMatch In colMatches
arrItems = Split(oMatch,"|")
strRecord = ""
For i = 0 To UBound(arrItems)
strItem = Trim(arrItems(i))
strRecord = strRecord & ";" & strItem
Next
'To remove the strange character in front of the string.
strRecord = Right(strRecord,Len(strRecord)-5)
'To remove the strange character at the end of the string.
strRecord = Left(strRecord,Len(strRecord)-2)
objList.Add objList.Count, strRecord
Next

For Each objItem in objList.Items
Wscript.Echo Chr(34) & objItem & Chr(34)
Next

I have to test the output string by using the Chr(34) to make sure that may record is clean.

Thank you for the help anyway.