Hacking WSUS #3 of 5 -- Creating a MS Number to Knowledgebase Article Number Comparison Report
Ever felt the pain of comparing the patch number (the "MS0X-0XX Number") to the knowledgebase article number (the "Q Number") when you're trying to figure out which patches to push? WSUS lists patches by their knowledgebase article number. But, the monthly patch announcements you get from Microsoft typically list them by their patch number. Translating between these two numbers can be a pain in the neck.
What's truly interesting (thank you Microsoft!) about the WSUS database is that its completely open and available, no matter if you use MSDE or SQL as your database engine. This means that you can peer into the database to find out more information about the workstations on your network and their patch level. The database is highly normalized, which means that it can be a chore to link the primary and foreign keys together to get a useful report. But, perseverence will prevail if you know what you're looking for.
This, the third script in our series, leverages this ability to peer into the WSUS database to create a comparison report which shows the MS0X-0XX number for each patch and the associated knowledgebase article number for them. Its great for keeping these straight in your head. Note that I haven't tested this script against WSUS 3.0.
You'll need to change the first line to the name of your WSUS server. If you're using MSDE as your database for WSUS, you'll need to open up permissions so you can access it remotely.
Click the link below for the code:
strWSUSServer = "myWSUSServerName"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objTextFile = fso.OpenTextFile("OUTPUT.csv", 2, True)
objTextFile.WriteLine("MS Number,Q Number")
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
dbconn = "Driver={SQL Server};Server=" & strWSUSServer & ";Database=SUSDB"
conn.open dbconn
strSQLQuery = "SELECT dbo.tbSecurityBulletinForRevision.SecurityBulletinID, dbo.tbLocalizedProperty.Title FROM dbo.tbLocalizedPropertyForRevision INNER JOIN dbo.tbLocalizedProperty ON dbo.tbLocalizedPropertyForRevision.LocalizedPropertyID = dbo.tbLocalizedProperty.LocalizedPropertyID INNER JOIN dbo.tbSecurityBulletinForRevision ON dbo.tbLocalizedPropertyForRevision.RevisionID = dbo.tbSecurityBulletinForRevision.RevisionID WHERE (dbo.tbLocalizedPropertyForRevision.LanguageID = 1033) ORDER BY dbo.tbSecurityBulletinForRevision.SecurityBulletinID"
rs.Open strSQLQuery, conn, 3, 3
While Not rs.EOF
objTextFile.WriteLine(rs.Fields(0).Value & "," & Replace(rs.Fields(1).Value, ",", ""))
rs.MoveNext
Wend
WScript.Echo "Done!"