Any better alternative for WSUS report? [closed]

Question

I am using WSUS server on Server2012R2. The reporting function is really painful.

Sometimes I only need to have a simple report to see if a specific KB is installed or not on a list of computers from an OU. But WSUS report just wont gives you that simple answer.

Is there a better solution / software can generate the report for that?

Answer 1

From TechNet's Choose the Database Used for WSUS

The WSUS database stores the following types of information:

1. WSUS server configuration information

2. Metadata that describes each update

3. Information about client computers, updates, and client interaction with updates

Selecting a database

1. Windows Internal Database ships with WSUS. This version of SQL Server does not have a user interface or tools. Administrators are meant to interact with these products through WSUS.

2. Microsoft SQL Server is the full-featured database software from Microsoft. WSUS requires SQL Server. If you use the full version of SQL Server, the SQL Server administrator should enable the nested triggers option in SQL Server.

Do this before the WSUS administrator installs WSUS and specifies the database during the setup process. WSUS Setup enables the recursive triggers option, which is a database-specific option; however, it does not enable the nested triggers option, which is a server global option.

---

SUGGESTED SOLUTION (if you opt SQL Server as Database):

Once you've moved to SQL Server database for installing WSUS data, you can fire your own SQL queries on the SUSDB database which is created by WSUS.

There are several tables and views in WSUS which you can query to get the relevant details.

Sample query for getting the list of all Windows Updates installed in a particular system:

select tbct1.IPAddress, tbct1.FullDomainName, tbct1.LastReportedRebootTime,vu.DefaultTitle
 from SUSDB.dbo.tbUpdateStatusPerComputer AS tbuspc,
          SUSDB.dbo.tbUpdate AS tbu ,
           SUSDB.dbo.tbComputerTarget AS tbct1 ,
          SUSDB.PUBLIC_VIEWS.vUpdate AS vu ,
          SUSDB.PUBLIC_VIEWS.vUpdateAdditionalInfoUrl AS vuaiu
            where tbuspc.SummarizationState in (4) and ipAddress like '<system-ip>'
             and tbuspc.LocalUpdateID = tbu.LocalUpdateID 
            and tbuspc.TargetID = tbct1.TargetID
            and tbu.UpdateID = vu.UpdateId
            and  vu.UpdateId = vuaiu.UpdateId 
            and vuaiu.LocaleId='1033';
-- here SummarizationState 4 means installed.
-- you can search on the web for different possible SummarizationStates in WSUS.