WSUS Client Report Script by — Rob Dunn

So, if you are looking for a report of some/specific or all WSUS Clients, then you might want to check out Rob Dunn‘s script ‘WSUS_Client_Report.vbs‘. 


This is really great. For instance, If I want to view a report of all WSUS Client computers in my Active Directory domain, then I can just enter any common letter such as “A” or “.” or “com” or “Domain-Name” in the input box and hit OK. Voila! You will see a report of all WSUS Client computers.


This is very sweet. Thanks Rob!


NOTES:



  1. You must be in the WSUS administrator’s group to see the report, and if you are running this against a remote SQL box, you must be an admin to get to the database. You will need to change the connection string if you only have access to the SUSDB instance on the SQL server.

  2. Edit strWSUSDBServer = “Your-WSUS-Server” / “Your-WSUS-Server:8530”

  3. Edit strWSUSServer = “Your-WSUS-Server” / “Your-WSUS-Server:8530”

‘ ——————-START SCRIPT——————


‘Input name of computer and link directly to that computers’ WSUS report.

‘You must be in the WSUS admins group on the server you are querying against
‘You must have access to the database to perform queries

‘An html page will be generated with a list of computers that matched the
‘ search criteria you specified.
Const ForWriting = 2


Dim strComputerID, t, strComputerIDArray


‘Your WSUS server.  If you are running remote SQL, enter your SQL server name
‘ here instead.
strWSUSDBServer = “sadadsms-mn”


‘Your WSUS server – i.e. where you run the administration from.  If you are
‘ not running remote SQL, then this variable should be the same as the variable
‘ above.
strWSUSServer = “sadadsms-mn:8530”


‘Set some font settings for our HTML page.
fface = “arial”
fcolor = “black”
fsize = “2”


Set myconn = CreateObject(“adodb.connection”)


strComputer = InputBox(“Enter the search criteria for the computer name you wish to ” _
 & “retreive statitistics for:”,”Enter computer name search text”)


‘If nothing was entered, then quit.
If strComputer = “” Then wscript.quit


‘set up the connection string.  This is using Windows NT authentication
‘ noted by Integrated Security=SSPI.
connection = “Provider=SQLOLEDB;” & _
        “Data Source=” & strWSUSDBServer & “;” & _
        “Initial Catalog=SUSDB;” & _
        “Integrated Security=SSPI”


‘Open the connection to the database.
myconn.open (connection)


‘Set some objects.
Set WshShell = WScript.CreateObject(“WScript.Shell”)
Set WshSysEnv = WshShell.Environment(“PROCESS”)
Set ws = wscript.CreateObject(“Scripting.FileSystemObject”)
Set result = CreateObject(“adodb.recordset”)


‘Define our temporary report index file.
tempfile = WshSysEnv(“TEMP”) & “\” & “WSUS_Computer_Report.htm”


‘Open the file for writing.
Set t = ws.OpenTextFile (tempfile, ForWriting, True)


‘Generate the HTML
Call setupHTM(“begin”)


‘Create a SQL string that would bring back the results where the fulldomainname field has any instance
‘ of the strcomputer text in it.
SQL = “SELECT computerID,FullDomainName from tbComputerTarget WHERE FullDomainName LIKE ‘%” & strComputer & “%'”


‘Run the query.
Call GetComputerInfo


‘Close the HTML
Call SetupHTM(“end”)


‘If no search results were found, produce a msgbox and exit.
If strComputerID = “” Then
 msgbox “No computers with %” & strComputer & “% in the computer name field does not appear in the WSUS database specified on ” _
  & “the server ” & strWSUSServer & “.”
 wscript.quit
Else
 ‘otherwise, open up the html page.
 Set oShell = CreateObject(“WScript.Shell”)
 oShell.Run tempfile,1,false


End If


Sub GetComputerInfo
  set result = myconn.execute(SQL)
   if not result.EOF then
  While not result.EOF
   ‘Put the field results for ComputerID into the strComputerID variable
   strComputerID = result(“ComputerID”)
   ‘Put the field results for FullDomainName into the strComputer variable
   strComputer = result(“FullDomainName”)
   ‘Write the hyperlink into a table row in the html file.
   t.writeline(“<tr><td><font face='” & fface & “‘ color='” _
    & fcolor & “‘ size='” & fsize & “‘><a href=’http://” & strWSUSServer _
    & “/WsusAdmin/Reporting/ComputerDetailsReport.aspx?computerId=” & strComputerID & “‘>” _
    & strComputer & “</a></font></td></tr>”)
   ‘Move to the next record.
   result.movenext()
   
  Wend
 Else 
 End If
End Sub


Function SetupHTM(strVar)
 ‘If this is the beginning of the file, set up the head, title, and table for the page.
 If LCase(strVar) = “begin” Then
  t.writeline (“<html><head><title> WSUS Search Results </title></head><body>”)
  t.writeline (“<font face='” & fface & “‘ color='” & fcolor & “‘><strong>” _
   & ” Click on a computername to view the WSUS client report.</strong></font><table>”)
 ElseIf LCase(strVar) = “end” Then
  ‘if this is the end of the file, close out the table and html code.  Then close the
  ‘ file entirely.
  t.writeline (“</table></html>”)
  t.close
 End If
End Function


‘ ——————–END SCRIPT——————-


MORE INFORMATION


Shortcut to particular WSUS client report
http://www.vbshf.com/vbshf/forum/forums/thread-view.asp?tid=206&posts=1&start=1

Leave a Reply

Your email address will not be published. Required fields are marked *