Software Update Group Patch Status


Hi Team.

Currently he gave the client a patch status report of the month, where he shows me all the computers and their different patch status (Installed, Not required, Error, in progress, pending restart or unknown status).query di - HTMD Forum - Welcome to the world of Device Management! This is community build by Device Management Admins for Device Management Admins❤️ Ask your questions!! We are here to help you! - Software Update Group Patch Status

More than 100 software update group (SUG) including SSU are deployed.

I have a query that I use to query each SUG for the deployment ID, how do I integrate more than one deployment ID? It takes me a long time to query all SUGs.

There are many integrated reports but the customer wants with those features.

Could you guide me to improve that query?



SET @DEPLOYMENTID=N'{415972C3-61EE-40D4-BAC3-DAA61F66ACD9}’ — deployment id
SET @params = N’@DEPLOYMENTID nvarchar(38)’;

DECLARE @columnas varchar(max)
SET @columnas = ”

SELECT @columnas = coalesce(@columnas + ‘[KB’ + cast(ui.ArticleID as varchar(20))
+’-‘+ cast(ui.CI_ID as varchar(20))+’],’, ”)
FROM v_CIAssignment a
JOIN v_CIAssignmentToCI atc
JOIN CI_CIAssignments ass
ON ass.AssignmentID = atc.AssignmentID
JOIN v_ConfigurationItems cis
ON cis.CI_ID = atc.CI_ID
JOIN v_Collection coll
ON coll.CollID = ass.TargetCollectionID
ON atc.AssignmentID=a.AssignmentID
JOIN v_UpdateCIs ui
ON ui.CI_ID=atc.CI_ID
WHERE a.Assignment_UniqueID=@DEPLOYMENTID and a.AssignmentType in (1,5)
ORDER BY ui.ArticleID, ui.CI_ID
–select @columnas column1

set @columnas = left(@columnas,LEN(@columnas)-1)

–select @columnas column2

DECLARE @SQLString nvarchar(max);

set @SQLString = N’
SELECT aci.CI_ID, uc.CI_ID CI_IDuc, aci.AssignmentID, uc.ResourceID, uc.StateType, uc.StateID
, uc.LastEnforcementStatusMsgID
FROM v_UpdateState_Combined uc
JOIN v_CIAssignmentToCI aci
ON aci.CI_ID = uc.CI_ID


m.Name0 as Equipo
, ”KB”+ ui.ArticleID +”-”+ cast(ui.CI_ID as varchar(10)) UpdateID
, case when sn.StateName = ”Update is Installed” then ”Installed”
when sn.StateName = ”Pending System Restart” then ”Restart”
when sn.StateName = ”Successfully installed update” then ”Installed”
when sn.StateName = ”Update is Required” then ”in progress”
when sn.StateName = ”Update is not Required”then ”not Required”
when sn.StateName = ”Failed to download update”then ”error”
when sn.StateName = ”Failed to install update” then ”error”
when sn.StateName = ”General Failure” then ”error”
when sn.StateName = ”Detection State Unknown”then ”State Unknown”
else sn.StateName end as Status
JOIN v_ConfigurationItems cis
ON cis.CI_ID = uc.CI_IDuc
JOIN CI_CIAssignments ass
ON ass.AssignmentID = uc.AssignmentID
JOIN v_Collection coll
ON coll.CollID = ass.TargetCollectionID
JOIN v_UpdateCIs ui
ON ui.CI_ID=uc.CI_ID
JOIN v_CIAssignmentTargetedMachines ast
ON ast.ResourceID=uc.ResourceID and ast.AssignmentID=uc.AssignmentID
JOIN v_R_System m
ON m.ResourceID=uc.ResourceID and isnull(m.Obsolete0,0)=0
JOIN v_CIAssignment a
ON uc.AssignmentID=a.AssignmentID and a.Assignment_UniqueID=@DEPLOYMENTID
and a.AssignmentType in (1,5)
LEFT JOIN v_StateNames sn
ON sn.TopicType = uc.StateType and sn.StateID = uc.StateID
LEFT JOIN v_RA_System_SMSAssignedSites asite
ON m.ResourceID = asite.ResourceID
LEFT JOIN v_AdvertisementStatusInformation statusinfo
ON statusinfo.MessageID=nullif(uc.LastEnforcementStatusMsgID&0x0000FFFF, 0)
) AS SourceTable
FOR UpdateID IN (‘ + @columnas + ‘)
) AS PivotTable
Order by Equipo;


EXECUTE sp_executesql @SQLString, @params, @DEPLOYMENTID


Answers ( 3 )


    Mr Anoop.
    Could you help me or indicate some ideas to improve this?

    1- These are my SUGs.:

    2- For each SUG executed the deployment.

    3- After completing the deployment (after an end date) I have to deliver the patch status.
    4- In the patch state it tells me: What workstation or servers have pending restart or as I show in the image gave error by disk space, it is a specific state of the deployment.

    5- Report Final:



      What you are trying to achieve with this report?

      Can you be specific with the requirements that you want for each client compliance status for individual SUG?

      Best answer

Leave an answer

Sorry, you do not have permission to answer to this question .