How to convert a MBSA scan report to Excel?

I was looking for a way to convert a mbsa scan to a .csv or excel format. In my search found Nelson's Araujo Blog with a vbs script close to what i was looking for. Nelsons's script uses a MBSA2LR.XSL rule file and I modified it to add some more info i.e Severity, as it is set to give only a few details.

I wanted more, i wanted all the info, then started to look for powershell and found scripts talking about powershell reading xml files and how it is very powerfull-shell ;-).

I realized I needed to learn about the xml structure and how it works, and saying that I opened a mbsa file with my favorite editor Notepad++ and saw all its data crumbled and making not much sense.

<SecScan ID="0" DisplayName="SRV" Machine="SKYNET-MGMT2" Date="2012-09-28 16:42:32" LDate="28/09/2012 4:42 PM" Domain="SKYNET" IP="172.16.253.53" Grade="8" HotfixDataVersion="2012-09-10T18:45:06Z" MbsaToolVersion="2.2.2170.0" IsWorkgroup="False" SUSServer="http://SKYNET-MGMT2:80" HFFlags="20" SecurityUpdatesScanDone="True" WUSSource="Microsoft Update (offline), Windows Server Update Services" IsCSAMode="false">
 <IPList><IP addr="-1408172747" /></IPList>
 <AdditCabs><Cab Prop="" /></AdditCabs>
 <Check ID="500" Grade="8" Type="5" Cat="1" Rank="1" Name="Developer Tools, Runtimes, and Redistributables Security Updates" URL1="Help/Check5311.html" URL2="Help/Check5311fix.html" GroupID="48ce8c86-6850-4f68-8e9d-7dc8535ced60" GroupName="Developer Tools, Runtimes, and Redistributables"><Advice>2 security updates are missing and not approved.</Advice><Detail><UpdateData ID="MS09-062" GUID="7eedad1b-142b-4c6d-bbb2-cc2361292c69" BulletinID="MS09-062" KBID="971117" Type="1" IsInstalled="false" Severity="4" RestartRequired="false" WUSApproved="false"><Title>Security Update for Report Viewer Redistributable 2005 Service Pack 1 (KB971117)</Title><References><BulletinURL>http://www.microsoft.com/technet/security/bulletin/MS09-062.mspx</BulletinURL><InformationURL>http://go.microsoft.com/fwlink/?LinkId=162540</InformationURL><DownloadURL>http://download.windowsupdate.com/msdownload/update/software/secu/2009/09/reportviewer_d15fed4710cf58d108c3ca1074ee7ebffe9b9159.exe</DownloadURL></References></UpdateData><UpdateData ID="MS09-062" GUID="66dda06f-3682-417b-92c2-3ce3d91de4f3" BulletinID="MS09-062" KBID="971118" Type="1" IsInstalled="false" Severity="4" RestartRequired="false" WUSApproved="false"><Title>Security Update for Report Viewer Redistributable 2008 (KB971118)</Title><References><BulletinURL>http://www.microsoft.com/technet/security/bulletin/MS09-062.mspx</BulletinURL><InformationURL>http://go.microsoft.com/fwlink/?LinkId=162541</InformationURL><DownloadURL>http://download.windowsupdate.com/msdownload/update/software/secu/2009/09/reportviewer_6f993e3c43c49ab40c5eb32dd8a6cc6d3580aba6.exe</DownloadURL></References></UpdateData></Detail></Check>

Okay so the mbsa it is actually a xml file .. uhmmm ..

This got me thinking and I started to test here and there.

Going to what you really care by reading this post here are my findings to convert a mbsa file to excel (applicable only in Excel 2010)

• Make a copy of your mbsa file and rename its extension to xml (i.e srv1.mbsa to srv1.xlm)
  One can use forfiles to do it for the folder where the .mbsa files are
       forfiles /m *.mbsa /c "cmd /c copy @file @fname.xml"
• Open Excel.
• Go to Data>From Other Sources>From XML Data Import.
• Browse to the xml file, i.e srv1.xml
• In the Import Data window select OK (or pick your choices)
• Voila!!!! it is there, all the MBSA data, formatted and ready to be sorted, vlooked, etc, etc

I was very happy with my finding, it saved me time.Hope you find it useful too.