Custom WSUS Reports

WSUS provides robust reporting capabilities to monitor update distribution status, client compliance, and activity. While built-in reports offer basic insights, custom reports allow deeper analysis tailored to your environment. This section explains how to configure WSUS for custom reporting using SQL queries, PowerShell, and automation, with specific guidance for client activity metrics.

---

Built-in Reports for Quick Insights

WSUS includes pre-configured reports accessible via the WSUS console or PowerShell. These reports provide high-level metrics like update distribution status and client compliance.

Example: Generate "Update Distribution Status" Report via PowerShell

# Connect to WSUS server
$wsus = Get-WsusServer -Name "WSUS_SERVER" -Port 8530

# Validate report identifier exists in WSUS version
$reportNames = $wsus.GetReports() | Select-Object -ExpandProperty Name
if ($reportNames -contains "UpdateDistributionStatus") {
    $report = $wsus.GetReport("UpdateDistributionStatus")
    $report.ExportToHtml("C:\Reports\DistributionStatus.html")
} else {
    Write-Warning "Report 'UpdateDistributionStatus' not found in this WSUS version. Check available reports: $reportNames"
}

This script exports the report to an HTML file for easy review, with validation for report identifiers.

---

Custom Reports Using SQL Queries

For granular analysis, query the WSUS SQL database directly. Key tables include Updates, Computers, and Status.

Example: SQL Query for Client Compliance (Expanded Statuses)

SELECT 
    c.Name AS ComputerName, 
    u.Title AS UpdateTitle, 
    s.Status AS Status
FROM 
    Computers c
JOIN 
    ComputerTargets ct ON c.Id = ct.ComputerId
JOIN 
    Updates u ON ct.UpdateId = u.Id
JOIN 
    Status s ON ct.StatusId = s.Id
WHERE 
    u.Title LIKE '%Critical%'
    AND (
        s.Status = 'Installed' 
        OR s.Status = 'Not Installed' 
        OR s.Status = 'Stalled'
    )

This query identifies computers with critical updates in various compliance states.

Example: SQL Query for Client Activity Metrics

SELECT 
    c.Name AS ComputerName, 
    COUNT(CASE WHEN s.Status = 'Failed' THEN 1 END) AS FailedUpdates, 
    COUNT(CASE WHEN s.Status = 'Downloaded' THEN 1 END) AS DownloadedUpdates
FROM 
    Computers c
JOIN 
    ComputerTargets ct ON c.Id = ct.ComputerId
JOIN 
    Updates u ON ct.UpdateId = u.Id
JOIN 
    Status s ON ct.StatusId = s.Id
WHERE 
    u.Title LIKE '%Critical%'
GROUP BY 
    c.Name
ORDER BY 
    DownloadedUpdates DESC

This query calculates failed and downloaded update counts per computer.

Steps to Create Custom Reports:
1. Access the SUSDB Database: Use SQL Server Management Studio (SSMS) to connect to the SUSDB database.
2. Write Queries: Use the above structure to filter data based on your needs (e.g., update types, time ranges, or groups).
3. Export Results: Use SELECT INTO OUTFILE or save to a table for later analysis.

Note: Verify table/column names match your WSUS version's schema (e.g., ComputerTargets may vary).

---

Automating Report Generation

Schedule reports using PowerShell and Task Scheduler for regular audits.

Example: PowerShell Script to Export Compliance Data

# Query SQL database and export to CSV
$connectionString = "Server=WSUS_SQL_SERVER;Database=SUSDB;User ID=sql_user;Password=sql_password;Trusted_Connection=False;"
$command = "SELECT c.Name, u.Title, s.Status FROM Computers c JOIN ComputerTargets ct ON c.Id = ct.ComputerId JOIN Updates u ON ct.UpdateId = u.Id JOIN Status s ON ct.StatusId = s.Id WHERE u.Title LIKE '%Critical%' AND (s.Status = 'Installed' OR s.Status = 'Not Installed' OR s.Status = 'Stalled')"
Invoke-Sqlcmd -Query $command -ConnectionString $connectionString | Export-Csv "C:\Reports\ComplianceReport.csv" -NoTypeInformation

Scheduling: Use Task Scheduler to run this script daily or weekly.

Example: PowerShell Script for Client Activity Metrics

# Export client activity metrics
$command = "SELECT c.Name, COUNT(CASE WHEN s.Status = 'Failed' THEN 1 END) AS FailedUpdates, COUNT(CASE WHEN s.Status = 'Downloaded' THEN 1 END) AS DownloadedUpdates FROM Computers c JOIN ComputerTargets ct ON c.Id = ct.ComputerId JOIN Updates u ON ct.UpdateId = u.Id JOIN Status s ON ct.StatusId = s.Id WHERE u.Title LIKE '%Critical%' GROUP BY c.Name"
Invoke-Sqlcmd -Query $command -ConnectionString $connectionString | Export-Csv "C:\Reports\ClientActivityReport.csv" -NoTypeInformation

---

Key takeaways

• Use built-in reports for quick status checks and PowerShell for automation.
• Custom SQL queries enable detailed analysis of compliance, distribution, and client activity metrics.
• Automate reporting with PowerShell and Task Scheduler to ensure consistent visibility.
• Always back up the WSUS database before running custom queries.
• Consider using SQL Server Reporting Services (SSRS) for advanced formatting and scheduling (optional advanced step).