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:
When 2 Then 'Low'
When 6 Then 'Moderate'
When 8 Then 'Important'
When 10 Then 'Critical'
Else 'NA' End as 'Severity',
CIALL.CategoryInstanceName as 'Update Classification',
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.