My customer one day just asked me: “Can System Center Configuration Manager send me regularly a report with required patches for my environment?” My answer was for sure: “Yes, he can.”

When I have checked the default queries I have found more reports for Software Compliance, but for scheduling I had to choose from the drop down lists e.x. Collection, Vendor, Update Class to schedule a report.

That means I couldn’t automate such a report. So I created myself a report where I can show my customer what he wants.

First of all I had to find out where can I find the required data in the SQL database and then I had to make the correct SQL query to get the same result what the customer would like to see. Below you can see the custom SQL string:

SELECT
BulletinID,
ArticleID,
CASE((Severity))
When 2 Then 'Low'
When 6 Then 'Moderate'
When 8 Then 'Important'
When 10 Then 'Critical'
Else 'NA' End as 'Severity',
Title,
CIALL.CategoryInstanceName as 'Update Classification',
DatePosted
 FROM v_UpdateInfo as UI
  JOIN v_UpdateComplianceStatus as CS
  JOIN v_CICategoryInfo_All as CIALL
  JOIN v_CategoryInfo as CI
 ON CIALL.CategoryInstanceID = CI.CategoryInstanceID AND CIALL.CategoryTypeName = 'UpdateClassification'
 ON CS.CI_ID = CIALL.CI_ID
 ON UI.CI_ID = CS.CI_ID
 WHERE UI.ArticleID IS NOT NULL
  AND UI.IsSuperseded = 0
  AND UI.IsExpired = 0
  AND CS.Status = 2
  AND UI.IsDeployed = 1
GROUP BY BulletinID, ArticleID, Severity, DatePosted, Title, CIALL.CategoryInstanceName
ORDER BY BulletinID DESC

With these table joins you are able to show almost the same attributes as with a custom query in the software updates menu.

After this half victory I created a SQL-based report in the SCCM console:

My dataset query is the above created SQL query. The fields what will be shown in the report I had to manually:

We are almost done.

Now a subscription can be create for our newly made report:

The required patch report looks below will be send by email to the customer in every month: