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.
We are almost done.