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 - How To Manage Devices Community 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 [email protected] 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 [email protected]
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


in progress 0
Rafael Aguilar 1 month 3 Answers 45 views Beginner 0

Answers ( 3 )

  1. 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:


Leave an answer

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