This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(6.4, 24%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EV%3C/text%3E%3C/svg%3E)
All reports in MECM show the deployment status of updates, but they all start from deployment selection.
I would like to create a report, which lists all Server Systems that receive any deployment of updates, but that are reporting the status of "In Progress\Installing update(s)".
I tried to create a report, but I sank into the thousand views of the database without being able to make correlations that work for my target.
Does anyone have any suggestions or have they already created something that can help me?
Thank you all for your support and patience.
V_gs_updatecompliancestatusall ucsa and v_updateinfo ui with ucsa.scanstatus =2 should give you a list of all missing updates. Not exactly what you are looking for, but you can combine it with deployed updates status and that should give you a reasonably relevant report.
Your suggestions have helped me understand many things and I am trying some queries but I still have some difficulties, see subsequent comments. Thank you for your support and patience.
Hi, @Vid3al
Thank you for posting in Microsoft Q&A forum.
You may try the query like below:
SELECT
sy.Name0 AS 'Server Name',
sy.Operating_System_Name_and0 AS 'OS Version',
sn.StateName AS 'Update Status',
us.LastStatusChangeTime AS 'Last Status Update',
uds.AssignmentName AS 'Deployment Name',
us.LastEnforcementMessageID
FROM v_R_System sy
INNER JOIN v_Update_ComplianceStatus us ON sy.ResourceID = us.ResourceID
INNER JOIN v_StateNames sn ON us.LastEnforcementMessageID = sn.StateID
INNER JOIN v_UpdateDeploymentSummary uds on uds.CI_ID = us.CI_ID
WHERE sn.TopicType = 402
AND sy.Operating_System_Name_and0 LIKE '%Server%'
ORDER BY us.LastStatusChangeTime DESC
For the common views we can use for software update, we may refer to:
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".
After the way you tag questions on Q&A is updated, for any "Microsoft Configuration Manager" related problem, you can tag it with "Microsoft Intune", and then "Microsoft Configuration Manager" as the child tag.
Just checking if there's any progress or updates?
Thank you very much for your suggestion.
I started working with your example, but I noticed that your query also shows the Servers that actually installed the updates.
I checked, and your query shows correct information but maybe they confuse the real status, in fact in the Console in the monitoring node Deployment Status of the "SUG" is compliant, in fact the installation status (Status = "Installed") and ("Enforcement State" = "Failed to install update").
By checking directly above the server taken as an example, in fact the updates are installed.
This server was in error due to low system disk space, so we freed up space and manually started the installation of the updates from the software center, which were then successful, but the state described above does not change.
That is, the update is installed, and the information in the database is present, but it still keeps showing the last status when it was in error("Enforcement State" = "Failed to install update"), instead of showing example "success".
Strange that in the database it continues to exist ("Enforcement State" = "Failed to install update") even though the update in the database marked it as installed. Is this normal behavior?
I would like the query to show the updates requested but not installed, then with this query generate a report (report builder) with two columns, server name and compliance status. In the compliance column, you will need to show custom statuses with the example words "Compliant" or "Not Compliant".
Starting with a SQL query, I already have experience with how to create a report with a report builder, but I don't know if it is possible to create a column with a custom state in the result of my report without making any changes to the database. I want to highlight in the report a custom status based on the data in the database without making changes to the latter.
I have never created a report like this.
Thank you for your support and patience.