Weekly quickTIP
Reports Aplenty
WSUS' reports sometimes aren't enough -- tap SQL Server Management Studio Express Edition to get what you need.
- By Greg Shields
- 05/05/2008
WSUS is an excellent tool for approving and deploying patches to clients throughout your network, but sometimes I find its reports lacking the information I need. WSUS' native reports can show me information about computers, updates and groups -- but what if I want combinations of data or specific filters that aren't available natively in the interface?
In those cases, the only way is to generate my own reports directly through WSUS' SQL database itself. For its database, WSUS 3.0 can use either SQL or, in smaller environments, it can link to a local copy of the Windows Internal Database. If you're using SQL as your database, you've probably also installed SQL Management Studio for generating queries. But if you're using the no-cost Windows Internal Database, those tools don't come natively.
One graphical tool that can help is the SQL Server 2005 Management Studio Express Edition, a free download from Microsoft's Web site. Install this tool locally to your WSUS server and you can generate SQL queries through its interface.
One problem with WSUS' database, however, is that it is not natively configured for use by external tools like SQL Server Management Studio. There are two ways you can eliminate or get around this restriction. The hard way is to install the full version of SQL Server Express 2005 and run both the Surface Area Configuration Utility and the SQL Server Configuration Utility to enable the TCP/IP protocol and remote access on the database. But that involves an added installation, and also removes a layer of security built into the database. It is possible to work around this limitation using the SQL Server Management Studio alone if you connect to the database using Windows Authentication and the following connection string:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
If anything, give it a try just to see how much and what kinds of data your WSUS server is storing about the clients on your network. You'll be impressed with the sheer quantity of data that WSUS keeps to itself.
About the Author
Greg Shields is Author Evangelist with PluralSight, and is a globally-recognized expert on systems management, virtualization, and cloud technologies. A multiple-year recipient of the Microsoft MVP, VMware vExpert, and Citrix CTP awards, Greg is a contributing editor for Redmond Magazine and Virtualization Review Magazine, and is a frequent speaker at IT conferences worldwide. Reach him on Twitter at @concentratedgreg.